Exception: store MySQL escape data to ensure the accuracy of data storage

Time:2022-1-19

file

[read the full text]

**1. Why use escape?
**

Because when using MySQL database for data storage, if there are special characters, the saved data will be missing. For example, when a file path needs to be inserted into the database as a string, the saved string will be incomplete if the string contains special characters such as “/”.

The original data string to be saved should be like this.

image_path = '/usr/load/images/python.jpg'

As a result, when directly saved to the database, the real data may be as follows.

image_path = 'usrloadimagespython.jpg'

Therefore, record the occurrence of this abnormal situation and how to solve this problem.

**2. How to use escape to solve this problem?
**

In the module of pymysql, a function is specially provided to solve this problem, that is escape_ String() function.

Use escape directly when using_ The string () function passes the string to be escaped as a parameter, and the return result is the correct string we need.

The following function is to use the module of pymsql to insert data. First import the required module or function object.

Import pymysql # MySQL database operation Library
Import logging # log processing library

logger = logging. Getlogger ("database log") # sets the log name
logger. Setlevel (logging. Debug) # sets the log level

def get_conn():
    '''
    Connect database function
    :return:
    '''
    logger. Info ("start database connection")
    coon = pymysql.connect(host='127.0.0.1',
           user='root',
           password='root',
           database='data')
    logger. Info ("complete database connection")
    return coon

Write a function to save data for the implementation of new functions in the data list.

def insert_data():
    '''
    Database insert record function
    :return:
    '''
    logger. Info ("start data saving")
    Id = input ('data sequence number ')
    id = int(id)
    boc_ image_ Path = str (input ('picture address'))
    logger. Debug ("get database connection...")
    connection = get_conn()
    logger. Debug ("database connection has been obtained")
    cursor = connection.cursor()
    from pymysql.converters import escape_string
    logger. Debug ("escape the string containing special characters when saving data")
    sql = "insert into boc(id_,boc_image_path) values('%d','%s')" % (
    id, escape_ String (boc_image_path)) # here, the data is inserted into the picture address BOC_ image_ Path string, use BOC_ image_ The path() function was escaped
    try:
        cursor.execute(sql)
        connection.commit()
        logger. Info ("finish data saving")
    except:
        logger. Error ("exception occurred in data saving, execute rollback operation")
        connection.rollback()
    connection.close()

**3. Using escape_ What is the problem when escaping the string () function?
**

There is a difference between the high version and the low version, because of the different versions of escape_ There are also differences where the string () function exists.

When the version of pymsql is lower than 0.10.1, use the following method to import.

from pymysql import escape_string

When the version of pymysql is higher than 0.10.1, use pymysql Escape under converters module_ String. I’m using the version of 0.16 to operate.

from pymysql.converters import escape_string

file

[previous highlights]

Make a small alarm clock and do things according to the plan

Use pyqt5 the date control to make a small calendar for easy viewing

Combine tens of thousands of pictures into one picture and make a great mosaic!

Gadgets batch convert MP3 audio format to WAV format

Instead of H5, you can directly use the pywebio module to realize the web page