Problems encountered in writing the HTML content of articles into the database during the development of Django blog

Little knowledge , Great challenge ！ This article is participating in “   A programmer must have a little knowledge   ” Creative activities

pit 1: Python operation mysql The database appears pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax;

The following is python In doing mysql Database insert data code operation ：

import pymysql.cursors

    #  Circular warehousing operation 
    for ll in range(0, len(wallPaperBeanList)):
        #  Connect MySQL database 
        connection = pymysql.connect(host='127.0.0.1', port=3306, user='ad', password='ad', db='AllThingArePower',
                                     charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

        #  adopt cursor Create cursors 
        cursor = connection.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)

        #  Submit SQL
        connection.commit()
        #  Close data connection 
        connection.close()


 Copy code 

After running, the following exception appears , The complete exception log information is pasted 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 code 

No problem with normal text only html Text error reporting , At first I always thought it was mysql There are mistakes in grammar , But I have never found anything that can be changed , After Baidu, there is a blog saying mysql When splicing statements, there may be parameters with double quotation marks , Use pymysql.escape_string() Method to handle the parameters with double quotation marks , This solution didn't solve my problem , Later, I found a better solution , Just don't use % perhaps + Operator to splice SQL sentence , Placeholders should be used . namely execute Second parameter of .

The modified code ：

import pymysql.cursors

    #  Circular warehousing operation 
    for ll in range(0, len(wallPaperBeanList)):
        #  Connect MySQL database 
        connection = pymysql.connect(host='127.0.0.1', port=3306, user='ad', password='ad', db='AllThingArePower',
                                     charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

        #  adopt cursor Create cursors 
        cursor = connection.cursor()
                cursor.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(wallPaperBeanList[ll].img_tag)))

        #  Submit SQL
        connection.commit()
        #  Close data connection 
 Copy code 

pit 2: An error occurred while writing to the database : Mysql Failure , abnormal pymysql.err.InternalError: (1366, "Incorrect string value: '\xF0\x9D\x90\xBF;......

Problem description ：

　　 Insert Mysql It failed ,python The code reports the following exception ：

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

Cause analysis ：

　　UTF-8 The encoding could be two 、 Three 、 Four bytes .Emoji The expression is 4 Bytes , and Mysql Of utf8 Code the most 3 Bytes , So the data doesn't plug in .

Solution ：

　　 modify Mysql Table character set and Pymysql Character set when connecting to Library .

　　1、 modify Mysql Character set of table

　　　　 explain ： Convert the established table character set to utf8mb4, The sorting rule is changed to utf8mb4_bin

　　　 command ：alter table TABLE_NAME convert to character set utf8mb4 collate utf8mb4_bin; （ take TABLE_NAME Replace with your table name ）
 Copy code 

　　　　 Be careful ： The collation is not utf8mb4_general_ci, It is utf8mb4_bin, Don't take it for granted

　　2、 Modify the character set of database connection 　　

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

 Copy code