时间:2021-07-01 10:21:17 帮助过:73人阅读
python 代码如下:
- # -*- coding:utf-8 -*-
- import pandas as pd
- import pymysql
- import sys
- from sqlalchemy import create_engine
- def read_mysql_and_insert():
- try:
- conn = pymysql.connect(host='localhost',user='user1',password='123456',db='test',charset='utf8')
- except pymysql.err.OperationalError as e:
- print('Error is '+str(e))
- sys.exit()
- try:
- engine = create_engine('mysql+pymysql://user1:123456@localhost:3306/test')
- except sqlalchemy.exc.OperationalError as e:
- print('Error is '+str(e))
- sys.exit()
- except sqlalchemy.exc.InternalError as e:
- print('Error is '+str(e))
- sys.exit()
- try:
- sql = 'select * from sum_case'
- df = pd.read_sql(sql, con=conn)
- except pymysql.err.ProgrammingError as e:
- print('Error is '+str(e))
- sys.exit()
- print(df.head())
- df.to_sql(name='sum_case_1',con=engine,if_exists='append',index=False)
- conn.close()
- print('ok')
- if __name__ == '__main__':
- df = read_mysql_and_insert()
另外需要注意的还有。
1) test数据库里有两个表,建表语句如下:
- CREATE TABLE `sum_case` (
- `type_id` tinyint(2) DEFAULT NULL,
- `type_name` varchar(5) DEFAULT NULL,
- KEY `b` (`type_name`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- CREATE TABLE `sum_case_1` (
- `type_id` tinyint(2) DEFAULT NULL,
- `type_name` varchar(5) DEFAULT NULL,
- KEY `b` (`type_name`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入初始数据
- insert into sum_case (type_id,type_name) values (1,'a'),(2,'b'),(3,'c')
2)创建user1用户
- grant select, update,insert on test.* to 'user1'@'localhost' identified by '123456'
相关推荐:
Python在groupby分组后提取指定位置记录方法
以上就是python3 pandas 读取MySQL数据和插入的详细内容,更多请关注Gxl网其它相关文章!