如何使用 sqlite3.exe 命令行工具自动化进程?

我正在尝试将大量数据(550 万行)批量加载到 SQLite 数据库文件中.通过 INSERT 加载似乎太慢了，所以我尝试使用 sqlite3 命令行工具和 .import 命令.

如果我手动输入命令，它可以完美运行，但我一生都无法弄清楚如何从脚本(.bat 文件或 python 脚本；我在 Windows 机器上工作)自动执行它.

我在命令行发出的命令是:

>sqlite3 数据库.dbsqlite>创建表 log_entry ( <snip> );sqlite>.分隔符	"sqlite>.import logfile.log log_entry

但是我尝试的任何方法都无法从 bat 文件或 python 脚本中得到它.

我一直在尝试这样的事情:

sqlite3 "database.db" .separator "	" .import logfile.log log_entryecho '.separator "	" .import logfile.log log_entry' |sqlite3 数据库.db

我当然可以以某种方式做到这一点?

解决方案

用你想输入到 sqlite 命令行程序的行创建一个文本文件，像这样:

<前>CREATE TABLE log_entry ();.分隔符 ".import logfile.log log_entry

然后只需调用 sqlite3 database.db <命令.txt

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 "	"
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 "	" .import logfile.log log_entry

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

Surely I can do this somehow?

解决方案

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

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

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