How Python writes to MySQL using pandas read CSV files

Source: Internet
Author: User
Tags pandas read csv
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.