# Module functions and constants

The `pg` module defines a few functions that allow to connect to a database and to define “default variables” that override the environment variables used by PostgreSQL.

These “default variables” were designed to allow you to handle general connection parameters without heavy code in your programs. You can prompt the user for a value, put it in the default variable, and forget it, without having to modify your environment. The support for default variables can be disabled by not setting the `default_vars` option in the Python setup file. Methods relative to this are specified by the tag [DV].

All variables are set to `None` at module initialization, specifying that standard environment variables should be used.

## connect – Open a PostgreSQL connection

pg.connect([dbname][, host][, port][, opt][, user][, passwd])

  Open a `pg` connection

  Parameters

    - dbname – name of connected database (None = `defbase`)

    - host (str or None) – name of the server host (None = `defhost`)

    - port (int) – port used by the database server (-1 = `defport`)

    - opt (str or None) – connection options (None = `defopt`)

    - user (str or None) – PostgreSQL user (None = `defuser`)

    - passwd (str or None) – password for user (None = `defpasswd`)

  Returns

    If successful, the `Connection` handling the connection

  Return type

    `Connection`

  Raises

    - TypeError – bad argument type, or too many arguments

    - SyntaxError – duplicate argument definition

    - pg.InternalError – some error occurred during pg connection definition

    - Exception – (all exceptions relative to object allocation)

This function opens a connection to a specified database on a given PostgreSQL server. You can use keywords here, as described in the Python tutorial. The names of the keywords are the name of the parameters given in the syntax line. The `opt` parameter can be used to pass command-line options to the server. For a precise description of the parameters, please refer to the PostgreSQL user manual.

If you want to add additional parameters not specified here, you must pass a connection string or a connection URI instead of the `dbname` (as in `con3` and `con4` in the following example).

Example:

    import pg

    con1 = pg.connect('testdb', 'myhost', 5432, None, 'bob', None)
    con2 = pg.connect(dbname='testdb', host='myhost', user='bob')
    con3 = pg.connect('host=myhost user=bob dbname=testdb connect_timeout=10')
    con4 = pg.connect('postgresql://bob@myhost/testdb?connect_timeout=10')

## get/set_defhost – default server host [DV]

pg.get_defhost(host)

  Get the default host

  Returns

    the current default host specification

  Return type

    str or None

  Raises

    TypeError – too many arguments

This method returns the current default host specification, or `None` if the environment variables should be used. Environment variables won’t be looked up.

pg.set_defhost(host)

  Set the default host

  Parameters

    host (str or None) – the new default host specification

  Returns

    the previous default host specification

  Return type

    str or None

  Raises

    TypeError – bad argument type, or too many arguments

This methods sets the default host value for new connections. If `None` is supplied as parameter, environment variables will be used in future connections. It returns the previous setting for default host.

## get/set_defport – default server port [DV]

pg.get_defport()

  Get the default port

  Returns

    the current default port specification

  Return type

    int

  Raises

    TypeError – too many arguments

This method returns the current default port specification, or `None` if the environment variables should be used. Environment variables won’t be looked up.

pg.set_defport(port)

  Set the default port

  Parameters

    port (int) – the new default port

  Returns

    previous default port specification

  Return type

    int or None

This methods sets the default port value for new connections. If -1 is supplied as parameter, environment variables will be used in future connections. It returns the previous setting for default port.

## get/set_defopt – default connection options [DV]

pg.get_defopt()

  Get the default connection options

  Returns

    the current default options specification

  Return type

    str or None

  Raises

    TypeError – too many arguments

This method returns the current default connection options specification, or `None` if the environment variables should be used. Environment variables won’t be looked up.

pg.set_defopt(options)

  Set the default connection options

  Parameters

    options (str or None) – the new default connection options

  Returns

    previous default options specification

  Return type

    str or None

  Raises

    TypeError – bad argument type, or too many arguments

This methods sets the default connection options value for new connections. If `None` is supplied as parameter, environment variables will be used in future connections. It returns the previous setting for default options.

## get/set_defbase – default database name [DV]

pg.get_defbase()

  Get the default database name

  Returns

    the current default database name specification

  Return type

    str or None

  Raises

    TypeError – too many arguments

This method returns the current default database name specification, or `None` if the environment variables should be used. Environment variables won’t be looked up.

pg.set_defbase(base)

  Set the default database name

  Parameters

    base (str or None) – the new default base name

  Returns

    the previous default database name specification

  Return type

    str or None

  Raises

    TypeError – bad argument type, or too many arguments

This method sets the default database name value for new connections. If `None` is supplied as parameter, environment variables will be used in future connections. It returns the previous setting for default host.

## get/set_defuser – default database user [DV]

pg.get_defuser()

  Get the default database user

  Returns

    the current default database user specification

  Return type

    str or None

  Raises

    TypeError – too many arguments

This method returns the current default database user specification, or `None` if the environment variables should be used. Environment variables won’t be looked up.

pg.set_defuser(user)

  Set the default database user

  Parameters

    user – the new default database user

  Returns

    the previous default database user specification

  Return type

    str or None

  Raises

    TypeError – bad argument type, or too many arguments

This method sets the default database user name for new connections. If `None` is supplied as parameter, environment variables will be used in future connections. It returns the previous setting for default host.

## get/set_defpasswd – default database password [DV]

pg.get_defpasswd()

  Get the default database password

  Returns

    the current default database password specification

  Return type

    str or None

  Raises

    TypeError – too many arguments

This method returns the current default database password specification, or `None` if the environment variables should be used. Environment variables won’t be looked up.

pg.set_defpasswd(passwd)

  Set the default database password

  Parameters

    passwd – the new default database password

  Returns

    the previous default database password specification

  Return type

    str or None

  Raises

    TypeError – bad argument type, or too many arguments

This method sets the default database password for new connections. If `None` is supplied as parameter, environment variables will be used in future connections. It returns the previous setting for default host.

## escape_string – escape a string for use within SQL

pg.escape_string(string)

  Escape a string for use within SQL

  Parameters

    string (str) – the string that is to be escaped

  Returns

    the escaped string

  Return type

    str

  Raises

    TypeError – bad argument type, or too many arguments

This function escapes a string for use within an SQL command. This is useful when inserting data values as literal constants in SQL commands. Certain characters (such as quotes and backslashes) must be escaped to prevent them from being interpreted specially by the SQL parser. `escape_string()` performs this operation. Note that there is also a `Connection` method with the same name which takes connection properties into account.

Note

It is especially important to do proper escaping when handling strings that were received from an untrustworthy source. Otherwise there is a security risk: you are vulnerable to “SQL injection” attacks wherein unwanted SQL commands are fed to your database.

Example:

    name = input("Name? ")
    phone = con.query("select phone from employees where name='%s'"
        % escape_string(name)).getresult()

## escape_bytea – escape binary data for use within SQL

pg.escape_bytea(datastring)

  escape binary data for use within SQL as type `bytea`

  Parameters

    datastring (str) – string containing the binary data that is to be escaped

  Returns

    the escaped string

  Return type

    str

  Raises

    TypeError – bad argument type, or too many arguments

Escapes binary data for use within an SQL command with the type `bytea`. As with `escape_string()`, this is only used when inserting data directly into an SQL command string.

Note that there is also a `Connection` method with the same name which takes connection properties into account.

Example:

    picture = open('garfield.gif', 'rb').read()
    con.query("update pictures set img='%s' where name='Garfield'"
        % escape_bytea(picture))

## unescape_bytea – unescape data that has been retrieved as text

pg.unescape_bytea(string)

  Unescape `bytea` data that has been retrieved as text

  Parameters

    datastring (str) – the `bytea` data string that has been retrieved as text

  Returns

    byte string containing the binary data

  Return type

    bytes

  Raises

    TypeError – bad argument type, or too many arguments

Converts an escaped string representation of binary data stored as `bytea` into the raw byte string representing the binary data – this is the reverse of `escape_bytea()`. Since the `Query` results will already return unescaped byte strings, you normally don’t have to use this method.

Note that there is also a `DB` method with the same name which does exactly the same.

## get/set_decimal – decimal type to be used for numeric values

pg.get_decimal()

  Get the decimal type to be used for numeric values

  Returns

    the Python class used for PostgreSQL numeric values

  Return type

    class

This function returns the Python class that is used by PyGreSQL to hold PostgreSQL numeric values. The default class is `decimal.Decimal` if available, otherwise the `float` type is used.

pg.set_decimal(cls)

  Set a decimal type to be used for numeric values

  Parameters

    cls (class) – the Python class to be used for PostgreSQL numeric values

This function can be used to specify the Python class that shall be used by PyGreSQL to hold PostgreSQL numeric values. The default class is `decimal.Decimal` if available, otherwise the `float` type is used.

## get/set_decimal_point – decimal mark used for monetary values

pg.get_decimal_point()

  Get the decimal mark used for monetary values

  Returns

    string with one character representing the decimal mark

  Return type

    str

This function returns the decimal mark used by PyGreSQL to interpret PostgreSQL monetary values when converting them to decimal numbers. The default setting is `'.'` as a decimal point. This setting is not adapted automatically to the locale used by PostgreSQL, but you can use `set_decimal()` to set a different decimal mark manually. A return value of `None` means monetary values are not interpreted as decimal numbers, but returned as strings including the formatting and currency.

New in version 4.1.1.

pg.set_decimal_point(string)

  Specify which decimal mark is used for interpreting monetary values

  Parameters

    string (str) – string with one character representing the decimal mark

This function can be used to specify the decimal mark used by PyGreSQL to interpret PostgreSQL monetary values. The default value is ‘.’ as a decimal point. This value is not adapted automatically to the locale used by PostgreSQL, so if you are dealing with a database set to a locale that uses a `','` instead of `'.'` as the decimal point, then you need to call `set_decimal(',')` to have PyGreSQL interpret monetary values correctly. If you don’t want money values to be converted to decimal numbers, then you can call `set_decimal(None)`, which will cause PyGreSQL to return monetary values as strings including their formatting and currency.

New in version 4.1.1.

## get/set_bool – whether boolean values are returned as bool objects

pg.get_bool()

  Check whether boolean values are returned as bool objects

  Returns

    whether or not bool objects will be returned

  Return type

    bool

This function checks whether PyGreSQL returns PostgreSQL boolean values converted to Python bool objects, or as `'f'` and `'t'` strings which are the values used internally by PostgreSQL. By default, conversion to bool objects is activated, but you can disable this with the `set_bool()` function.

New in version 4.2.

pg.set_bool(on)

  Set whether boolean values are returned as bool objects

  Parameters

    on – whether or not bool objects shall be returned

This function can be used to specify whether PyGreSQL shall return PostgreSQL boolean values converted to Python bool objects, or as `'f'` and `'t'` strings which are the values used internally by PostgreSQL. By default, conversion to bool objects is activated, but you can disable this by calling `set_bool(True)`.

New in version 4.2.

Changed in version 5.0: Boolean values had been returned as string by default in earlier versions.

## get/set_array – whether arrays are returned as list objects

pg.get_array()

  Check whether arrays are returned as list objects

  Returns

    whether or not list objects will be returned

  Return type

    bool

This function checks whether PyGreSQL returns PostgreSQL arrays converted to Python list objects, or simply as text in the internal special output syntax of PostgreSQL. By default, conversion to list objects is activated, but you can disable this with the `set_array()` function.

New in version 5.0.

pg.set_array(on)

  Set whether arrays are returned as list objects

  Parameters

    on – whether or not list objects shall be returned

This function can be used to specify whether PyGreSQL shall return PostgreSQL arrays converted to Python list objects, or simply as text in the internal special output syntax of PostgreSQL. By default, conversion to list objects is activated, but you can disable this by calling `set_array(False)`.

New in version 5.0.

Changed in version 5.0: Arrays had been always returned as text strings in earlier versions.

## get/set_bytea_escaped – whether bytea data is returned escaped

pg.get_bytea_escaped()

  Check whether bytea values are returned as escaped strings

  Returns

    whether or not bytea objects will be returned escaped

  Return type

    bool

This function checks whether PyGreSQL returns PostgreSQL `bytea` values in escaped form or in unescaped from as byte strings. By default, bytea values will be returned unescaped as byte strings, but you can change this with the `set_bytea_escaped()` function.

New in version 5.0.

pg.set_bytea_escaped(on)

  Set whether bytea values are returned as escaped strings

  Parameters

    on – whether or not bytea objects shall be returned escaped

This function can be used to specify whether PyGreSQL shall return PostgreSQL `bytea` values in escaped form or in unescaped from as byte strings. By default, bytea values will be returned unescaped as byte strings, but you can change this by calling `set_bytea_escaped(True)`.

New in version 5.0.

Changed in version 5.0: Bytea data had been returned in escaped form by default in earlier versions.

## get/set_jsondecode – decoding JSON format

pg.get_jsondecode()

  Get the function that deserializes JSON formatted strings

This returns the function used by PyGreSQL to construct Python objects from JSON formatted strings.

pg.set_jsondecode(func)

  Set a function that will deserialize JSON formatted strings

  Parameters

    func – the function to be used for deserializing JSON strings

You can use this if you do not want to deserialize JSON strings coming in from the database, or if want to use a different function than the standard function `json.loads()` or if you want to use it with parameters different from the default ones. If you set this function to None, then the automatic deserialization of JSON strings will be deactivated.

New in version 5.0.

Changed in version 5.0: JSON data had been always returned as text strings in earlier versions.

## get/set_datestyle – assume a fixed date style

pg.get_datestyle()

  Get the assumed date style for typecasting

This returns the PostgreSQL date style that is silently assumed when typecasting dates or None if no fixed date style is assumed, in which case the date style is requested from the database when necessary (this is the default). Note that this method will not get the date style that is currently set in the session or in the database. You can get the current setting with the methods `DB.get_parameter()` and `Connection.parameter()`. You can also get the date format corresponding to the current date style by calling `Connection.date_format()`.

New in version 5.0.

pg.set_datestyle(datestyle)

  Set a fixed date style that shall be assumed when typecasting

  Parameters

    datestyle (str) – the date style that shall be assumed, or None if no fixed dat style shall be assumed

PyGreSQL is able to automatically pick up the right date style for typecasting date values from the database, even if you change it for the current session with a `SET DateStyle` command. This is happens very effectively without an additional database request being involved. If you still want to have PyGreSQL always assume a fixed date style instead, then you can set one with this function. Note that calling this function will not alter the date style of the database or the current session. You can do that by calling the method `DB.set_parameter()` instead.

New in version 5.0.

## get/set_typecast – custom typecasting

PyGreSQL uses typecast functions to cast the raw data coming from the database to Python objects suitable for the particular database type. These functions take a single string argument that represents the data to be casted and must return the casted value.

PyGreSQL provides through its C extension module basic typecast functions for the common database types, but if you want to add more typecast functions, you can set these using the following functions.

pg.get_typecast(typ)

  Get the global cast function for the given database type

  Parameters

    typ (str) – PostgreSQL type name

  Returns

    the typecast function for the specified type

  Return type

    function or None

New in version 5.0.

pg.set_typecast(typ, cast)

  Set a global typecast function for the given database type(s)

  Parameters

    - typ (str or int) – PostgreSQL type name or list of type names

    - cast – the typecast function to be set for the specified type(s)

The typecast function must take one string object as argument and return a Python object into which the PostgreSQL type shall be casted. If the function takes another parameter named connection, then the current database connection will also be passed to the typecast function. This may sometimes be necessary to look up certain database settings.

New in version 5.0.

Note that database connections cache types and their cast functions using connection specific `DbTypes` objects. You can also get, set and reset typecast functions on the connection level using the methods `DbTypes.get_typecast()`, `DbTypes.set_typecast()` and `DbTypes.reset_typecast()` of the `DB.dbtypes` object. This will not affect other connections or future connections. In order to be sure a global change is picked up by a running connection, you must reopen it or call `DbTypes.reset_typecast()` on the `DB.dbtypes` object.

Also note that the typecasting for all of the basic types happens already in the C extension module. The typecast functions that can be set with the above methods are only called for the types that are not already supported by the C extension module.

## cast_array/record – fast parsers for arrays and records

PostgreSQL returns arrays and records (composite types) using a special output syntax with several quirks that cannot easily and quickly be parsed in Python. Therefore the C extension module provides two fast parsers that allow quickly turning these text representations into Python objects: Arrays will be converted to Python lists, and records to Python tuples. These fast parsers are used automatically by PyGreSQL in order to return arrays and records from database queries as lists and tuples, so you normally don’t need to call them directly. You may only need them for typecasting arrays of data types that are not supported by default in PostgreSQL.

pg.cast_array(string[, cast][, delim])

  Cast a string representing a PostgreSQL array to a Python list

  Parameters

    - string (str) – the string with the text representation of the array

    - cast (callable or None) – a typecast function for the elements of the array

    - delim – delimiter character between adjacent elements

  Returns

    a list representing the PostgreSQL array in Python

  Return type

    list

  Raises

    - TypeError – invalid argument types

    - ValueError – error in the syntax of the given array

This function takes a string containing the text representation of a PostgreSQL array (which may look like `'{{1,2}{3,4}}'` for a two-dimensional array), a typecast function cast that is called for every element, and an optional delimiter character delim (usually a comma), and returns a Python list representing the array (which may be nested like `[[1, 2], [3, 4]]` in this example). The cast function must take a single argument which will be the text representation of the element and must output the corresponding Python object that shall be put into the list. If you don’t pass a cast function or set it to None, then unprocessed text strings will be returned as elements of the array. If you don’t pass a delimiter character, then a comma will be used by default.

New in version 5.0.

pg.cast_record(string[, cast][, delim])

  Cast a string representing a PostgreSQL record to a Python tuple

  Parameters

    - string (str) – the string with the text representation of the record

    - cast (callable, list or tuple of callables, or None) – typecast function(s) for the elements of the record

    - delim – delimiter character between adjacent elements

  Returns

    a tuple representing the PostgreSQL record in Python

  Return type

    tuple

  Raises

    - TypeError – invalid argument types

    - ValueError – error in the syntax of the given array

This function takes a string containing the text representation of a PostgreSQL record (which may look like `'(1,a,2,b)'` for a record composed of four fields), a typecast function cast that is called for every element, or a list or tuple of such functions corresponding to the individual fields of the record, and an optional delimiter character delim (usually a comma), and returns a Python tuple representing the record (which may be inhomogeneous like `(1, 'a', 2, 'b')` in this example). The cast function(s) must take a single argument which will be the text representation of the element and must output the corresponding Python object that shall be put into the tuple. If you don’t pass cast function(s) or pass None instead, then unprocessed text strings will be returned as elements of the tuple. If you don’t pass a delimiter character, then a comma will be used by default.

New in version 5.0.

Note that besides using parentheses instead of braces, there are other subtle differences in escaping special characters and NULL values between the syntax used for arrays and the one used for composite types, which these functions take into account.

## Type helpers

The module provides the following type helper functions. You can wrap parameters with these functions when passing them to `DB.query()` or `DB.query_formatted()` in order to give PyGreSQL a hint about the type of the parameters, if it cannot be derived from the context.

pg.Bytea(bytes)

  A wrapper for holding a bytea value

New in version 5.0.

pg.HStore(dict)

  A wrapper for holding an hstore dictionary

New in version 5.0.

pg.Json(obj)

  A wrapper for holding an object serializable to JSON

New in version 5.0.

The following additional type helper is only meaningful when used with `DB.query_formatted()`. It marks a parameter as text that shall be literally included into the SQL. This is useful for passing table names for instance.

pg.Literal(sql)

  A wrapper for holding a literal SQL string

New in version 5.0.

## Module constants

Some constants are defined in the module dictionary. They are intended to be used as parameters for methods calls. You should refer to the libpq description in the PostgreSQL user manual for more information about them. These constants are:

pg.version

pg.__version__

  constants that give the current version

pg.INV_READ

pg.INV_WRITE

  large objects access modes, used by `Connection.locreate()` and `LargeObject.open()`

pg.SEEK_SET

pg.SEEK_CUR

pg.SEEK_END

  positional flags, used by `LargeObject.seek()`

pg.TRANS_IDLE

pg.TRANS_ACTIVE

pg.TRANS_INTRANS

pg.TRANS_INERROR

pg.TRANS_UNKNOWN

  transaction states, used by `Connection.transaction()`