Example 17-8. PP4E\Dbase\Sql\loaddb1.py
"""
load table from comma-delimited text file; equivalent to this nonportable SQL:
load data local infile 'data.txt' into table people fields terminated by ','"
"""
import sqlite3
conn = sqlite3.connect('dbase1')
curs = conn.cursor()
file = open('data.txt')
rows = [line.rstrip().split(',') for line in file]
for rec in rows:
curs.execute('insert into people values (?, ?, ?)', rec)
conn.commit() # commit changes now, if db supports transactions
conn.close() # close, del call rollback if changes not committed yet
As is, Example 17-8 is a top-level script geared toward one particular case. It’s hardly
any extra work to generalize this into a function that can be imported and used in a
variety of scenarios, as in Example 17-9—a much more widely useful module and
command-line script.
Example 17-9. PP4E\Dbase\Sql\loaddb.py
"""
load table from comma-delimited text file: reusable/generalized version
Importable functions; command-line usage: loaddb.py dbfile? datafile? table?
"""
def login(dbfile):
import sqlite3
conn = sqlite3.connect(dbfile) # create or open db file
curs = conn.cursor()
return conn, curs
def loaddb(curs, table, datafile, conn=None, verbose=True):
file = open(datafile) # x,x,x\nx,x,x\n
rows = [line.rstrip().split(',') for line in file] # [[x,x,x], [x,x,x]]
rows = [str(tuple(rec)) for rec in rows] # ["(x,x,x)", "(x,x,x)"]
for recstr in rows:
curs.execute('insert into ' + table + ' values ' + recstr)
if conn: conn.commit()
if verbose: print(len(rows), 'rows loaded')
if name == 'main':
import sys
dbfile, datafile, table = 'dbase1', 'data.txt', 'people'
if len(sys.argv) > 1: dbfile = sys.argv[1]
if len(sys.argv) > 2: datafile = sys.argv[2]
if len(sys.argv) > 3: table = sys.argv[3]
conn, curs = login(dbfile)
loaddb(curs, table, datafile, conn)
1348 | Chapter 17: Databases and Persistence