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.

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.

New in version 5.1.

namedresult/namediter – get query values a 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.

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:

named tuple 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 InvalidResultError: result does not have exactly one row

Raises:
  • 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 InvalidResultError: result does not have exactly one row

Raises:
  • 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:

named tuple :raises InvalidResultError: result does not have exactly one row

Raises:
  • 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 InvalidResultError: result does not have exactly one row

Raises:
  • 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 fields names of previous query result

Query.listfields()

List fields names of previous query result

Returns:field names
Return type:list
Raises:TypeError – too many parameters

This method returns the list 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.

ntuples – return number of tuples in query object

Query.ntuples()

Return number of tuples in query object

Returns:number of tuples in Query
Return type:int
Raises:TypeError – Too many arguments.

This method returns the number of tuples in the query result.

Deprecated since version 5.1: You can use the normal len() function instead.