转义

在Python使用sqlite3模块新增数据时,一般需要把引号转义,避免SQL报语法错误。sqlite3使用​ ​?​ ​​自动转义。
举个例子,表结构如下:

CREATE TABLE "article" (
"id" INTEGER NOT NULL,
"title" TEXT,
PRIMARY KEY ("id")
);

入库操作:

import sqlite3
con = sqlite3.connect('D:/python3_test/test.db3')
cur = con.cursor()
data = {'title': 'Test"'}
sql = 'INSERT INTO article(title) VALUES ("%(title)s")' % data
print(sql)
cur.execute(sql)
con.commit()
cur.close()
con.close()

如果数据里带有引号,就会报错:

OperationalError                          Traceback (most recent call last)
<ipython-input-21-6dfab03a5e15> in <module>
5 sql = 'INSERT INTO article(title) VALUES ("%(title)s")' % data
6 print(sql)
----> 7 cur.execute(sql)
8 con.commit()
9 cur.close()

OperationalError: unrecognized token: ""Test"")"

Python sqlite转义,解决报错:OperationalError: unrecognized token_python


用​ ​?​ ​转义一下,就可以了:

import sqlite3
con = sqlite3.connect('D:/python3_test/test.db3')
cur = con.cursor()
data = {'title': 'Test"'}
sql = 'INSERT INTO article(title) VALUES (?)'
print(sql)
cur.execute(sql, (data['title'],))
con.commit()
cur.close()
con.close()

注意: 表名、字段名不能转义 ,否则报错。表名、字段名可以用反引号(`)转义。

OperationalError: near “?”: syntax error

Python sqlite转义,解决报错:OperationalError: unrecognized token_sqlite_02


Python sqlite转义,解决报错:OperationalError: unrecognized token_python_03

参考

​https://docs.python.org/2/library/sqlite3.html#converting-sqlite-values-to-custom-python-types​ ​​ ​ ​

​https://stackoverflow.com/questions/35524719/python-sqlite-escape-string-select-column​