用于 MySQL 的转义字符串 Python
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3617052/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Escape string Python for MySQL
提问by Lauren?iu Dasc?lu
I use Python and MySQLdb to download web pages and store them into database. The problem I have is that I can't save complicated strings in the database because they are not properly escaped.
我使用 Python 和 MySQLdb 下载网页并将它们存储到数据库中。我遇到的问题是我无法在数据库中保存复杂的字符串，因为它们没有正确转义。
Is there a function in Python that I can use to escape a string for MySQL? I tried with '''(triple simple quotes) and """, but it didn't work. I know that PHP has mysql_escape_string(), is something similar in Python?
Python 中有一个函数可以用来为 MySQL 转义字符串吗？我试过'''（三重简单引号）和"""，但没有用。我知道 PHP 有mysql_escape_string()，在 Python 中有类似的东西吗？
Thanks.
谢谢。
回答by miku
conn.escape_string()
See MySQL C API function mapping: http://mysql-python.sourceforge.net/MySQLdb.html
参见 MySQL C API 函数映射：http: //mysql-python.sourceforge.net/MySQLdb.html
回答by Eric Leschinski
Use sqlalchemy's text function to remove the interpretation of special characters:
使用sqlalchemy的text函数去掉特殊字符的解释：
Note the use of the function text("your_insert_statement")below. What it does is communicate to sqlalchemy that all of the questionmarks and percent signs in the passed in string should be considered as literals.
注意text("your_insert_statement")下面函数的使用。它的作用是与 sqlalchemy 沟通，传入的字符串中的所有问号和百分号都应被视为文字。
import sqlalchemy
from sqlalchemy import text
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import re
engine = sqlalchemy.create_engine("mysql+mysqlconnector://%s:%s@%s/%s"
% ("your_username", "your_password", "your_hostname_mysql_server:3306",
"your_database"),
pool_size=3, pool_recycle=3600)
conn = engine.connect()
myfile = open('access2.log', 'r')
lines = myfile.readlines()
penguins = []
for line in lines:
elements = re.split('\s+', line)
print "item: " + elements[0]
linedate = datetime.fromtimestamp(float(elements[0]))
mydate = linedate.strftime("%Y-%m-%d %H:%M:%S.%f")
penguins.append(text(
"insert into your_table (foobar) values('%%%????')"))
for penguin in penguins:
print penguin
conn.execute(penguin)
conn.close()
回答by User
The MySQLdb library will actually do this for you, if you use their implementations to build an SQL query string instead of trying to build your own.
如果您使用 MySQLdb 库的实现来构建 SQL 查询字符串而不是尝试构建自己的，那么 MySQLdb 库实际上会为您完成此操作。
Don't do:
不要这样做：
sql = "INSERT INTO TABLE_A (COL_A,COL_B) VALUES (%s, %s)" % (val1, val2)
cursor.execute(sql)
Do:
做：
sql = "INSERT INTO TABLE_A (COL_A,COL_B) VALUES (%s, %s)"
cursor.execute(sql, (val1, val2))
回答by Martin Thoma
>>> import MySQLdb
>>> example = r"""I don't like "special" chars ˉ\_(ツ)_/ˉ"""
>>> example
'I don\'t like "special" chars \xc2\xaf\_(\xe3\x83\x84)_/\xc2\xaf'
>>> MySQLdb.escape_string(example)
'I don\\'t like \"special\" chars \xc2\xaf\\_(\xe3\x83\x84)_/\xc2\xaf'
回答by Agnel Vishal
{!a}applies ascii()and hence escapes non-ASCII characters like quotes and even emoticons.
Here is an example
{!a}适用ascii()并因此转义非 ASCII 字符，如引号甚至表情符号。这是一个例子
cursor.execute("UPDATE skcript set author='{!a}',Count='{:d}' where url='{!s}'".format(authors),leng,url))
回答by ELOUAJIB Imad
install sqlescapy package:
安装sqlescapy包：
pip install sqlescapy
then you can escape variables in you raw query
然后你可以在你的原始查询中转义变量
from sqlescapy import sqlescape
query = """
SELECT * FROM "bar_table" WHERE id='%s'
""" % sqlescape(user_input)
回答by Saurav Panda
One other way to work around this is using something like this when using mysqlclient in python.
解决此问题的另一种方法是在 python 中使用 mysqlclient 时使用类似的方法。
suppose the data you want to enter is like this <ol><li><strong style="background-color: rgb(255, 255, 0);">Saurav\'s List</strong></li></ol>. It contains both double qoute and single quote.
假设您要输入的数据是这样的<ol><li><strong style="background-color: rgb(255, 255, 0);">Saurav\'s List</strong></li></ol>。它包含双引号和单引号。
You can use the following method to escape the quotes:
您可以使用以下方法来转义引号：
statement = """ Update chats set html='{}' """.format(html_string.replace("'","\\\'"))
statement = """ 更新聊天记录集 html='{}' """.format(html_string.replace("'","\\\'"))
Note: three \ characters are needed to escape the single quote which is there in unformatted python string.
注意：需要三个 \ 字符来转义未格式化的 python 字符串中的单引号。
