Tables and Columns

Each database in the real database is represented by a table class in the Python land. These table classes map virtual columns to real columns, allowing access of them in your code easily and intuitively.

Each table class subclasses an instance of the table base - a special object that stores some metadata about the current database the application is running. The function table_base() can be used to create a new table base object for subclassing.

from asyncqlio import table_base
Table = table_base()

Internally, this makes a clone of a Table object backed by the TableMeta which is then used to customize your tables.

Defining Tables

Tables are defined by making a new class inheriting from your table base object, corresponding to a table in the database.

class Server(Table, table_name="server"):
    ...

Note that table_name is passed explicitly here - this is optional. If no table name is passed a name will be automatically generated from the table name made all lowercase.

Table classes themselves are technically instances of TableMeta, and as such all the methods of a TableMeta object are available on a table object.

class asyncqlio.orm.schema.table.TableMeta(tblname: str, tblbases: tuple, class_body: dict, register: bool = True, *args, **kwargs)

Bases: type

The metaclass for a table object. This represents the “type” of a table class.

Creates a new Table instance.

Parameters:

• register – Should this table be registered in the TableMetadata?
• table_name – The name for this table.

columns

Returns:A list of Column this Table has.

iter_relationships() → typing.Generator[[asyncqlio.orm.schema.relationship.Relationship, NoneType], NoneType]

Returns:A generator that yields Relationship objects for this table.

iter_columns() → typing.Generator[[asyncqlio.orm.schema.column.Column, NoneType], NoneType]

Returns:A generator that yields Column objects for this table.

get_column(column_name: str, *, raise_si: bool = False) → typing.Union[asyncqlio.orm.schema.column.Column, NoneType]

Gets a column by name.

Parameters:column_name –

The column name to lookup.

This can be one of the following:

• The column’s name
• The column’s alias_name() for this table

Returns:The Column associated with that name, or None if no column was found.

get_relationship(relationship_name) → typing.Union[asyncqlio.orm.schema.relationship.Relationship, NoneType]

Gets a relationship by name.

Parameters:relationship_name – The name of the relationship to get. Returns:The Relationship associated with that name, or None if it doesn’t existr.

primary_key

Getter:The PrimaryKey for this table. Setter:A new PrimaryKey for this table.

Note

A primary key will automatically be calculated from columns at define time, if any columns have primary_key set to True.

mro() → list

return a type’s method resolution order

Adding Columns

Columns on tables are represented by Column objects - these objects are strictly only on the table classes and not the rows. They provide useful functions for query building and an easy way to map data from a request onto a table.

Columns are added to table objects with a simple attribute setting syntax. To add a column to a table, you only need to do this:

class Server(Table, table_name="server"):
    id = Column(Int(), primary_key=True, unique=True)

In this example, a column called id is added to the table with the type Int, and is set to be a primary key and unique. Of course, you can name it anything and add a different type; all that matters is that the object is a Column.

class asyncqlio.orm.schema.column.Column

Bases: object

Represents a column in a table in a database.

class MyTable(Table):
    id = Column(Integer, primary_key=True)

The id column will mirror the ID of records in the table when fetching, etc. and can be set on a record when storing in a table.

sess = db.get_session()
user = await sess.select(User).where(User.id == 2).first()

print(user.id)  # 2

Parameters:

• type – The ColumnType that represents the type of this column.
• primary_key – Is this column the table’s Primary Key (the unique identifier that identifies each row)?
• nullable – Can this column be NULL?
• default – The client-side default for this column. If no value is provided when inserting, this value will automatically be added to the insert query.
• autoincrement – Should this column auto-increment? This will create a serial sequence.
• index – Should this column be indexed?
• unique – Is this column unique?
• foreign_key – The ForeignKey associated with this column.

__init__()

Parameters:

• type – The ColumnType that represents the type of this column.
• primary_key – Is this column the table’s Primary Key (the unique identifier that identifies each row)?
• nullable – Can this column be NULL?
• default – The client-side default for this column. If no value is provided when inserting, this value will automatically be added to the insert query.
• autoincrement – Should this column auto-increment? This will create a serial sequence.
• index – Should this column be indexed?
• unique – Is this column unique?
• foreign_key – The ForeignKey associated with this column.

name = None

The name of the column. This can be manually set, or automatically set when set on a table.

table = None

The Table instance this Column is associated with.

type = None

The ColumnType that represents the type of this column.

default = None

The default for this column.

primary_key = None

If this Column is a primary key.

nullable = None

If this Column is nullable.

autoincrement = None

If this Column is to autoincrement.

indexed = None

If this Column is indexed.

unique = None

If this Column is unique.

foreign_key = None

The foreign key associated with this column.

__set_name__(owner, name)

Called to update the table and the name of this Column.

Parameters:

• owner – The Table this Column is on.
• name – The str name of this table.

eq(other) → asyncqlio.orm.operators.Eq

Checks if this column is equal to something else.

Note

This is the easy way to check if a column equals another column in a WHERE clause, because the default __eq__ behaviour returns a bool rather than an operator.

ne(other) → asyncqlio.orm.operators.NEq

Checks if this column is not equal to something else.

Note

This is the easy way to check if a column doesn’t equal another column in a WHERE clause, because the default __ne__ behaviour returns a bool rather than an operator.

asc() → asyncqlio.orm.operators.AscSorter

Returns the ascending sorter operator for this column.

desc() → asyncqlio.orm.operators.DescSorter

Returns the descending sorter operator for this column.

set(value: typing.Any) → asyncqlio.orm.operators.ValueSetter

Sets this column in a bulk update.

incr(value: typing.Any) → asyncqlio.orm.operators.IncrementSetter

Increments this column in a bulk update.

__add__(other)

Magic method for incr()

decr(value: typing.Any) → asyncqlio.orm.operators.DecrementSetter

Decrements this column in a bulk update.

__sub__(other)

Magic method for decr()

quoted_fullname_with_table(table: asyncqlio.orm.schema.table.TableMeta) → str

Gets the quoted fullname with a table. This is used for columns with alias tables.

Parameters:table – The Table or AliasedTable to use. Returns:

quoted_name

Gets the quoted name for this column.

This returns the column name in “column” format.

quoted_fullname

Gets the full quoted name for this column.

This returns the column name in “table”.”column” format.

foreign_column

Returns:The foreign Column this is associated with, or None otherwise.

__delattr__

Implement delattr(self, name).

__dir__() → list

default dir() implementation

__format__()

default object formatter

__getattribute__

Return getattr(self, name).

__new__()

Create and return a new object. See help(type) for accurate signature.

__reduce__()

helper for pickle

__reduce_ex__()

helper for pickle

__setattr__

Implement setattr(self, name, value).

__sizeof__() → int

size of object in memory, in bytes

__str__

Return str(self).

__subclasshook__()

Abstract classes can override this to customize issubclass().

This is invoked early on by abc.ABCMeta.__subclasscheck__(). It should return True, False or NotImplemented. If it returns NotImplemented, the normal algorithm is used. Otherwise, it overrides the normal algorithm (and the outcome is cached).

__weakref__

list of weak references to the object (if defined)

alias_name(table=None, quoted: bool = False) → str

Gets the alias name for a column, given the table.

This is in the format of t_<table name>_<column_name>.

Parameters:

• table – The Table to use to generate the alias name. This is useful for aliased tables.
• quoted – Should the name be quoted?

Returns:

A str representing the alias name.

Primary Keys

Tables can have primary keys, which uniquely identify rows in a table, and are made up of from 1 to N columns in the table. Typically keys with multiple columns are known as compound primary keys. For convenience, an object provides primary keys on table classes.

class asyncqlio.orm.schema.table.PrimaryKey(*cols: asyncqlio.orm.schema.column.Column)

Bases: object

Represents the primary key of a table.

A primary key can be on any 1 to N columns in a table.

class Something(Table):
    first_id = Column(Integer)
    second_id = Column(Integer)

pkey = PrimaryKey(Something.first_id, Something.second_id)
Something.primary_key = pkey

Alternatively, the primary key can be automatically calculated by passing primary_key=True to columns in their constructor:

class Something(Table):
    id = Column(Integer, primary_key=True)

print(Something.primary_key)

Primary keys will be automatically generated on a table when multiple columns are marked as

columns = None

A list of Column that this primary key encompasses.

table = None

The table this primary key is bound to.

primary_key in the constructor, but a PrimaryKey object can be constructed manually and set on Table.primary_key.

Column Types

All columns in both SQL and Python have a type - the column type. This defines what data they store, what operators they can use, and so on. In asyncqlio, the first parameter passed to a column is its type; this gives it extra functionality and defines how it stores data passed to it both from the user and the database.

For implementing your own types, see creating-col-types.

exception asyncqlio.orm.schema.types.ColumnValidationError

Bases: asyncqlio.exc.DatabaseException

Raised when a column fails validation.

with_traceback()

Exception.with_traceback(tb) – set self.__traceback__ to tb and return self.

class asyncqlio.orm.schema.types.ColumnType

Bases: abc.ABC

Implements some underlying mechanisms for a Column.

The only method that is required to be implemented on children is ColumnType.sql() - which is used in CREATE TABLE declarations, etc. ColumnType.on_set(), ColumnType.on_get() and so on are not required to be implemented - the defaults will work fine.

The ColumnType is responsible for actually loading the data from the row’s internal storage and to the user code.

# we hate fun
def on_get(self, row, column):
    return "lol"

...

# row is a random row object
# load the `fun` column which has this weird type
value = row.fun
print(value)  # "lol", regardless of what was stored in the database.

Accordingly, it is also responsible for storing the data into the row’s internal storage.

def on_set(*args, **kwargs):
    return None

row.not_fun = 1
print(row.not_fun)  # None - no value was stored in the row

To actually insert a value into the row’s storage table, use ColumnType.store_value(). Correspondingly, loading a value from the row’s storage table can be achieved with ColumnType.load_value(). These functions should be used, as they are guarenteed to work across all versions.

Columns will proxy bad attribute accesses from the Column object to this type object - meaning types can implement custom operators, if applicable.

class User(Table):
    id = Column(MyWeirdType())

...

# MyWeirdType implements `.contains`
# the contains call is proxied to (MyWeirdType instance).contains("heck")
q = await sess.select(User).where(User.id.contains("heck")).first()

column

The column this type object is associated with.

sql() → str

Returns:The str SQL name of this type.

validate_set(row: asyncqlio.orm.schema.table.Table, value: typing.Any) → bool

Validates that the item being set is valid. This is called by the default on_set.

Parameters:

• row – The row being set.
• value – The value to set.

Returns:

A bool indicating if this is valid or not.

store_value(row: asyncqlio.orm.schema.table.Table, value: typing.Any)

Stores a value in the row’s storage table.

This is for internal usage only.

Parameters:

• row – The row to store in.
• value – The value to store in the row.

on_set(row: asyncqlio.orm.schema.table.Table, value: typing.Any) → typing.Any

Called when a value is a set on this column.

This is the default method - it will call ColumnType.validate_set() to validate the type before storing it. This is useful for simple column types.

Parameters:

• row – The row this value is being set on.
• value – The value being set.

on_get(row: asyncqlio.orm.schema.table.Table) → typing.Any

Called when a value is retrieved from this column.

Parameters:row – The row that is being retrieved. Returns:The value of the row’s internal storage.

classmethod create_default() → asyncqlio.orm.schema.types.ColumnType

Creates the default object for this table in the event that a type is passed to a column, instead of an instance.

in_(*args) → asyncqlio.orm.operators.In

Returns an IN operator, checking if a value in this column is in a tuple of items.

Parameters:args – The items to check.

class asyncqlio.orm.schema.types.String(size: int = -1)

Bases: asyncqlio.orm.schema.types.ColumnType

Represents a VARCHAR() type.

size = None

The max size of this String.

like(other: str) → asyncqlio.orm.operators.Like

Returns a LIKE operator, checking if this column is LIKE another string.

Parameters:other – The other string to check.

ilike(other: str) → typing.Union[asyncqlio.orm.operators.ILike, asyncqlio.orm.operators.HackyILike]

Returns an ILIKE operator, checking if this column is case-insensitive LIKE another string.

Warning

This is not supported in all DB backends.

Parameters:other – The other string to check.

create_default() → asyncqlio.orm.schema.types.ColumnType

Creates the default object for this table in the event that a type is passed to a column, instead of an instance.

in_(*args) → asyncqlio.orm.operators.In

Returns an IN operator, checking if a value in this column is in a tuple of items.

Parameters:args – The items to check.

on_get(row: asyncqlio.orm.schema.table.Table) → typing.Any

Called when a value is retrieved from this column.

Parameters:row – The row that is being retrieved. Returns:The value of the row’s internal storage.

on_set(row: asyncqlio.orm.schema.table.Table, value: typing.Any) → typing.Any

Called when a value is a set on this column.

This is the default method - it will call ColumnType.validate_set() to validate the type before storing it. This is useful for simple column types.

Parameters:

• row – The row this value is being set on.
• value – The value being set.

store_value(row: asyncqlio.orm.schema.table.Table, value: typing.Any)

Stores a value in the row’s storage table.

This is for internal usage only.

Parameters:

• row – The row to store in.
• value – The value to store in the row.

class asyncqlio.orm.schema.types.Text

Bases: asyncqlio.orm.schema.types.String

Represents a TEXT type. TEXT type columns are very similar to String type objects, except that they have no size limit.

Note

This is preferable to the String type in some databases.

Warning

This is deprecated in MSSQL.

create_default() → asyncqlio.orm.schema.types.ColumnType

Creates the default object for this table in the event that a type is passed to a column, instead of an instance.

ilike(other: str) → typing.Union[asyncqlio.orm.operators.ILike, asyncqlio.orm.operators.HackyILike]

Returns an ILIKE operator, checking if this column is case-insensitive LIKE another string.

Warning

This is not supported in all DB backends.

Parameters:other – The other string to check.

in_(*args) → asyncqlio.orm.operators.In

Returns an IN operator, checking if a value in this column is in a tuple of items.

Parameters:args – The items to check.

like(other: str) → asyncqlio.orm.operators.Like

Returns a LIKE operator, checking if this column is LIKE another string.

Parameters:other – The other string to check.

on_get(row: asyncqlio.orm.schema.table.Table) → typing.Any

Called when a value is retrieved from this column.

Parameters:row – The row that is being retrieved. Returns:The value of the row’s internal storage.

on_set(row: asyncqlio.orm.schema.table.Table, value: typing.Any) → typing.Any

Called when a value is a set on this column.

This is the default method - it will call ColumnType.validate_set() to validate the type before storing it. This is useful for simple column types.

Parameters:

• row – The row this value is being set on.
• value – The value being set.

store_value(row: asyncqlio.orm.schema.table.Table, value: typing.Any)

Stores a value in the row’s storage table.

This is for internal usage only.

Parameters:

• row – The row to store in.
• value – The value to store in the row.

class asyncqlio.orm.schema.types.Boolean

Bases: asyncqlio.orm.schema.types.ColumnType

Represents a BOOL type.

create_default() → asyncqlio.orm.schema.types.ColumnType

Creates the default object for this table in the event that a type is passed to a column, instead of an instance.

in_(*args) → asyncqlio.orm.operators.In

Returns an IN operator, checking if a value in this column is in a tuple of items.

Parameters:args – The items to check.

on_get(row: asyncqlio.orm.schema.table.Table) → typing.Any

Called when a value is retrieved from this column.

Parameters:row – The row that is being retrieved. Returns:The value of the row’s internal storage.

on_set(row: asyncqlio.orm.schema.table.Table, value: typing.Any) → typing.Any

Called when a value is a set on this column.

This is the default method - it will call ColumnType.validate_set() to validate the type before storing it. This is useful for simple column types.

Parameters:

• row – The row this value is being set on.
• value – The value being set.

store_value(row: asyncqlio.orm.schema.table.Table, value: typing.Any)

Stores a value in the row’s storage table.

This is for internal usage only.

Parameters:

• row – The row to store in.
• value – The value to store in the row.

class asyncqlio.orm.schema.types.Integer

Bases: asyncqlio.orm.schema.types.ColumnType

Represents an INTEGER type.

Warning

This represents a 32-bit integer (2**31-1 to -2**32)

validate_set(row, value: typing.Any)

Checks if this int is in range for the type.

create_default() → asyncqlio.orm.schema.types.ColumnType

Creates the default object for this table in the event that a type is passed to a column, instead of an instance.

in_(*args) → asyncqlio.orm.operators.In

Returns an IN operator, checking if a value in this column is in a tuple of items.

Parameters:args – The items to check.

on_get(row: asyncqlio.orm.schema.table.Table) → typing.Any

Called when a value is retrieved from this column.

Parameters:row – The row that is being retrieved. Returns:The value of the row’s internal storage.

store_value(row: asyncqlio.orm.schema.table.Table, value: typing.Any)

Stores a value in the row’s storage table.

This is for internal usage only.

Parameters:

• row – The row to store in.
• value – The value to store in the row.

class asyncqlio.orm.schema.types.SmallInt

Bases: asyncqlio.orm.schema.types.Integer

Represents a SMALLINT type.

create_default() → asyncqlio.orm.schema.types.ColumnType

Creates the default object for this table in the event that a type is passed to a column, instead of an instance.

in_(*args) → asyncqlio.orm.operators.In

Returns an IN operator, checking if a value in this column is in a tuple of items.

Parameters:args – The items to check.

on_get(row: asyncqlio.orm.schema.table.Table) → typing.Any

Called when a value is retrieved from this column.

Parameters:row – The row that is being retrieved. Returns:The value of the row’s internal storage.

store_value(row: asyncqlio.orm.schema.table.Table, value: typing.Any)

Stores a value in the row’s storage table.

This is for internal usage only.

Parameters:

• row – The row to store in.
• value – The value to store in the row.

class asyncqlio.orm.schema.types.BigInt

Bases: asyncqlio.orm.schema.types.Integer

Represents a BIGINT type.

create_default() → asyncqlio.orm.schema.types.ColumnType

Creates the default object for this table in the event that a type is passed to a column, instead of an instance.

in_(*args) → asyncqlio.orm.operators.In

Returns an IN operator, checking if a value in this column is in a tuple of items.

Parameters:args – The items to check.

on_get(row: asyncqlio.orm.schema.table.Table) → typing.Any

Called when a value is retrieved from this column.

Parameters:row – The row that is being retrieved. Returns:The value of the row’s internal storage.

store_value(row: asyncqlio.orm.schema.table.Table, value: typing.Any)

Stores a value in the row’s storage table.

This is for internal usage only.

Parameters:

• row – The row to store in.
• value – The value to store in the row.

class asyncqlio.orm.schema.types.Timestamp

Bases: asyncqlio.orm.schema.types.ColumnType

Represents a TIMESTAMP type.

create_default() → asyncqlio.orm.schema.types.ColumnType

Creates the default object for this table in the event that a type is passed to a column, instead of an instance.

in_(*args) → asyncqlio.orm.operators.In

Returns an IN operator, checking if a value in this column is in a tuple of items.

Parameters:args – The items to check.

on_get(row: asyncqlio.orm.schema.table.Table) → typing.Any

Called when a value is retrieved from this column.

Parameters:row – The row that is being retrieved. Returns:The value of the row’s internal storage.

on_set(row: asyncqlio.orm.schema.table.Table, value: typing.Any) → typing.Any

Called when a value is a set on this column.

This is the default method - it will call ColumnType.validate_set() to validate the type before storing it. This is useful for simple column types.

Parameters:

• row – The row this value is being set on.
• value – The value being set.

store_value(row: asyncqlio.orm.schema.table.Table, value: typing.Any)

Stores a value in the row’s storage table.

This is for internal usage only.

Parameters:

• row – The row to store in.
• value – The value to store in the row.

Row Objects

In asyncqlio, a row object is simply an instance of a Table. To create one, you can call the table object (much like creating a normal instance of a class):

row = User()

To provide values for the columns, you can pass keyword arguments to the constructor corresponding with the names of the columns, like so:

row = User(id=1, name="heck")