Python – Execute sqlite3 “dot” commands from Python or register collation in command line utility

collationpythonsqlite

My sqlite3 database contains a "collate" column-constraint. I've placed it in the schema for the table, to prevent accidentally neglecting to use the necessary collation. However this means when running sqlite3 from the command line, and not from my Python code, the collation referenced in the schema is not present, and I'm unable to use dot commands.

sqlite> .import data.txt table_name
Error: no such collation sequence: my_collation

Furthermore, creating the connection from Python, and adding the collation required runs into this problem:

connWithCollation.execute(".import data.txt table_name")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near ".": syntax error

The execute function it would appear does not want to pass the sqlite3 dot command through.

How can I execute sqlite3 dot commands when necessary collation functions are not present? Alternatively, how can I execute sqlite3 dot commands from Python?

Best Answer

You can call dot commands from Python using the subprocess module, which basically invokes a shell. If you need to use multiple dot commands, you can pass them as separate shell arguments - using a semicolon to separate them won't work.

import subprocess
subprocess.call(["sqlite3", "xxx.db", 
  ".mode tabs", 
  ".import file.tsv table_name"])