Python SQLite3插入没有返回错误但表中没有数据

Question

我需要你的建议和帮助。

我正在编写一段代码来解析某个网站的区域名称和相应区域的链接。之后，我想将区域的名称和链接存储在数据库(sqlite3)中。数据库已创建，表已创建，但是数据无法插入到表中。我尝试过一些尝试和错误，但没有成功。因此，我制作了这个帖子。

这是我的代码:

'''
usage python capstonePy.py http://www.liverpoolfc.com/fans/lfc-official-supporters-clubs

URL: http://www.liverpoolfc.com/fans/lfc-official-supporters-clubs

Official supporters URL pattern:
http://www.liverpoolfc.com/fans/lfc-official-supporters-clubs/[region]
'''

from sys import argv
from os.path import exists
from BeautifulSoup import *
import urllib
import re
import sqlite3

class FindSupporters:

    def __init__(self, *args, **kwargs):
        #parsing the url from the command line      
        url = argv[1]

        #make a new database
        cur = new_db('liverpudlian.sqlite3')

        #open and read the url      
        fhand = open_and_read(url)

        #print how many characters have been retrieved
        suc_ret(len(fhand))

        #make a list of links (href)
        linklst = find_link(fhand)

        #make a list of supporters regions
        offsuplinklst = fans_link(linklst)

        #make a new table and insert the data
        officialsup_table(cur, offsuplinklst, 'liverpudlian.sqlite3')

        sqlite3.connect('liverpudlian.sqlite3').close()     

def new_db(name):
    conn = sqlite3.connect(name)
    cur = conn.cursor()
    return cur

def open_and_read(url):
    try:    
        fhand = urllib.urlopen(url).read()
    except:
        print '\n'  
        print "+------------------------------------------------------------------------------+"
        print "|\t\t\t\tError: URL not found.\t\t\t\t|"
        print "+------------------------------------------------------------------------------+"
        print '\n'      
        quit()
    return fhand

def suc_ret(length):
    print '\n'  
    print "+------------------------------------------------------------------------------+"
    print "|\t\t", length, "characters have been successfully retrieved\t\t|"
    print "+------------------------------------------------------------------------------+"
    print '\n'

def find_link(fhand):
    links = []
    tags = []
    soup = BeautifulSoup(fhand)
    tags = soup('a')
    for tag in tags:
        tag = tag.get('href',None)
        if tag is not None :
            links.append(tag)
    return links

def fans_link(linklst):
    offsuplinklst = []
    for link in linklst:
        link = str(link)        
        link = link.rstrip()
        fans = re.findall('.*fans/.+clubs/(.+)', link)
        if len(fans) > 0:   
            offsuplinklst.append(fans[0])
    return offsuplinklst

def officialsup_table(cur, offsuplinklst, name):
    cur.execute('''
    create table if not exists OfficialSup
    (ID integer primary key,
    Region text unique,
    Link text unique,
    Retrieved integer)''')
    cur.execute('select Region from OfficialSup where Retrieved = 1 limit 1')   
    try :   
        cur.fetchone()[0]'
    except :        
        for i in range(len(offsuplinklst)):
            reg = offsuplinklst[i]
            link = 'http://www.liverpoolfc.com/fans/lfc-official-supporters-clubs/'+offsuplinklst[i]            
            cur.execute('insert into OfficialSup (Region, Link, Retrieved) values (?, ?, 1)', (reg, link))
    sqlite3.connect(name).commit()

FindSupporters()

可能是officialsup_table方法中的错误。然而，我的尝试并没有带来任何好的结果。

非常感谢!

问候，阿诺德·A.

Answer 1

您需要使用创建光标的同一连接实例进行提交。改进 new_db 以返回 conn 和 cur:

def new_db(name):
    conn = sqlite3.connect(name)
    cur = conn.cursor()
    return conn, cur

您现在需要以不同的方式读取函数的结果:

class FindSupporters:

    def __init__(self, *args, **kwargs):
        #parsing the url from the command line      
        url = argv[1]

        #make a new database
        conn, cur = new_db('liverpudlian.sqlite3')

        # ...

将连接对象传递给 officialsup_table 函数并调用 commit():

def officialsup_table(conn, cur, offsuplinklst, name):
    cur.execute('''
    create table if not exists OfficialSup
    (ID integer primary key,
    Region text unique,
    Link text unique,
    Retrieved integer)''')
    conn.commit()

    cur.execute('select Region from OfficialSup where Retrieved = 1 limit 1')   
    try :   
        cur.fetchone()[0]
    except :        
        for i in range(len(offsuplinklst)):
            reg = offsuplinklst[i]
            link = 'http://www.liverpoolfc.com/fans/lfc-official-supporters-clubs/'+offsuplinklst[i]            
            cur.execute('insert into OfficialSup (Region, Link, Retrieved) values (?, ?, 1)', (reg, link))
    conn.commit()