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 exampleSqliteQueryBuilder
orPostgresQueryBuilder
.To implement a sub-class of
BaseQueryBuilder
, you must:Implement all methods marked with
@abstractmethod
, such asbuild_query()
,all()
andfetch()
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 theQ_
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 overrideget_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()
andfetch()
- builds and returns a string SQL query using the various class attributes such aswhere_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 ofcursor_name
norcursor_class
.- Parameters
- 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)
-
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
|
Initialize self. |
|
Executes the current query, and returns an iterable cursor (results are loaded as you iterate the cursor) |
Used internally by |
|
|
|
|
Executes the current query, and fetches the first result as a |
|
Similar to |
|
Create and return a new database cursor object. |
|
Add one or more columns to group by clause. |
|
Add a limit/offset. |
|
example: order(‘mycol’, ‘othercol’) == ORDER BY mycol, othercol DESC |
|
Alias of |
|
Add columns to select clause, specify as individual args. |
|
For adding a simple col=value clause with “AND” before it (if at least 1 other clause). |
|
For adding simple col=value clause with “OR” before it (if at least 1 other clause). |