Python3 pandas read MySQL data and insert

Below for everyone to share an article Python3 pandas read MySQL data and insert instance, have very good reference value, hope to be helpful to everybody. Come and see it together.

The Python code is as follows:

#-*-Coding:utf-8-*-import pandas as Pdimport pymysqlimport sysfrom sqlalchemy import create_enginedef read_mysql_and_in SERT ():  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)) ' C9/>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 ()

Another thing to be aware of is that.

1) There are two tables in the test database, with the following table statements:

CREATE TABLE ' sum_case ' (  ' type_id ' tinyint (2) default null,  ' type_name ' varchar (5) default NULL,  KEY ' B ' (' t Ype_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;

Inserting 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 '