python3 pandas reads MySQL data and inserts-Python Tutorial-php.cn

python3 pandas reads MySQL data and inserts

不言

Release： 2018-04-20 13:57:38

Original

4615 people have browsed it

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=&#39;localhost&#39;,user=&#39;user1&#39;,password=&#39;123456&#39;,db=&#39;test&#39;,charset=&#39;utf8&#39;)
 except pymysql.err.OperationalError as e:
  print(&#39;Error is &#39;+str(e))
  sys.exit()
  
 try:
  engine = create_engine(&#39;mysql+pymysql://user1:123456@localhost:3306/test&#39;)
 except sqlalchemy.exc.OperationalError as e:
  print(&#39;Error is &#39;+str(e))
  sys.exit()
 except sqlalchemy.exc.InternalError as e:
  print(&#39;Error is &#39;+str(e))
  sys.exit()
  
 try: 
  sql = &#39;select * from sum_case&#39;
  df = pd.read_sql(sql, con=conn) 
 except pymysql.err.ProgrammingError as e:
  print(&#39;Error is &#39;+str(e))
  sys.exit() 

 print(df.head())
 df.to_sql(name=&#39;sum_case_1&#39;,con=engine,if_exists=&#39;append&#39;,index=False)
 conn.close()
 print(&#39;ok&#39;)
 
if __name__ == &#39;__main__&#39;: 
 df = read_mysql_and_insert()

Copy after login

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;

Copy after login

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;

Copy after login

Insert initial data

insert into sum_case (type_id,type_name) values (1,&#39;a&#39;),(2,&#39;b&#39;),(3,&#39;c&#39;)

Copy after login

2) Create user1 user

grant select, update,insert on test.* to &#39;user1&#39;@&#39;localhost&#39; identified by &#39;123456&#39;

Copy after login

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!