How to automate a process with the sqlite3.exe command line tool?

Question

I'm trying to bulk load a lot of data ( 5.5 million rows ) into an SQLite database file. Loading via INSERTs seems to be far too slow, so I'm trying to use the sqlite3 command line tool and the .import command.

It works perfectly if I enter the commands by hand, but I can't for the life of me work out how to automate it from a script ( .bat file or python script; I'm working on a Windows machine ).

The commands I issue at the command line are these:

> sqlite3 database.db sqlite> CREATE TABLE log_entry ( <snip> ); sqlite> .separator "\t" sqlite> .import logfile.log log_entry 

But nothing I try will get this to work from a bat file or python script.

I've been trying things like:

sqlite3 "database.db" .separator "\t" .import logfile.log log_entry  echo '.separator "\t" .import logfile.log log_entry' | sqlite3 database.db 

Surely I can do this somehow?

Answer 1

Create a text file with the lines you want to enter into the sqlite command line program, like this:

 CREATE TABLE log_entry (  ); .separator "\t" .import logfile.log log_entry

and then just call sqlite3 database.db < commands.txt