python - SQLite 如何向列中添加数据？

Question

我重写了我的“死囚牢房”程序以添加一些改进并使其与 Python 3 兼容。

现在是这样的:

import sqlite3
import re
import urllib.request
from bs4 import BeautifulSoup
import requests
import string

URL=[]

conn = sqlite3.connect('prison.sqlite') 
conn.text_factory = str
cur = conn.cursor() 
cur.execute("DROP TABLE IF EXISTS prison")
cur.execute("CREATE TABLE Prison (Execution text,Statement text, LastName text, Firstname text, TDCJNumber text, Age integer, Date text, Race text, County text)")
conn.commit()
url='http://www.tdcj.state.tx.us/death_row/dr_executed_offenders.html'
lines = urllib.request.urlopen(url)
prisondata = lines.read()
lines.close()
soup = BeautifulSoup(prisondata,"html.parser")
rows = soup.find_all('tr')
url2 = url[:38]
for row in rows:
    td = row.find_all('td')
    try:
        Execution = str(td[0].get_text())
        cur.execute("INSERT OR IGNORE INTO prison (Execution) VALUES(?);", (str(Execution),))
        lastname= str(td[3].get_text())
        cur.execute("INSERT OR IGNORE INTO prison (Execution) VALUES(?);", (str(Execution),))
        firstname= str(td[4].get_text())
        cur.execute("INSERT OR IGNORE INTO prison (Firstname) VALUES(?);", (str(firstname),))
        tdcj= str(td[5].get_text())
        cur.execute("INSERT OR IGNORE INTO prison (TDCJNumber) VALUES(?);", (str(tdcj),))
        Age= str(td[6].get_text())
        cur.execute("INSERT OR IGNORE INTO prison (Age) VALUES(?);", (str(Age),))
        Date= str(td[7].get_text())
        cur.execute("INSERT OR IGNORE INTO prison (Date) VALUES(?);", (str(Date),))
        Race= str(td[8].get_text())
        cur.execute("INSERT OR IGNORE INTO prison (Race) VALUES(?);", (str(Race),))
        County= str(td[9].get_text())
        cur.execute("INSERT OR IGNORE INTO prison (County) VALUES(?);", (str(County),))
        links = row.find_all("a")
        link = links[1].get("href")
        LastStatementLink = url2 + link
        lines2 = urllib.request.urlopen(LastStatementLink)
        URL.append(LastStatementLink)

    except Exception:
        print ("An error has occured")
        continue

for U in URL:
    try:
        r = requests.get(U) 
        r.raise_for_status() 
        print ("URL OK"), U
        document = urllib.request.urlopen(U)
        html = document.read()
        soup = BeautifulSoup(html,"html.parser")
        pattern = re.compile("Last Statement:") 
        Statement = soup.find(text=pattern).findNext('p').contents[0]
        print (Statement.encode("utf-8"))
        cur.execute("INSERT OR IGNORE INTO prison (Statement) VALUES(?);", (str(Statement),))
        continue 
    except requests.exceptions.HTTPError as err:
        print (err)

conn.commit()
conn.close()

我已经尝试了几个小时来将正确的数据插入到 SQLite) 数据库中，但我似乎无法正确插入。例如:

 for row in rows:
        td = row.find_all('td')
        try:
            Execution = str(td[0].get_text())
            cur.execute("INSERT OR IGNORE INTO prison (Execution) VALUES(?);", (str(Execution),))

程序必须在指定网站的位置 [0] 处找到表中的所有执行编号，并将其添加到数据库中的“执行”列它也必须对数据的所有其他部分执行此操作.

问题是 python 似乎每次要向数据库中添加一条数据时都会创建一个新行。程序运行，但我的 SQLite 数据库中有 4000 行而不是大约 540 行。

我已经尝试了多种方法，但我似乎无法将数据放入正确的列中，或者最终得到正确数量的行。

我一直在考虑一种不同的方法:将数据片段添加到列表中，然后再将它们添加到数据库中。这意味着我将有 8 个数据列表(每个执行编号、名字、姓氏等一个列表)但是如何将列表中的数据添加到 SQLite？

谢谢!

Answer 1

The problem is that python seems to create a new row every time it has to add a piece of data to the database. The program runs, but I have 4000 rows instead of around 540 in my SQLite-database.

咳咳，不是 python 做的，而是你做的。你有 8 个插入语句，而你应该有一个

 Execution = str(td[0].get_text())
        cur.execute("INSERT OR IGNORE INTO prison (Execution,Firstname, ..., Race,) VALUES(?,?,?,?,?,?,?);", (str(Execution) , str(Firstname), ...))

应该这样做。注意计算所有 ?，确保列出所有列名，并且您已为所有这些列添加了值。

此外，请注意变量应以小写字母开头是惯例。