Database#

The primary interface for interacting with your database is the sqlservice.database.Database class. It integrates with SQLAlchemy engines, sessions, and your declarative base model’s metadata to provide an abstraction layer on top of it.

Before we get to the good stuff, let’s first start by creating our database object:

from sqlservice import Database

# Let's assume you've define the User, UserAbout, UserDevice, and UserDeviceKey
# as illustrated in the "Model" section of the docs in a module called models.py
# with a declarative base named Model.
from models import Model

db = Database("sqlite://", model_class=Model)

Note

For details on the available configuration values, see sqlservice.database.Database.

The Database class can be used as a provider of database connections and sessions using the 2.0 style of SQLAlchemy:

conn = db.connect()
session = db.session()

# Or as context-managers...
with db.connect() as conn:
    # high-level DB-API connection

with db.session() as session:
    # ORM session, commit as you go

with db.begin() as session:
    # ORM session, automatic commit when context-manager exits

Engine#

Nothing fancy here. The sqlservice.database.Database.engine` is created using sqlalchemy.create_engine under the hood using the database settings provided. Typically, the engine won’t be used directly. Instead, database connections and sessions can be created using these methods:

sqlservice.database.Database.connect() - Returns an instance of sqlalchemy.Connection
sqlservice.database.Database.session() - Returns an instance of sqlservice.session.Session` which is a subclass of sqlalchemy.orm.Session
sqlservice.database.Database.begin() - Begins a transaction and returns an instance of sqlservice.session.Session`

Session#

The sqlservice.database.Database.session() and sqlservice.database.Database.begin() methods are factories that provide ORM sessions. The main difference is that begin() is a context-manager that will start a transaction when entering the context-manager, commit the transaction on exit, and automatically rollback if an unhandled exception occurs.

The default class used to create sessions is sqlservice.session.Session which extends sqlalchemy.orm.Session. You can override the session class using Database(session_class=MySession).

For more details, see the sqlservice.session module.

ORM Models#

The declarative base model passed into Database (or autogenerated when excluded) has its metadata available at sqlservice.database.Database.metadata. There are several metadata based methods available.

Create Model Tables#

Create all ORM model tables with:

db.create_all()

This will issue the appropriate SQL DDL statements that can get your database up and running quickly. For full migration integration, see alembic.

Drop Model Tables#

Drop all ORM model tables with:

db.drop_all()

Reflect Models#

Reflect existing database schema without predefining ORM models or Table objects:

db.reflect()
print(db.tables)

For more details, see the sqlservice.database module.