sql读取数据直接存成pandas、字典

1. sql2Dataframe

导包

import pymysql
import pandas as pd

获取mysql链接

def get_db():
    #打开数据库连接
    db = pymysql.connect(
        host='*.*.*.*',
        port=3306,
        user = "wws",
        passwd = "yourpasswd",
        db = "youdb")
    return db
db = get_db()

写sql 读数据保存

sql = "select `name`,`name_en`,`collage_name`,`description` from `yourdb`;"
#cursor= db.cursor()
sql_data = pd.read_sql_query(sql,db)
df = pd.DataFrame(sql_data, columns=[ 'name','name_en','collage_name','description'])
df

成了！
or

sql = "select `name`,`name_en`,`collage_name`,`description` from `yourdb`;"
cursor= db.cursor()
cursor.execute(sql)
sql_data = cursor.fetchall()
df = pd.DataFrame(sql_data, columns=[ 'name','name_en','collage_name','description'])
df

2. sql2dict

import pymysql
 
def get_sql_conn():
    """
    获取数据库连接
    """
    conn= pymysql.connect(host="*.*.*.*",user="username",password="password",db="dbname")
    cursor = conn.cursor()
    return conn,cursor
    
def get_index_dict(cursor):
    """
    获取数据库对应表中的字段名
    """
    index_dict=dict()
    index=0
    for desc in cursor.description:
        index_dict[desc[0]]=index
        index=index+1
    return index_dict
    
def get_dict_data_sql(cursor,sql):
    """
    运行sql语句，获取结果，并根据表中字段名，转化成dict格式（默认是tuple格式）
    """
    cursor.execute(sql)
    data=cursor.fetchall()
    index_dict=get_index_dict(cursor)
    res=[]
    for datai in data:
        resi=dict()
        for indexi in index_dict:
            resi[indexi]=datai[index_dict[indexi]]
        res.append(resi)
    return res
 
def main():
    con,cursor = get_sql_conn()
    sql = "SELECT * FROM user WHERE 1=1"
	print(get_dict_data_sql(cursor, sql))
 
if __name__ == '__main__':
    main()