从两个列表创建SQL表

Question

我正在尝试创建一个SQL表格：我想给每个电影的标题和评分分配一个ID，然后创建一个包含三个字段的表。

到目前为止，我的代码是：

import re
import sqlite3
#conn = sqlite3.connect('imdb.db')
#c = conn.cursor()

#c.execute('''CREATE TABLE imdb
         (mov_id, Title, Rating)''')

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]
        Rating= d[3]

        list = [Title,Rating]
        print list
        # Insert a row of data
#c.execute("INSERT INTO imdb VALUES %r;" %(tuple(Rating)))")
#       conn.commit()

我的列表看起来是这样的：

               Movie Title                                 Rating

['The Lord of the Rings: The Return of the King (2003)\n', '8.9']
['The Lord of the Rings: The Fellowship of the Ring (2001)\n', '8.8']
['The Lord of the Rings: The Two Towers (2002)\n', '8.7']

我该如何把这些放入SQL数据库中呢？

Answer 1

更新：

import re
import sqlite3
conn = sqlite3.connect('imdb.db')
c = conn.cursor()

c.execute('''CREATE TABLE imdb (mov_id ROWID, Title, Rating)''')

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]

    # Insert a row of data
        c.execute('INSERT INTO imdb (Title, Rating) values ("%s","%s")'%(list[0],list[1]))
        conn.commit()
        for row in c.execute('SELECT * FROM imdb ORDER BY Title'):
            print row

输出：

    OperationalError                          Traceback (most recent call last)
 <ipython-input-598-0de924f55a23> in <module>()
     24 
     25         # Insert a row of data
---> 26         c.execute('INSERT INTO imdb (Title, Rating) values ("%s","%s")'%     (list[0],list[1]))
     27         conn.commit()
     28         for row in c.execute('SELECT * FROM imdb ORDER BY Title'):

 OperationalError: near "5": syntax error

(None, u'The Lord of the Rings: The Return of the King (2003)', u'8.9')
(None, u'The Lord of the Rings: The Fellowship of the Ring (2001)', u'8.8')
(None, u'The Lord of the Rings: The Return of the King (2003)', u'8.9')
(None, u'The Lord of the Rings: The Fellowship of the Ring (2001)', u'8.8')
(None, u'The Lord of the Rings: The Return of the King (2003)', u'8.9')
(None, u'The Lord of the Rings: The Two Towers (2002)', u'8.7')

Answer 2

如果你不想手动输入电影的ID，可以这样修改你的imdb表的创建方式：

c.execute('''CREATE TABLE imdb (mov_id ROWID, Title, Rating)''')

这样你在插入数据的时候就可以不需要指定mov_id了。

然后，只需要用下面的代码来添加你的记录：

c.execute('INSERT INTO imdb (Title, Rating) values ("%s","%s")'%(list[0],list[1]))

把这段代码放在你原来用于插入数据的那一行替代掉就可以了。

顺便说一下，我个人建议你在处理标题时用rstrip()来去掉末尾的换行符，如果你不需要的话：

Title = d[4].rstrip()