在Python中执行sqlite3的"dot"命令或在命令行实用程序中注册排序规则

Question

我的sqlite3数据库包含一个"collate"列约束。我已经将其放置在表的模式中，以防止意外地忽略必需的排序规则。然而，这意味着当从命令行而不是从我的Python代码运行sqlite3时，模式中引用的排序规则不存在，因此我无法使用点命令。

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

此外，从 Python 创建连接并添加所需的排序规则会遇到这个问题：

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

看起来execute函数似乎不想通过sqlite3点命令。

当必要排序函数不存在时，我该如何执行sqlite3点命令？或者，我应该如何从Python中执行sqlite3点命令？

- Matt Joiner

Answer 1

你可以使用subprocess模块从Python中调用dot命令，它会基本上调用一个shell。如果你需要使用多个dot命令，你可以将它们作为独立的shell参数传递 - 使用分号来分隔它们不起作用。

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

- polm23

Answer 2

.import 命令在 SQLite shell 中是一个内置命令。它由 shell 程序处理，而非 SQL 引擎，因此无法像 SQL 语句一样执行。

查看 SQLite 的 shell.c 源代码，似乎 .import 只是一个循环，从数据文件中读取行，按分隔符拆分，并将字段作为参数值传递给准备好的 INSERT 语句。因此，你可以轻松地使用 Python 代码模仿 .import 的行为。

我在 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()

- Bill Karwin

---

我的问题得到了很好的回答 :) - Matt Joiner

这是一个完美的答案/示例，可以帮助任何想要开始使用Python编程中SQLite的人！ - Blairg23

Answer 3

您可以使用SQLite内置的load_extension()函数或在SQLite的命令行shell中使用.load命令来加载新的字符序列和函数。显然，扩展必须是用C编写的。

但是您无法从Python中调用点命令，因为点命令是特定于命令行shell工具的。

- newtover

---

这个答案的最后一句话是错误的。下面的答案展示了如何做到这一点。更一般地，您可以像这样在命令行中调用sqlite点命令：sqlite3 database.sqlite 'dot command 1' 'dot command 2'。 - user2225804

@user2225804，当然，您可以将任何东西委托给另一个进程，但是安装的Python不会假定已安装sqlite3二进制文件。例如，您可以使用Python slim docker镜像，它将包含相应的_sqlite3....so在lib-dynload中，但是没有sqlite3二进制文件。但是，如果您有选择，您可以安装sqlite3甚至用另一种语言编写。 - newtover

你正在强加一些问题中不存在的限制。问题是“或者，我如何从Python执行sqlite3点命令？”它没有提到避免子进程的任何内容，而且坦率地说，这似乎甚至不是大多数人关心的限制。被接受的答案应该至少力求准确性。 - user2225804

Answer 4

点命令仅适用于sqlite3可执行文件。为了模仿它们的行为，您将不得不用Python代码和DB-API调用的等效组合来替换它们。

- Ignacio Vazquez-Abrams

---

有些命令似乎与琐碎的事情相去甚远。你如何添加标题或选择列分隔符？ - Rodrigo Lopez

Answer 5

APSW Python对SQLite的封装包含一个与SQLite兼容的Shell。您还可以扩展它以添加更多自己选择的命令、额外的输出模式等。(声明：我是APSW作者。)

• APSW shell
• APSW和pysqlite之间的区别

- Roger Binns

---

很高兴能在使用Scrapy与SQLite的答案中看到您的包被使用。 - not2qubit

Answer 6

subprocess.run()比subprocess.call()更受欢迎。以下是我的答案：

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

print(schema)

- yoonghm