Python Sqlite3：创建架构而不必使用第二个数据库 [英] Python Sqlite3: Create a schema without having to use a second database

问题描述

我想在python中为sqlite3数据库创建模式，但是sqlite似乎不支持 CREATE SCHEMA （ sqlite文档）。我研究了 ATTACH ，它似乎可以通过使用第二个数据库来完成这项工作，但我只需要一个具有模式的数据库。

I would like to create a schema for a sqlite3 database in python, but sqlite does not appear to support CREATE SCHEMA (sqlite docs). I've looked into ATTACH, and it seems like it would do the job by using a second database but I only need one database that has a schema.

我想按照以下方式做些事情：

I would like to do something along these lines:

import sqlite3
db = sqlite3.connect('db_file.db')
db.execute("CREATE TABLE my_schema.my_table(column TYPE);")
db.commit()

但是，它会引发异常：

Traceback (most recent call last):
  File "C:/Users/Mod/Projects/sandbox/test_db.py", line 8, in <module>
    db.execute("CREATE TABLE my_schema.my_table(column TYPE);")
OperationalError: unknown database my_schema

我知道我可以像这样使用 ATTACH ...

I know I can use ATTACH like so...

import sqlite3
db = sqlite3.connect('db_file.db')
db.execute("ATTACH DATABASE 'db_file_2.db' AS 'my_schema';")
db.execute("CREATE TABLE my_schema.my_table(column TYPE);")
db.commit()

...但是有没有办法创建没有第二个数据库的架构？

...but is there any way to create a schema without a second database?

推荐答案

主数据库始终被命名为 main ，您不能更改该名称。

The main database is always named main, you cannot change that name.

您可以只需创建一个内存数据库并使用任意名称将数据库附加到数据库：

You can just create an in-memory database and attach your database to that using an arbitrary name:

conn = sqlite3.connect(':memory:')
conn.execute("attach ? as 'schemaname'", (filename,))

但是，如果要使用数据库作为af对于主要的MySQL数据库，我敦促您查看 SQLAlchemy 以便在此处处理数据库抽象并让它进行担心为MySQL或SQLite生成正确的SQL。

However, if you are going to be using the database as a fallback for a main MySQL database, I urge you to look in to SQLAlchemy to handle the database abstraction here and let it worry about generating the correct SQL for either MySQL or SQLite.

这篇关于Python Sqlite3：创建架构而不必使用第二个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！