2021_w_.1.python 驱动MySQLdb（create_engine）代码

该博客展示了如何使用Python的pandas和sqlalchemy库连接到MySQL数据库，执行SQL查询并写入数据。首先定义了连接数据库的函数，然后通过`pd.read_sql`读取SQL查询结果。此外，还提供了设置序号和更新数据框的功能，并将结果写回数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import pandas as pd
import numpy as np
import MySQLdb
from sqlalchemy import create_engine


# pip install mysqlclient
# df = pd.read_sql(db_to_df_sql_list[i],con=mysql_conn)
def cur_sql(sql_str,mysql_conn):
    mysql_conn=connect_mysql()
    cur =mysql_conn.cursor()
    cur.execute(sql_str)
# print("执行：%s"%(sql_str))
    mysql_conn.commit()
    return cur.fetchall()

def read_sql_str(sql_str,mysql_conn):
    df_read = pd.read_sql(sql_str,con=mysql_conn)
    return df_read

def connect_mysql():
#     db_conf_dict={
#                 'host':'localhost',  #	ip
#                 'port':3306 , #	端口
#                 'user':'root',  #	账户名
#                 'passwd':'123456' , #	密码
#                 'db':'099.'  ,#	库名
#                 'charset':'utf8' ## 	编码
#                 }
# mysql_conn = MySQLdb.connect(**db_conf_dict)
     # MySQL的用户：root, 密码:147369, 端口：3306,数据库：mydb
    engine = create_engine('mysql://root:123456@localhost:3306/###########?charset=utf8')
    return engine

def set_num(df_read,value_list):
    # tab_all=cur_sql(sql_str,mysql_conn)
    # tab_all
    # df_read=pd.read_excel(r"进账区间内汇总排序.xlsx",dtype=str)
    #时间排序
    df_read=df_read.sort_values(by='jysj',ascending=True)
    df_read=df_read.reset_index(drop=True)
    df_read['序号']=np.NaN
    count=0
    for i in df_read[df_read.jydfmc==value_list[1]]['序号'].index.to_list():
        df_read.loc[i,'序号']=count
        count+=1    
    df_read['序号'].fillna(method="ffill",inplace=True)
    return df_read

def write_data_in_db(df_read,engine):
    df_read.to_sql('texts',  con=engine, if_exists='replace', index=False) 
    print("写入数据库成功！！")
    
    
    
    
    
value_list=['a','b']

sql_str_1="SELECT * FROM gas_bank_records WHERE jymc='"+value_list[0]+"';"
sql_str_2='''
SELECT * FROM 
(
SELECT 
CXKH,
JYMC,
JDBZ,
JYDFMC,
jydfzkh,
序号,
SUM_JYJE,
MAX_JYSJ,
ROW_NUMBER() 
OVER(PARTITION BY CXKH,JYMC,JYDFMC,jydfzkh,JDBZ,序号 ORDER BY  SUM_JYJE DESC) RANK_K
FROM
(

SELECT 
CXKH,JYMC,JYDFMC,jydfzkh,JDBZ,序号,
MAX(JYSJ)  OVER(PARTITION BY CXKH,JYMC,JYDFMC,jydfzkh,JDBZ,序号 ORDER BY  jysj)  MAX_JYSJ,
SUM(JYJE)  OVER(PARTITION BY CXKH,JYMC,JYDFMC,jydfzkh,JDBZ,序号 ORDER BY  jysj)  SUM_JYJE
FROM texts WHERE 序号 >=0
order by jysj
)
a
order by 序号
)
b 
WHERE RANK_K=1
ORDER BY max_jysj
;
'''


engine=connect_mysql()
mysql_conn=engine.connect()
df_read = read_sql_str(sql_str_1,mysql_conn)
df_read=set_num(df_read,value_list)
write_data_in_db(df_read,engine)
#在连接一次数据库 否则连接丢失
mysql_conn=engine.connect()
df_read = pd.read_sql(sql_str_2,con=mysql_conn)
df_read.to_excel(value_list[0]+".xlsx",index=False)
print(value_list[0]+".xlsx")