sun, 15-jan-2006, 13:09

A few hours ago I wrote about a script I wrote that displays your New Yorker DVD reading lists in a convenient format. I just finished writing a similar script (again using Python and the pysqlite python module) which dumps the SQL statements you'd need to restore a reading list to the database. To restore the data to the database you'll also need the command line sqlite3 tool.

Use the script, reading_list_dump.py, to periodically dump your readings lists to a file:

$ ./reading_list_dump.py CSS > css_reading_list.sql

The file you created (css_reading_list.sql in this example) will contain SQL like:

INSERT INTO ReadingListEntry VALUES ( 700, 7, 181437 );
INSERT INTO ReadingListEntry VALUES ( 701, 7, 154899 );
INSERT INTO ReadingLists VALUES ( 7, 'CSS' );

To insert them into the database, close the Viewer program and use the sqlite3 command line program to upload the file:

$ sqlite3 -init css_reading_list.sql ny-sqlite-3.db

One important note: The table that's being updated (ReadingListEntry) has a primary key field that has to be unique in the table. It's the first number in the INSERT statements above (700, 701, etc.). Before running the sqlite3 command, you should make sure that there are no rows in the ReadingListEntry table that match the data you're inserting. If there are, you'll need to edit the dump file and change those numbers until they don't conflict with the existing table.

To find out what the largest value in the ReadingListEntry table is, run the following commands:

$ sqlite3  ny-sqlite-3.db
sqlite3> SELECT ReadingListEntryID FROM ReadingListEntry ORDER BY ReadingListEntryID DESC LIMIT 1;
585

All the values for the first field in the dump file for the ReadingListEntry table should be larger than this number.

tags: books  New Yorker 
Meta Photolog Archives