Yes, I could have written yet another little Python script to do this, but it turns out there’s a really easy way to export a SQLite database table to a CSV file from the command-line interface.
% sqlite3 databasefile.sqlite
sqlite> .headers on
sqlite> .mode csv
sqlite> .output outputfile.csv
sqlite> select * from table_name;
And that’s it. Couldn’t be easier really.
Hat tip to http://www.sqlitetutorial.net/sqlite-export-csv/
I was given a table of additional data that needed to be added to an existing table in a SQLite database. Since I was processing CSV files exported from the database, I thought I would just concatenate the files – until I realized that the ID columns would contain duplicates. Now, this wasn’t crucial for my case but it could have caused confusion at some point in the future. Therefore I decided to do the appending in the database, and then export the combined data into a new CSV file.
The first step was to be able to read both database files. I began by just opening the original database:
and then “attaching” the new database with the data I wanted to append (the quotes are necessary):
sqlite> attach 'my_other_db.sqlite' as db2;
(Of course, both tables must have exactly the same schema!)
My reading around had suggested that it would be easy to import the new data with a simple
insert into ... select * from ... command. However, this gave me an error because the values in the ID column (which is the Primary Key) were not unique:
Error: UNIQUE constraint failed: table1.ID
I scratched my head, and then decided to just select the columns with data. But that didn’t work either because the list of columns being inserted didn’t match the list of columns in the original table (well, d’uh!):
Error: table table1 has 10 columns but 9 values were supplied
I scratched my head some more, and then read the documentation about autoincrementing primary keys in SQLite. It turns out that a
ROWID must be specified in the list for an insert statement. A lightbulb switched on as I read about how new
ROWIDs are assigned in SQLite if a
NULL value is given. This prompted me to try:
sqlite> insert into table1 select NULL, col1, col2, ... from db2.table1;
Success! I now had my combined table, which I exported to CSV and continued with my analysis.
In short, SQLite advises against setting up an autoincrementing primary key, and just allows the user to give an explicit
NULL value to allocate the next available number.
It may be noted that the above was all done via the SQLite command line interface. It seems that this is the way to go: my attempts to attach a database in DB Browser for SQLite, for example, failed with a message about not being able to attach a database in a transaction. That, and all the examples on Stackoverflow showed the command-line being used. Other graphical browsers may differ.