Type – Type objects and constructors

Type constructors

For binding to an operation’s input parameters, PostgreSQL needs to have the input in a particular format. However, from the parameters to the Cursor.execute() and Cursor.executemany() methods it is not always obvious as which PostgreSQL data types they shall be bound. For instance, a Python string could be bound as a simple char value, or also as a date or a time. Or a list could be bound as a array or a json object. To make the intention clear in such cases, you can wrap the parameters in type helper objects. PyGreSQL provides the constructors defined below to create such objects that can hold special values. When passed to the cursor methods, PyGreSQL can then detect the proper type of the input parameter and bind it accordingly.

The pgdb module exports the following type constructors as part of the DB-API 2 standard:

pgdb.Date(year, month, day)

Construct an object holding a date value

pgdb.Time(hour[, minute][, second][, microsecond][, tzinfo])

Construct an object holding a time value

pgdb.Timestamp(year, month, day[, hour][, minute][, second][, microsecond][, tzinfo])

Construct an object holding a time stamp value

pgdb.DateFromTicks(ticks)

Construct an object holding a date value from the given ticks value

pgdb.TimeFromTicks(ticks)

Construct an object holding a time value from the given ticks value

pgdb.TimestampFromTicks(ticks)

Construct an object holding a time stamp from the given ticks value

pgdb.Binary(bytes)

Construct an object capable of holding a (long) binary string value

Additionally, PyGreSQL provides the following constructors for PostgreSQL specific data types:

pgdb.Interval(days, hours=0, minutes=0, seconds=0, microseconds=0)

Construct an object holding a time interval value

New in version 5.0.

pgdb.Uuid([hex][, bytes][, bytes_le][, fields][, int][, version])

Construct an object holding a UUID value

New in version 5.0.

pgdb.Hstore(dict)

Construct a wrapper for holding an hstore dictionary

New in version 5.0.

pgdb.Json(obj[, encode])

Construct a wrapper for holding an object serializable to JSON

You can pass an optional serialization function as a parameter. By default, PyGreSQL uses json.dumps() to serialize it.

pgdb.Literal(sql)

Construct a wrapper for holding a literal SQL string

New in version 5.0.

Example for using a type constructor:

>>> cursor.execute("create table jsondata (data jsonb)")
>>> data = {'id': 1, 'name': 'John Doe', 'kids': ['Johnnie', 'Janie']}
>>> cursor.execute("insert into jsondata values (%s)", [Json(data)])

Note

SQL NULL values are always represented by the Python None singleton on input and output.

Type objects

class pgdb.DbType

The Cursor.description attribute returns information about each of the result columns of a query. The type_code must compare equal to one of the DbType objects defined below. Type objects can be equal to more than one type code (e.g. DATETIME is equal to the type codes for date, time and timestamp columns).

The pgdb module exports the following DbType objects as part of the DB-API 2 standard:

STRING

Used to describe columns that are string-based (e.g. char, varchar, text)

BINARY

Used to describe (long) binary columns (bytea)

NUMBER

Used to describe numeric columns (e.g. int, float, numeric, money)

DATETIME

Used to describe date/time columns (e.g. date, time, timestamp, interval)

ROWID

Used to describe the oid column of PostgreSQL database tables

Note

The following more specific type objects are not part of the DB-API 2 standard.

BOOL

Used to describe boolean columns

SMALLINT

Used to describe smallint columns

INTEGER

Used to describe integer columns

LONG

Used to describe bigint columns

FLOAT

Used to describe float columns

NUMERIC

Used to describe numeric columns

MONEY

Used to describe money columns

DATE

Used to describe date columns

TIME

Used to describe time columns

TIMESTAMP

Used to describe timestamp columns

INTERVAL

Used to describe date and time interval columns

UUID

Used to describe uuid columns

HSTORE

Used to describe hstore columns

New in version 5.0.

JSON

Used to describe json and jsonb columns

New in version 5.0.

ARRAY

Used to describe columns containing PostgreSQL arrays

New in version 5.0.

RECORD

Used to describe columns containing PostgreSQL records

New in version 5.0.

Example for using some type objects:

>>> cursor = con.cursor()
>>> cursor.execute("create table jsondata (created date, data jsonb)")
>>> cursor.execute("select * from jsondata")
>>> (created, data) = (d.type_code for d in cursor.description)
>>> created == DATE
True
>>> created == DATETIME
True
>>> created == TIME
False
>>> data == JSON
True
>>> data == STRING
False