API Documentation

Note that most of the SQL shown is informative, not literal. This library never directly inserts user provided values in to the SQL.

Connections

class dqo.Database(sync_src=None, async_src=None, sync_dialect=None, async_dialect=None)

Parameters:

• src – A function returning a database connection, or a connection pool.
• dialect – The database Dilect to speak (optional).

The Database controls connections to your database. The src parameter is required. For example:

db = dqo.Database(
  src=lambda: psycopg2.connect("dbname='mydb'"),
)

If you’re connecting with the async library asyncpg:

db = dqo.Database(
  src=lambda: asyncpg.connect(database='mydb')
)

If you’re doing asyncpg connection pooling:

db = dqo.Database(
  src=asyncpg.create_pool(database='mydb')
)

If you don’t pass in the dialect it will be auto-detected by opening and closing a single connection.

You typically assign a database one of three places…

To either (or both) of the global default databases:

dqo.SYNC_DB = ...
dqo.ASYNC_DB = ...

As the default for a table:

@dqo.Table(sync_db=[...], async_db=[...])
class User:
  [...]

Or bound to a given query:

User.ALL.bind(sync_db=db).first()

dialect

This property returns the dialect associated with this database. (It auto-switches between sync and async depending on context.)

class dqo.Dialect

Parameters:

• version – The database version. Example: 10, ‘9.2.6’, etc.
• lib – The library used. Example: psycopg2, asyncpg, etc.

All parameters are optional, even calling it as function is optional. Examples:

dqo.Dialect.POSTGRES
dqo.Dialect.POSTGRES(10)
dqo.Dialect.POSTGRES(version='9.2.6')
dqo.Dialect.POSTGRES(lib='psycopg2')
dqo.Dialect.POSTGRES(10, lib=asyncpg)

Querying

class dqo.query.Query(tbl)

You don’t instantiate this directly, rather every table will have a reference like User.ALL.

All query objects are immutable. Methods are either builder methods (that return a new query) or terminal (that execute SQL on the database).

Builder Methods

The following methods all return a new immutable query.

bind(db_or_tx)

Parameters:db_or_tx – Either a database or a transaction object.

Specifies what database or connection transaction to use for this query. For example:

db = dqo.Database(src=...)
q = User.ALL.where(...).bind(db)
for user in q:
  # do something

limit(n)

Parameters:int – The max number of rows to return.

Example:

User.ALL.limit(10)

Equivalent to .top(10).

order_by(*columns)

Specifies the ordering for the query. Example:

User.ALL.order_by(User.last_name, User.first_name)

To control the order:

User.ALL.order_by(User.name.asc)
User.ALL.order_by(User.name.desc)

Every call clobbers any previous calls to order_by.

Only effects SELECT queries. Other query types accept but ignore order by directives. They intentionally don’t throw an error, as it’s common to change a select into something else as part of a workflow. Example:

to_delete = User.ALL.where(name='John').order_by(User.kind)
preview(to_delete) # display who's to be deleted
to_delete.delete()

select(*columns)

Parameters:columns – One or more columns to select.

By default all columns on a table are selected. You can customize this by calling select() with one or more columns which should be selected in the query. For instance, to select only:

User.ALL.select(User.id, User.name, User.email)

To select all columns except the user’s email, prepend the negative operator:

User.ALL.select(-User.email)

To add a column (or something else, like a function) for selection, use the positive operator:

User.ALL.select(+dqo.sql.LOWER(User.first_name))

If you send in an explicit list (no +/-) you will replace the existing selected columns. If you pass in +/- columns, you will modify the existing selected columns. You cannot do both in the came call.

set(**kwargs)

Sets values in preperation for an update. Example:

User.ALL.where(id=1).set(name='John').update()

top(int)

Parameters:int – The max number of rows to return.

Example:

User.ALL.top(10)

Equivalent to .limit(10).

top(n)

where(*conditions, **kwargs)

Adds conditions to a query. For example:

User.ALL.where(User.email == 'someone@somewhere.com')

Keyword arguments will be evaluated to fields on the bound table. For example, the above statement could also be written as:

User.ALL.where(email='someone@somewhere.com')

Multiple calls to where() will result in a SQL AND. For example:

User.ALL.where(name='John').where(email='me@here.com')

Would result in:

select * from users
where name='John' and email='me@here.com'

All of the following would also result in the same SQL as above:

User.ALL.where(name='John', email='me@here.com')
User.ALL.where(User.name=='John', User.email=='me@here.com')
User.ALL.where(
  (User.name=='John') & (User.email=='me@here.com')
)

An OR statment can be created with the bitwise or operator:

User.ALL.where(
  (User.name=='John') | (User.email=='me@here.com')
)

When using the & and | operators, make sure you wrap the condition in parentheses as they have lower precedence than others like ==.

Terminal Methods

The following methods all execute SQL and return data, or if executed in an async environment a coroutine.

__aiter__()

All queries are async iterable. For example:

async for user in User.ALL:
  # do something asynchronously

__iter__()

All queries are iterable. For example:

for user in User.ALL:
  # do something

If in a transaction or a with block defining the scope of the connection, the results will stream. If a query has to open its own connection it will load all records before streaming. This is because there is no guarantee an iterator will complete, and waiting for the garbage collector is a fool’s game.

count()

Returns:The number of rows matching the query.

does a count(1) of the existing query. Example:

>>> User.ALL.count()
42

count_by(*columns)

Returns:A dict where the keys are the db values of the columns selected and the values are their counts.

>>> User.ALL.count_by(User.name)
{'John':1, 'Paul':2}

If multiple columns are passed, the keys will be tuples.

>>> User.ALL.count_by(User.first_name, User.last_name)
{('John','Smith'):1, ('Paul','Anderson'):2}

delete()

Returns:The number of rows deleted.

Deletes the records matched by a given query. Example:

>>> User.ALL.where(id=1).delete()
1

first()

Returns:An instance of the selected type or None if not found.

Adds a .limit(1) to a given query and returns the first result (if any). Example:

user = User.ALL.first()

In async code:

user = await User.ALL.first()

insert(*args, **data)

Inserts one or more rows. If keyword arguments are passed, a single row is inserted returning the primary key (if defined). For example:

>>> User.ALL.insert(name='John', email='me@here.org')
42

If you have a dict of values:

user = User.ALL.insert(**{'name':'John', 'email':'me@here.org'})

If a list of dicts are passed, multiple rows are inserted efficiently in a single query, and a list of their primary keys are returned:

>>> User.ALL.insert(
  {'name':'John', 'email':'me@here.org'},
  {'name':'Paul', 'email':'paul@here.org'},
)
[42, 43]

plus(*foreign_keys)

This is very useful syntax sugar for SQL joins. Takes a list of foreign keys to define the paths of the object graph you want

to query. For instance, let’s assume you have:

If I want to query all people with their employer:

for person in Person.ALL.plus(Person.employer):
  print(person, person.employer)

If I want to query all employers with their employees:

for company in Company.ALL.plus(Person.employer):
  print(company)
  for person in company.employees:
    print('\t', person)

If I want to know the industry everyone works in:

q = Person.ALL.plus(Person.employer, Company.industry)
for person in q:
  print(person, person.employer.industry)

If you want the company with its employees and industry:

q = Company.ALL \
    .plus(Person.employer) \
    .plus(Company.industry)
for company in q:
  print(company, 'in', company.industry)
  for person in company.employees:
    print('\t', person)

How about all industries with companies and all their employees:

q = Industry.ALL.plus(Company.industry, Person.employer)
for industry in q:
  print(industry)
  for company in industry.companies:
    print('\t', industry.company)
    for person in company.employees:
      print('\t\t', person)

Every call to this method represents a path in the object graph to be queried, and they can be as long as you wish. If some paths overlap, the individual legs will not be duplicated in the resulting query.

None of these calls will generate O(n) database lookups.

update()

Returns:The number of rows updated.

Executes an update for previously set values.

User.ALL.where(id=1).set(name='John').update()

Columns and Conditions

class dqo.column.BaseColumn

All columns obey normal python operators.

Syntax	SQL
User.name == 'John'	name = 'john'
User.age < 5	age < 5
User.age <= 5	age <= 5
User.age > 5	age > 5
User.age >= 5	age >= 5
User.name != 'John'	name <> 'john'
(User.name=='John') & (User.age < 5)	name='john' and age=5
(User.age < 2) | (User.age >= 65)	age<2 or age>=65

In addition, the following condition generators are supported:

frm(s)

Returns this column bound to a given table alias. Example:

A.ALL.left_join(B.as_('b')).where(B.id.frm('b').is_not_null)

in_(something)

Returns a condition where this column is in a list or inner query.

is_not_null

Returns condition where this column is not null.

is_null

Returns condition where this column is null.

not_in(something)

Returns a condition where this column is in a list or inner query.

SQL Functions and Literals

dqo.sql(literal)

dqo.sql is a special object that generates functions and other SQL literals for use in your queries. Literals are inserted verbatim into the expressed SQL, so make sure you never use untrusted data!

Code here assumes you’ve: from dqo import sql

For example, while:

User.name == 'John'

would generate:

name = ?

and 'John' would be passed in as an argument to your database library, this:

User.name == sql.JOHN

would generate:

name = JOHN

which probably would not make sense to your database. A more likely example:

User.name == sql.CONCAT(
  User.first_name, ' ', User.last_name
)

would generate:

name = CONCAT(first_name, ' ', last_name)

If your literal isn’t a valid Python identifier, pass it in as a parameter:

# generates COUNT(*)
sql.COUNT(sql('*'))

The above illistrates the syntax, but is actually unecessary for COUNT(), which has special checks for sql.count('*') and sql.count(1) since they’re such common calls.

A common operation on a query might be:

# TODO
User.ALL.select(
  +sql.COALESCE(User.name, 'Unknown').as(User.name)
)

To provide a query-specific default for the user’s name.

Schema Definition

To define a table, add the @dqo.Table decorator to a class.

dqo.Table(name=None, db=None, aka=None)

Parameters:

• name – The name of the table in the database.
• db – The database to use for regular Python code. If None defaults to dqo.DB if defined.
• aka – A string or list of strings with previous names of this table, used for renaming.

A decorator used to turn a class into a dqo database table. For example:

@dqo.Table()
class Product:
  name = dqo.Column(str)
  keywords = dqo.Column([str])

class dqo.Column(kind, name=None, null=True, default=None, index=False, unique=False, primary_key=False, tz=None, aka=None)

Parameters:

• kind – A Python type to be mapped to a database column type. Or a single-element list (containing a type) representing an array column.
• name – The database name of the column.
• null – If the column can be null or not.
• default – The default for the column. If a constant, stored in the database, else (like a lambda) will be calculated on object creation.
• index – If a single column index (w/ the database’s default type, ie. BTREE) should be created for this column.
• unique – If a single column UNIQUE index should be created for this column.
• primary_key – If this column should be the primary key for this table.
• aka – A string or list of strings with previous names of this column, used for renaming.
• tz – If a datatime column, can specify if it’s a timezone aware column.

You can create ARRAY columns with subtypes by passing in a list of a single type. Example:

@dqo.Table
class Product:
  name = dqo.Column(str)
  keywords = dqo.Column([str])

asc

The ascending form of the column, used in order_by().

desc

The descending form of the column, used in order_by().

class dqo.PrimaryKey(*columns)

Parameters:columns – The columns this primary key is composed of.

To identify a single column as a primary key, add primary_key=True to its column definiton. For example:

@dqo.Table()
class A:
  id = dqo.Column(int, primary_key=True)

This will make the id column the primary key for the table.

To create a multi-column primary key, use the PrimaryKey class, passing in the component columns. For example:

@dqo.Table()
class A:
  part1 = dqo.Column(int)
  part2 = dqo.Column(int)
  _pk = dqo.PrimaryKey(part1, part2)

Calling it _pk isn’t necessary or special, just convention. The name of the variable isn’t used, just make sure it doesn’t conflict with any column names.

class dqo.ForeignKey(*to_columns, fake=False, null=True)

Parameters:

• to_columns – The columns this foreign key refers to.
• fake – A “fake” foreign key will give you the syntax sugar without actually creating the restriction in the database. This is sometimes important for performance reasons.
• null – If nulls are allowed.

Defines a foreign key relationship from this table to another. For example:

@dqo.Table()
class A:
  id = dqo.Column(int, primary_key=True)

@dqo.Table()
class B:
  id = dqo.Column(int, primary_key=True)
  a = dqo.ForeignKey(A.id)

IF you want to create a multi-column foreign key, pass in multiple columns:

@dqo.Table()
class B:
  id = dqo.Column(int, primary_key=True)
  my_a = dqo.ForeignKey(A.part1, A.part2)

A foreign key creates one database column for every referenced column, named by a combination of the foreign key name and the columns it’s referencing. So the above example would have columns my_a_part1 and my_a_part2 on table b as shown:

alter table b add foreign key (my_a_part1,my_a_part2)
references a (part1,part2)

class dqo.Index(*columns, unique=False, method=None, include=None, name=None)

Parameters:

• columns – The columns this index is composed of.
• unique – If this is a unique contraint or not.
• method – The indexing method (btree, hash, gist, spgist, gin, or brin on PosrgreSQL).
• include – A list of non-indexed columns to store in the index. Postgres 11 feature - see [https://www.postgresql.org/docs/current/static/sql-createindex.html]
• name – The name of the index.

Creates an index on the current table. For example:

@dqo.Table()
class Something:
  col1 = dqo.Column(int)
  col2 = dqo.Column(int)
  _idx1 = dqo.Index(col1, col2, unique=True)

Would generate:

create table "a" (col1 integer, col2 integer)
create unique index on "a" (col1,col2)

Columns have convenience parameters index and unique to simplify making single column indexs. Example:

@dqo.Table()
class Something:
  col1 = dqo.Column(int, index=True)
  col2 = dqo.Column(int, unique=True)