PostgresQueryBuilder

class privex.db.query.postgres.PostgresQueryBuilder(table: str, connection=None, **kwargs)[source]

A simple SQL query builder / ORM, designed for use with PostgreSQL. May or may not work with other RDBMS’s.

Basic Usage:

First, inject your psycopg2 connection into QueryBuilder, so it’s available to all instances.

>>> PostgresQueryBuilder.conn = psycopg2.connect(user='bob', dbname='my_db')

Now, just construct the class, passing the table name to query.

>>> q = PostgresQueryBuilder('orders')

You can execute each query building method either on their own line, and/or you can chain them together.

WARNING: many methods such as select() do not escape your input. Only where() and where_or() use prepared statements, with a placeholder for the value you pass.

>>> q.select('full_name', 'address')
>>> q.select('SUM(order_amt) as total_spend').where('country', 'FR')         ...     .where('SUM(order_amt)', '100', compare='>=')
>>> q.group_by('full_name', 'address')

Once you’ve finished building your query, simply call either all() (return all results as a list) or fetch() (returns the first result, or None if there’s no match)

>>> results = q.order('full_name', direction='ASC').all()
>>> print(results[0])

Output:

dict{'full_name': 'Aaron Doe', 'address': '123 Fake St.', 'total_spend': 127.88}

You can call build_query() to see the query that would be sent to PostgreSQL, showing the value placeholders (e.g. %s)

>>> print(q.build_query())

Output:

SELECT full_name, address, SUM(order_amt) as total_spend FROM orders WHERE country = %s
AND SUM(order_amt) >= %s GROUP BY full_name, address ORDER BY full_name ASC;

Copyright:

+===================================================+
|                 © 2019 Privex Inc.                |
|               https://www.privex.io               |
+===================================================+
|                                                   |
|        Privex Database Library                    |
|                                                   |
|        Core Developer(s):                         |
|                                                   |
|          (+)  Chris (@someguy123) [Privex]        |
|                                                   |
+===================================================+
__init__(table: str, connection=None, **kwargs)[source]

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

all(query_mode=<QueryMode.DEFAULT: 'default'>) → 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 = PostgresQueryBuilder('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, loads rows as you iterate, saving RAM

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

fetch(query_mode=<QueryMode.DEFAULT: 'default'>) → 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

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) → psycopg2.extensions.cursor[source]

Create and return a new Postgres cursor object

query_mode_cursor(query_mode: privex.db.query.base.QueryMode, replace_cursor=True, cursor_mgr=True)[source]

Return a cursor object with the cursor class based on the query_mode, using the query_mode to cursor class map in _cursor_map

Parameters

  • query_mode (QueryMode) – The QueryMode to obtain a cursor for

  • replace_cursor (bool) – (Default: True) If True, replace the shared instance _cursor with this new cursor.

  • cursor_mgr (bool) – Wrap the cursor object in CursorManager

Returns

select_date(*args)[source]

Add columns to be returned as an ISO formatted date to the select clause. Specify as individual args. Do not use ‘col AS x’. NOTE: no escaping is used!

example: q.select_date(‘created_at’, ‘updated_at’) can also chain: q.select_date(‘mycol’).select_date(‘othercol’)

Parameters

args – date columns to select as individual arguments

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

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 Postgres cursor object

query_mode_cursor(query_mode[, …])

Return a cursor object with the cursor class based on the query_mode, using the query_mode to cursor class map in _cursor_map

select_date(*args)

Add columns to be returned as an ISO formatted date to the select clause.