Python uses Pandas to read CSV files and write them to MySQL

Summarize the various problems I encountered recently when using Python to read and write CSV to save the database.

Recommended related mysql video tutorials: "mysql tutorial"

Code:

reload(sys)
sys.setdefaultencoding('utf-8')
host = '127.0.0.1'
port = 3306
db = 'world'
user = 'root'
password = '123456'

con = MySQLdb.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)

If nothing else happens, it will print Say something: database flavor MySQL is not supported
I found the answer on stackoverflow: The flavor 'mysql' is deprecated in pandas version 0.19.

Let's try another way:

reload(sys)
sys.setdefaultencoding('utf-8')
host = '127.0.0.1'
port = 3306
db = '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)

After running, ok, you can save the index parameter to indicate whether to store the index of the DataFrame as a column. Generally speaking, it is not needed, so the value is False

Now it seems that the problem has been solved, but there is still There is a small problem.
If I have a csv file containing Chinese (my Window):
name age class
Xiao Ming 15 first grade
Xiao Zhang 18 third grade

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

try:
    df = pd.read_csv(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)

After printing, the characters are garbled . It is best to specify the encoding when we read csv. My local GBK:

df = pd.read_csv(r'C:\Users\xx\Desktop\data.csv',encoding='gbk')

We can print information normally, but an error is reported again. The error is as follows:

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

It's still an encoding problem. The reason is that we didn't specify the encoding when we saved it to the database. I was also fooled when I was trying to solve this problem. Everything on the Internet is available. I won’t talk about the process, but look at the code:

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

Solved

Related articles:

Real IP request Pandas detailed explanation of Python data analysis

Detailed explanation of analyzing cdn logs through the pandas library in Python

Tutorial on using Python’s pandas framework to manipulate data in Excel files

The above is the detailed content of Python uses Pandas to read CSV files and write them to MySQL. For more information, please follow other related articles on the PHP Chinese website!