Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

Pit 1: Python mysql database operation appear pymysql. Err. ProgrammingError: (1064, “You have an error in your SQL syntax;

Mysql > insert data into mysql database

Import Pymysql.cursors # loop into the library for LL in range(0, len(wallPaperBeanList)): Connection = pymysql.connect(host='127.0.0.1', port=3306, user=' AD ', password=' AD ', db='AllThingArePower', charset='utf8mb4', Cursorclass = pymysql. Your cursors. # DictCursor) create a cursor through cursor cursor = connection. The cursor () insert_sql = "INSERT INTO 'wallpaper' ('category','view_img','img','created_time','img_tag') VALUES ("+ wallPaperBeanList[ll].category +','+wallPaperBeanList[ll].view_img +','+wallPaperBeanList[ll].img +','+wallPaperBeanList[ll].created_time +','+'null' +')' # print('category==' + wallPaperBeanList[ll].category + '; view_img==' + str( # wallPaperBeanList[ll].view_img) + '; img==' + str(wallPaperBeanList[ll].img) + '; created_time==' + str(wallPaperBeanList[ll].created_time) + '; Img_tag ==' + STR (wallPaperBeanList[ll].img_tag)) cursor.execute(insert_SQL) # commit SQL connection.mit () # close data connection connection.close()Copy the code

After running, the following exception occurs. The complete exception log information is posted below:

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near ''wallpaper' ('category','view_img','img','created_time','img_tag') VALUES (Origi' at line 1")1
Copy the code

At first, I always thought there was something wrong with the mysql syntax, but I couldn’t find anything that could be changed. Some blogs on Baidu said that there might be double quotation marks in the parameter when the mysql statement was concatenated. The pymysql.escape_string() method does not solve my problem. A better solution is to use placeholders instead of % or + operators to concatenate SQL statements. The second argument to execute.

Modified code:

Import Pymysql.cursors # loop into the library for LL in range(0, len(wallPaperBeanList)): Connection = pymysql.connect(host='127.0.0.1', port=3306, user=' AD ', password=' AD ', db='AllThingArePower', charset='utf8mb4', Cursorclass = pymysql. Your cursors. # DictCursor) create a cursor through cursor cursor = connection. The cursor () cursor. The execute (' insert into wallpaper (category,view_img,img,created_time,img_tag) values (%s,%s,%s,%s,%s)', (str(wallPaperBeanList[ll].category), str( wallPaperBeanList[ll].view_img),str(wallPaperBeanList[ll].img),str(wallPaperBeanList[ll].created_time),str(wallPaperBean List[ll].img_tag))) # submit SQL connection.mit () # close data connectionCopy the code

Pit 2: halfway through writing database: the Mysql failure, abnormal pymysql. Err. InternalError: (1366, “Incorrect string value: ‘\ xF0 \ x9D \ x90 \ xBF; .

Problem Description:

Failed to insert Mysql, python code reported the following exception:

pymysql.err.InternalError: (1366, “Incorrect string value: ‘\xF0\x9D\x90\xBF; .

Cause analysis:

Utf-8 encoding may be two, three, or four bytes. Emoji are four bytes long, and Mysql utF8 encodes up to three bytes long, so data cannot be inserted.

Solution:

Modify the Mysql table character set and Pymysql connect library character set.

Alter table character set

Alter table character set to UTF8MB4, collation to UTF8MB4_bin

Alter table TABLE_NAME convert to character set UTf8mb4 collate UTf8MB4_bin; (Replace TABLE_NAME with your table name)Copy the code

Note: the collation is not UTf8MB4_general_CI, but UTf8MB4_bin. Don’t take it for granted

2, modify the database connection character set

conn = pymysql.connect(host='localhost', user='root', password='root', port=3306, db='cncb', charset='utf8mb4')Copy the code