Python Sqlite3: Create a schema without having to use a second database

Question

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

However, it throws an exception:

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

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?

Answer 1

In our project, we are using Postgresql as the production database with separate schemas for each service. I wanted to use Sqlite3 for unit testing. So I needed a way to create a schema.

The accepted answer helped me figure out the solution. I use SQLAlchemy in my project (in Flask along with Flask-SQLAlchemy).

I want to share how I did it here in the hope that it may help someone.

Suppose I am using in-memory database, and the schema I want to use is my_schema. The commands would look like the following:

from sqlalchemy import create_engine

eng = create_engine("sqlite://")
conn = eng.connect()
conn.execute("attach ':memory:' as my_schema")

You could find more information about SQLite usage in SQLAlchemy at: http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html

In case you are using Flask-SQLAlchemy, you could do the same as the above example in the following way:

from flask_sqlalchemy import SQLAlchemy

SQLALCHEMY_DATABASE_URI = 'sqlite://'

db = SQLAlchemy()
# Create Flask app object, attach it to db object, and then do:
with app.app_context():
    with db.engine.connect() as con:
        con.execute("attach ':memory:' as svc_kpi")