Pandas to_ SQL function pit avoidance guide "with correct code to run"

In use today Pandas Of to_sql When the method is used , There's a lot of problems , After a search , There are few reliable answers , All kinds of misguided , I wish to record

#  My environment ：

Mysql: 8.0.25
Python: 3.8.11

pandas: 1.3.4
sqlalchemy: 1.4.32
pymysql: 1.0.2

Let's look at it first to_sql Method parameters

DataFrame.to_sql(name, 
				 con, 
				 schema=None, 
                 if_exists='fail', 
	             index=True, 
                 index_label=None, 
                 chunksize=None, 
                 dtype=None, 
                 method=None)

There are four parameters that really need attention ：

• name： The table name corresponding to the database
• con： Database engine
• if_exists： If the table exists , How to choose , The default is fail
  • If database table There is , Need to be set as append
• index： take DataFrame The index is written as a column , Default True
  • If database table No corresponding field , Need to be set as False

adopt Pandas It can be seen from the documents , Parameters con Support only sqlalchemy.engine perhaps sqlite3.Connection

con：sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection
Using SQLAlchemy makes it possible to use any DB supported by that library. Legacy support is provided > for sqlite3.Connection objects. The user is responsible for engine disposal and connection closure for the > SQLAlchemy connectable See here.

Premise ： Guarantee Dataframe Data type of each column And Mysql The data types of each field are consistent

therefore , The correct code is as follows ：

import pymysql
from sqlalchemy import create_engine

pymysql.install_as_MySQLdb()
engine = create_engine('mysql+mysqldb://user:passwd@localhost/db?charset=utf8')
df.to_sql(name='user', conengine)

perhaps

import pymysql
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://user:passwd@localhost/db?charset=utf8')
df.to_sql(name='user', conengine)