current position：Home>[MySQL daily skill] deal with special characters and null values when operating MySQL database with Python
[MySQL daily skill] deal with special characters and null values when operating MySQL database with Python
You want to use Python 1 Send a message constructed as a string SQL Insert statement to MySQL Server side , However, specific field values may contain special characters such as quotation marks and backslashes , perhaps
NULL And other special values . Or, , You can construct SQL Statements are constructed through external input , And you want to avoid SQL The occurrence of Injection .
For the above problems , You can use Python Connect MySQL Driven API Placeholder mechanism or reference function provided in （ This paper focuses on the former ）, This makes the insertion of data more secure .
For the problems mentioned at the beginning of this article , Here is a case . for example ：
INSERT INTO profile (name,birth,color,foods,cats) VALUES('De'Mont','1973-01-12','blue','eggroll',4);
The problem with the above case is , For fields
name The value of
'De'Mont' , There is a single quote between a pair of single quotes . If you want the above SQL Statement is valid , Character escape can be realized in any of the following ways ：
- Put a single quote before the single quote that caused the problem ：
INSERT INTO profile (name,birth,color,foods,cats) VALUES('De''Mont','1973-01-12','blue','eggroll',4);
- Put a backslash before the single quotation mark that causes the problem ：
INSERT INTO profile (name,birth,color,foods,cats) VALUES('De\'Mont','1973-01-12','blue','eggroll',4);
De'MontUse a pair of double quotation marks to enclose 2：
INSERT INTO profile (name,birth,color,foods,cats) VALUES("De'Mont",'1973-01-12','blue','eggroll',4);
actually , If you manually construct a string form first SQL sentence , Then pass it through Python Send to MySQL Server side , Then you can manually escape characters in a way similar to the above when constructing . But the problem is , If in the above statement
name The value of the field is stored in the variable , You may not know whether the value represented by the variable needs to be handled with character escape .
What's worse is , The database may also store binary data such as pictures or audio , These binary data may represent any character . What is more , The inserted value is null at the root .
besides , In the age of the Internet , The need to deal with this problem correctly is more urgent , Because it is used to construct SQL Many of the field values of the statement come from the user's form input , This will not only lead to possible values containing characters that need special processing , It may also be caused by malicious user input SQL Inject .
therefore , You must be able to use a common way to handle input in all situations .
in addition , although SQL Statement
NULL Not a special character , But in the use of Python In the form of string SQL The statement is sent to MySQL On the server side , Special attention should also be paid to . stay SQL In the sentence ,
NULL No value . for example ： In the above case , If you don't know
De'Mont My favorite color , You can put
color Field set to
NULL , But here's the thing , Following SQL The sentence is incorrect ：
INSERT INTO profile (name,birth,color,foods,cats) VALUES('De''Mont','1973-01-12','NULL','eggroll',4);
Below SQL The statement is correct ：
INSERT INTO profile (name,birth,color,foods,cats) VALUES('De''Mont','1973-01-12',NULL,'eggroll',4);
Same thing , If
color The specific value of comes from a Python The variables in the , Then you need to first know whether the value represented by the variable is null , To determine the structure SQL Whether the statement needs quotation marks at both ends .
actually , For the above problem of dealing with special characters and null values , You can use Python Connect MySQL Driven API Placeholder mechanism provided in . In particular , stay SQL Use placeholders... In statements , Then bind the field value and placeholder one by one . At this time , API Will do all the work you need to do ： Escape special characters or map special values to
Python Connect MySQL Of API Use
%s3 The format qualifier is used as SQL Placeholders in statements . The specific use of placeholders is through the call
execute() Method by passing in two parameters ：
- In string form SQL sentence , It contains the format qualifier
- A sequence of values from each field , Each value corresponds to the placeholder one by one .
cursor = conn.cursor() cursor.execute(''' INSERT INTO profile (name,birth,color,foods,cats) VALUES(%s,%s,%s,%s,%s) ''', ("De'Mont", "1973-01-12", None, "eggroll", 4)) cursor.close() conn.commit()
In the above case, we can also see , If necessary, it means that the value is
NULL , So in Python in
None Bind with placeholders .
Final , from
execute() Method to MySQL Server side SQL The statement is in the following form ：
INSERT INTO profile (name,birth,color,foods,cats) VALUES('De\'Mont','1973-01-12',NULL,'eggroll',4)
You may need to refer to 【MySQL Daily skill 】 Use Python Connect 、 choice 、 Disconnect database ︎
In this case, ensure that the database
ANSI_QUOTESSQL Mode is not in effect . ︎
If you want to use... In a value
%Literal , You need to in SQL Use in statement
author[TakingCoding4Granted],Please bring the original link to reprint, thank you.
The sidebar is recommended
- Introduction to the differences between Python and Java
- Explain Python CONDA in detail
- The pycham downloaded by MAC reports an error as soon as it is opened. The downloaded Python interpreter is also the latest version
- From entry to mastery, python full stack engineers have personally taught Python core technology and practical combat for ten years
- Python is used to detect some problems of word frequency in English text.
- How to choose between excel, database and pandas (Python third-party library)?
- WxPython download has been reporting errors
- Pyside6 UIC and other tools cannot be found in the higher version of pyside6 (QT for Python 6). How to solve it?
- About Python Crawlers
- Successfully imported pandas, unable to use dataframe
guess what you like
How to extract some keywords in the path with Python
Python encountered a problem reading the file!
When Python is packaged into exe, an error is reported when opening assertionerror: C: \ users \ Acer \ appdata \ local \ temp\_ MEI105682\distutils\core. pyc
Eight practical "no code" features of Python
Python meets SQL, so a useful Python third-party library appears
100 Python algorithm super detailed explanation: a hundred dollars and a hundred chickens
[fundamentals of Python] Python code and so on
When Python uses probit regression, the program statement is deleted by mistake, and then it appears_ raise_ linalgerror_ Unrecognized error of singular
Python testing Nicholas theorem
Accelerating parallel computing based on python (BL) 136
- Python dynamic programming (knapsack problem and longest common substring)
- Django uses queryset filter save, and an 'queryset' object has no attribute 'Save' error occurs. Solution?
- Analysis of built-in functions in Python learning
- Python office automation - 90 - file automation management - cleaning up duplicate files and batch modifying file names
- Python office automation - 91 - word file Automation - word operation and reading word files
- After python, go also runs smoothly on the browser
- Self taught Python 26 method
- Summary of Python Tkinter component function examples (code + effect picture) (RadioButton | button | entry | menu | text)
- Python implementation of official selection sorting of Luogu question list
- Application of Django template
- Get project root path and other paths in Python project
- Get, rename, and delete file names in Python projects
- How to set the width and height of Python operation table
- Python string preceded by 'f' R 'B' U '
- JSON and other types convert to each other in Python
- Key value of key combination in pynput in Python
- Conversion of Python PDF file to word file
- Interface testing uses Python decorators
- Get the current time in Python
- Python course notes -- Python string, detailed explanation of related functions
- Python file and folder operations
- Python file content operation
- Three basic data quality evaluation methods and python implementation
- Python data structure and mathematical algorithm examples (continuously updating)
- GUI interface mail sending program based on SMTP protocol server and python language
- Python application tool development: mail sending GUI program
- Python application tool development: PIP instruction GUI program
- Application development based on Python and MySQL database: student information score management system version 1.0
- [Python] sort and sorted
- [Python] create a two-dimensional array with list, which is easy to step on
- Multiply [email protected]
- About creating in Python project folder__ init__. Understanding of PY
- Python, zsbd
- Smplify introduction and in python2 7 operation in environment
- Boost(2)：boost. Python library introduction and simple examples
- Boost (3): encapsulate C + + classes into Python classes
- Boost (5): extract the type of C + + language and extract class from Python object
- Boost(6)：Boost. How Python converts C + + parameter and return value types
- Boost(7)：Boost. Python encapsulates overloaded functions and passes default parameters
- Boost(8)：Boost. Python implements Python objects and various types of built-in operations and methods