query¶
-
GenericDBWrapper.
query
(sql: str, *params, fetch='all', **kwparams) → Tuple[Optional[iter], Any, privex.db.types.GenericCursor][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 setquery_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 likeres[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 tocursor.execute
.fetch (str) – Fetch mode. Either
all
(returncursor.fetchall()
as first return arg),one
(returncursor.fetchone()
), orno
(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), ordict
(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
ordict
. NOTE: If you’ve setfetch='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 afor
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.