Python 操作MySQL
目录
UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
[案例]：使用python生成一段代码，读取excel文件A的sheet0页面，写入mysql， 再读取excel文件B，与当前mysql里的数据对比，把增量部分数据写入mysql。最后将 mysql里所有的数据，读取后写入excel文件A的sheet1页面。
首先，确保您已经安装了以下库：
pip install pandas openpyxl mysql-connector-python sqlalchemy
【代码】
import pandas as pd
from sqlalchemy import create_engine
import mysql.connector
# 配置数据库连接
db_config = {
'user': 'your_username',
'password': 'your_password',
'host': 'your_host',
'database': 'your_database'
}
# 创建SQLAlchemy引擎
engine = create_engine(f"mysql+mysqlconnector://{db_config['user']}:{db_config['password']}@{db_config['host']}/{db_config['database']}")
# 读取Excel文件A的sheet0页面
file_A = 'path_to_excel_file_A.xlsx'
df_A = pd.read_excel(file_A, sheet_name='Sheet0')
# 将数据写入MySQL
df_A.to_sql('table_A', con=engine, if_exists='replace', index=False)
# 读取Excel文件B
file_B = 'path_to_excel_file_B.xlsx'
df_B = pd.read_excel(file_B)
# 从MySQL读取现有数据
df_mysql = pd.read_sql('SELECT * FROM table_A', con=engine)
# 找出增量数据
df_increment = df_B[~df_B.apply(tuple,1).isin(df_mysql.apply(tuple,1))]
# 将增量数据插入MySQL
if not df_increment.empty:
df_increment.to_sql('table_A', con=engine, if_exists='append', index=False)
# 读取MySQL中所有数据
df_all = pd.read_sql('SELECT * FROM table_A', con=engine)
# 将所有数据写入Excel文件A的sheet1页面
with pd.ExcelWriter(file_A, engine='openpyxl', mode='a') as writer:
df_all.to_excel(writer, sheet_name='Sheet1', index=False)
print("操作完成")
pymysql的使用
起手式--Python连接MySQL的几种方式:
1.方式一:config+db+cur.execute (变量替换)
import pymysql
#开始连接数据库,使用pymysql,需要先import
config={
"host":"127.0.0.1",
"port":3306,
"user":"root",
"password":"123456",
"database":"market_data"#设置要连接的数据库名
}
db = pymysql.connect(**config)
sql1="insert into market_data.snap_fx_mid_price (datetime, currency_pair_cn, currency_pair_eg, fx_rate,bps) values (%s,%s,%s,%s,%s)"
col1=(dt,vrtName,vrtEName,price,bp)
cur=db.cursor() ##获取游标
cur.execute(sql1,col1)
db.commit()
#关闭游标和数据库的连接
cur.close()
db.close()
2.方式二: conn+execute(sql)
#重置gl00501表中id列的序号,使之连续
conn=py.connect('localhost',user='root',passwd='123456',db='ods_data')
cur=conn.cursor()
sql1='set @i=0;'
sql2='update ods_data.gl00501 set id=(@i:=@i+1);'
sql3='alter table ods_data.gl00501 auto_increment =0;'
cur.execute(sql1)
cur.execute(sql2)
cur.execute(sql3)
cur.close()
conn.close()
end_time=time.time()
3.方式三 pandas+config+db+read_sql
import pandas as pd
import pymysql as py
from sqlalchemy import create_engine
#开始连接数据库,使用pymysql,需要先import
config={
"host":"127.0.0.1",
"port":3306,
"user":"root",
"password":"123456",
"database":"temp_practice"#设置要连接的数据库名
}
db = py.connect(**config)
#导入表格
df_a=pd.read_sql('SELECT * FROM temp_practice.aaa', db)
df_b=pd.read_sql('SELECT * FROM temp_practice.bbb', db)
df=pd.read_sql('select * from temp_practice.aaa union SELECT id, name, phone, "" comments FROM temp_practice.bbb',db)
print(df_a)
print(df_b)
print(df)
#sqlalchemy登场
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/temp_practice')
#可以使用 echo=True 来显示加载数据库所执行的SQL语句
#将读好的df列表数据写入SQL对应表中
df=df.to_sql('aaaa',engine, if_exists='replace', index=False)
#写入文件要注意的,应该保持源表的列名与目标数据表的列名一致,否则不能插入.
#先在config里指定数据库名称, 然后在df.to_sql里直接指定表名即可.
#if_exists有三种选项, replace(替代), append(追加), fail (什么都不做)
cur=db.cursor()
cur.close()
db.close()
print("所有数据已经成功添加")
4.方式三 pandas+config+db+read_sql
#sqlalchemy登场
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/temp_practice')
#可以使用 echo=True 来显示加载数据库所执行的SQL语句
#将读好的df列表数据写入SQL对应表中
df=df.to_sql('aaaa',engine, if_exists='replace', index=False)
#写入文件要注意的,应该保持源表的列名与目标数据表的列名一致,否则不能插入.
#先在config里指定数据库名称, 然后在df.to_sql里直接指定表名即可.
#if_exists有三种选项, replace(替代), append(追加), fail (什么都不做)
cur=db.cursor()
cur.close()
db.close()
参考文档
https://www.cnblogs.com/guyouyin123/p/11396888.html
https://zhuanlan.zhihu.com/p/139763027
