Execute sqlite3 "dot" commands from Python or register collation in command line utility

Question

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?

score 17 · Answer 1 · answered Sep 20 '17 at 05:15

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"])

score 10 · Answer 2 · answered May 06 '10 at 01:38

The .import command in the sqlite shell is a builtin command. It's processed by the shell program, not the SQL engine, so you can't execute it like an SQL statement.

Reading code for SQLite's shell.c, it seems that .import is simply a loop, reading lines from the data file, splitting on the separator, and passing the fields as parameter values to a prepared INSERT statement. So you should be able to mimic the behavior of .import with Python code easily.

I tested the following with Python 2.6:

import sqlite3
import csv

conn = sqlite3.connect(':memory:')

conn.execute('create table mytable (col1 text, col2 text, col3 text)')

csvReader = csv.reader(open('mydata.csv'), delimiter=',', quotechar='"')

for row in csvReader:
        conn.execute('insert into mytable (col1, col2, col3) values (?, ?, ?)', row)

cur = conn.cursor()
cur.execute('select * from mytable')
print cur.fetchall()

Perfect answer/example to help anyone starting out on SQLite programming in Python! — Blairg23, Apr 11 '16 at 21:34

score 9 · Accepted Answer · answered May 05 '10 at 21:12

9

You can load new collating sequences and functions using load_extension() built-in SQLite function or .load command in command line shell for SQLite. Obviously, extensions shold be written in C.

And you can not call dot-commands from python, because dot-commands are specific to the command line shell tool.

answered May 05 '10 at 21:12

newtover

31,286
11
84
89

1

The last sentence of this answer is false. [This answer below](https://stackoverflow.com/a/46313782/2225804) shows how to do it. More generally, you can call sqlite dot commands at the command line like this `sqlite3 database.sqlite 'dot command 1' 'dot command 2'`. – user2225804 Jul 29 '21 at 19:19
@user2225804, well, certainly, you can delegate anything to another process, but installed python doesn't assume installed `sqlite3` binary. For instance, you may take a python slim docker image, it will contain the corresponding `_sqlite3....so` in `lib-dynload`, but there will be no `sqlite3` binary. But yes, you can install sqlite3 or even write in another language if you have an option. – newtover Jul 30 '21 at 14:06
1

You're imposing constraints that are not in the question. The question is "Alternatively, how can I execute sqlite3 dot commands from Python?" It doesn't say anything about avoiding subprocesses, and frankly, that doesn't even seem like a constraint most people would care about. Accepted answers should at a minimum strive for accuracy. – user2225804 Jul 31 '21 at 22:43

score 8 · Answer 4 · answered Feb 27 '10 at 03:07

8

The dot commands are only available to the sqlite3 executable. You will have to replace them with the equivalent combination of Python code and DB-API calls in order to mimic their behavior.

answered Feb 27 '10 at 03:07

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

2

Some commands seem far away from trivial. How would you add headers or choose the column separator? – Rodrigo Lopez May 02 '13 at 18:01

score 5 · Answer 5 · edited Sep 27 '18 at 20:19

5

The APSW Python wrapper for SQLite does include a shell compatible with the SQLite one. You can also extend it to add more commands of your own choosing, extra output modes etc. (Disclosure: I am the APSW author.)

edited Sep 27 '18 at 20:19

Adobe

12,967
10
85
126

answered Sep 27 '18 at 13:48

Roger Binns

3,203
1
24
33

It would have been great to see your package used in an answer in: [using SQLite with Scrapy](https://stackoverflow.com/questions/3261858/does-anyone-have-example-code-for-a-sqlite-pipeline-in-scrapy). – not2qubit Oct 04 '18 at 22:25

score 0 · Answer 6 · answered Nov 13 '19 at 10:33

0

subprocess.run() is preferred oversubprocess.call(). Below is my answer:

import subprocess
schema = subprocess.run(
    ['sqlite3',
     'xxx.db',
     '.schema'
    ],
    capture_output=True
).output.decode('utf-8')

print(schema)

answered Nov 13 '19 at 10:33

yoonghm

4,198
1
32
48

Execute sqlite3 "dot" commands from Python or register collation in command line utility

6 Answers6

Linked