query

PostgresWrapper.query(sql: str, *params, fetch='all', **kwparams) → Tuple[Optional[iter], Any, psycopg2.extensions.cursor][source]

Create an instance of your database wrapper:

>>> db = GenericDBWrapper()

Querying with prepared SQL queries and returning a single row:

>>> res, res_exec, cur = db.query("SELECT * FROM users WHERE first_name = ?;", ['John'], fetch='one')
>>> res
(12, 'John', 'Doe', '123 Example Road',)
>>> cur.close()

Querying with plain SQL queries, using query_mode, handling an iterator result, and using the cursor

If your database API returns rows as tuple``s or ``list``s, you can use ``query_mode='dict' (or set query_mode in the constructor) to convert any row results into dictionaries which map each column to their values.

>>> res, _, cur = db.query("SELECT * FROM users;", fetch='all', query_mode='dict')

When querying with fetch='all', depending on your database API, res may be an iterator, and cannot be accessed via an index like res[0].

You should make sure to iterate the rows using a for loop:

>>> for row in res:
...     print(row['first_name'], ':', row)
John : {'first_name': 'John', 'last_name': 'Doe', 'id': 12}
Dave : {'first_name': 'Dave', 'last_name': 'Johnson', 'id': 13}
Aaron : {'first_name': 'Aaron', 'last_name': 'Swartz', 'id': 14}

Or, if the result object is a generator, then you can auto-iterate the results into a list using x = list(res):

>>> rows = list(res)
>>> rows[0]
{'first_name': 'John', 'last_name': 'Doe', 'id': 12}

Using the returned cursor (third return item), we can access various metadata about our query. Note that cursor objects vary between database APIs, and not all methods/attributes may be available, or may return different data than shown below:

>>> cur.description  # cursor.description often contains the column names matching the query columns
(('id', None, None, None, None, None, None), ('first_name', None, None, None, None, None, None),
 ('last_name', None, None, None, None, None, None))

>>> _, _, cur = db.query("INSERT INTO users (first_name, last_name) VALUES ('a', 'b')", fetch='no')
>>> cur.rowcount   # cursor.rowcount tells us how many rows were affected by a query
1
>>> cur.lastrowid  # cursor.lastrowid tells us the ID of the last row we inserted with this cursor
3
Parameters

  • sql (str) – An SQL query to execute

  • params – Any positional arguments other than sql will be passed to cursor.execute.

  • fetch (str) – Fetch mode. Either all (return cursor.fetchall() as first return arg), one (return cursor.fetchone()), or no (do not fetch. first return arg is None).

  • kwparams – Any keyword arguments that aren’t specified as parameters / keyword args for this method will be forwarded to cursor.execute

Key GenericCursor cursor

Use this specific cursor instead of automatically obtaining one

Key cursor_name

If your database API supports named cursors (e.g. PostgreSQL), then you may specify cursor_name as a keyword argument to use a named cursor for this query.

Key query_mode

Either flat (fetch results as they were originally returned from the DB), or dict (use _zip_cols() to convert tuple/list rows into dicts mapping col:value).

Return iter results

(tuple item 1) An iterable such as a generator, or storage type e.g. list or dict. NOTE: If you’ve set fetch='all', depending on your database adapter, this may be a generator or other form of iterator that cannot be directly accessed via index (i.e. res[123]). Instead you must iterate it with a for loop, or cast it into a list/tuple to automatically iterate it into an indexed object, e.g. ``list(res)`

Return Any res_exec

(tuple item 2) The object returned from running cur.execute(sql, *params, **kwparams). This may be a cursor, but may also vary based on database API.

Return GenericCursor cur

(tuple item 3) The cursor that was used to execute and fetch your query. To allow for use with server side cursors, the cursor is NOT closed automatically. To avoid stale cursors, it’s best to run cur.close() when you’re done with handling the returned results.