Database helpers¶

database_exists¶

sqlalchemy_utils.functions.database_exists(url)[source]¶

Check if a database exists.

Parameters:	url – A SQLAlchemy engine URL.

Performs backend-specific testing to quickly determine if a database exists on the server.

database_exists('postgresql://postgres@localhost/name')  #=> False
create_database('postgresql://postgres@localhost/name')
database_exists('postgresql://postgres@localhost/name')  #=> True

Supports checking against a constructed URL as well.

engine = create_engine('postgresql://postgres@localhost/name')
database_exists(engine.url)  #=> False
create_database(engine.url)
database_exists(engine.url)  #=> True

create_database¶

sqlalchemy_utils.functions.create_database(url, encoding='utf8', template=None)[source]¶

Issue the appropriate CREATE DATABASE statement.

Parameters:	url – A SQLAlchemy engine URL. encoding – The encoding to create the database as. template – The name of the template from which to create the new database. At the moment only supported by PostgreSQL driver.

To create a database, you can pass a simple URL that would have been passed to create_engine.

create_database('postgresql://postgres@localhost/name')

You may also pass the url from an existing engine.

create_database(engine.url)

Has full support for mysql, postgres, and sqlite. In theory, other database engines should be supported.

drop_database¶

sqlalchemy_utils.functions.drop_database(url)[source]¶

Issue the appropriate DROP DATABASE statement.

Parameters:	url – A SQLAlchemy engine URL.

Works similar to the create_database method in that both url text and a constructed url are accepted.

drop_database('postgresql://postgres@localhost/name')
drop_database(engine.url)

has_index¶

sqlalchemy_utils.functions.has_index(column_or_constraint)[source]¶

Return whether or not given column or the columns of given foreign key constraint have an index. A column has an index if it has a single column index or it is the first column in compound column index.

A foreign key constraint has an index if the constraint columns are the first columns in compound column index.

Parameters:	column_or_constraint – SQLAlchemy Column object or SA ForeignKeyConstraint object

from sqlalchemy_utils import has_index


class Article(Base):
    __tablename__ = 'article'
    id = sa.Column(sa.Integer, primary_key=True)
    title = sa.Column(sa.String(100))
    is_published = sa.Column(sa.Boolean, index=True)
    is_deleted = sa.Column(sa.Boolean)
    is_archived = sa.Column(sa.Boolean)

    __table_args__ = (
        sa.Index('my_index', is_deleted, is_archived),
    )


table = Article.__table__

has_index(table.c.is_published) # True
has_index(table.c.is_deleted)   # True
has_index(table.c.is_archived)  # False

Also supports primary key indexes

from sqlalchemy_utils import has_index


class ArticleTranslation(Base):
    __tablename__ = 'article_translation'
    id = sa.Column(sa.Integer, primary_key=True)
    locale = sa.Column(sa.String(10), primary_key=True)
    title = sa.Column(sa.String(100))


table = ArticleTranslation.__table__

has_index(table.c.locale)   # False
has_index(table.c.id)       # True

This function supports foreign key constraints as well

class User(Base):
    __tablename__ = 'user'
    first_name = sa.Column(sa.Unicode(255), primary_key=True)
    last_name = sa.Column(sa.Unicode(255), primary_key=True)

class Article(Base):
    __tablename__ = 'article'
    id = sa.Column(sa.Integer, primary_key=True)
    author_first_name = sa.Column(sa.Unicode(255))
    author_last_name = sa.Column(sa.Unicode(255))
    __table_args__ = (
        sa.ForeignKeyConstraint(
            [author_first_name, author_last_name],
            [User.first_name, User.last_name]
        ),
        sa.Index(
            'my_index',
            author_first_name,
            author_last_name
        )
    )

table = Article.__table__
constraint = list(table.foreign_keys)[0].constraint

has_index(constraint)  # True

has_unique_index¶

sqlalchemy_utils.functions.has_unique_index(column_or_constraint)[source]¶

Return whether or not given column or given foreign key constraint has a unique index.

A column has a unique index if it has a single column primary key index or it has a single column UniqueConstraint.

A foreign key constraint has a unique index if the columns of the constraint are the same as the columns of table primary key or the coluns of any unique index or any unique constraint of the given table.

Parameters:	column – SQLAlchemy Column object

from sqlalchemy_utils import has_unique_index


class Article(Base):
    __tablename__ = 'article'
    id = sa.Column(sa.Integer, primary_key=True)
    title = sa.Column(sa.String(100))
    is_published = sa.Column(sa.Boolean, unique=True)
    is_deleted = sa.Column(sa.Boolean)
    is_archived = sa.Column(sa.Boolean)


table = Article.__table__

has_unique_index(table.c.is_published) # True
has_unique_index(table.c.is_deleted)   # False
has_unique_index(table.c.id)           # True

This function supports foreign key constraints as well

class User(Base):
    __tablename__ = 'user'
    first_name = sa.Column(sa.Unicode(255), primary_key=True)
    last_name = sa.Column(sa.Unicode(255), primary_key=True)

class Article(Base):
    __tablename__ = 'article'
    id = sa.Column(sa.Integer, primary_key=True)
    author_first_name = sa.Column(sa.Unicode(255))
    author_last_name = sa.Column(sa.Unicode(255))
    __table_args__ = (
        sa.ForeignKeyConstraint(
            [author_first_name, author_last_name],
            [User.first_name, User.last_name]
        ),
        sa.Index(
            'my_index',
            author_first_name,
            author_last_name,
            unique=True
        )
    )

table = Article.__table__
constraint = list(table.foreign_keys)[0].constraint

has_unique_index(constraint)  # True

Raises:	TypeError – if given column does not belong to a Table object

json_sql¶

sqlalchemy_utils.functions.json_sql(value, scalars_to_json=True)[source]¶

Convert python data structures to PostgreSQL specific SQLAlchemy JSON constructs. This function is extremly useful if you need to build PostgreSQL JSON on python side.

Note

This function needs PostgreSQL >= 9.4

Scalars are converted to to_json SQLAlchemy function objects

json_sql(1)     # Equals SQL: to_json(1)

json_sql('a')   # to_json('a')

Mappings are converted to json_build_object constructs

json_sql({'a': 'c', '2': 5})  # json_build_object('a', 'c', '2', 5)

Sequences (other than strings) are converted to json_build_array constructs

json_sql([1, 2, 3])  # json_build_array(1, 2, 3)

You can also nest these data structures

json_sql({'a': [1, 2, 3]})
# json_build_object('a', json_build_array[1, 2, 3])

Parameters:	value – value to be converted to SQLAlchemy PostgreSQL function constructs

render_expression¶

sqlalchemy_utils.functions.render_expression(expression, bind, stream=None)[source]¶

Generate a SQL expression from the passed python expression.

Only the global variable, engine, is available for use in the expression. Additional local variables may be passed in the context parameter.

Note this function is meant for convenience and protected usage. Do NOT blindly pass user input to this function as it uses exec.

Parameters:	bind – A SQLAlchemy engine or bind URL. stream – Render all DDL operations to the stream.

render_statement¶

sqlalchemy_utils.functions.render_statement(statement, bind=None)[source]¶

Generate an SQL expression string with bound parameters rendered inline for the given SQLAlchemy statement.

Parameters:	statement – SQLAlchemy Query object. bind – Optional SQLAlchemy bind, if None uses the bind of the given query object.