Loading Database Tables from Files
One of the nice things about using Python in the database domain is that you can
combine the power of the SQL query language with the power of the Python general-
purpose programming language. They naturally complement each other.
Loading with SQL and Python
Suppose, for example, that you want to load a database table from a flat file, where
each line in the file represents a database row, with individual field values separated by
commas. Examples 17-6 and 17-7 list two such datafiles we’re going to be using here.
Example 17-6. PP4E\Dbase\Sql\data.txt
bob,devel,50000
sue,music,60000
ann,devel,40000
tim,admin,30000
kim,devel,60000
Example 17-7. PP4E\Dbase\Sql\data2.txt
bob,developer,80000
sue,music,90000
ann,manager,80000
Now, some database systems like MySQL have a handy SQL statement for loading such
a table quickly. Its load data statement parses and loads data from a text file, located
on either the client or the server machine. In the following, the first command deletes
all records in the table, and we’re using the fact that Python automatically concatenates
adjacent string literals to split the SQL statement over multiple lines:
# Using MySQL (currently available for Python 2.X only)
...log into MySQL first...>>> curs.execute('delete from people') # all records
>>> curs.execute(
... "load data local infile 'data.txt' "
... "into table people fields terminated by ','")>>> curs.execute('select * from people')
>>> for row in curs.fetchall(): print(row)
...
('bob', 'devel', 50000L)
('sue', 'music', 60000L) # 2.X long integers
('ann', 'devel', 40000L)
('tim', 'admin', 30000L)
('kim', 'devel', 60000L)
>>> conn.commit()This works as expected. But what if you must use a system like the SQLite database
used in this book, which lacks this specific SQL statement? Or, perhaps you just need
to do something more custom than this MySQL statement allows. Not to worry—a
1344 | Chapter 17: Databases and Persistence