When a python string consists of a MySQL command, the string contains single or double quotation marks resulting in an error resolution

Referenced from: 48036839

One, in the composition of the SQL statement and send the command exactly according to the style in Python to pass, so when executed in MySQL encountered the wrong command, by single quotation marks or double quotation marks. Therefore, before composing a string, you should manually precede the single or double quotation marks in the string with a backslash, so that when combined into a string, MySQL will be able to recognize it. For example: str= "" "SELECT COUNT (*) from%s where%s.appid="%s "" ""% (self._tb_name,self._tb_name,appid) print Strcur.execute ("%s "% (str)) If AppID is a variable containing single or double quotes, such as I ' m XXX and say" hi! " This variable contains single quotes, double quotes. This consists of the following SQL statement: SELECT COUNT (*) from table where table. AppId = "I ' m XXX and say" hi! "" In this case, the SQL statement will obviously go wrong. Therefore, the single quotation marks in the AppID should be processed before the STR is composed. Use the Replace method to precede single and double quotation marks with backslashes. Appid=appid.replace ("'", "\\\ '") converts the single quotation mark to the \ Single quotation mark Appid=appid.replace (' "', ' \\\ ') to the double quotation marks so that the composed SQL statement becomes the SELECT COUNT (*) From table where table. AppId = "I\ ' m XXX and say \" hi!\ "so MySQL correctly identifies the double quotation marks in the string as a single quotation mark. Python often encounters special characters such as single quotes and double quotes when inserting data into the MySQL database.

Workaround:
Cur.execute (U "Update table set name =%s where id =%s;" ', (Name.decode (' Utf-8 '), index))

Example:
Name= "I ' Mhere"
Note: Cursor.execute () can accept a parameter, or it can accept two parameters:
(1) Cur.execute ("INSERT into resource (Cid,name) values (%s,%s)", (12,name));
This format is to accept two parameters, MySQLdb will automatically escape the string for you and quote, do not have to escape their own, after the execution of this statement, the Resource table has one more record: I ' mhere
(2) Cur.execute ("INSERT into resource (Cid,name) values (%s,%s)"% (12,name));
This format uses the Python string format itself to generate a query, that is, to execute a parameter, you must escape the string and add quotation marks, that is, the upper statement is wrong, should be modified to:
Name = mysqldb.escape_string (name);
Cursor.execute ("INSERT into resource (Cid,name) values (%s, '%s ')"% (12,name));
So the record inserted is the same as (1): I ' MherePersonal Category: Pythonmysql

When a python string consists of a MySQL command, the string contains single or double quotation marks resulting in an error resolution