Query methods¶
- class pg.Query¶
The Query
object returned by Connection.query()
and
DB.query()
can be used as an iterable returning rows as tuples.
You can also directly access row tuples using their index, and get
the number of rows with the len()
function.
The Query
class also provides the following methods for accessing
the results of the query:
getresult – get query values as list of tuples¶
- Query.getresult()¶
Get query values as list of tuples
- Returns:
result values as a list of tuples
- Return type:
list
- Raises:
TypeError – too many (any) parameters
MemoryError – internal memory error
This method returns query results as a list of tuples.
More information about this result may be accessed using
Query.listfields()
, Query.fieldname()
and Query.fieldnum()
methods.
Note that since PyGreSQL 5.0 this method will return the values of array type columns as Python lists.
Since PyGreSQL 5.1 the Query
can be also used directly as
an iterable sequence, i.e. you can iterate over the Query
object to get the same tuples as returned by Query.getresult()
.
This is slightly more efficient than getting the full list of results,
but note that the full result is always fetched from the server anyway
when the query is executed.
You can also call len()
on a query to find the number of rows
in the result, and access row tuples using their index directly on
the Query
object.
When the Query
object was returned by Connection.send_query()
,
other return values are also possible, as documented there.
dictresult/dictiter – get query values as dictionaries¶
- Query.dictresult()¶
Get query values as list of dictionaries
- Returns:
result values as a list of dictionaries
- Return type:
list
- Raises:
TypeError – too many (any) parameters
MemoryError – internal memory error
This method returns query results as a list of dictionaries which have the field names as keys.
If the query has duplicate field names, you will get the value for the field with the highest index in the query.
Note that since PyGreSQL 5.0 this method will return the values of array type columns as Python lists.
- Query.dictiter()¶
Get query values as iterable of dictionaries
- Returns:
result values as an iterable of dictionaries
- Return type:
iterable
- Raises:
TypeError – too many (any) parameters
MemoryError – internal memory error
This method returns query results as an iterable of dictionaries which have the field names as keys. This is slightly more efficient than getting the full list of results as dictionaries, but note that the full result is always fetched from the server anyway when the query is executed.
If the query has duplicate field names, you will get the value for the field with the highest index in the query.
When the Query
object was returned by Connection.send_query()
,
other return values are also possible, as documented there.
New in version 5.1.
namedresult/namediter – get query values as named tuples¶
- Query.namedresult()¶
Get query values as list of named tuples
- Returns:
result values as a list of named tuples
- Return type:
list
- Raises:
TypeError – too many (any) parameters
TypeError – named tuples not supported
MemoryError – internal memory error
This method returns query results as a list of named tuples with proper field names.
Column names in the database that are not valid as field names for named tuples (particularly, names starting with an underscore) are automatically renamed to valid positional names.
Note that since PyGreSQL 5.0 this method will return the values of array type columns as Python lists.
New in version 4.1.
- Query.namediter()¶
Get query values as iterable of named tuples
- Returns:
result values as an iterable of named tuples
- Return type:
iterable
- Raises:
TypeError – too many (any) parameters
TypeError – named tuples not supported
MemoryError – internal memory error
This method returns query results as an iterable of named tuples with proper field names. This is slightly more efficient than getting the full list of results as named tuples, but note that the full result is always fetched from the server anyway when the query is executed.
Column names in the database that are not valid as field names for named tuples (particularly, names starting with an underscore) are automatically renamed to valid positional names.
When the Query
object was returned by Connection.send_query()
,
other return values are also possible, as documented there.
New in version 5.1.
scalarresult/scalariter – get query values as scalars¶
- Query.scalarresult()¶
Get first fields from query result as list of scalar values
- Returns:
first fields from result as a list of scalar values
- Return type:
list
- Raises:
TypeError – too many (any) parameters
MemoryError – internal memory error
This method returns the first fields from the query results as a list of scalar values in the order returned by the server.
New in version 5.1.
- Query.scalariter()¶
Get first fields from query result as iterable of scalar values
- Returns:
first fields from result as an iterable of scalar values
- Return type:
list
- Raises:
TypeError – too many (any) parameters
MemoryError – internal memory error
This method returns the first fields from the query results as an iterable of scalar values in the order returned by the server. This is slightly more efficient than getting the full list of results as rows or scalar values, but note that the full result is always fetched from the server anyway when the query is executed.
New in version 5.1.
one/onedict/onenamed/onescalar – get one result of a query¶
- Query.one()¶
Get one row from the result of a query as a tuple
- Returns:
next row from the query results as a tuple of fields
- Return type:
tuple or None
- Raises:
TypeError – too many (any) parameters
MemoryError – internal memory error
Returns only one row from the result as a tuple of fields.
This method can be called multiple times to return more rows. It returns None if the result does not contain one more row.
New in version 5.1.
- Query.onedict()¶
Get one row from the result of a query as a dictionary
- Returns:
next row from the query results as a dictionary
- Return type:
dict or None
- Raises:
TypeError – too many (any) parameters
MemoryError – internal memory error
Returns only one row from the result as a dictionary with the field names used as the keys.
This method can be called multiple times to return more rows. It returns None if the result does not contain one more row.
New in version 5.1.
- Query.onenamed()¶
Get one row from the result of a query as named tuple
- Returns:
next row from the query results as a named tuple
- Return type:
namedtuple or None
- Raises:
TypeError – too many (any) parameters
MemoryError – internal memory error
Returns only one row from the result as a named tuple with proper field names.
Column names in the database that are not valid as field names for named tuples (particularly, names starting with an underscore) are automatically renamed to valid positional names.
This method can be called multiple times to return more rows. It returns None if the result does not contain one more row.
New in version 5.1.
- Query.onescalar()¶
Get one row from the result of a query as scalar value
- Returns:
next row from the query results as a scalar value
- Return type:
type of first field or None
- Raises:
TypeError – too many (any) parameters
MemoryError – internal memory error
Returns the first field of the next row from the result as a scalar value.
This method can be called multiple times to return more rows as scalars. It returns None if the result does not contain one more row.
New in version 5.1.
single/singledict/singlenamed/singlescalar – get single result of a query¶
- Query.single()¶
Get single row from the result of a query as a tuple
- Returns:
single row from the query results as a tuple of fields
- Return type:
tuple
- Raises:
pg.InvalidResultError – result does not have exactly one row
TypeError – too many (any) parameters
MemoryError – internal memory error
Returns a single row from the result as a tuple of fields.
This method returns the same single row when called multiple times.
It raises an pg.InvalidResultError
if the result does not have exactly
one row. More specifically, this will be of type pg.NoResultError
if it
is empty and of type pg.MultipleResultsError
if it has multiple rows.
New in version 5.1.
- Query.singledict()¶
Get single row from the result of a query as a dictionary
- Returns:
single row from the query results as a dictionary
- Return type:
dict
- Raises:
pg.InvalidResultError – result does not have exactly one row
TypeError – too many (any) parameters
MemoryError – internal memory error
Returns a single row from the result as a dictionary with the field names used as the keys.
This method returns the same single row when called multiple times.
It raises an pg.InvalidResultError
if the result does not have exactly
one row. More specifically, this will be of type pg.NoResultError
if it
is empty and of type pg.MultipleResultsError
if it has multiple rows.
New in version 5.1.
- Query.singlenamed()¶
Get single row from the result of a query as named tuple
- Returns:
single row from the query results as a named tuple
- Return type:
namedtuple
- Raises:
pg.InvalidResultError – result does not have exactly one row
TypeError – too many (any) parameters
MemoryError – internal memory error
Returns single row from the result as a named tuple with proper field names.
Column names in the database that are not valid as field names for named tuples (particularly, names starting with an underscore) are automatically renamed to valid positional names.
This method returns the same single row when called multiple times.
It raises an pg.InvalidResultError
if the result does not have exactly
one row. More specifically, this will be of type pg.NoResultError
if it
is empty and of type pg.MultipleResultsError
if it has multiple rows.
New in version 5.1.
- Query.singlescalar()¶
Get single row from the result of a query as scalar value
- Returns:
single row from the query results as a scalar value
- Return type:
type of first field
- Raises:
pg.InvalidResultError – result does not have exactly one row
TypeError – too many (any) parameters
MemoryError – internal memory error
Returns the first field of a single row from the result as a scalar value.
This method returns the same single row as scalar when called multiple times.
It raises an pg.InvalidResultError
if the result does not have exactly
one row. More specifically, this will be of type pg.NoResultError
if it
is empty and of type pg.MultipleResultsError
if it has multiple rows.
New in version 5.1.
listfields – list field names of query result¶
- Query.listfields()¶
List field names of query result
- Returns:
field names
- Return type:
tuple
- Raises:
TypeError – too many parameters
This method returns the tuple of field names defined for the query result. The fields are in the same order as the result values.
fieldname, fieldnum – field name/number conversion¶
- Query.fieldname(num)¶
Get field name from its number
- Parameters:
num (int) – field number
- Returns:
field name
- Return type:
str
- Raises:
TypeError – invalid connection, bad parameter type, or too many parameters
ValueError – invalid field number
This method allows to find a field name from its rank number. It can be useful for displaying a result. The fields are in the same order as the result values.
- Query.fieldnum(name)¶
Get field number from its name
- Parameters:
name (str) – field name
- Returns:
field number
- Return type:
int
- Raises:
TypeError – invalid connection, bad parameter type, or too many parameters
ValueError – unknown field name
This method returns a field number given its name. It can be used to build a function that converts result list strings to their correct type, using a hardcoded table definition. The number returned is the field rank in the query result.
fieldinfo – detailed info about query result fields¶
- Query.fieldinfo([field])¶
Get information on one or all fields of the query
- Parameters:
field (int or str) – a column number or name (optional)
- Returns:
field info tuple(s) for all fields or given field
- Return type:
tuple
- Raises:
IndexError – field does not exist
TypeError – too many parameters
If the field
is specified by passing either a column number or a field
name, a four-tuple with information for the specified field of the query
result will be returned. If no field
is specified, a tuple of four-tuples
for every field of the previous query result will be returned, in the same
order as they appear in the query result.
The four-tuples contain the following information: The field name, the internal OID number of the field type, the size in bytes of the column or a negative value if it is of variable size, and a type-specific modifier value.
New in version 5.2.
memsize – return number of bytes allocated by query result¶
- Query.memsize()¶
Return number of bytes allocated by query result
- Returns:
number of bytes allocated for the query result
- Return type:
int
- Raises:
TypeError – Too many arguments.
This method returns the number of bytes allocated for the query result.
New in version 5.2: (needs PostgreSQL >= 12)