pandas操作mysql数据库

代码

#%% pandas操作sql数据库
from sqlalchemy import create_engine,exc   #用来捕捉sqlalchemy的异常
import pandas as pd

#建立连接
conn = create_engine("mysql+pymysql://root:root@localhost/niuke",
                     max_overflow=10, pool_size=2, pool_recycle=21600)
#读取本地数据，形成dataframe
ceshi_df = pd.read_excel('./自己测试/pandas操作数据库.xlsx')

#1、增  to_sql
ceshi_df.to_sql('zuhe',conn,index=False,if_exists='append')
'''
参数说明：
zuhe ：要导入的数据的表名
conn：数据库的连接
if_exists 
参数，如果表存在
	fail: Raise a ValueError.    报错
	replace: Drop the table before inserting new values.  #删除然后再新增
	append: Insert new values to the existing table.    #追加到已存在的表
#%% pandas操作sql数据库
from sqlalchemy import create_engine,exc   #用来捕捉sqlalchemy的异常
import pandas as pd

#建立连接
conn = create_engine("mysql+pymysql://root:root@localhost/niuke",
                     max_overflow=10, pool_size=2, pool_recycle=21600)


#1、增  to_sql
ceshi_df.to_sql('zuhe',conn,index=False,if_exists='append')
'''
参数说明：
zuhe ：要导入的数据的表名
conn：数据库的连接
if_exists 
参数，如果表存在
	fail: Raise a ValueError.    报错
	replace: Drop the table before inserting new values.  #删除然后再新增
	append: Insert new values to the existing table.    #追加到已存在的表
	ceshi_df.to_sql('zuhe',conn,index=False,if_exists='append')
'''
#2、删除1
#构建单条删除语句，并传递参数
del_sql = '''delete from zuhe where ciyu_x = %(ciyu)s and DEH_MID = %(deh)s'''
deh = 'J000162'
ciyu = 2
#成功执行删除操作后，无论数据是否确实删除都会出现，ResourceClosedError错误。属于第三方包的异常
#查阅资料后，可以导入相应的包，可以进行捕捉
try :
    pd.read_sql(del_sql,conn,params={'ciyu':ciyu ,'deh' :deh})
except exc.ResourceClosedError:
    print('ResourceClosedError产生异常，删除操作可能已经成功。')
'''
参考下面的网址
https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries
>> cur.execute("""
...     INSERT INTO some_table (an_int, a_date, another_date, a_string)
...     VALUES (%(int)s, %(date)s, %(date)s, %(str)s);
...     """,
...     {'int': 10, 'str': "O'Reilly", 'date': datetime.date(2005, 11, 18)})
'''
#2、删除2
# #批量删除，根据DEH_MID号  不成功
# ceshi_drop=ceshi_df.head(5)  #截取一部分数据进行删除
# del_list_sql='delete from zuhe where  DEH_MID = ANY(%(deh_list)s)'
# deh_list=ceshi_drop['DEH_MID'].tolist()

# #deh_list=str(ceshi_drop['DEH_MID'].tolist()).replace('[','(').replace(']',')')

# try :
#     pd.read_sql(del_list_sql,conn,params={'deh_list':deh_list})
# except exc.ResourceClosedError:
#     print('ResourceClosedError产生异常，删除操作可能已经成功。')
#3、改  略

#4、查
#直接读取表
ceshi_df = pd.read_sql_table('zuhe',conn)
#利用查询读取数据。
ceshi_df = pd.read_sql('select * from zuhe',conn)
#类似
ceshi_df = pd.read_sql_query('select * from zuhe',conn)

思考

pandas与数据库的结合，主要是因为pandas能够快速读取和写入数据到数据库中，而删除和修改的操作与pandas不太适合，应该选择生成一个pymysql光标对象connection.cursor()，然后由光标对象执行sql语句更方便一些。
pymsql操作数据库教程
或者用SQLAlchemy模块进行操作
SQLAlchemy教程

他山之石

pandas read_sql与read_sql_table、read_sql_query 的区别
Psycopg 2.8.7.dev0文档