如何在python中声明sql中的变量?
我已经试了这么久:
import re
import sqlite3
conn = sqlite3.connect('imdb1.db')
c = conn.cursor()
c.execute('''CREATE TABLE imdb1 (ROWID int, Title varchar(40), Rating int)''')
x = open("ratings.list.txt","r")
movread = x.readlines()
x.close()
#s = raw_input('Search: ').lower()
for ns in movread:
if 'the lord of the' in ns.lower():
d = re.split('s+',ns,4)
Title = d[4].rstrip()
Rating= d[3]
list = [Title,Rating]
print list
# Insert a row of data
c.execute('INSERT INTO imdb1 ( Title, Rating) values ("%s","%s")'%(list[0],list[1]))
conn.commit()
输出: ---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
<ipython-input-90-0d9cfec4960a> in <module>()
25 print list
26 # Insert a row of data
---> 27 c.execute('INSERT INTO imdb1 ( Title, Rating) values ("%s","%s")'%(list[0],list[1]))
28 conn.commit()
29
OperationalError: near "5": syntax error
['The Lord of the Rings: The Return of the King (2003)', '8.9']
['The Lord of the Rings: The Fellowship of the Ring (2001)', '8.8']
['The Lord of the Rings: The Two Towers (2002)', '8.7']
['"5 Second Movies" (2007) {The Lord of the Rings and the Two Towers (#1.63)}', '6.2']
所以它看起来像当我到达底部的"5 ",我不能把列表放入我的sqldb我怎么能做到呢?我试图声明变量类型,但它没有工作!
问题是您所替换的字符串有引号。与其使用python的字符串格式,不如使用sqlite的:
c.execute('INSERT INTO imdb1 ( Title, Rating) values (?, ?)', (list[0],list[1]))
注意,这是在为数据库格式化字符串时应该遵守的一般原则。使用恶意格式化的电影标题,用户可能会破坏数据库1(称为SQL注入)。
1我不确定他们在这里会造成多大的损害,因为sqlite的execute只执行一条语句,但我确信其他比我更有经验的人可能会编造一些相当讨厌的东西。