SQLAlchemy-Wrapper

API

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

Configuration

class sqlalchemy_wrapper.SQLAlchemy(uri='sqlite://', app=None, echo=False, pool_size=None, pool_timeout=None, pool_recycle=None, convert_unicode=True, isolation_level=None, record_queries=False, metadata=None, query_cls=<class 'sqlalchemy_wrapper.helpers.BaseQuery'>, model_class=<class 'sqlalchemy_wrapper.helpers.Model'>, **session_options)

This class is used to instantiate a SQLAlchemy connection to a database.

db = SQLAlchemy(_uri_to_database_)

The class also provides access to all the SQLAlchemy functions from the sqlalchemy and sqlalchemy.orm modules. So you can declare models like this:

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

In a web application or a multithreaded environment you need to call db.session.remove() after each response, and db.session.rollback() if an error occurs.

However, there’s no need to do it if your application object has an after_request and on_exception hooks, just pass your application object at creation:

app = Flask(__name__)
db = SQLAlchemy('sqlite://', app=app)

or later:

db = SQLAlchemy()

app = Flask(__name__)
db.init_app(app)

Check types carefully

Don’t perform type or isinstance checks against db.Table, which emulates Table behavior but is not a class. db.Table exposes the Table interface, but is a function which allows omission of metadata.

add(*args, **kwargs)

Proxy for self.session.add.

apply_driver_hacks(options)

This method is called before engine creation and used to inject driver specific hacks into the options.

The options parameter is a dictionary of keyword arguments that will then be used to call the sqlalchemy.create_engine() function.

The default implementation provides some saner defaults for things like pool sizes for MySQL and sqlite.

commit()

Proxy for self.session.commit.

create_all()

Creates all tables.

delete(*args, **kwargs)

Proxy for self.session.delete.

drop_all()

Drops all tables.

engine

Gives access to the engine.

flush(*args, **kwargs)

Proxy for self.session.flush.

get_engine(current_app)

Proxy for compatibility with flask-debugtoolbar

init_app(app)

In a web application or a multithreaded environment you need to call db.session.remove() after each response, and db.session.rollback() if an error occurs.

This callback can be used to setup the application’s after_request and on_exception hooks to do that automatically.

Flask, Bottle and webpy are supported. Other frameworks might also apply if their hook syntax are the same.

make_declarative_base(model_class, metadata=None)

Creates the declarative base.

metadata

Proxy for Model.metadata.

query

Proxy for self.session.query.

reflect(meta=None)

Reflects tables from the database.

rollback()

Proxy for self.session.rollback.

set_bottle_hooks(app, shutdown, rollback)

Setup the bottle-specific after_request to call db.session.remove() after each response.

set_flask_hooks(app, shutdown, rollback)

Setup the app.after_request and app.on_exception hooks to call db.session.remove() after each response, and db.session.rollback() if an error occurs.

set_webpy_hooks(app, shutdown, rollback)

Setup the webpy-specific web.unloadhook to call db.session.remove() after each response.

Connection URI Format

For a complete list of connection URIs head over to the SQLAlchemy documentation under (Supported Databases). This here 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 sqlalchemy_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.