current position:Home>Problems encountered in writing the HTML content of articles into the database during the development of Django blog

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

2022-01-29 15:40:45 Random code 3000

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 

copyright notice
author[Random code 3000],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2022/01/202201291540434723.html

Random recommended