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

Here’s how to run groups of SQL statements in a transaction:

rolling back a transaction:

Another query, using some PostgreSQL functions:

A query that returns the PostgreSQL interval type:

A round-trip with a PostgreSQL point type:

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:

JSON always comes back from the server de-serialized. If the JSON you want to send is a dict then you can just do:

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

Find the column metadata returned from a query:

PostgreSQL notices are stored in a deque called Connection.notices and added using the append() method. Similarly there are Connection.notifications for notifications and Connection.parameter_statuses for changes to the server configuration. Here’s an example:

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

Instead the docs say that you can send null as an alternative to ALL, which does work:

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

instead you can write it using the unnest function:

and you can do the same for NOT IN.

In PostgreSQL parameters can only be used for data values, not identifiers. Sometimes this might not work as expected, for example the following fails:

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. For these cases the SQL has to be created manually, being careful to use the identifier() and literal() functions to escape the values to avoid SQL injection attacks:

The SQL COPY statement can be used to copy from and to a file or file-like object. Here' an example using the CSV format:

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

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

Say you had a column called My Column. Since it’s case sensitive and contains a space, you’d have to surround it by double quotes. But you can’t do:

since Python uses double quotes to delimit string literals, so one solution is to use Python’s triple quotes to delimit the string instead:

another solution, that’s especially useful if the identifier comes from an untrusted source, is to use the identifier() function, which correctly quotes and escapes the identifier as needed:

this approach guards against SQL injection attacks.

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:

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.

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:

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.

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

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

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

Prepared statements can be useful in improving performance when you have a statement that’s executed repeatedly. Here’s an example:

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

or perhaps you may want to use some of the same environment variables that libpq uses:

It might be asked, why doesn’t pg8000 have this behaviour built in? The thinking follows the second aphorism of The Zen of Python:

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.

To connect to the server using SSL defaults do:

To connect over SSL with custom settings, set the ssl_context parameter to an ssl.SSLContext object:

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:

You can use the SQL commands DECLARE, FETCH, MOVE and CLOSE to manipulate server-side cursors. For example:

There’s a set of SQL functions for manipulating BLOBs. Here’s an example:

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

Another query, using some PostgreSQL functions:

A query that returns the PostgreSQL interval type:

A round-trip with a PostgreSQL point type:

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

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

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:

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

Use the columns names retrieved from a query:

PostgreSQL notices are stored in a deque called Connection.notices and added using the append() method. Similarly there are Connection.notifications for notifications and Connection.parameter_statuses for changes to the server configuration. Here’s an example:

The SQL COPY statement can be used to copy from and to a file or file-like object:

You can use the SQL commands DECLARE, FETCH, MOVE and CLOSE to manipulate server-side cursors. For example:

There’s a set of SQL functions for manipulating BLOBs. Here’s an example:

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

For errors that originate within pg8000.

For errors that originate from the server.

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 ssl.SSLContext created using ssl.create_default_context()

• An instance of 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 TCP keepalive. The default is True.

application_name

Sets the 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 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').

A deque of server-side 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.

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

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

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 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.

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.

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

Closes the database connection.

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

typ

The Python class that the adapter is for.

out_func

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

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

oid

The PostgreSQL type identifier found in the pg_type system calalog.

in_func

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

Returns a PreparedStatement object which represents a prepared statement on the server. It can subsequently be repeatedly executed as shown in the example.

sql

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

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

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

kwargs

The parameters of the prepared statement.

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

Correctly quotes and escapes a string to be used as an SQL identifier.

ident

The str to be used as an SQL identifier.

Correctly quotes and escapes a value to be used as an SQL literal.

value

The value to be used as an SQL literal.

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.

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

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/*

• When connecting, raise an InterfaceError('network error') rather than let the underlying struct.error float up.

• Make licence text the same as that used by the OSI. Previously the licence wording differed slightly from the BSD 3 Clause licence at https://opensource.org/licenses/BSD-3-Clause. This meant that automated tools didn’t pick it up as being Open Source. The changes are believed to not alter the meaning of the license at all.

• Fix more cases where a ResourceWarning would be raise because of a socket that had been left open.

• We now have a single InterfaceError with the message 'network error' for all network errors, with the underlying exception held in the cause of the exception.

• To prevent a ResourceWarning close socket if a connection can’t be created.

• Return pg +/-infinity dates as str. Previously +/-infinity pg values would cause an error when returned, but now we return +/-infinity as strings.

• Add SQL escape functions identifier() and literal() to the native API. For use when a query can’t be parameterised and the SQL string has to be created using untrusted values.

• 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.

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

• 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.

• 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.

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

• 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.

• 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.

• 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.

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

• 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.

• 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.

• 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.

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

• Support more PostgreSQL array types.

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

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

• 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.

• 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.

• 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.

• The TPC method Connection.tpc_prepare() was broken.

• 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().

• 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

• Use the 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.

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

• 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.

• For TCP connections (as opposed to Unix socket connections) the 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.).

• 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.

• 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.

• 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.

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

• 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.

• Use the Scramp library for the SCRAM implementation.

• Fixed bug where SQL such as make_interval(days := 10) fail on the := part. Thanks to sanepal for reporting this.

• We weren’t correctly uploading releases to PyPI, which led to confusion when dropping Python 2 compatibility. Thanks to Pierre Roux for his 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.

• 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 cagdass for his code.

• 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.

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

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

• 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.

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.

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 #106. Thanks to @vadv for the contribution.

• Fix warnings from PostgreSQL "not in a transaction", when calling .rollback() while not in a transaction, issue #113. Thanks to @jamadden for the contribution.

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

• 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.

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

• 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.

• 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.

• 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.

• 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.

• 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).

• 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.

• 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.

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

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

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

• 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. mfenniak/pg8000#10

• Fix scale on numeric types. mfenniak/pg8000#13

• Fix numeric_send. Thanks to Christian Hofstaedtler.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

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