今天在使用pandas的to_sql方法时,遇到一堆问题,各种百度谷歌,真正靠谱的答案少之又少,真不知道那些文章的作者有没有运行过他文章中的代码???特在此做一个梳理。
打个广告,想学JAVA爬虫的朋友点这里
为什么要使用to_sql方法
mysql数据库表结构
CREATE TABLE `my_balance` ( `id` int(11) NOT NULL AUTO_INCREMENT, `balance` decimal(20,4) NOT NULL COMMENT '余额', `account` varchar(30) DEFAULT NULL COMMENT '账户', `charges` decimal(10,4) NOT NULL COMMENT '交易手续费/每笔', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='账户表'
现在要向my_balance
表中插入一下数据,下面我们来看三段代码:
普通pymysql sql insert 写法(强烈不推介这种写法)
import pymysql.cursors # 建立数据库连接 db = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='tushare', charset='utf8') balance = 11500.0000 account = 8888 charges = 0.0005 # insert 语句 sql = f"insert into `tushare`.`my_balance` ( `balance`, `account`, `charges`) values ( '{balance}', '{account}', '{charges}')" # 执行新增或更新数据操作,返回受影响的行数 cursor = db.cursor() res_row = cursor.execute(sql) # 提交事务 db.commit() # 关闭链接 db.close()
- 优点:代码比较直观易懂,适合初学者读
- 缺点:每次一个
insert
语句都要复制一堆代码,代码复用性为0.
pandas拼接insert写法(这种写法有BUG)
import pandas as pd import pymysql.cursors def init_db(): # 建立数据库连接 db = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='tushare', charset='utf8') return db def insert_data_pandasType(table_name, valuses, db): ''' 向数据库中新增数据 table_name:库名 valuses:字段名称和对应数据,组成的数组 ''' pop = pd.Series(valuses) into = ','.join(pop.keys().values) val = ','.join(str(v) for v in pop.values) sql = f"insert into {table_name}({into}) values({val})" cursor = db.cursor() return cursor.execute(sql) # 执行SQL db = init_db() values = {'balance': 11400.0000, 'account': '8866.ZH', 'charges': 0.0005} insert_data_pandasType('my_balance', values, db) db.commit() db.close()
'account': '8866.ZH'
问题出在这里,错误如下:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ZH,0.0005)
所以只能存储没带标点符号的字符串~!
so,优缺点就不说了,这BUG太明显,压根不能用~!
to_sql写法
import pandas as pd from sqlalchemy import create_engine engine = create_engine( "mysql+pymysql://root:root@localhost:3306/tushare?charset=utf8") df = pd.DataFrame({'balance': [10000.000], 'account': ['13579.SH'], 'charges': [0.0005]}) df.to_sql(name='my_balance', con=engine, if_exists='append', index=False, index_label='id')
你没有看错,就这么几行就搞定了insert了,是不是非常爽~!但是有几点需要注意下:
错误信息:Execution failed on sql ‘SELECT name FROM sqlite_master WHERE type=’table’ AND name=?;’: not all arguments converted during string formatting
数据库链接不再使用pymysql
,而改用sqlalchemy
,con=engine
而不是con=db
官方文档
最最最坑的地方出现了,大家注意:
错误:No module named ‘MySQLdb’
百度到的代码,基本99%都是这么写的:
mysql+mysqldb://root:root@localhost:3306/tushare?charset=utf8
但是,如果按照如上写法,在python3.6(我的python版本)环境下会出现找不到mysqldb
模块错误!
正确的写法如下,因为python3将mysqldb
改为pymysql
了!!!
mysql+pymysql://root:root@localhost:3306/tushare?charset=utf8
if_exists 的参数说明
fail的意思如果表存在,啥也不做
replace的意思,如果表存在,删了表,再建立一个新表,把数据插入
append的意思,如果表存在,把数据插入,如果表不存在创建一个表!!
index=False, index_label=’id’
如果你数据库中主键名称为index
则不用设置这两项,如果不是,请按照主键名称设置!
附上一篇文章:to_sql与普通insert性能对比测试
关于to_sql事务问题
看到这里,有编程经验的朋友,一定会发现,使用
to_sql
的代码中,没有看到事务,在有多个insert
或者update
的情况时,如果回滚提交呢?从下面这篇文章中寻找答案吧~!
不能翻墙的小伙伴请看下面代码示例:
engine = create_engine( "mysql+pymysql://root:root@localhost:3306/tushare?charset=utf8") df = pd.DataFrame({'balance': [10000.000], 'account': ['13579.SH'], 'charges': [0.0005]}) df1 = pd.DataFrame({'balance': [1234.000], 'account': ['17886.SH'], 'charges': [0.0005]}) # 开始事物 with engine.begin() as conn: df.to_sql(name='my_balance', con=conn, if_exists='append', index=False, index_label='id') # 故意出现错误的代码,测试事物回滚 df1.to_sql(name='my_balance', con=conn, if_exists='append')
to_sql后获取自增主键
import pandas as pd df['ID'] = pd.read_sql_query('select MAX(ID)+1 from db_table',cnx).iloc[0,0] + range(len(df))
引擎为mssql+pyodbc,即sql server和pyodbc,to_sql之后没有任何报错,但数据库没有数