= pg8000
:toc: preamble

pg8000 is a pure-link:http://www.python.org/[Python]
http://www.postgresql.org/[PostgreSQL] driver that complies with
http://www.python.org/dev/peps/pep-0249/[DB-API 2.0]. It is tested on Python
versions 3.6+, on CPython and PyPy, and PostgreSQL versions 9.6+.
pg8000's name comes from the belief that it is probably about the 8000th
PostgreSQL interface for Python. pg8000 is distributed under the BSD 3-clause
license.

All bug reports, feature requests and contributions are welcome at
http://github.com/tlocke/pg8000/.

image::https://github.com/tlocke/pg8000/workflows/pg8000/badge.svg[Build Status]


== Installation

To install pg8000 using `pip` type:

`pip install pg8000`


== Native API Interactive Examples

pg8000 comes with two APIs, the native pg8000 API and the DB-API 2.0 standard
API. These are the examples for the native API, and the DB-API 2.0 examples
follow in the next section.


=== Basic Example

Import pg8000, connect to the database, create a table, add some rows and then
query the table:

[source,python]
----
>>> import pg8000.native
>>>
>>> # Connect to the database with user name postgres
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> # Create a temporary table
>>>
>>> con.run("CREATE TEMPORARY TABLE book (id SERIAL, title TEXT)")
>>>
>>> # Populate the table
>>>
>>> for title in ("Ender's Game", "The Magus"):
...     con.run("INSERT INTO book (title) VALUES (:title)", title=title)
>>>
>>> # Print all the rows in the table
>>>
>>> for row in con.run("SELECT * FROM book"):
...     print(row)
[1, "Ender's Game"]
[2, 'The Magus']

----


=== Transactions

Here's how to run groups of SQL statements in a
https://www.postgresql.org/docs/current/tutorial-transactions.html[transaction]:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("START TRANSACTION")
>>>
>>> # Create a temporary table
>>> con.run("CREATE TEMPORARY TABLE book (id SERIAL, title TEXT)")
>>>
>>> for title in ("Ender's Game", "The Magus", "Phineas Finn"):
...     con.run("INSERT INTO book (title) VALUES (:title)", title=title)
>>> con.run("COMMIT")
>>> for row in con.run("SELECT * FROM book"):
...     print(row)
[1, "Ender's Game"]
[2, 'The Magus']
[3, 'Phineas Finn']

----

rolling back a transaction:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> # Create a temporary table
>>> con.run("CREATE TEMPORARY TABLE book (id SERIAL, title TEXT)")
>>>
>>> for title in ("Ender's Game", "The Magus", "Phineas Finn"):
...     con.run("INSERT INTO book (title) VALUES (:title)", title=title)
>>>
>>> con.run("START TRANSACTION")
>>> con.run("DELETE FROM book WHERE title = :title", title="Phineas Finn") 
>>> con.run("ROLLBACK")
>>> for row in con.run("SELECT * FROM book"):
...     print(row)
[1, "Ender's Game"]
[2, 'The Magus']
[3, 'Phineas Finn']

----


=== Query Using Fuctions

Another query, using some PostgreSQL functions:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("SELECT TO_CHAR(TIMESTAMP '2021-10-10', 'YYYY BC')")
[['2021 AD']]

----


=== Interval Type

A query that returns the PostgreSQL interval type:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> import datetime
>>>
>>> ts = datetime.date(1980, 4, 27)
>>> con.run("SELECT timestamp '2013-12-01 16:06' - :ts", ts=ts)
[[datetime.timedelta(days=12271, seconds=57960)]]

----


=== Point Type

A round-trip with a
https://www.postgresql.org/docs/current/datatype-geometric.html[PostgreSQL
point] type:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("SELECT CAST(:pt as point)", pt='(2.3,1)')
[['(2.3,1)']]

----


=== Client Encoding

When communicating with the server, pg8000 uses the character set that the
server asks it to use (the client encoding). By default the client encoding is
the database's character set (chosen when the database is created), but the
client encoding can be changed in a number of ways (eg. setting
CLIENT_ENCODING in postgresql.conf). Another way of changing the client
encoding is by using an SQL command. For example:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("SET CLIENT_ENCODING TO 'UTF8'")
>>> con.run("SHOW CLIENT_ENCODING")
[['UTF8']]

----


=== JSON

https://www.postgresql.org/docs/current/datatype-json.html[JSON] always comes
back from the server de-serialized. If the JSON you want to send is a `dict`
then you can just do:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> val = {'name': 'Apollo 11 Cave', 'zebra': True, 'age': 26.003}
>>> con.run("SELECT CAST(:apollo as jsonb)", apollo=val)
[[{'age': 26.003, 'name': 'Apollo 11 Cave', 'zebra': True}]]

----

JSON can always be sent in serialized form to the server:

[source,python]
----
>>> import json
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>>
>>> val = ['Apollo 11 Cave', True, 26.003]
>>> con.run("SELECT CAST(:apollo as jsonb)", apollo=json.dumps(val))
[[['Apollo 11 Cave', True, 26.003]]]

----


=== Retrieve Column Metadata From Results

Find the column metadata returned from a query:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("create temporary table quark (id serial, name text)")
>>> for name in ('Up', 'Down'):
...     con.run("INSERT INTO quark (name) VALUES (:name)", name=name)
>>> # Now execute the query
>>>
>>> con.run("SELECT * FROM quark")
[[1, 'Up'], [2, 'Down']]
>>>
>>> # and retried the metadata
>>>
>>> con.columns
[{'table_oid': ..., 'column_attrnum': 1, 'type_oid': 23, 'type_size': 4, 'type_modifier': -1, 'format': 0, 'name': 'id'}, {'table_oid': ..., 'column_attrnum': 2, 'type_oid': 25, 'type_size': -1, 'type_modifier': -1, 'format': 0, 'name': 'name'}]
>>>
>>> # Show just the column names
>>>
>>> [c['name'] for c in con.columns]
['id', 'name']

----


=== Notices And Notifications

PostgreSQL https://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html[notices]
are stored in a deque called `Connection.notices` and added using the
`append()` method. Similarly there are `Connection.notifications` for
https://www.postgresql.org/docs/current/static/sql-notify.html[notifications]
and `Connection.parameter_statuses` for changes to the server configuration.
Here's an example:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("LISTEN aliens_landed")
>>> con.run("NOTIFY aliens_landed")
>>> # A notification is a tuple containing (backend_pid, channel, payload)
>>>
>>> con.notifications[0]
(..., 'aliens_landed', '')

----


=== LIMIT ALL

You might think that the following would work, but in fact it fails:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("SELECT 'silo 1' LIMIT :lim", lim='ALL')
Traceback (most recent call last):
pg8000.exceptions.DatabaseError: ...

----

Instead the https://www.postgresql.org/docs/current/sql-select.html[docs say]
that you can send `null` as an alternative to `ALL`, which does work:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("SELECT 'silo 1' LIMIT :lim", lim=None)
[['silo 1']]

----


=== IN and NOT IN

You might think that the following would work, but in fact the server doesn't
like it:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("SELECT 'silo 1' WHERE 'a' IN :v", v=('a', 'b'))
Traceback (most recent call last):
pg8000.exceptions.DatabaseError: ...

----

instead you can write it using the
https://www.postgresql.org/docs/current/functions-array.html[`unnest`]
function:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run(
...     "SELECT 'silo 1' WHERE 'a' IN (SELECT unnest(CAST(:v as varchar[])))",
...     v=('a', 'b'))
[['silo 1']]

----

and you can do the same for `NOT IN`.


=== Many SQL Statements Can't Be Parameterized

In PostgreSQL parameters can only be used for
https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS[data values, not identifiers]. Sometimes this might not work as expected,
for example the following fails:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("CREATE USER juan WITH PASSWORD :password", password='quail')
Traceback (most recent call last):
pg8000.exceptions.DatabaseError: ...

----

It fails because the PostgreSQL server doesn't allow this statement to have
any parameters. There are many SQL statements that one might think would have
parameters, but don't.


=== COPY from and to a file

The SQL https://www.postgresql.org/docs/current/sql-copy.html[COPY] statement
can be used to copy from and to a file or file-like object. Here' an example
using the CSV format:

[source,python]
----

>>> import pg8000.native
>>> from io import StringIO
>>> import csv
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> # Create a CSV file in memory
>>>
>>> stream_in = StringIO()
>>> csv_writer = csv.writer(stream_in)
>>> csv_writer.writerow([1, "electron"])
12
>>> csv_writer.writerow([2, "muon"])
8
>>> csv_writer.writerow([3, "tau"])
7
>>> stream_in.seek(0)
0
>>>
>>> # Create a table and then copy the CSV into it
>>>
>>> con.run("CREATE TEMPORARY TABLE lepton (id SERIAL, name TEXT)")
>>> con.run("COPY lepton FROM STDIN WITH (FORMAT CSV)", stream=stream_in)
>>>
>>> # COPY from a table to a stream
>>>
>>> stream_out = StringIO()
>>> con.run("COPY lepton TO STDOUT WITH (FORMAT CSV)", stream=stream_out)
>>> stream_out.seek(0)
0
>>> for row in csv.reader(stream_out):
...     print(row)
['1', 'electron']
['2', 'muon']
['3', 'tau']

----


=== Execute Multiple SQL Statements

If you want to execute a series of SQL statements (eg. an `.sql` file), you
can run them as expected:

[source,python]
----

>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> statements = "SELECT 5; SELECT 'Erich Fromm';"
>>>
>>> con.run(statements)
[[5], ['Erich Fromm']]

----

The only caveat is that when executing multiple statements you can't have any
parameters.


=== Quoted Identifiers in SQL

Say you had a column called `My Column`. Since it's case sensitive and
contains a space, you'd have to
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERSdouble[surround it by double quotes]. But you can't do:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("select 'hello' as "My Column"")
Traceback (most recent call last):
SyntaxError: invalid syntax...

----

since Python uses double quotes to delimit string literals, so one solution is
to use Python's
https://docs.python.org/3/tutorial/introduction.html#strings[triple quotes]
to delimit the string instead:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run('''select 'hello' as "My Column"''')
[['hello']]

----


=== Custom adapter from a Python type to a PostgreSQL type

pg8000 has a mapping from Python types to PostgreSQL types for when it needs
to send SQL parameters to the server. The default mapping that comes with
pg8000 is designed to work well in most cases, but you might want to add or
replace the default mapping.

A Python `datetime.timedelta` object is sent to the server as a PostgreSQL
`interval` type,  which has the `oid` 1186. But let's say we wanted to create
our own Python class to be sent as an `interval` type. Then we'd have to
register an adapter:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> class MyInterval(str):
...     pass
>>>
>>> def my_interval_out(my_interval):
...     return my_interval  # Must return a str
>>>
>>> con.register_out_adapter(MyInterval, my_interval_out)
>>> con.run("SELECT CAST(:interval as interval)", interval=MyInterval("2 hours"))
[[datetime.timedelta(seconds=7200)]]

----

Note that it still came back as a `datetime.timedelta` object because we only
changed the mapping from Python to PostgreSQL. See below for an example of how
to change the mapping from PostgreSQL to Python.


=== Custom adapter from a PostgreSQL type to a Python type

pg8000 has a mapping from PostgreSQL types to Python types for when it receives
SQL results from the server. The default mapping that comes with pg8000 is
designed to work well in most cases, but you might want to add or replace the
default mapping.

If pg800 recieves PostgreSQL `interval` type, which has the `oid` 1186, it
converts it into a Python `datetime.timedelta` object. But let's say we wanted
to create our own Python class to be used instead of `datetime.timedelta`. Then
we'd have to register an adapter:


[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> class MyInterval(str):
...     pass
>>>
>>> def my_interval_in(my_interval_str):  # The parameter is of type str
...     return MyInterval(my_interval)
>>>
>>> con.register_in_adapter(1186, my_interval_in)
>>> con.run("SELECT \'2 years'")
[['2 years']]

----

Note that registering the 'in' adapter only afects the mapping from the
PostgreSQL type to the Python type. See above for an example of how to change
the mapping from PostgreSQL to Python.


=== Could Not Determine Data Type Of Parameter

Sometimes you'll get the 'could not determine data type of parameter' error
message from the server:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("SELECT :v IS NULL", v=None)
Traceback (most recent call last):
pg8000.exceptions.DatabaseError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42P18', 'M': 'could not determine data type of parameter $1', 'F': 'postgres.c', 'L': '...', 'R': 'exec_parse_message'}

----

One way of solving it is to put a `cast` in the SQL:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>

>>> con.run("SELECT cast(:v as TIMESTAMP) IS NULL", v=None)
[[True]]

----

Another way is to override the type that pg8000 sends along with each
parameter:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> con.run("SELECT :v IS NULL", v=None, types={'v': pg8000.native.TIMESTAMP})
[[True]]

----


=== Prepared Statements

https://www.postgresql.org/docs/current/sql-prepare.html[Prepared statements]
can be useful in improving performance when you have a statement that's
executed repeatedly. Here's an example:


[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection("postgres", password="cpsnow")
>>>
>>> # Create the prepared statement
>>> ps = con.prepare("SELECT cast(:v as varchar)")
>>>
>>> # Exceute the statement repeatedly
>>> ps.run(v="speedy")
[['speedy']]
>>> ps.run(v="rapid")
[['rapid']]
>>> ps.run(v="swift")
[['swift']]
>>>
>>> # Close the prepared statement, releasing resources on the server
>>> ps.close()

----


=== Use Environment Variables As Connection Defaults

You might want to use the current user as the database username for example:

[source,python]
----
>>> import pg8000.native
>>> import getpass
>>>
>>> # Connect to the database with current user name
>>> username = getpass.getuser()
>>> connection = pg8000.native.Connection(username, password="cpsnow")
>>>
>>> connection.run("SELECT 'pilau'")
[['pilau']]

----

or perhaps you may want to use some of the same
https://www.postgresql.org/docs/current/libpq-envars.html[environment variables
that libpq uses]:

[source,python]
----
>>> import pg8000.native
>>> from os import environ
>>>
>>> username = environ.get('PGUSER', 'postgres')
>>> password = environ.get('PGPASSWORD', 'cpsnow')
>>> host = environ.get('PGHOST', 'localhost')
>>> port = environ.get('PGPORT', '5432')
>>> database = environ.get('PGDATABASE')
>>>
>>> connection = pg8000.native.Connection(
...     username, password=password, host=host, port=port, database=database)
>>>
>>> connection.run("SELECT 'Mr Cairo'")
[['Mr Cairo']]

----

It might be asked, why doesn't pg8000 have this behaviour built in? The
thinking follows the second aphorism of
https://www.python.org/dev/peps/pep-0020/[The Zen of Python]:

[quote]
Explicit is better than implicit.

So we've taken the approach of only being able to set connection parameters
using the `pg8000.native.Connection()` constructor.


=== Connect To PostgreSQL Over SSL

To connect to the server using SSL defaults do:

[source,python]
----

import pg8000.native


connection = pg8000.native.Connection(
    username, password="cpsnow", ssl_context=True)
connection.run("SELECT 'The game is afoot!'")

----

To connect over SSL with custom settings, set the `ssl_context` parameter to
an https://docs.python.org/3/library/ssl.html#ssl.SSLContext[`ssl.SSLContext`]
object:

[source,python]
----

import pg8000.native
import ssl


ssl_context = ssl.SSLContext()
ssl_context.verify_mode = ssl.CERT_REQUIRED
ssl_context.load_verify_locations('root.pem')        
connection = pg8000.native.Connection(
    username, password="cpsnow", ssl_context=ssl_context)

----

It may be that your PostgreSQL server is behind an SSL proxy server in which
case you can set a pg8000-specific attribute
`ssl.SSLContext.request_ssl = False` which tells pg8000 to connect using an
SSL socket, but not to request SSL from the PostgreSQL server:

[source,python]
----

import pg8000.native
import ssl


ssl_context = ssl.SSLContext()
ssl_context.request_ssl = False
connection = pg8000.native.Connection(
    username, password="cpsnow", ssl_context=ssl_context)

----


=== Server-Side Cursors

You can use the SQL commands
https://www.postgresql.org/docs/current/sql-declare.html[`DECLARE`],
https://www.postgresql.org/docs/current/sql-fetch.html[`FETCH`],
https://www.postgresql.org/docs/current/sql-move.html[`MOVE`] and
https://www.postgresql.org/docs/current/sql-close.html[`CLOSE`] to manipulate
server-side cursors. For example:

[source,python]
----

>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection(username, password="cpsnow")
>>> con.run("START TRANSACTION")
>>> con.run("DECLARE c SCROLL CURSOR FOR SELECT * FROM generate_series(1, 100)")
>>> con.run("FETCH FORWARD 5 FROM c")
[[1], [2], [3], [4], [5]]
>>> con.run("MOVE FORWARD 50 FROM c")
>>> con.run("FETCH BACKWARD 10 FROM c")
[[54], [53], [52], [51], [50], [49], [48], [47], [46], [45]]
>>> con.run("CLOSE c")
>>> con.run("ROLLBACK")

----


=== BLOBs (Binary Large Objects)

There's a set of
https://www.postgresql.org/docs/current/lo-funcs.html[SQL functions]
for manipulating BLOBs. Here's an example:

[source,python]
----
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection(username, password="cpsnow")
>>>
>>> # Create a BLOB and get its oid
>>> data = b'hello'
>>> res = con.run("SELECT lo_from_bytea(0, :data)", data=data)
>>> oid = res[0][0]
>>>
>>> # Create a table and store the oid of the BLOB
>>> con.run("CREATE TEMPORARY TABLE image (raster oid)")
>>>
>>> con.run("INSERT INTO image (raster) VALUES (:oid)", oid=oid)
>>> # Retrieve the data using the oid
>>> con.run("SELECT lo_get(:oid)", oid=oid)
[[b'hello']]
>>>
>>> # Add some data to the end of the BLOB
>>> more_data = b' all'
>>> offset = len(data)
>>> con.run(
...     "SELECT lo_put(:oid, :offset, :data)",
...     oid=oid, offset=offset, data=more_data)
[['']]
>>> con.run("SELECT lo_get(:oid)", oid=oid)
[[b'hello all']]
>>>
>>> # Download a part of the data
>>> con.run("SELECT lo_get(:oid, 6, 3)", oid=oid)
[[b'all']]

----


== DB-API 2 Interactive Examples

These examples stick to the DB-API 2.0 standard.


=== Basic Example

Import pg8000, connect to the database, create a table, add some rows and then
query the table:

[source,python]
----
>>> import pg8000.dbapi
>>>
>>> conn = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cursor = conn.cursor()
>>> cursor.execute("CREATE TEMPORARY TABLE book (id SERIAL, title TEXT)")
>>> cursor.execute(
...     "INSERT INTO book (title) VALUES (%s), (%s) RETURNING id, title",
...     ("Ender's Game", "Speaker for the Dead"))
>>> results = cursor.fetchall()
>>> for row in results:
...     id, title = row
...     print("id = %s, title = %s" % (id, title))
id = 1, title = Ender's Game
id = 2, title = Speaker for the Dead
>>> conn.commit()

----


=== Query Using Fuctions

Another query, using some PostgreSQL functions:

[source,python]
----
>>> import pg8000.dbapi
>>>
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cursor = con.cursor()
>>>
>>> cursor.execute("SELECT TO_CHAR(TIMESTAMP '2021-10-10', 'YYYY BC')")
>>> cursor.fetchone()
['2021 AD']

----


=== Interval Type

A query that returns the PostgreSQL interval type:

[source,python]
----
>>> import datetime
>>> import pg8000.dbapi
>>>
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cursor = con.cursor()
>>>
>>> cursor.execute("SELECT timestamp '2013-12-01 16:06' - %s",
... (datetime.date(1980, 4, 27),))
>>> cursor.fetchone()
[datetime.timedelta(days=12271, seconds=57960)]

----


=== Point Type

A round-trip with a
https://www.postgresql.org/docs/current/datatype-geometric.html[PostgreSQL
point] type:

[source,python]
----
>>> import pg8000.dbapi
>>>
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cursor = con.cursor()
>>>
>>> cursor.execute("SELECT cast(%s as point)", ('(2.3,1)',))
>>> cursor.fetchone()
['(2.3,1)']

----


=== Numeric Parameter Style

pg8000 supports all the DB-API parameter styles. Here's an example of using
the 'numeric' parameter style:

[source,python]
----
>>> import pg8000.dbapi
>>>
>>> pg8000.dbapi.paramstyle = "numeric"
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cursor = con.cursor()
>>>
>>> cursor.execute("SELECT array_prepend(:1, CAST(:2 AS int[]))", (500, [1, 2, 3, 4],))
>>> cursor.fetchone()
[[500, 1, 2, 3, 4]]
>>> pg8000.dbapi.paramstyle = "format"
>>> conn.rollback()

----


=== Autocommit

Following the DB-API specification, autocommit is off by default. It can be
turned on by using the autocommit property of the connection.

[source,python]
----
>>> import pg8000.dbapi
>>>
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> con.autocommit = True
>>>
>>> cur = con.cursor()
>>> cur.execute("vacuum")
>>> conn.autocommit = False
>>> cur.close()

----


=== Client Encoding

When communicating with the server, pg8000 uses the character set that the
server asks it to use (the client encoding). By default the client encoding is
the database's character set (chosen when the database is created), but the
client encoding can be changed in a number of ways (eg. setting
CLIENT_ENCODING in postgresql.conf). Another way of changing the client
encoding is by using an SQL command. For example:

[source,python]
----
>>> import pg8000.dbapi
>>>
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cur = con.cursor()
>>> cur.execute("SET CLIENT_ENCODING TO 'UTF8'")
>>> cur.execute("SHOW CLIENT_ENCODING")
>>> cur.fetchone()
['UTF8']
>>> cur.close()

----


=== JSON

JSON is sent to the server serialized, and returned de-serialized. Here's an
example:

[source,python]
----
>>> import json
>>> import pg8000.dbapi
>>>
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cur = con.cursor()
>>> val = ['Apollo 11 Cave', True, 26.003]
>>> cur.execute("SELECT cast(%s as json)", (json.dumps(val),))
>>> cur.fetchone()
[['Apollo 11 Cave', True, 26.003]]
>>> cur.close()

----


=== Retrieve Column Names From Results

Use the columns names retrieved from a query:

[source,python]
----
>>> import pg8000
>>> conn = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> c = conn.cursor()
>>> c.execute("create temporary table quark (id serial, name text)")
>>> c.executemany("INSERT INTO quark (name) VALUES (%s)", (("Up",), ("Down",)))
>>> #
>>> # Now retrieve the results
>>> #
>>> c.execute("select * from quark")
>>> rows = c.fetchall()
>>> keys = [k[0] for k in c.description]
>>> results = [dict(zip(keys, row)) for row in rows]
>>> assert results == [{'id': 1, 'name': 'Up'}, {'id': 2, 'name': 'Down'}]

----


=== Notices

PostgreSQL https://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html[notices]
are stored in a deque called `Connection.notices` and added using the
`append()` method. Similarly there are `Connection.notifications` for
https://www.postgresql.org/docs/current/static/sql-notify.html[notifications]
and `Connection.parameter_statuses` for changes to the server configuration.
Here's an example:

[source,python]
----
>>> import pg8000.dbapi
>>>
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cur = con.cursor()
>>> cur.execute("LISTEN aliens_landed")
>>> cur.execute("NOTIFY aliens_landed")
>>> con.commit()
>>> con.notifications[0][1]
'aliens_landed'

----


=== COPY from and to a file

The SQL https://www.postgresql.org/docs/current/sql-copy.html[COPY] statement
can be used to copy from and to a file or file-like object:

[source,python]
----
>>> from io import StringIO
>>> import pg8000.dbapi
>>>
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cur = con.cursor()
>>> #
>>> # COPY from a stream to a table
>>> #
>>> stream_in = StringIO('1\telectron\n2\tmuon\n3\ttau\n')
>>> cur = conn.cursor()
>>> cur.execute("create temporary table lepton (id serial, name text)")
>>> cur.execute("COPY lepton FROM stdin", stream=stream_in)
>>> #
>>> # Now COPY from a table to a stream
>>> #
>>> stream_out = StringIO()
>>> cur.execute("copy lepton to stdout", stream=stream_out)
>>> stream_out.getvalue()
'1\telectron\n2\tmuon\n3\ttau\n'

----


=== Server-Side Cursors

You can use the SQL commands
https://www.postgresql.org/docs/current/sql-declare.html[`DECLARE`],
https://www.postgresql.org/docs/current/sql-fetch.html[`FETCH`],
https://www.postgresql.org/docs/current/sql-move.html[`MOVE`] and
https://www.postgresql.org/docs/current/sql-close.html[`CLOSE`] to manipulate
server-side cursors. For example:

[source,python]
----
>>> import pg8000.dbapi
>>>
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cur = con.cursor()
>>> cur.execute("START TRANSACTION")
>>> cur.execute(
...    "DECLARE c SCROLL CURSOR FOR SELECT * FROM generate_series(1, 100)")
>>> cur.execute("FETCH FORWARD 5 FROM c")
>>> cur.fetchall()
([1], [2], [3], [4], [5])
>>> cur.execute("MOVE FORWARD 50 FROM c")
>>> cur.execute("FETCH BACKWARD 10 FROM c")
>>> cur.fetchall()
([54], [53], [52], [51], [50], [49], [48], [47], [46], [45])
>>> cur.execute("CLOSE c")
>>> cur.execute("ROLLBACK")

----


=== BLOBs (Binary Large Objects)

There's a set of
https://www.postgresql.org/docs/current/lo-funcs.html[SQL functions]
for manipulating BLOBs. Here's an example:

[source,python]
----
>>> import pg8000.dbapi
>>>
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cur = con.cursor()
>>>
>>> # Create a BLOB and get its oid
>>> data = b'hello'
>>> cur = conn.cursor()
>>> cur.execute("SELECT lo_from_bytea(0, %s)", [data])
>>> oid = cur.fetchone()[0]
>>>
>>> # Create a table and store the oid of the BLOB
>>> cur.execute("CREATE TEMPORARY TABLE image (raster oid)")
>>> cur.execute("INSERT INTO image (raster) VALUES (%s)", [oid])
>>>
>>> # Retrieve the data using the oid
>>> cur.execute("SELECT lo_get(%s)", [oid])
>>> cur.fetchall()
([b'hello'],)
>>>
>>> # Add some data to the end of the BLOB
>>> more_data = b' all'
>>> offset = len(data)
>>> cur.execute("SELECT lo_put(%s, %s, %s)", [oid, offset, more_data])
>>> cur.execute("SELECT lo_get(%s)", [oid])
>>> cur.fetchall()
([b'hello all'],)
>>>
>>> # Download a part of the data
>>> cur.execute("SELECT lo_get(%s, 6, 3)", [oid])
>>> cur.fetchall()
([b'all'],)

----



== Type Mapping

The following table shows the default mapping between Python types and
PostgreSQL types, and vice versa.

If pg8000 doesn't recognize a type that it receives from PostgreSQL, it will
return it as a `str` type. This is how pg8000 handles PostgreSQL `enum` and
XML types. It's possible to change the default mapping using adapters (see the
examples).

.Python to PostgreSQL Type Mapping
|===
| Python Type | PostgreSQL Type | Notes

| bool
| bool
|

| int
| int4
|

| str
| text
|

| float
| float8
|

| decimal.Decimal
| numeric
|

| bytes
| bytea
|

| datetime.datetime (without tzinfo)
| timestamp without timezone
| See note below.

| datetime.datetime (with tzinfo)
| timestamp with timezone
| See note below.

| datetime.date
| date
| See note below.

| datetime.time
| time without time zone
|

| datetime.timedelta
| interval
|

| None
| NULL
|

| uuid.UUID
| uuid
|

| ipaddress.IPv4Address
| inet
|

| ipaddress.IPv6Address
| inet
|

| ipaddress.IPv4Network
| inet
|

| ipaddress.IPv6Network
| inet
|

| int
| xid
|

| list of int
| INT4[]
|

| list of float
| FLOAT8[]
|

| list of bool
| BOOL[]
|

| list of str
| TEXT[]
|

| int
| int2vector
| Only from PostgreSQL to Python

| JSON
| json, jsonb
| The Python JSON is provided as a Python serialized string. Results returned
  as de-serialized JSON.
|===


[[_theory_of_operation]]
== Theory Of Operation

{empty} +

[quote, Jochen Liedtke, Liedtke's minimality principle]
____
A concept is tolerated inside the microkernel only if moving it outside the
kernel, i.e., permitting competing implementations, would prevent the
implementation of the system's required functionality.
____


pg8000 is designed to be used with one thread per connection.

Pg8000 communicates with the database using the
https://www.postgresql.org/docs/current/protocol.html[PostgreSQL Frontend/Backend
Protocol] (FEBE). If a query has no parameters, pg8000 uses the
'simple query protocol'. If a query does have parameters, pg8000 uses the
'extended query protocol' with unnamed prepared statements. The steps for a query with
parameters are:

. Query comes in.
. Send a PARSE message to the server to create an unnamed prepared statement.
. Send a BIND message to run against the unnamed prepared statement, resulting in an
  unnamed portal on the server.
. Send an EXECUTE message to read all the results from the portal.

It's also possible to use named prepared statements. In which case the prepared
statement persists on the server, and represented in pg8000 using a PreparedStatement
object. This means that the PARSE step gets executed once up front, and then only the
BIND and EXECUTE steps are repeated subsequently.

There are a lot of PostgreSQL data types, but few primitive data types in Python. By
default, pg8000 doesn't send PostgreSQL data type information in the PARSE step, in
which case PostgreSQL assumes the types implied by the SQL statement. In some cases
PostgreSQL can't work out a parameter type and so an
https://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS[explicit cast] can be used in the SQL.

In the FEBE protocol, each query parameter can be sent to the server either as binary
or text according to the format code. In pg8000 the parameters are always sent as text.

* PostgreSQL has +/-infinity values for dates and timestamps, but Python does not.
  Pg8000 handles this by returning +/-infinity strings in results, and in parameters
  the strings +/- infinity can be used.

* PostgreSQL dates/timestamps can have values outside the range of Python datetimes.
  These are handled using the underlying PostgreSQL storage method. I don't know of any
  users of pg8000 that use this feature, so get in touch if it affects you.

* Occasionally, the network connection between pg8000 and the server may go
  down. If pg8000 encounters a network problem it'll raise an `InterfaceError`
  with an error message starting with `network error` and with the original
  exception set as the
  https://docs.python.org/3/reference/simple_stmts.html#the-raise-statement[cause].


== Native API Docs

=== pg8000.native.Connection(user, host='localhost', database=None, port=5432, password=None, source_address=None, unix_sock=None, ssl_context=None, timeout=None, tcp_keepalive=True, application_name=None, replication=None)

Creates a connection to a PostgreSQL database.

user::
  The username to connect to the PostgreSQL server with. If your server
  character encoding is not `ascii` or `utf8`, then you need to provide
  `user` as bytes, eg. `'my_name'.encode('EUC-JP')`.

host::
  The hostname of the PostgreSQL server to connect with. Providing this
  parameter is necessary for TCP/IP connections. One of either `host` or
  `unix_sock` must be provided. The default is `localhost`.

database::
  The name of the database instance to connect with. If `None` then the
  PostgreSQL server will assume the database name is the same as the username.
  If your server character encoding is not `ascii` or `utf8`, then you need to
  provide `database` as bytes, eg. `'my_db'.encode('EUC-JP')`.

port::
  The TCP/IP port of the PostgreSQL server instance.  This parameter defaults
  to `5432`, the registered common port of PostgreSQL TCP/IP servers.

password::
  The user password to connect to the server with. This parameter is optional;
  if omitted and the database server requests password-based authentication,
  the connection will fail to open. If this parameter is provided but not
  requested by the server, no error will occur. +
   +
  If your server character encoding is not `ascii` or `utf8`, then
  you need to provide `password` as bytes, eg.
  `'my_password'.encode('EUC-JP')`.


source_address::
  The source IP address which initiates the connection to the PostgreSQL server.
  The default is `None` which means that the operating system will choose the
  source address.

unix_sock::
  The path to the UNIX socket to access the database through, for example,
  `'/tmp/.s.PGSQL.5432'`. One of either `host` or `unix_sock` must be provided.

ssl_context::
  This governs SSL encryption for TCP/IP sockets. It can have three values:
    * `None`, meaning no SSL (the default)
    * `True`, means use SSL with an
       https://docs.python.org/3/library/ssl.html#ssl.SSLContext[`ssl.SSLContext`]
       created using
      https://docs.python.org/3/library/ssl.html#ssl.create_default_context[`ssl.create_default_context()`]
    *  An instance of
       https://docs.python.org/3/library/ssl.html#ssl.SSLContext[`ssl.SSLContext`]
       which will be used to create the SSL connection. +
       +
       If your PostgreSQL server is behind an SSL proxy, you can set the
       pg8000-specific attribute `ssl.SSLContext.request_ssl = False`, which
       tells pg8000 to use an SSL socket, but not to request SSL from the
       PostgreSQL server. Note that this means you can't use SCRAM
       authentication with channel binding.

timeout::
  This is the time in seconds before the connection to the server will time
  out. The default is `None` which means no timeout.

tcp_keepalive::
  If `True` then use
  https://en.wikipedia.org/wiki/Keepalive#TCP_keepalive[TCP keepalive]. The
  default is `True`.

application_name::
  Sets the https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-APPLICATION-NAME[application_name]. If your server character encoding is not
  `ascii` or `utf8`, then you need to provide values as bytes, eg.
  `'my_application_name'.encode('EUC-JP')`. The default is `None` which means
  that the server will set the application name.

replication::
  Used to run in https://www.postgresql.org/docs/12/protocol-replication.html[streaming replication mode].
  If your server character encoding is not `ascii` or `utf8`, then you need to
  provide values as bytes, eg. `'database'.encode('EUC-JP')`.


=== pg8000.native.Error

Generic exception that is the base exception of the other error exceptions.


=== pg8000.native.InterfaceError

For errors that originate within pg8000.


=== pg8000.native.DatabaseError

For errors that originate from the server.

=== pg8000.native.Connection.notifications

A deque of server-side
https://www.postgresql.org/docs/current/sql-notify.html[notifications] received
by this database connection (via the LISTEN / NOTIFY PostgreSQL commands). Each
list item is a three-element tuple containing the PostgreSQL backend PID that
issued the notify, the channel and the payload.


=== pg8000.native.Connection.notices

A deque of server-side notices received by this database connection.


=== pg8000.native.Connection.parameter_statuses

A deque of server-side parameter statuses received by this database connection.


=== pg8000.native.Connection.run(sql, stream=None, types=None, **kwargs)

Executes an sql statement, and returns the results as a `list`. For example:

`con.run("SELECT * FROM cities where population > :pop", pop=10000)`

sql::
  The SQL statement to execute. Parameter placeholders appear as a `:` followed
  by the parameter name.

stream::
  For use with the PostgreSQL
http://www.postgresql.org/docs/current/static/sql-copy.html[COPY] command. For
a `COPY FROM` the parameter must be a readable file-like object, and for
`COPY TO` it must be writable.

types::
  A dictionary of oids. A key corresponds to a parameter. 

kwargs::
  The parameters of the SQL statement.


=== pg8000.native.Connection.row_count

This read-only attribute contains the number of rows that the last `run()`
method produced (for query statements like `SELECT`) or affected (for
modification statements like `UPDATE`.

The value is -1 if:

* No `run()` method has been performed yet.
* There was no rowcount associated with the last `run()`.
* Using a `SELECT` query statement on a PostgreSQL server older than version
  9.
* Using a `COPY` query statement on PostgreSQL server version 8.1 or older.


=== pg8000.native.Connection.columns

A list of column metadata. Each item in the list is a dictionary with the
following keys:

* name
* table_oid
* column_attrnum
* type_oid
* type_size
* type_modifier
* format


=== pg8000.native.Connection.close()

Closes the database connection.


=== pg8000.native.Connection.register_out_adapter(typ, oid, out_func)

Register a type adapter for types going out from pg8000 to the server.

typ::
  The Python class that the adapter is for.

oid::
  The PostgreSQL type identifier found in the
  https://www.postgresql.org/docs/current/catalog-pg-type.html[pg_type system
  calalog].

out_func::
  A function that takes the Python object and returns its string representation
  in the format that the server requires.


=== pg8000.native.Connection.register_in_adapter(oid, in_func)

Register a type adapter for types coming in from the server to pg8000.

oid::
  The PostgreSQL type identifier found in the
  https://www.postgresql.org/docs/current/catalog-pg-type.html[pg_type system
  calalog].

in_func::
  A function that takes the PostgreSQL string representation and returns
  a corresponding Python object.


=== pg8000.native.Connection.prepare(sql)

Returns a PreparedStatement object which represents a
https://www.postgresql.org/docs/current/sql-prepare.html[prepared statement] on
the server. It can subsequently be repeatedly executed as shown in the
<<_prepared_statements, example>>.

sql::
  The SQL statement to prepare. Parameter placeholders appear as a `:` followed
  by the parameter name.


=== pg8000.native.PreparedStatement

A prepared statement object is returned by the
`pg8000.native.Connection.prepare()` method of a connection. It has the
following methods:


=== pg8000.native.PreparedStatement.run(**kwargs)

Executes the prepared statement, and returns the results as a `tuple`.

kwargs::
  The parameters of the prepared statement.


=== pg8000.native.PreparedStatement.close()

Closes the prepared statement, releasing the prepared statement held on the
server.


== DB-API 2 Docs


=== Properties


==== pg8000.dbapi.apilevel

The DBAPI level supported, currently "2.0".

This property is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


==== pg8000.dbapi.threadsafety

Integer constant stating the level of thread safety the DBAPI interface
supports. For pg8000, the threadsafety value is 1, meaning that threads may
share the module but not connections.

This property is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

==== pg8000.dbapi.paramstyle

String property stating the type of parameter marker formatting expected by
the interface.  This value defaults to "format", in which parameters are
marked in this format: "WHERE name=%s".

This property is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

As an extension to the DBAPI specification, this value is not constant; it
can be changed to any of the following values:

qmark::
  Question mark style, eg. `WHERE name=?`

numeric::
  Numeric positional style, eg. `WHERE name=:1`

named::
  Named style, eg. `WHERE name=:paramname`

format::
  printf format codes, eg. `WHERE name=%s`

pyformat::
  Python format codes, eg. `WHERE name=%(paramname)s`


==== pg8000.dbapi.STRING

String type oid.

==== pg8000.dbapi.BINARY


==== pg8000.dbapi.NUMBER

Numeric type oid.


==== pg8000.dbapi.DATETIME

Timestamp type oid


==== pg8000.dbapi.ROWID

ROWID type oid


=== Functions

==== pg8000.dbapi.connect(user, host='localhost', database=None, port=5432, password=None, source_address=None, unix_sock=None, ssl_context=None, timeout=None, tcp_keepalive=True, application_name=None, replication=None)

Creates a connection to a PostgreSQL database.

This property is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

user::
  The username to connect to the PostgreSQL server with. If your server
  character encoding is not `ascii` or `utf8`, then you need to provide
  `user` as bytes, eg. `'my_name'.encode('EUC-JP')`.

host::
  The hostname of the PostgreSQL server to connect with. Providing this
  parameter is necessary for TCP/IP connections. One of either `host` or
  `unix_sock` must be provided. The default is `localhost`.

database::
  The name of the database instance to connect with. If `None` then the
  PostgreSQL server will assume the database name is the same as the username.
  If your server character encoding is not `ascii` or `utf8`, then you need to
  provide `database` as bytes, eg. `'my_db'.encode('EUC-JP')`.

port::
  The TCP/IP port of the PostgreSQL server instance.  This parameter defaults
  to `5432`, the registered common port of PostgreSQL TCP/IP servers.

password::
  The user password to connect to the server with. This parameter is optional;
  if omitted and the database server requests password-based authentication,
  the connection will fail to open. If this parameter is provided but not
  requested by the server, no error will occur. +
   +
  If your server character encoding is not `ascii` or `utf8`, then
  you need to provide `password` as bytes, eg.
  `'my_password'.encode('EUC-JP')`.


source_address::
  The source IP address which initiates the connection to the PostgreSQL server.
  The default is `None` which means that the operating system will choose the
  source address.

unix_sock::
  The path to the UNIX socket to access the database through, for example,
  `'/tmp/.s.PGSQL.5432'`. One of either `host` or `unix_sock` must be provided.

ssl_context::
  This governs SSL encryption for TCP/IP sockets. It can have three values:
    * `None`, meaning no SSL (the default)
    * `True`, means use SSL with an
       https://docs.python.org/3/library/ssl.html#ssl.SSLContext[`ssl.SSLContext`]
       created using
      https://docs.python.org/3/library/ssl.html#ssl.create_default_context[`ssl.create_default_context()`]
    *  An instance of
       https://docs.python.org/3/library/ssl.html#ssl.SSLContext[`ssl.SSLContext`]
       which will be used to create the SSL connection. +
       +
       If your PostgreSQL server is behind an SSL proxy, you can set the
       pg8000-specific attribute `ssl.SSLContext.request_ssl = False`, which
       tells pg8000 to use an SSL socket, but not to request SSL from the
       PostgreSQL server. Note that this means you can't use SCRAM
       authentication with channel binding.

timeout::
  This is the time in seconds before the connection to the server will time
  out. The default is `None` which means no timeout.

tcp_keepalive::
  If `True` then use
  https://en.wikipedia.org/wiki/Keepalive#TCP_keepalive[TCP keepalive]. The
  default is `True`.

application_name::
  Sets the https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-APPLICATION-NAME[application_name]. If your server character encoding is not
  `ascii` or `utf8`, then you need to provide values as bytes, eg.
  `'my_application_name'.encode('EUC-JP')`. The default is `None` which means
  that the server will set the application name.

replication::
  Used to run in https://www.postgresql.org/docs/12/protocol-replication.html[streaming replication mode].
  If your server character encoding is not `ascii` or `utf8`, then you need to
  provide values as bytes, eg. `'database'.encode('EUC-JP')`.


==== pg8000.dbapi.Date(year, month, day)

Constuct an object holding a date value.

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

Returns: `datetime.date`


==== pg8000.dbapi.Time(hour, minute, second)

Construct an object holding a time value.

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

Returns: `datetime.time`


==== pg8000.dbapi.Timestamp(year, month, day, hour, minute, second)

Construct an object holding a timestamp value.

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

Returns: `datetime.datetime`


==== pg8000.dbapi.DateFromTicks(ticks)

Construct an object holding a date value from the given ticks value (number of
seconds since the epoch).

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

Returns: `datetime.datetime`


==== pg8000.dbapi.TimeFromTicks(ticks)

Construct an objet holding a time value from the given ticks value (number of
seconds since the epoch).

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

Returns: `datetime.time`


==== pg8000.dbapi.TimestampFromTicks(ticks)

Construct an object holding a timestamp value from the given ticks value
(number of seconds since the epoch).


This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

Returns: `datetime.datetime`


==== pg8000.dbapi.Binary(value)

Construct an object holding binary data.

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

Returns: `bytes`.


=== Generic Exceptions

Pg8000 uses the standard DBAPI 2.0 exception tree as "generic" exceptions.
Generally, more specific exception types are raised; these specific exception
types are derived from the generic exceptions.

==== pg8000.dbapi.Warning

Generic exception raised for important database warnings like data truncations.
This exception is not currently used by pg8000.

This exception is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

==== pg8000.dbapi.Error

Generic exception that is the base exception of all other error exceptions.

This exception is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


==== pg8000.dbapi.InterfaceError

Generic exception raised for errors that are related to the database interface
rather than the database itself. For example, if the interface attempts to use
an SSL connection but the server refuses, an InterfaceError will be raised.

This exception is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


==== pg8000.dbapi.DatabaseError

Generic exception raised for errors that are related to the database. This
exception is currently never raised by pg8000.

This exception is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


==== pg8000.dbapi.DataError

Generic exception raised for errors that are due to problems with the processed
data. This exception is not currently raised by pg8000.

This exception is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


==== pg8000.dbapi.OperationalError

Generic exception raised for errors that are related to the database's
operation and not necessarily under the control of the programmer. This
exception is currently never raised by pg8000.

This exception is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


==== pg8000.dbapi.IntegrityError

Generic exception raised when the relational integrity of the database is
affected. This exception is not currently raised by pg8000.

This exception is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


==== pg8000.dbapi.InternalError

Generic exception raised when the database encounters an internal error. This
is currently only raised when unexpected state occurs in the pg8000 interface
itself, and is typically the result of a interface bug.

This exception is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


==== pg8000.dbapi.ProgrammingError

Generic exception raised for programming errors.  For example, this exception
is raised if more parameter fields are in a query string than there are
available parameters.

This exception is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


==== pg8000.dbapi.NotSupportedError

Generic exception raised in case a method or database API was used which is not
supported by the database.

This exception is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


=== Classes


==== pg8000.dbapi.Connection

A connection object is returned by the `pg8000.connect()` function. It
represents a single physical connection to a PostgreSQL database.

===== pg8000.dbapi.Connection.notifications

A deque of server-side
https://www.postgresql.org/docs/current/sql-notify.html[notifications] received
by this database connection (via the LISTEN / NOTIFY PostgreSQL commands). Each
list item is a three-element tuple containing the PostgreSQL backend PID that
issued the notify, the channel and the payload.

This attribute is not part of the DBAPI standard; it is a pg8000 extension.


===== pg8000.dbapi.Connection.notices

A deque of server-side notices received by this database connection.

This attribute is not part of the DBAPI standard; it is a pg8000 extension.


===== pg8000.dbapi.Connection.parameter_statuses

A deque of server-side parameter statuses received by this database connection.

This attribute is not part of the DBAPI standard; it is a pg8000 extension.


===== pg8000.dbapi.Connection.autocommit

Following the DB-API specification, autocommit is off by default. It can be
turned on by setting this boolean pg8000-specific autocommit property to True.

New in version 1.9.


===== pg8000.dbapi.Connection.close()

Closes the database connection.

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Connection.cursor()

Creates a `pg8000.Cursor` object bound to this connection.

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Connection.rollback()

Rolls back the current database transaction.

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Connection.tpc_begin(xid)

Begins a TPC transaction with the given transaction ID xid. This method should
be called outside of a transaction (i.e. nothing may have executed since the
last `commit()`  or `rollback()`. Furthermore, it is an error to call
`commit()` or `rollback()` within the TPC transaction. A `ProgrammingError` is
raised, if the application calls `commit()` or `rollback()` during an active
TPC transaction.

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Connection.tpc_commit(xid=None)

When called with no arguments, `tpc_commit()` commits a TPC transaction
previously prepared with `tpc_prepare()`. If `tpc_commit()` is called prior to
`tpc_prepare()`, a single phase commit is performed. A transaction manager may
choose to do this if only a single resource is participating in the global
transaction.

When called with a transaction ID `xid`, the database commits the given
transaction. If an invalid transaction ID is provided, a
ProgrammingError will be raised. This form should be called outside of
a transaction, and is intended for use in recovery.

On return, the TPC transaction is ended.

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Connection.tpc_prepare()

Performs the first phase of a transaction started with .tpc_begin(). A
ProgrammingError is be raised if this method is called outside of a TPC
transaction.

After calling `tpc_prepare()`, no statements can be executed until
`tpc_commit()` or `tpc_rollback()` have been called.

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Connection.tpc_recover()

Returns a list of pending transaction IDs suitable for use with
`tpc_commit(xid)` or `tpc_rollback(xid)`

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Connection.tpc_rollback(xid=None)

When called with no arguments, `tpc_rollback()` rolls back a TPC transaction.
It may be called before or after `tpc_prepare()`.

When called with a transaction ID xid, it rolls back the given transaction. If
an invalid transaction ID is provided, a `ProgrammingError` is raised. This
form should be called outside of a transaction, and is intended for use in
recovery.

On return, the TPC transaction is ended.

This function is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

===== pg8000.dbapi.Connection.xid(format_id, global_transaction_id, branch_qualifier)

Create a Transaction IDs (only global_transaction_id is used in pg) format_id
and branch_qualifier are not used in postgres global_transaction_id may be any
string identifier supported by postgres returns a tuple (format_id,
global_transaction_id, branch_qualifier)


==== pg8000.dbapi.Cursor

A cursor object is returned by the `pg8000.dbapi.Connection.cursor()` method
of a connection. It has the following attributes and methods:

===== pg8000.dbapi.Cursor.arraysize

This read/write attribute specifies the number of rows to fetch at a time with
`pg8000.dbapi.Cursor.fetchmany()`.  It defaults to 1.


===== pg8000.dbapi.Cursor.connection

This read-only attribute contains a reference to the connection object
(an instance of `pg8000.dbapi.Connection`) on which the cursor was created.

This attribute is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Cursor.rowcount

This read-only attribute contains the number of rows that the last
`execute()` or `executemany()` method produced (for query statements like
`SELECT`) or affected (for modification statements like `UPDATE`.

The value is -1 if:

* No `execute()` or `executemany()` method has been performed yet on the
  cursor.
* There was no rowcount associated with the last `execute()`.
* At least one of the statements executed as part of an `executemany()` had no
  row count associated with it.
* Using a `SELECT` query statement on a PostgreSQL server older than version
  9.
* Using a `COPY` query statement on PostgreSQL server version 8.1 or older.

This attribute is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Cursor.description

This read-only attribute is a sequence of 7-item sequences. Each value contains
information describing one result column. The 7 items returned for each column
are (name, type_code, display_size, internal_size, precision, scale, null_ok).
Only the first two values are provided by the current implementation.

This attribute is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Cursor.close()

Closes the cursor.

This method is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Cursor.execute(operation, args=None, stream=None)

Executes a database operation. Parameters may be provided as a sequence, or as
a mapping, depending upon the value of `pg8000.paramstyle`. Returns the cursor,
which may be iterated over.

This method is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


operation::
  The SQL statement to execute.

args::
  If `pg8000.dbapi.paramstyle` is `qmark`, `numeric`, or `format`, this
argument should be an array of parameters to bind into the statement. If
`pg8000.dbapi.paramstyle` is `named`, the argument should be a `dict` mapping of
parameters. If `pg8000.dbapi.paramstyle' is `pyformat`, the argument value may be
either an array or a mapping.

stream::
  This is a pg8000 extension for use with the PostgreSQL
http://www.postgresql.org/docs/current/static/sql-copy.html[COPY] command. For
a `COPY FROM` the parameter must be a readable file-like object, and for
`COPY TO` it must be writable.

New in version 1.9.11.


===== pg8000.dbapi.Cursor.executemany(operation, param_sets)

Prepare a database operation, and then execute it against all parameter
sequences or mappings provided.

This method is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

operation::
  The SQL statement to execute.
parameter_sets::
  A sequence of parameters to execute the statement with. The values in the
  sequence should be sequences or mappings of parameters, the same as the args
  argument of the `pg8000.dbapi.Cursor.execute()` method.


===== pg8000.dbapi.Cursor.callproc(procname, parameters=None)

Call a stored database procedure with the given name and optional parameters.

This method is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


procname::
  The name of the procedure to call.

parameters::
  A list of parameters.


===== pg8000.dbapi.Cursor.fetchall()

Fetches all remaining rows of a query result.

This method is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

Returns: A sequence, each entry of which is a sequence of field values making
up a row.


===== pg8000.dbapi.Cursor.fetchmany(size=None)

Fetches the next set of rows of a query result.

This method is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

size::
  The number of rows to fetch when called.  If not provided, the
  `pg8000.dbapi.Cursor.arraysize` attribute value is used instead.

Returns: A sequence, each entry of which is a sequence of field values making
up a row.  If no more rows are available, an empty sequence will be returned.


===== pg8000.dbapi.Cursor.fetchone()

Fetch the next row of a query result set.

This method is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].

Returns: A row as a sequence of field values, or `None` if no more rows are
available.


===== pg8000.dbapi.Cursor.setinputsizes(*sizes)

Used to set the parameter types of the next query. This is useful if it's
difficult for pg8000 to work out the types from the parameters themselves
(eg. for parameters of type None).

sizes::
  Positional parameters that are either the Python type of the parameter to be
  sent, or the PostgreSQL oid. Common oids are available as constants such as
  pg8000.STRING, pg8000.INTEGER, pg8000.TIME etc.

This method is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification].


===== pg8000.dbapi.Cursor.setoutputsize(size, column=None)

This method is part of the
http://www.python.org/dev/peps/pep-0249/[DBAPI 2.0 specification], however, it
is not implemented by pg8000.


==== pg8000.dbapi.Interval

An Interval represents a measurement of time.  In PostgreSQL, an interval is
defined in the measure of months, days, and microseconds; as such, the pg8000
interval type represents the same information.

Note that values of the `pg8000.Interval.microseconds`, `pg8000.Interval.days`,
and `pg8000.Interval.months` properties are independently measured and cannot
be converted to each other. A month may be 28, 29, 30, or 31 days, and a day
may occasionally be lengthened slightly by a leap second.


===== pg8000.dbapi.Interval.microseconds

Measure of microseconds in the interval.

The microseconds value is constrained to fit into a signed 64-bit integer. Any
attempt to set a value too large or too small will result in an OverflowError
being raised.


===== pg8000.dbapi.Interval.days

Measure of days in the interval.

The days value is constrained to fit into a signed 32-bit integer. Any attempt
to set a value too large or too small will result in an OverflowError being
raised.


===== pg8000.dbapi.Interval.months

Measure of months in the interval.

The months value is constrained to fit into a signed 32-bit integer. Any
attempt to set a value too large or too small will result in an OverflowError
being raised.


== Tests

* Install http://testrun.org/tox/latest/[tox]: `pip install tox`
* Enable the PostgreSQL hstore extension by running the SQL command:
  `create extension hstore;`
* Add a line to pg_hba.conf for the various authentication options:

....
host    pg8000_md5           all        127.0.0.1/32            md5
host    pg8000_gss           all        127.0.0.1/32            gss
host    pg8000_password      all        127.0.0.1/32            password
host    pg8000_scram_sha_256 all        127.0.0.1/32            scram-sha-256
host    all                  all        127.0.0.1/32            trust
....

* Set password encryption to `scram-sha-256` in `postgresql.conf`:
  `password_encryption = 'scram-sha-256'`
* Set the password for the postgres user: `ALTER USER postgresql WITH PASSWORD 'pw';`
* Run `tox` from the `pg8000` directory: `tox`

This will run the tests against the Python version of the virtual environment,
on the machine, and the installed PostgreSQL version listening on port 5432, or
the `PGPORT` environment variable if set.

Benchmarks are run as part of the test suite at `tests/test_benchmarks.py`.


== Doing A Release Of pg8000

Run `tox` to make sure all tests pass, then update the release notes, then do:

....
git tag -a x.y.z -m "version x.y.z"
rm -r build
rm -r dist
python setup.py sdist bdist_wheel --python-tag py3
for f in dist/*; do gpg --detach-sign -a $f; done
twine upload dist/*
....


== Release Notes

=== Version 1.23.0, 2021-11-13

* If a query has no parameters, then the query will no longer be parsed. Although there
  are performance benefits for doing this, the main reason is to avoid query rewriting,
  which can introduce errors.


=== Version 1.22.1, 2021-11-10

* Fix bug in PGInterval type where `str()` failed for a millennia value.


=== Version 1.22.0, 2021-10-13

* Rather than specifying the oids in the `Parse` step of the Postgres protocol, pg8000
  now omits them, and so Postgres will use the oids it determines from the query. This
  makes the pg8000 code simplier and also it should also make the nuances of type
  matching more straightforward.


=== Version 1.21.3, 2021-10-10

* Legacy prepared statement fails if the result is null. Thanks to Carlos
  https://github.com/carlkid1499 for reporting this.

* For the currency part of the pg8000 test suite, add `C.UTF8` as a supported `LANG`.


=== Version 1.21.2, 2021-09-14

* The `executemany()` method fails if the `param_sets` parameter is empty. Thanks to
  https://github.com/GKTheOne for reporting this.


=== Version 1.21.1, 2021-08-25

* Require Scramp version 1.4.1 or higher so that pg8000 can cope with SCRAM with
  channel binding with certificates using a `sha512` hash algorithm.


=== Version 1.21.0, 2021-07-31

* For some SQL statements the server doesn't send back a result set (note that no
  result set is different from a result set with zero rows). Previously we didn't
  distinguish between no results and zero rows, but now we do. For `pg8000.dbapi` it
  means that an exception is raised if `fetchall()` is called when no results have been
  returned, bringing pg8000 into line with the DBAPI 2 standard. For `pg8000.native`
  this means that `run()` returns None if there is no result.


=== Version 1.20.0, 2021-07-03

* Allow text stream as 'stream' parameter in run(). Previously we only allowed a bytes 
  stream, but now the stream can be a text stream, in which case pg8000 handles the 
  encodings.


=== Version 1.19.5, 2021-05-18

* Handle invalid character encodings in error strings from the server.


=== Version 1.19.4, 2021-05-03

* A FLUSH message should only be send after an extended-query message, but pg8000 was
  sending it at other times as well. This affected AWS RDS Proxy.


=== Version 1.19.3, 2021-04-24

* The type (oid) of integer arrays wasn't being detected correctly. It was only going
  by the first element, but it should look at all the items. That's fixed now.


=== Version 1.19.2, 2021-04-07

* In version 1.19.1 we tried to parse the PostgreSQL `MONEY` type to return a `Decimal`
  but since the format of `MONEY` is locale-dependent this is too difficult and
  unreliable and so now we revert to returning a `str`.


=== Version 1.19.1, 2021-04-03

* Fix bug where setinputsizes() was only used for the first parameter set of
  executemany().

* Support more PostgreSQL array types.


=== Version 1.19.0, 2021-03-28

* Network error exceptions are now wrapped in an `InterfaceError`, with the
  original exception as the cause. The error message for network errors allways
  start with the string `network error`.

* Upgraded to version 1.3.0 of Scramp, which has better error handling.


=== Version 1.18.0, 2021-03-06

* The `pg8000.dbapi.Cursor.callproc()` method is now implemented.

* SCRAM channel binding is now supported. That means SCRAM mechanisms ending in
  '-PLUS' such as SCRAM-SHA-256-PLUS are now supported when connecting to the
  server.

* A custom attribute `ssl.SSLContext.request_ssl` can be set to `False` to
  tell pg8000 to connect using an SSL socket, but to not request SSL from
  the PostgreSQL server. This is useful if you're connecting to a PostgreSQL
  server that's behind an SSL proxy.


=== Version 1.17.0, 2021-01-30

* The API is now split in two, pg8000.native and pg8000.dbapi. The legacy API
  still exists in this release, but will be removed in another release. The
  idea is that pg8000.dbapi can stick strictly to the DB-API 2 specification,
  while pg8000.native can focus on useability without having to worry about
  compatibility with the DB-API standard.

* The column name in `Connection.description` used to be returned as a
  `bytes` but now it's returned as a `str`.

* Removed extra wrapper types PGJson, PGEnum etc. These were never properly
  documented and the problem they solve can be solved using CAST in the SQL or
  by using setinputsizes.



=== Version 1.16.6, 2020-10-10

* The column name in `Connection.description` used to be returned as a
  `bytes` but now it's returned as a `str`.

* Removed extra wrapper types PGJson, PGEnum etc. These were never properly
  documented and the problem they solve can be solved using CAST in the SQL or
  by using setinputsizes.


=== Version 1.16.5, 2020-08-07

* The TPC method `Connection.tpc_prepare()` was broken.


=== Version 1.16.4, 2020-08-03

* Include the `payload` in the tuples in `Connection.notifications`.
* More constants (eg. `DECIMAL` and `TEXT_ARRAY`) are now available for
  PostgreSQL types that are used in `setinputsizes()`.


=== Version 1.16.3, 2020-07-26

* If an unrecognized parameter is sent to `Cursor.setinputsizes()` use the
  `pg8000.UNKNOWN` type (705).
* When communicating with a PostgreSQL server with version < 8.2.0, `FETCH`
  commands don't have a row count.
* Include in the source distribution all necessary test files from the `test`
  directory in


=== Version 1.16.2, 2020-07-25

* Use the
  https://www.postgresql.org/docs/current/protocol-flow.html#id-1.10.5.7.4[simple query]
  cycle for queries that don't have parameters. This should give a performance
  improvement and also means that multiple statements can be executed in one go
  (as long as they don't have parameters) whereas previously the `sqlparse` had
  to be used.


=== Version 1.16.1, 2020-07-18

* Enable the `Cursor.setinputsizes()` method. Previously this method didn't
  do anything. It's an optional method of the DBAPI 2.0 specification.


=== Version 1.16.0, 2020-07-11

* This is a backwardly incompatible release of pg8000.

* All data types are now sent as text rather than binary.

* Using adapters, custom types can be plugged in to pg8000.

* Previously, named prepared statements were used for all statements.
  Now unnamed prepared statements are used by default, and named prepared
  statements can be used explicitly by calling the Connection.prepare()
  method, which returns a PreparedStatement object.


=== Version 1.15.3, 2020-06-14

* For TCP connections (as opposed to Unix socket connections) the
  https://docs.python.org/3/library/socket.html#socket.create_connection[`socket.create_connection`]
  function is now used. This means pg8000 now works with IPv6 as well as IPv4.

* Better error messages for failed connections. A 'cause' exception is now
  added to the top-level pg8000 exception, and the error message contains the
  details of what was being connected to (host, port etc.).


=== Version 1.15.2, 2020-04-16

* Added a new method `run()` to the connection, which lets you run queries
  directly without using a `Cursor`. It always uses the `named` parameter
  style, and the parameters are provided using keyword arguments. There are now
  two sets of interactive examples, one using the pg8000 extensions, and one
  using just DB-API features.

* Better error message if certain parameters in the `connect()` function are of
  the wrong type.

* The constructor of the `Connection` class now has the same signature as the
  `connect()` function, which makes it easier to use the `Connection` class
  directly if you want to.


=== Version 1.15.1, 2020-04-04

* Up to now the only supported way to create a new connection was to use the
  `connect()` function. However, some people are using the `Connect` class
  directly and this change makes it a bit easier to do that by making the class
  use a contructor which has the same signature as the `connect()` function.


=== Version 1.15.0, 2020-04-04

* Abandon the idea of arbitrary `init_params` in the connect() function. We now
  go back to having a fixed number of arguments. The argument `replication` has
  been added as this is the only extra init param that was needed. The reason
  for going back to a fixed number of aguments is that you get better feedback
  if you accidently mis-type a parameter name.

* The `max_prepared_statements` parameter has been moved from being a module
  property to being an argument of the connect() function.


=== Version 1.14.1, 2020-03-23

* Ignore any `init_params` that have a value of `None`. This seems to be more
  useful and the behaviour is more expected.


=== Version 1.14.0, 2020-03-21

* Tests are now included in the source distribution.

* Any extra keyword parameters of the `connect()` function are sent as
  initialization parameters when the PostgreSQL session starts. See the API
  docs for more information. Thanks to Patrick Hayes for suggesting this.

* The ssl.wrap_socket function is deprecated, so we now give the user the
  option of using a default `SSLContext` or to pass in a custom one. This is a
  backwardly incompatible change. See the API docs for more info. Thanks to
  Jonathan Ross Rogers <jrogers@emphasys-software.com> for his work on this.

* Oversized integers are now returned as a `Decimal` type, whereas before a
  `None` was returned. Thanks to Igor Kaplounenko <igor.kaplounenko@intel.com>
  for his work on this.

* Allow setting of connection source address in the `connect()` function. See
  the API docs for more details. Thanks to David King
  <davidking@davids-mbp.home> for his work on this.


=== Version 1.13.2, 2019-06-30

* Use the https://pypi.org/project/scramp/[Scramp] library for the SCRAM
  implementation.

* Fixed bug where SQL such as `make_interval(days := 10)` fail on the `:=`
  part. Thanks to https://github.com/sanepal[sanepal] for reporting this.


=== Version 1.13.1, 2019-02-06

* We weren't correctly uploading releases to PyPI, which led to confusion
  when dropping Python 2 compatibility. Thanks to
  https://github.com/piroux[Pierre Roux] for his
  https://github.com/tlocke/pg8000/issues/7[detailed explanation] of what
  went wrong and how to correct it.

* Fixed bug where references to the `six` library were still in the code, even
  though we don't use `six` anymore.


=== Version 1.13.0, 2019-02-01

* Remove support for Python 2.

* Support the scram-sha-256 authentication protocol. Reading through the
  https://github.com/cagdass/scrampy code was a great help in implementing
  this, so thanks to https://github.com/cagdass[cagdass] for his code.


=== Version 1.12.4, 2019-01-05

* Support the PostgreSQL cast operator `::` in SQL statements.

* Added support for more advanced SSL options. See docs on `connect` function
  for more details.

* TCP keepalives enabled by default, can be set in the `connect` function.

* Fixed bug in array dimension calculation.

* Can now use the `with` keyword with connection objects.


=== Version 1.12.3, 2018-08-22

* Make PGVarchar and PGText inherit from `str`. Simpler than inheriting from
  a PGType.


=== Version 1.12.2, 2018-06-28

* Add PGVarchar and PGText wrapper types. This allows fine control over the
  string type that is sent to PostgreSQL by pg8000.


=== Version 1.12.1, 2018-06-12


* Revert back to the Python 3 `str` type being sent as an `unknown` type,
  rather than the `text` type as it was in the previous release. The reason is
  that with the `unknown` type there's the convenience of using a plain Python
  string for JSON, Enum etc. There's always the option of using the
  `pg8000.PGJson` and `pg8000.PGEnum` wrappers if precise control over the
  PostgreSQL type is needed.


=== Version 1.12.0, 2018-06-12

Note that this version is not backward compatible with previous versions.

* The Python 3 `str` type was sent as an `unknown` type, but now it's sent as
 the nearest PostgreSQL type `text`.

* pg8000 now recognizes that inline SQL comments end with a newline.

* Single `%` characters now allowed in SQL comments.

* The wrappers `pg8000.PGJson`, `pg8000.PGJsonb` and `pg8000.PGTsvector` can
  now be used to contain Python values to be used as parameters. The wrapper
  `pg8000.PGEnum` can by used for Python 2, as it doesn't have a standard
  `enum.Enum` type.


=== Version 1.11.0, 2017-08-16

Note that this version is not backward compatible with previous versions.

* The Python `int` type was sent as an `unknown` type, but now it's sent as the
  nearest matching PostgreSQL type. Thanks to Patrick Hayes.

* Prepared statements are now closed on the server when pg8000 clears them from
  its cache.

* Previously a `%` within an SQL literal had to be escaped, but this is no
  longer the case.

* Notifications, notices and parameter statuses are now handled by simple
  `dequeue` buffers. See docs for more details.

* Connections and cursors are no longer threadsafe. So to be clear, neither
  connections or cursors should be shared between threads. One thread per
  connection is mandatory now. This has been done for performance reasons, and
  to simplify the code.

* Rather than reading results from the server in batches, pg8000 now always
  downloads them in one go. This avoids `portal closed` errors and makes things
  a bit quicker, but now one has to avoid downloading too many rows in a single
  query.

* Attempts to return something informative if the returned PostgreSQL timestamp
  value is outside the range of the Python datetime.

* Allow empty arrays as parameters, assume they're of string type.

* The cursor now has a context manager, so it can be used with the `with`
  keyword. Thanks to Ildar Musin.

* Add support for `application_name` parameter when connecting to database,
  issue https://github.com/mfenniak/pg8000/pull/106[#106]. Thanks to
  https://github.com/vadv[@vadv] for the contribution.

* Fix warnings from PostgreSQL "not in a transaction", when calling
  ``.rollback()`` while not in a transaction, issue
  https://github.com/mfenniak/pg8000/issues/113[#113]. Thanks to
  https://github.com/jamadden[@jamadden] for the contribution.

* Errors from the server are now always passed through in full.


=== Version 1.10.6, 2016-06-10

* Fixed a problem where we weren't handling the password connection parameter
  correctly. Now it's handled in the same way as the 'user' and 'database'
  parameters, ie. if the password is bytes, then pass it straight through to the
  database, if it's a string then encode it with utf8.

* It used to be that if the 'user' parameter to the connection function was
  'None', then pg8000 would try and look at environment variables to find a
  username. Now we just go by the 'user' parameter only, and give an error if
  it's None.


=== Version 1.10.5, 2016-03-04

- Include LICENCE text and sources for docs in the source distribution (the
  tarball).


=== Version 1.10.4, 2016-02-27

* Fixed bug where if a str is sent as a query parameter, and then with the same
  cursor an int is sent instead of a string, for the same query, then it fails.

* Under Python 2, a str type is now sent 'as is', ie. as a byte string rather
  than trying to decode and send according to the client encoding. Under Python
  2 it's recommended to send text as unicode() objects.

* Dropped and added support for Python versions. Now pg8000 supports
  Python 2.7+ and Python 3.3+.

* Dropped and added support for PostgreSQL versions. Now pg8000 supports
  PostgreSQL 9.1+.

* pg8000 uses the 'six' library for making the same code run on both Python 2
  and Python 3. We used to include it as a file in the pg8000 source code. Now
  we have it as a separate dependency that's installed with 'pip install'. The
  reason for doing this is that package maintainers for OS distributions
  prefer unbundled libaries.


=== Version 1.10.3, 2016-01-07

* Removed testing for PostgreSQL 9.0 as it's not longer supported by the
  PostgreSQL Global Development Group.
* Fixed bug where pg8000 would fail with datetimes if PostgreSQL was compiled
  with the integer_datetimes option set to 'off'. The bug was in the
  timestamp_send_float function.


=== Version 1.10.2, 2015-03-17

* If there's a socket exception thrown when communicating with the database,
  it is now wrapped in an OperationalError exception, to conform to the DB-API
  spec.

* Previously, pg8000 didn't recognize the EmptyQueryResponse (that the server
  sends back if the SQL query is an empty string) now we raise a
  ProgrammingError exception.

* Added socket timeout option for Python 3.

* If the server returns an error, we used to initialize the ProgramerException
  with just the first three fields of the error. Now we initialize the
  ProgrammerException with all the fields.

* Use relative imports inside package.

* User and database names given as bytes. The user and database parameters of
  the connect() function are now passed directly as bytes to the server. If the
  type of the parameter is unicode, pg8000 converts it to bytes using the uft8
  encoding.

* Added support for JSON and JSONB Postgres types. We take the approach of
  taking serialized JSON (str) as an SQL parameter, but returning results as
  de-serialized JSON (Python objects). See the example in the Quickstart.

* Added CircleCI continuous integration.

* String support in arrays now allow letters like "u", braces and whitespace.


=== Version 1.10.1, 2014-09-15

* Add support for the Wheel package format.

* Remove option to set a connection timeout. For communicating with the server,
  pg8000 uses a file-like object using socket.makefile() but you can't use this
  if the underlying socket has a timeout.


=== Version 1.10.0, 2014-08-30

* Remove the old ``pg8000.dbapi`` and ``pg8000.DBAPI`` namespaces. For example,
  now only ``pg8000.connect()`` will work, and ``pg8000.dbapi.connect()``
  won't work any more.

* Parse server version string with LooseVersion. This should solve the problems
  that people have been having when using versions of PostgreSQL such as
  ``9.4beta2``.

* Message if portal suspended in autocommit. Give a proper error message if the
  portal is suspended while in autocommit mode. The error is that the portal is
  closed when the transaction is closed, and so in autocommit mode the portal
  will be immediately closed. The bottom line is, don't use autocommit mode if
  there's a chance of retrieving more rows than the cache holds (currently 100).


=== Version 1.9.14, 2014-08-02

* Make ``executemany()`` set ``rowcount``. Previously, ``executemany()`` would
  always set ``rowcount`` to -1. Now we set it to a meaningful value if
  possible. If any of the statements have a -1 ``rowcount`` then then the
  ``rowcount`` for the ``executemany()`` is -1, otherwise the ``executemany()``
  ``rowcount`` is the sum of the rowcounts of the individual statements.

* Support for password authentication. pg8000 didn't support plain text
  authentication, now it does.


=== Version 1.9.13, 2014-07-27

* Reverted to using the string ``connection is closed`` as the message of the
  exception that's thrown if a connection is closed. For a few versions we were
  using a slightly different one with capitalization and punctuation, but we've
  reverted to the original because it's easier for users of the library to
  consume.

* Previously, ``tpc_recover()`` would start a transaction if one was not already
  in progress. Now it won't.


=== Version 1.9.12, 2014-07-22

* Fixed bug in ``tpc_commit()`` where a single phase commit failed.


=== Version 1.9.11, 2014-07-20

* Add support for two-phase commit DBAPI extension. Thanks to Mariano Reingart's
  TPC code on the Google Code version:

  https://code.google.com/p/pg8000/source/detail?r=c8609701b348b1812c418e2c7

  on which the code for this commit is based.

* Deprecate ``copy_from()`` and ``copy_to()`` The methods ``copy_from()`` and
  ``copy_to()`` of the ``Cursor`` object are deprecated because it's simpler and
  more flexible to use the ``execute()`` method with a ``fileobj`` parameter.

* Fixed bug in reporting unsupported authentication codes. Thanks to
  https://github.com/hackgnar for reporting this and providing the fix.

* Have a default for the ``user`` paramater of the ``connect()`` function. If
  the ``user`` parameter of the ``connect()`` function isn't provided, look
  first for the ``PGUSER`` then the ``USER`` environment variables. Thanks to
  Alex Gaynor https://github.com/alex for this suggestion.

* Before PostgreSQL 8.2, ``COPY`` didn't give row count. Until PostgreSQL 8.2
  (which includes Amazon Redshift which forked at 8.0) the ``COPY`` command
  didn't return a row count, but pg8000 thought it did. That's fixed now.


=== Version 1.9.10, 2014-06-08

* Remember prepared statements. Now prepared statements are never closed, and
  pg8000 remembers which ones are on the server, and uses them when a query is
  repeated. This gives an increase in performance, because on subsequent
  queries the prepared statement doesn't need to be created each time.

* For performance reasons, pg8000 never closed portals explicitly, it just
  let the server close them at the end of the transaction. However, this can
  cause memory problems for long running transactions, so now pg800 always
  closes a portal after it's exhausted.

* Fixed bug where unicode arrays failed under Python 2. Thanks to
  https://github.com/jdkx for reporting this.

* A FLUSH message is now sent after every message (except SYNC). This is in
  accordance with the protocol docs, and ensures the server sends back its
  responses straight away.


=== Version 1.9.9, 2014-05-12

* The PostgreSQL interval type is now mapped to datetime.timedelta where
  possible. Previously the PostgreSQL interval type was always mapped to the
  pg8000.Interval type. However, to support the datetime.timedelta type we
  now use it whenever possible. Unfortunately it's not always possible because
  timedelta doesn't support months. If months are needed then the fall-back
  is the pg8000.Interval type. This approach means we handle timedelta in a
  similar way to other Python PostgreSQL drivers, and it makes pg8000
  compatible with popular ORMs like SQLAlchemy.

* Fixed bug in executemany() where a new prepared statement should be created
  for each variation in the oids of the parameter sets.


=== Version 1.9.8, 2014-05-05

* We used to ask the server for a description of the statement, and then ask
  for a description of each subsequent portal. We now only ask for a
  description of the statement. This results in a significant performance
  improvement, especially for executemany() calls and when using the
  'use_cache' option of the connect() function.

* Fixed warning in Python 3.4 which was saying that a socket hadn't been
  closed. It seems that closing a socket file doesn't close the underlying
  socket.

* Now should cope with PostgreSQL 8 versions before 8.4. This includes Amazon
  Redshift.

* Added 'unicode' alias for 'utf-8', which is needed for Amazon Redshift.

* Various other bug fixes.


=== Version 1.9.7, 2014-03-26

* Caching of prepared statements. There's now a 'use_cache' boolean parameter
  for the connect() function, which causes all prepared statements to be cached
  by pg8000, keyed on the SQL query string. This should speed things up
  significantly in most cases.

* Added support for the PostgreSQL inet type. It maps to the Python types
  IPv*Address and IPv*Network.

* Added support for PostgreSQL +/- infinity date and timestamp values. Now the
  Python value datetime.datetime.max maps to the PostgreSQL value 'infinity'
  and datetime.datetime.min maps to '-infinity', and the same for
  datetime.date.

* Added support for the PostgreSQL types int2vector and xid, which are mostly
  used internally by PostgreSQL.


=== Version 1.9.6, 2014-02-26

* Fixed a bug where 'portal does not exist' errors were being generated. Some
  queries that should have been run in a transaction were run in autocommit
  mode and so any that suspended a portal had the portal immediately closed,
  because a portal can only exist within a transaction. This has been solved by
  determining the transaction status from the READY_FOR_QUERY message.


=== Version 1.9.5, 2014-02-15

* Removed warn() calls for __next__() and __iter__(). Removing the warn() in
  __next__() improves the performance tests by ~20%.

* Increased performance of timestamp by ~20%. Should also improve timestamptz.

* Moved statement_number and portal_number from module to Connection. This
  should reduce lock contention for cases where there's a single module and
  lots of connections.

* Make decimal_out/in and time_in use client_encoding. These functions used to
  assume ascii, and I can't think of a case where that wouldn't work.
  Nonetheless, that theoretical bug is now fixed.

* Fixed a bug in cursor.executemany(), where a non-None parameter in a sequence
  of parameters, is None in a subsequent sequence of parameters.


=== Version 1.9.4, 2014-01-18

* Fixed a bug where with Python 2, a parameter with the value Decimal('12.44'),
  (and probably other numbers) isn't sent correctly to PostgreSQL, and so the
  command fails. This has been fixed by sending decimal types as text rather
  than binary. I'd imagine it's slightly faster too.


=== Version 1.9.3, 2014-01-16

* Fixed bug where there were missing trailing zeros after the decimal point in
  the NUMERIC type. For example, the NUMERIC value 1.0 was returned as 1 (with
  no zero after the decimal point).

  This is fixed this by making pg8000 use the text rather than binary
  representation for the numeric type. This actually doubles the speed of
  numeric queries.


=== Version 1.9.2, 2013-12-17

* Fixed incompatibility with PostgreSQL 8.4. In 8.4, the CommandComplete
  message doesn't return a row count if the command is SELECT. We now look at
  the server version and don't look for a row count for a SELECT with version
  8.4.


=== Version 1.9.1, 2013-12-15

* Fixed bug where the Python 2 'unicode' type wasn't recognized in a query
  parameter.


=== Version 1.9.0, 2013-12-01

* For Python 3, the :class:`bytes` type replaces the :class:`pg8000.Bytea`
  type. For backward compatibility the :class:`pg8000.Bytea` still works under
  Python 3, but its use is deprecated.

* A single codebase for Python 2 and 3.

* Everything (functions, properties, classes) is now available under the
  ``pg8000`` namespace. So for example:

  * pg8000.DBAPI.connect() -> pg8000.connect()
  * pg8000.DBAPI.apilevel -> pg8000.apilevel
  * pg8000.DBAPI.threadsafety -> pg8000.threadsafety
  * pg8000.DBAPI.paramstyle -> pg8000.paramstyle
  * pg8000.types.Bytea -> pg8000.Bytea
  * pg8000.types.Interval -> pg8000.Interval
  * pg8000.errors.Warning -> pg8000.Warning
  * pg8000.errors.Error -> pg8000.Error
  * pg8000.errors.InterfaceError -> pg8000.InterfaceError
  * pg8000.errors.DatabaseError -> pg8000.DatabaseError

  The old locations are deprecated, but still work for backward compatibility.

* Lots of performance improvements.

  * Faster receiving of ``numeric`` types.
  * Query only parsed when PreparedStatement is created.
  * PreparedStatement re-used in executemany()
  * Use ``collections.deque`` rather than ``list`` for the row cache. We're
    adding to one end and removing from the other. This is O(n) for a list but
    O(1) for a deque.
  * Find the conversion function and do the format code check in the
    ROW_DESCRIPTION handler, rather than every time in the ROW_DATA handler.
  * Use the 'unpack_from' form of struct, when unpacking the data row, so we
    don't have to slice the data.
  * Return row as a list for better performance. At the moment result rows are
    turned into a tuple before being returned. Returning the rows directly as a
    list speeds up the performance tests about 5%.
  * Simplify the event loop. Now the main event loop just continues until a
    READY_FOR_QUERY message is received. This follows the suggestion in the
    Postgres protocol docs. There's not much of a difference in speed, but the
    code is a bit simpler, and it should make things more robust.
  * Re-arrange the code as a state machine to give > 30% speedup.
  * Using pre-compiled struct objects. Pre-compiled struct objects are a bit
    faster than using the struct functions directly. It also hopefully adds to
    the readability of the code.
  * Speeded up _send. Before calling the socket 'write' method, we were
    checking that the 'data' type implements the 'buffer' interface (bytes or
    bytearray), but the check isn't needed because 'write' raises an exception
    if data is of the wrong type.


* Add facility for turning auto-commit on. This follows the suggestion of
  funkybob to fix the problem of not be able to execute a command such as
  'create database' that must be executed outside a transaction. Now you can do
  conn.autocommit = True and then execute 'create database'.

* Add support for the PostgreSQL ``uid`` type. Thanks to Rad Cirskis.

* Add support for the PostgreSQL XML type.

* Add support for the PostgreSQL ``enum`` user defined types.

* Fix a socket leak, where a problem opening a connection could leave a socket
  open.

* Fix empty array issue. https://github.com/mfenniak/pg8000/issues/10

* Fix scale on ``numeric`` types. https://github.com/mfenniak/pg8000/pull/13

* Fix numeric_send. Thanks to Christian Hofstaedtler.


=== Version 1.08, 2010-06-08

* Removed usage of deprecated :mod:`md5` module, replaced with :mod:`hashlib`.
  Thanks to Gavin Sherry for the patch.

* Start transactions on execute or executemany, rather than immediately at the
  end of previous transaction.  Thanks to Ben Moran for the patch.

* Add encoding lookups where needed, to address usage of SQL_ASCII encoding.
  Thanks to Benjamin Schweizer for the patch.

* Remove record type cache SQL query on every new pg8000 connection.

* Fix and test SSL connections.

* Handle out-of-band messages during authentication.


=== Version 1.07, 2009-01-06

* Added support for :meth:`~pg8000.dbapi.CursorWrapper.copy_to` and
  :meth:`~pg8000.dbapi.CursorWrapper.copy_from` methods on cursor objects, to
  allow the usage of the PostgreSQL COPY queries.  Thanks to Bob Ippolito for
  the original patch.

* Added the :attr:`~pg8000.dbapi.ConnectionWrapper.notifies` and
  :attr:`~pg8000.dbapi.ConnectionWrapper.notifies_lock` attributes to DBAPI
  connection objects to provide access to server-side event notifications.
  Thanks again to Bob Ippolito for the original patch.

* Improved performance using buffered socket I/O.

* Added valid range checks for :class:`~pg8000.types.Interval` attributes.

* Added binary transmission of :class:`~decimal.Decimal` values.  This permits
  full support for NUMERIC[] types, both send and receive.

* New `Sphinx <http://sphinx.pocoo.org/>`_-based website and documentation.


=== Version 1.06, 2008-12-09

* pg8000-py3: a branch of pg8000 fully supporting Python 3.0.

* New Sphinx-based documentation.

* Support for PostgreSQL array types -- INT2[], INT4[], INT8[], FLOAT[],
  DOUBLE[], BOOL[], and TEXT[].  New support permits both sending and
  receiving these values.

* Limited support for receiving RECORD types.  If a record type is received,
  it will be translated into a Python dict object.

* Fixed potential threading bug where the socket lock could be lost during
  error handling.


=== Version 1.05, 2008-09-03

* Proper support for timestamptz field type:

  * Reading a timestamptz field results in a datetime.datetime instance that
    has a valid tzinfo property.  tzinfo is always UTC.

  * Sending a datetime.datetime instance with a tzinfo value will be
    sent as a timestamptz type, with the appropriate tz conversions done.

* Map postgres < -- > python text encodings correctly.

* Fix bug where underscores were not permitted in pyformat names.

* Support "%s" in a pyformat strin.

* Add cursor.connection DB-API extension.

* Add cursor.next and cursor.__iter__ DB-API extensions.

* DBAPI documentation improvements.

* Don't attempt rollback in cursor.execute if a ConnectionClosedError occurs.

* Add warning for accessing exceptions as attributes on the connection object,
  as per DB-API spec.

* Fix up open connection when an unexpected connection occurs, rather than
  leaving the connection in an unusable state.

* Use setuptools/egg package format.


=== Version 1.04, 2008-05-12

* DBAPI 2.0 compatibility:

  * rowcount returns rows affected when appropriate (eg. UPDATE, DELETE)

  * Fix CursorWrapper.description to return a 7 element tuple, as per spec.

  * Fix CursorWrapper.rowcount when using executemany.

  * Fix CursorWrapper.fetchmany to return an empty sequence when no more
    results are available.

  * Add access to DBAPI exceptions through connection properties.

  * Raise exception on closing a closed connection.

  * Change DBAPI.STRING to varchar type.

  * rowcount returns -1 when appropriate.

  * DBAPI implementation now passes Stuart Bishop's Python DB API 2.0 Anal
    Compliance Unit Test.

* Make interface.Cursor class use unnamed prepared statement that binds to
  parameter value types.  This change increases the accuracy of PG's query
  plans by including parameter information, hence increasing performance in
  some scenarios.

* Raise exception when reading from a cursor without a result set.

* Fix bug where a parse error may have rendered a connection unusable.


=== Version 1.03, 2008-05-09

* Separate pg8000.py into multiple python modules within the pg8000 package.
  There should be no need for a client to change how pg8000 is imported.

* Fix bug in row_description property when query has not been completed.

* Fix bug in fetchmany dbapi method that did not properly deal with the end of
  result sets.

* Add close methods to DB connections.

* Add callback event handlers for server notices, notifications, and runtime
  configuration changes.

* Add boolean type output.

* Add date, time, and timestamp types in/out.

* Add recognition of "SQL_ASCII" client encoding, which maps to Python's
  "ascii" encoding.

* Add types.Interval class to represent PostgreSQL's interval data type, and
  appropriate wire send/receive methods.

* Remove unused type conversion methods.


=== Version 1.02, 2007-03-13

* Add complete DB-API 2.0 interface.

* Add basic SSL support via ssl connect bool.

* Rewrite pg8000_test.py to use Python's unittest library.

* Add bytea type support.

* Add support for parameter output types: NULL value, timestamp value, python
  long value.

* Add support for input parameter type oid.


=== Version 1.01, 2007-03-09

* Add support for writing floats and decimal objs up to PG backend.

* Add new error handling code and tests to make sure connection can recover
  from a database error.

* Fixed bug where timestamp types were not always returned in the same binary
  format from the PG backend.  Text format is now being used to send
  timestamps.

* Fixed bug where large packets from the server were not being read fully, due
  to socket.read not always returning full read size requested.  It was a
  lazy-coding bug.

* Added locks to make most of the library thread-safe.

* Added UNIX socket support.


=== Version 1.00, 2007-03-08

* First public release.  Although fully functional, this release is mostly
  lacking in production testing and in type support.
