Converting dbf to sqlite using Python is not populating table

Question

I've struggled over this issue for over an hour now. I'm trying to create a Sqlite database using a dbf table. When I create a list of records derived from a dbf to be used as input for the Sqlite executemany statement, the Sqlite table comes out empty. When I try to replicate the issue using Python interactively, the Sqlite execution is successful. The list generated from the dbf is populated when I run it - so the problem lies in the executemany statement.

import sqlite3
from dbfpy import dbf

streets = dbf.Dbf("streets_sample.dbf")

conn = sqlite3.connect('navteq.db')

conn.execute('PRAGMA synchronous = OFF')
conn.execute('PRAGMA journal_mode = MEMORY')

conn.execute('DROP TABLE IF EXISTS STREETS')

conn.execute('''CREATE TABLE STREETS
    (blink_id CHAR(8) PRIMARY KEY,
    bst_name VARCHAR(39),
    bst_nm_pref CHAR(2));''')

alink_id = []
ast_name = []
ast_nm_pref = []

for i in streets:
    alink_id.append(i["LINK_ID"])
    ast_name.append(i["ST_NAME"])
    ast_nm_pref.append(i["ST_NM_PREF"])

streets_table = zip(alink_id, ast_name, ast_nm_pref)

conn.executemany("INSERT OR IGNORE INTO STREETS VALUES(?,?,?)", streets_table)

conn.close()

This may not be the only issue, but you want to call conn.commit() to save the changes to the SQLite database. Reference: python.org/dev/peps/pep-0249/#commit — mechanical_meat
– mechanical_meat, Commented Oct 11, 2013 at 0:01
Hallelujah, that did it! Ugh, what a stupid issue. Thanks Bernie. I wish I could accept your comment as the answer. — baobobs
– baobobs, Commented Oct 11, 2013 at 0:05
You're most welcome. You can delete the question if you like. I think this issue comes up fairly often. Don't beat yourself up about and go get the next bug! :-) — mechanical_meat
– mechanical_meat, Commented Oct 11, 2013 at 0:07
@bernie: How about making an answer so we can upvote it and user1185790 can accept it? — Ethan Furman
– Ethan Furman, Commented Oct 11, 2013 at 0:13

mechanical_meat · Accepted Answer · 2013-10-11 00:14:43Z

5

This may not be the only issue, but you want to call conn.commit() to save the changes to the SQLite database. Reference: http://www.python.org/dev/peps/pep-0249/#commit

answered Oct 11, 2013 at 0:14

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting dbf to sqlite using Python is not populating table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related