通过python设置SQLite数据库的（默认）编码

Question

我想通过python驱动程序创建一个SQLite数据库。由于应用程序将在不同的操作系统上运行，我想明确指定 SQLite 数据库中文本的（默认）编码。

显然，我只能在使用此命令创建数据库之前执行此操作（请参阅 SQLite 文档）：

PRAGMA encoding = "UTF-8";

我的问题是创建/连接到数据库的 Python 方式没有指定（至少在我的理解中）在创建数据库之前设置 PRAGMA（例如编码）的方法（Python 文档）

因此，有没有办法在创建数据库之前/同时通过 python SQlite 驱动程序指定编码？

我目前看到的唯一解决方法（似乎有点hacky）是通过python 运行Shell 命令，但由于机器上未安装SQLite CLI，因此没有选择。

Answer 1

您可以创建数据库并在之后更改编码>>> import sqlite3>>> conn = sqlite3.connect('example.db')>>> c = conn.cursor()>>> c.execute('pragma encoding')<sqlite3.Cursor object at 0x7fa641241e30>>>> rows = c.fetchall()>>> for row in rows:...&nbsp; &nbsp; &nbsp;print(row)...&nbsp;('UTF-8',)>>> c.execute('pragma encoding=UTF16')<sqlite3.Cursor object at 0x7fa641241b20>>>> c.execute('pragma encoding')<sqlite3.Cursor object at 0x7fa641241e30>>>> rows = c.fetchall()>>> for row in rows:...&nbsp; &nbsp; &nbsp;print(row)...&nbsp;('UTF-16le',)请注意，需要编辑数据库才能使这些更改永久化，例如：>>> sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (...&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;id integer PRIMARY KEY,...&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;name text NOT NULL,...&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin_date text,...&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end_date text...&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;); """>>> c.execute(sql_create_projects_table)<sqlite3.Cursor object at 0x7f441ce90e30>>>> rows = c.fetchall()>>> for row in rows:...&nbsp; &nbsp; &nbsp;print(row)...>>> sql = ''' INSERT INTO projects(name,begin_date,end_date)...&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;VALUES(?,?,?) '''>>> project = ('Cool App with SQLite & Python', '2015-01-01', '2015-01-30');>>> c.execute(sql, project)<sqlite3.Cursor object at 0x7f441ce90e30>如果您至少不添加表，编码将回退到其默认值。希望这可以帮助。