Python, sqlite3 database empty!

[andrewtrench]

Hi.
I have a problem that I am tearing my hair out to solve. I am trying to transfer tens of thousands of records into a sqlite db using the script below.
The script seems to run okay and I can see the db growing as it runs to some 19megs but when I try and view the database it is empty and subsequently reports a size of 2kb!

See my rough code below. I am a journalist and not a pro programmer so be gentle.:)
Some of the things I have in there like isolation_level and BEGIN TRANSACTION and check_same_thred solved another problem I was having with the script throwing a "can't open database error' earlier. I don't have clue what they do but they solved that problem - and no doubt caused this one.

Is sqlite maybe the wrong option for a database like this. I'm trying to avoid mysql so I don't have to hassle with servers etc.

import os
import sqlite3
from xlrd import open_workbook


#----------------------------------------
# get data from excel file
#----------------------------------------
os.chdir(u'C://Users//andrew.trench//Desktop//Trust records//Pmb//')
list_of_files=os.listdir(u'C://Users//andrew.trench//Desktop//Trust records//Pmb')

 # create SQL table and fill it with data
    #----------------------------------------
os.chdir(u'C://Python26//')
database = sqlite3.connect('trusts.db',timeout=10, isolation_level=None, check_same_thread = False)

cursor = database.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS pretoria (
   RecordNr INTEGER,
   FileNo TEXT,
   TrustName TEXT,
   AuthorizationDate TEXT,
   Auditor TEXT,
   TrusteeSurname TEXT,
   TrusteeFirstName TEXT,
   TrusteeCompany TEXT,
   TrusteeTelephoneWork TEXT)''')

cursor.execute('BEGIN TRANSACTION')

for filename in list_of_files:
    
    print filename
    book=open_workbook(u'C://Users//andrew.trench//Desktop//Trust records//Pmb//'+filename)
    sheet=book.sheet_by_index(0)
    counter=1
    for rownum in range(sheet.nrows):
        while counter < sheet.nrows:
                database = sqlite3.connect('trusts.db',timeout=10, isolation_level=None, check_same_thread = False)
                cursor.execute("PRAGMA page_size = 32768;")
                database.commit()
                data=sheet.row_values(counter)
                while len(data)>9:
                    data.pop((len(data)-1))
                    
                cursor.execute('INSERT INTO pretoria VALUES (?,?,?,?,?,?,?,?,?)', data)
                database.commit()
                counter=counter+1
           
    
    database.close()

[woooee]

You open the database for every iteration of the for() loop and while() loop but the close is outside of the for() loop. You should open the SQLite database before the
for filename in list_of_files:
Then close it at the same indentation level (no indentation) at the end of the program, since you are inserting into the same DB every time. Also, this while() statement should be an if() since "data" can only have one length

while len(data)>9:
    data.pop((len(data)-1))

and I would print "data" before and after these statements so you know what it contains. If there are any more problems, please include some sample data and the type of object for the variable, "data".

[woooee]

Another possible problem:

counter=1
    for rownum in range(sheet.nrows):
        while counter < sheet.nrows:
                counter=counter+1

This code will add (sheet.nrows)*(sheet.nrows-1) records. In other words, it will add each of sheet.nows-1 records, sheet.rows number of times. If you just want to add a record for each sheet.rows, pick one or other, for() or while(). The for() will add one record for each rownum, and the while() will skip the first record and add one record for each counter.

[andrewtrench]

Thanks so much for the suggestions. I have fixed code as suggested and it is running properly through the records I am importing but the critical problem of the database ending up empty remains.
It seems as if the database (reporting an expected file size of several MBs once the script is complete) is "locked" by the python script and I cannot open it in SQLite Manager, for example. When I manually kill the python process the file reverts back to 2kb! with only table and field headings intact.

[woooee]

You will have to test the code one piece at a time. The following code is the first part of the code you posted (with the directories that I don't have commented out), and adds some test data to check if the DB connection works. It works on my machine so if it does not work on yours, there may be a permission problem. If it does work, then try adding data for one file only. If that does not work, then you know that it has something to do with the way the file is read and presented to the DB. Since we have no idea what data you are working with we can only go so far.

import os
import sqlite3


#----------------------------------------
# get data from excel file
#----------------------------------------
##os.chdir(u'C://Users//andrew.trench//Desktop//Trust records//Pmb//')
#list_of_files=os.listdir(u'C://Users//andrew.trench//Desktop//Trust records//Pmb')

 # create SQL table and fill it with data
    #----------------------------------------
#os.chdir(u'C://Python26//')
database = sqlite3.connect('trusts.db',timeout=10, isolation_level=None, check_same_thread = False)

cur = database.cursor()
cur.execute('''CREATE TABLE IF NOT EXISTS pretoria (
   RecordNr INTEGER,
   FileNo TEXT,
   TrustName TEXT,
   AuthorizationDate TEXT,
   Auditor TEXT,
   TrusteeSurname TEXT,
   TrusteeFirstName TEXT,
   TrusteeCompany TEXT,
   TrusteeTelephoneWork TEXT)''')

## insert some dummy recs
for ctr in range(10):
    str_ctr = str(ctr)
    test_tuple = (str_ctr, "File"+str_ctr, "Trust"+str_ctr, \
                  "Authoriz"+str_ctr, "Auditor"+str_ctr, "Surname"+str_ctr, \
                  "FirstName"+str_ctr, "Company"+str_ctr, "Tel"+str_ctr)
    cur.execute('INSERT INTO pretoria VALUES (?,?,?,?,?,?,?,?,?)', test_tuple)
database.commit()

##  print all recs to test the add
cur.execute('select * from pretoria')
recs_list = cur.fetchall()
for rec in recs_list:
    print "-"*70
    for each_field in rec:
        print each_field,
    print

[andrewtrench]

Thanks for all the great advice and the code tips which sorted out a bunch of other issues. In the end I switched over to MySql and everything works fine... one of those enigmas which will have to remain unsolved...