BaseQueryBuilder

class privex.db.query.base.BaseQueryBuilder(table: str, connection: privex.db.types.GenericConnection = None, **kwargs)[source]

This is an SQL query builder class which outputs ANSI compatible SQL queries, and can use connections/cursors to execute the queries that it builds.

This is an abstract base class (abc.ABC) meaning that it’s not designed to be constructed directly, instead it should be used as a parent class for a database specific query builder, for example SqliteQueryBuilder or PostgresQueryBuilder.

To implement a sub-class of BaseQueryBuilder, you must:

  • Implement all methods marked with @abstractmethod, such as build_query(), all() and fetch()

  • If your DBMS or it’s Python API doesn’t follow the default query configuration (see the attributes starting with Q_), then you should adjust the Q_ attributes in your class to match your DB / DB API.

    e.g. Set Q_DEFAULT_PLACEHOLDER = "?" if your DB API expects ? for prepared statement placeholders instead of %s.

  • While not required, you may wish to implement a constructor (__init__()), and override get_cursor() to adjust it to your database API requirements

__init__(table: str, connection: privex.db.types.GenericConnection = None, **kwargs)[source]

Initialize self. See help(type(self)) for accurate signature.

abstract all(query_mode=<QueryMode.ROW_DICT: 'dict'>) → Union[Iterable[dict], Iterable[tuple]][source]

Executes the current query, and returns an iterable cursor (results are loaded as you iterate the cursor)

Usage:

>>> results = BaseQueryBuilder('people').all()   # Equivalent to ``SELECT * FROM people;``
>>> for r in results:
>>>     print(r['first_name'], r['last_name'], r['phone'])
Return Iterable

A cursor which can be iterated using a for loop. Ideally, should load rows as you iterate, saving RAM.

abstract build_query() → str[source]

Used internally by all() and fetch() - builds and returns a string SQL query using the various class attributes such as where_clauses :return str query: The SQL query that will be sent to the database as a string

abstract fetch(query_mode=<QueryMode.ROW_DICT: 'dict'>) → Union[dict, tuple, None][source]

Executes the current query, and fetches the first result as a dict.

If there are no results, will return None

Return dict

The query result as a dictionary: {column: value, }

Return None

If no results are found

abstract fetch_next(query_mode=<QueryMode.ROW_DICT: 'dict'>) → Union[dict, tuple, None][source]

Similar to fetch(), but doesn’t close the cursor after the query, so can be ran more than once to iterate row-by-row over the results.

Parameters

query_mode (QueryMode) –

Returns

get_cursor(cursor_name=None, cursor_class=None, *args, **kwargs)[source]

Create and return a new database cursor object.

It’s recommended to override this method if you’re inheriting from this class, as this Generic version of get_cursor does not make use of cursor_name nor cursor_class.

Parameters
  • cursor_name (str) – (If DB API supports it) The name for this cursor

  • cursor_class (type) – (If DB API supports it) The cursor class to use

Return GenericCursor cursor

A cursor object which should implement at least the basic Python DB API Cursor functionality as specified in GenericCursor ((PEP 249)

group_by(*args)[source]

Add one or more columns to group by clause.

example: group_by(‘name’, ‘date’) == GROUP BY name, date

Parameters

args – One or more columns to group by

Returns

QueryBuilder object (for chaining)

limit(limit_num, offset=None)[source]

Add a limit/offset. When using offset you should use an ORDER BY to avoid issues. :param limit_num: Amount of rows to limit to :param offset: Offset by this many rows (optional) :return: QueryBuilder object (for chaining)

order(*args, direction='DESC')[source]

example: order(‘mycol’, ‘othercol’) == ORDER BY mycol, othercol DESC

Parameters
  • args – One or more order columns as individual args

  • direction – Direction to sort

Returns

QueryBuilder object (for chaining)

order_by(*args, **kwargs)[source]

Alias of order()

select(*args)[source]

Add columns to select clause, specify as individual args. NOTE: no escaping!

example:

q.select(‘mycol’, ‘othercol’, ‘somecol as thiscol’)

can also chain: q.select(‘mycol’).select(‘othercol’)

Parameters

args – columns to select as individual arguments

Returns

QueryBuilder object (for chaining)

where(col, val, compare='=', placeholder=None)[source]

For adding a simple col=value clause with “AND” before it (if at least 1 other clause). val is escaped properly

example: where(‘x’,’test’).where(‘y’,’thing’) produces prepared sql “WHERE x = %s AND y = %s”

Parameters
  • col – the column, function etc. to query

  • val – the value it should be equal to. most python objects will be converted and escaped properly

  • compare – instead of ‘=’, compare using this comparator, e.g. ‘>’, ‘<=’ etc.

  • placeholder – Set the value placeholder, e.g. placeholder=’HOST(%s)’

Returns

QueryBuilder object (for chaining)

where_or(col, val, compare='=', placeholder=None)[source]

For adding simple col=value clause with “OR” before it (if at least 1 other clause). val is escaped properly

example: where(‘x’,’test’).where_or(‘y’,’thing’) produces prepared sql “WHERE x = %s OR y = %s”

Parameters
  • col – the column, function etc. to query

  • val – the value it should be equal to. most python objects will be converted and escaped properly

  • compare – instead of ‘=’, compare using this comparator, e.g. ‘>’, ‘<=’ etc.

  • placeholder – Set the value placeholder, e.g. placeholder=’HOST(%s)’

Returns

QueryBuilder object (for chaining)

Methods

Methods

__init__(table[, connection])

Initialize self.

all([query_mode])

Executes the current query, and returns an iterable cursor (results are loaded as you iterate the cursor)

build_query()

Used internally by all() and fetch() - builds and returns a string SQL query using the various class attributes such as where_clauses :return str query: The SQL query that will be sent to the database as a string

close_cursor()

execute(*args, **kwargs)

fetch([query_mode])

Executes the current query, and fetches the first result as a dict.

fetch_next([query_mode])

Similar to fetch(), but doesn’t close the cursor after the query, so can be ran more than once to iterate row-by-row over the results.

get_cursor([cursor_name, cursor_class])

Create and return a new database cursor object.

group_by(*args)

Add one or more columns to group by clause.

limit(limit_num[, offset])

Add a limit/offset.

order(*args[, direction])

example: order(‘mycol’, ‘othercol’) == ORDER BY mycol, othercol DESC

order_by(*args, **kwargs)

Alias of order()

select(*args)

Add columns to select clause, specify as individual args.

where(col, val[, compare, placeholder])

For adding a simple col=value clause with “AND” before it (if at least 1 other clause).

where_or(col, val[, compare, placeholder])

For adding simple col=value clause with “OR” before it (if at least 1 other clause).