当前位置:Gxlcms > Python > python3pandas读取MySQL数据和插入

python3pandas读取MySQL数据和插入

时间:2021-07-01 10:21:17 帮助过:73人阅读

下面为大家分享一篇python3 pandas 读取MySQL数据和插入的实例,具有很好的参考价值,希望对大家有所帮助。一起过来看看吧

python 代码如下:


  1. # -*- coding:utf-8 -*-
  2. import pandas as pd
  3. import pymysql
  4. import sys
  5. from sqlalchemy import create_engine
  6. def read_mysql_and_insert():
  7. try:
  8. conn = pymysql.connect(host='localhost',user='user1',password='123456',db='test',charset='utf8')
  9. except pymysql.err.OperationalError as e:
  10. print('Error is '+str(e))
  11. sys.exit()
  12. try:
  13. engine = create_engine('mysql+pymysql://user1:123456@localhost:3306/test')
  14. except sqlalchemy.exc.OperationalError as e:
  15. print('Error is '+str(e))
  16. sys.exit()
  17. except sqlalchemy.exc.InternalError as e:
  18. print('Error is '+str(e))
  19. sys.exit()
  20. try:
  21. sql = 'select * from sum_case'
  22. df = pd.read_sql(sql, con=conn)
  23. except pymysql.err.ProgrammingError as e:
  24. print('Error is '+str(e))
  25. sys.exit()
  26. print(df.head())
  27. df.to_sql(name='sum_case_1',con=engine,if_exists='append',index=False)
  28. conn.close()
  29. print('ok')
  30. if __name__ == '__main__':
  31. df = read_mysql_and_insert()


另外需要注意的还有。

1) test数据库里有两个表,建表语句如下:


  1. CREATE TABLE `sum_case` (
  2. `type_id` tinyint(2) DEFAULT NULL,
  3. `type_name` varchar(5) DEFAULT NULL,
  4. KEY `b` (`type_name`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;



  1. CREATE TABLE `sum_case_1` (
  2. `type_id` tinyint(2) DEFAULT NULL,
  3. `type_name` varchar(5) DEFAULT NULL,
  4. KEY `b` (`type_name`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


插入初始数据


  1. insert into sum_case (type_id,type_name) values (1,'a'),(2,'b'),(3,'c')


2)创建user1用户


  1. grant select, update,insert on test.* to 'user1'@'localhost' identified by '123456'


相关推荐:

Python在groupby分组后提取指定位置记录方法



以上就是python3 pandas 读取MySQL数据和插入的详细内容,更多请关注Gxl网其它相关文章!

人气教程排行