SQLA-Wrapper

API

This part of the documentation documents all the public classes and functions in SQLA-Wrapper.

Configuration

class sqla_wrapper.SQLAlchemy(url='sqlite://', *, metadata=None, metaclass=None, model_class=<class 'sqla_wrapper.model.Model'>, scopefunc=None, **options)

This class is used to easily instantiate a SQLAlchemy connection to a database, to provide a base class for your models, and to get a session to interact with them.

db = SQLAlchemy(_uri_to_database_)

class User(db.Model):
    login = Column(String(80), unique=True)
    passw_hash = Column(String(80))

Warning

IMPORTANT

In a web application or a multithreaded environment you need to call session.remove() when a request/thread ends. Use your framework’s after_request hook, to do that. For example, in Flask:

app = Flask(…)
db = SQLAlchemy(…)

@app.teardown_appcontext
def shutdown(response=None):
    db.remove()
    return response

Use the db to interact with the data:

user = User('tiger')
db.add(user)
db.commit()
# etc

To query, you can use db.query

db.query(User).all()
db.query(User).filter_by(login == 'tiger').first()
# etc.

Tip

Scoping

By default, sessions are scoped to the current thread, but he SQLAlchemy documentation recommends scoping the session to something more application-specific if you can, like a web request in a web app.

To do that, you can use the scopefunc argument, passing a function that returns something unique (and hashable) like a request.

create_all(*args, **kwargs)

Creates all tables.

drop_all(*args, **kwargs)

Drops all tables.

metadata

Proxy for Model.metadata.

reconfigure(**kwargs)

Updates the session options.

Connection URI Format

For a complete list of connection URIs head over to the SQLAlchemy documentation under (Supported Databases). This section shows some common connection strings.

SQLAlchemy indicates the source of an Engine as a URI combined with optional keyword arguments to specify options for the Engine. The form of the URI is:

dialect+driver://username:password@host:port/database

Many of the parts in the string are optional. If no driver is specified the default one is selected (make sure to not include the + in that case).

Postgres:

postgresql://scott:tiger@localhost/mydatabase

MySQL:

mysql://scott:tiger@localhost/mydatabase

Oracle:

oracle://scott:tiger@127.0.0.1:1521/sidname

SQLite (note the four leading slashes):

sqlite:////absolute/path/to/foo.db

SQLite in memory:

sqlite:///:memory:

Models

class sqla_wrapper.BaseQuery(entities, session=None)

The default query object used for models. This can be subclassed and replaced for individual models by setting the query_cls attribute.

This is a subclass of a standard SQLAlchemy Query class and has all the methods of a standard query as well.

all()

Return the results represented by this query as a list. This results in an execution of the underlying query.

order_by(*criterion)

apply one or more ORDER BY criterion to the query and return the newly resulting query.

limit(limit)

Apply a LIMIT to the query and return the newly resulting query.

offset(offset)

Apply an OFFSET to the query and return the newly resulting query.

first()

Return the first result of this query or None if the result doesn‘t contain any rows. This results in an execution of the underlying query.

first_or_error(error)

Like first() but raises an error if not found instead of returning None.

get_or_error(uid, error)

Like get() but raises an error if not found instead of returning None.

paginate(**kwargs)

Paginate this results.

Returns an Paginator object.