How Python writes to MySQL using pandas read CSV files

Summarize the various issues that you have recently encountered in using Python to read and write CSV storage databases.
On the code:

Reload (SYS) sys.setdefaultencoding (' utf-8 ') host = ' 127.0.0.1 ' port = 3306db = ' World ' user = ' root ' password = ' 123456 ' con = M Ysqldb.connect (host=host,charset= "UTF8", Port=port,db=db,user=user,passwd=password) Try:    df = Pd.read_sql (sql= R ' select * from City ', Con=con)    df.to_sql (' Test ', con=con,flavor= ' MySQL ') except Exception as E:    print ( E.message)

No unexpected words will print out a sentence: Database flavor MySQL is not supported
Found the answer on StackOverflow: The flavor ' MySQL ' is deprecated in pandas version 0.19.

Let's change it in a different way:

Reload (SYS) sys.setdefaultencoding (' utf-8 ') host = ' 127.0.0.1 ' port = 3306db = ' World ' user = ' root ' password = ' 123456 ' engine = Create_engine (str (r "mysql+mysqldb://%s:" + '%s ' + "@%s/%s")% (user, password, host, db)) Try:    df = PD. Read_sql (sql=r ' select * from City ', Con=engine)    df.to_sql (' Test ', con=engine,if_exists= ' append ', Index=false) Except Exception as E:    print (E.message)

Run, OK, can be stored in the index parameter indicates whether the Dataframe index as a column to store, generally not required, so the assignment is False

Now it seems that the problem is solved, but there is a small problem.
If I have a CSV file that contains Chinese (i window):
Name Age class
Xiao Ming 151 grade
Xiao Zhang 183 grade

engine = Create_engine (str (r "mysql+mysqldb://%s:" + '%s ' + "@%s/%s")% (user, password, host, db)) Try:    df = Pd.read_cs V (R ' C:\Users\xx\Desktop\data.csv ')    print (DF)    df.to_sql (' Test ', con=engine, if_exists= ' append ', index= False) except Exception as E:    print (E.message)

The printing process was garbled later. We'd better specify the encoding when we read the CSV, my local GBK:

DF = pd.read_csv (R ' C:\Users\xx\Desktop\data.csv ', encoding= ' GBK ')

We can print the information normally, but the error is as follows:

Unicodeencodeerror: ' latin-1 ' codec can ' t encode characters in position 0-1: Ordinal not in range (256)

Or the coding problem, because we didn't specify the encoding when we saved it to the database. The solution to this problem is also a pit, the internet said anything. The process will not say, look at the code:

engine = Create_engine (str (r "mysql+mysqldb://%s:" + '%s ' + "@%s/%s?charset=utf8")% (user, password, host, db))

Solved the

Related articles:

Python Data analysis Real IP request pandas detailed

Analysis of CDN logs through the Pandas library in Python

Using Python's pandas framework to manipulate data tutorials in Excel files