Home  >  Article  >  Backend Development  >  python3 pandas reads MySQL data and inserts

python3 pandas reads MySQL data and inserts

不言
不言Original
2018-04-20 13:57:384567browse

The following is an example of reading and inserting MySQL data with python3 pandas. It has a good reference value and I hope it will be helpful to everyone. Let’s take a look together

The python code is as follows:


##

# -*- 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()


There are other things to note.

1) There are two tables in the test database. The table creation statements are as follows:


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 initial data


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


2) Create user1 user


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


Related recommendations:

Python extracts the specified location record method after groupby grouping



##

The above is the detailed content of python3 pandas reads MySQL data and inserts. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn