SQLite and Python. Values write without errors, but not in database once the program is terminated

Question

I experience a problem with Python and SQLite in a script which downloads data from the Internet and puts them into a SQLite database. In the beginning of the execution I open the connection and assign the cursor. This cursor is then sent to the methods which downloads the data and writes them to the database. So far I know the create_schema method is working since the database is created with the correct structure, the other methods seems to be failing, but in the script execute without raising errors. I've tried to go into the database using the sqlite3 termninal tool and select values from the tables, but also a select * returns an empty result. I'm not quite sure how the cursor works with methods, but hopefully posting it here will help me gain a better understanding. Thank in advance for your help.

(PS: Sorry about the indentations. I didn't find a good way to do indentations on all text in one go. Any tips on how to do this?)

Script:

# -*- coding: UTF-8 -*-
import sqlite3
import httplib
import urllib2
import os
from xml.dom import minidom, Node
from xml.etree import ElementTree

SITE = "http://data.stortinget.no/eksport/"
DATA = "data.db"

def get_perioder(cur):
    DOK = "stortingsperioder"
    try:
        page = urllib2.urlopen(SITE+DOK)
    except:
        print "Failed to fetch item "+DOK
   if page:
    tree = ElementTree.parse(page)
    root = tree.getroot()
    top = list(root)[2]
    elements = list(top)
    for el in elements:
        fra = el.find('{http://data.stortinget.no}fra').text
        per_id = el.find('{http://data.stortinget.no}id').text
        til = el.find('{http://data.stortinget.no}til').text
        print "id: %s fra: %s til: %s" % (per_id, fra, til)
        cur.execute("""INSERT INTO perioder(fra, id, til) VALUES('%s','%s','%s')""" % (fra, per_id, til))
else:
    print "Could not load page: "+DOK
return cur

def get_sesjoner(cur):
DOK = "sesjoner"
try:
     page = urllib2.urlopen(SITE+DOK)
except:
    print "Failed to fetch item "+DOK
if page:
    tree = ElementTree.parse(page)
    root = tree.getroot()
    top = list(root)[2]
    elements = list(top)
    for el in elements:
        fra = el.find('{http://data.stortinget.no}fra').text
        ses_id = el.find('{http://data.stortinget.no}id').text
        til = el.find('{http://data.stortinget.no}til').text
        assert attribute in (fra, ses_id, til)
        print "id: %s fra: %s til: %s" % (ses_id, fra, til)
        cur.execute("""INSERT INTO sesjoner(fra, id, til) VALUES(%s, %s, %s)""" % (fra, ses_id, til))
else:
    print "Could not load page: "+DOK
return cur

def get_emner(cur):
DOK = "emner"
try:
     page = urllib2.urlopen(SITE+DOK)
except:
    print "Failed to fetch item "+DOK

if not page:
    print "Could not load page:!! "+DOK
    return
tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[1]
elements = list(top)
for el in elements:
    navn = el.find('{http://data.stortinget.no}navn').text
    main_emne_id = el.find("{http://data.stortinget.no}id").text
    print "HOVED: %s %s" % (navn, main_emne_id)
    cur.execute("""INSERT INTO hovedemner(id, navn) VALUES('%s','%s');""" % (main_emne_id, navn))
    if("true" in el.find("{http://data.stortinget.no}er_hovedemne").text):
        for uel in el.find("{http://data.stortinget.no}underemne_liste"):
            navn = uel.find("{http://data.stortinget.no}navn").text
            emne_id = uel.find("{http://data.stortinget.no}id").text
            print "UNDER: %s %s, horer til: %s" % (navn, emne_id, main_emne_id)
            cur.execute("""INSERT INTO underemner(id, navn, hovedemne_id) VALUES('%s', '%s', '%s');""" % (emne_id, navn, main_emne_id))
return cur


def get_fylker(cur):
DOK = "fylker"
try:
     page = urllib2.urlopen(SITE+DOK)
except:
    print "Failed to fetch item "+DOK

tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[1]
elements = list(top)
for el in elements:
    fylke_id = el.find("{http://data.stortinget.no}id").text
    navn =  el.find("{http://data.stortinget.no}navn").text
    print ("id: %s, navn: %s") % (fylke_id, navn)
    cur.execute("""INSERT INTO fylker(id, navn) VALUES('%s','%s');""" % (fylke_id, navn))

return cur


def get_partier(cur):
DOK = "allepartier"
try:
     page = urllib2.urlopen(SITE+DOK)
except:
    print "Failed to fetch item "+DOK

tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[1]
elements = list(top)
for el in elements:
    parti_id = el.find("{http://data.stortinget.no}id").text
    navn =  el.find("{http://data.stortinget.no}navn").text
    print ("id: %s, navn: %s") % (parti_id, navn)
    cur.execute("""INSERT INTO partier(id, navn) VALUES('%s','%s');""" % (parti_id, navn))

return cur

def get_komiteer(cur):
DOK = "allekomiteer"
try:
     page = urllib2.urlopen(SITE+DOK)
except:
    print "Failed to fetch item "+DOK

tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[1]
elements = list(top)
for el in elements:
    kom_id = el.find("{http://data.stortinget.no}id").text
    navn = el.find("{http://data.stortinget.no}navn").text
    print "id: %s navn: %s" % (kom_id, navn)
    cur.execute("""INSERT INTO partier(id, navn) VALUES('%s','%s');""" % (kom_id, navn))
return cur


def get_representanter(cur):
DOK = "dagensrepresentanter"
try:
     page = urllib2.urlopen(SITE+DOK)
except:
    print "Failed to fetch item "+DOK

tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[1]
elements = list(top)
for el in elements:
    doedsdato = el.find("{http://data.stortinget.no}doedsdato").text
    etternavn = el.find("{http://data.stortinget.no}etternavn").text
    foedselsdato = el.find("{http://data.stortinget.no}foedselsdato").text
    fornavn = el.find("{http://data.stortinget.no}fornavn").text
    repr_id = el.find("{http://data.stortinget.no}id").text
    kjoenn = el.find("{http://data.stortinget.no}kjoenn").text
    fylke = el.find("{http://data.stortinget.no}fylke/{http://data.stortinget.no}id").text
    parti = el.find("{http://data.stortinget.no}parti/{http://data.stortinget.no}id").text
    #komiteer = el.find("{http://data.stortinget.no}komiteer_liste/{http://data.stortinget.no}komite/{http://data.stortinget.no}id").text
    print "repr: %s, %s %s, parti: %s, fylke: %s" % (repr_id, fornavn, etternavn, parti, fylke)
    cur.execute("""INSERT INTO representanter(doedsdato, etternavn, foedselsdato, fornavn, id, kjoenn, fylke, parti) VALUES('%s','%s','%s','%s','%s','%s','%s','%s');""" % (doedsdato, etternavn, foedselsdato, fornavn, repr_id, kjoenn, fylke, parti))

return cur

def create_schema(cur):
cur.execute("DROP TABLE IF EXISTS perioder")
perioder = "CREATE TABLE  perioder(fra varchar(255), id varchar(255), til varchar(255))"
cur.execute("DROP TABLE IF EXISTS sesjoner")
sesjoner = "CREATE TABLE sesjoner(fra varchar(255), id varchar(255), til varchar(255))"
cur.execute("DROP TABLE IF EXISTS hovedemner")
hovedemner = "CREATE TABLE hovedemner(id int, navn varchar(255));"
cur.execute("DROP TABLE IF EXISTS underemner")
underemner = "CREATE TABLE underemner(id int, navn varchar(255), hovedemne_id int)"
cur.execute("DROP TABLE IF EXISTS fylker")
fylker = "CREATE TABLE fylker(id varchar(255), navn varchar(255));"
cur.execute("DROP TABLE IF EXISTS partier")
partier = "CREATE TABLE partier(id varchar(255), navn varchar(255));"
cur.execute("DROP TABLE IF EXISTS komiteer")
komiteer = "CREATE TABLE komiteer(id varchar(255), navn varchar(255));"
cur.execute("DROP TABLE IF EXISTS representanter")
representanter = "CREATE TABLE representanter(doedsdato varchar(255), etternavn varchar(500), foedselsdato varchar(255), fornavn varchar(500), id varchar(255), kjoenn varchar(255), fylke varchar(255), parti varchar(255));"
cur.execute(perioder)
cur.execute(sesjoner)
cur.execute(hovedemner)
cur.execute(underemner)
cur.execute(fylker)
cur.execute(partier)
cur.execute(komiteer)
cur.execute(representanter)
return cur


if __name__ == "__main__":
conn = sqlite3.connect(DATA)
cur = conn.cursor()
cur = create_schema(cur) 
cur = get_perioder(cur)
cur = get_sesjoner(cur)
cur = get_emner(cur)
cur = get_fylker(cur)
cur = get_partier(cur)
cur = get_komiteer(cur)
cur = get_representanter(cur)
conn.close

Answer 1

From what I see you do not call commit() at the end. This might not write any data into the database though. From SQLITE3 Docs:

Connection.commit()

This method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.