Basic module usage

The basic Psycopg usage is common to all the database adapters implementingtheDB API 2.0 protocol. Here is an interactive session showing some of thebasic commands:

>>>importpsycopg2# Connect to an existing database>>>conn=psycopg2.connect("dbname=test user=postgres")# Open a cursor to perform database operations>>>cur=conn.cursor()# Execute a command: this creates a new table>>>cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")# Pass data to fill a query placeholders and let Psycopg perform# the correct conversion (no more SQL injections!)>>>cur.execute("INSERT INTO test (num, data) VALUES (%s,%s)",...(100,"abc'def"))# Query the database and obtain data as Python objects>>>cur.execute("SELECT * FROM test;")>>>cur.fetchone()(1, 100, "abc'def")# Make the changes to the database persistent>>>conn.commit()# Close communication with the database>>>cur.close()>>>conn.close()

The main entry points of Psycopg are:

Passing parameters to SQL queries

Psycopg converts Python variables to SQL values using their types: the Pythontype determines the function used to convert the object into a stringrepresentation suitable for PostgreSQL. Many standard Python types arealreadyadapted to the correct SQL representation.

Passing parameters to an SQL statement happens in functions such ascursor.execute() by using%s placeholders in the SQL statement, andpassing a sequence of values as the second argument of the function. Forexample the Python function call:

>>>cur.execute("""...    INSERT INTO some_table (an_int, a_date, a_string)...    VALUES (%s,%s,%s);...    """,...(10,datetime.date(2005,11,18),"O'Reilly"))

is converted into a SQL command similar to:

INSERTINTOsome_table(an_int,a_date,a_string)VALUES(10,'2005-11-18','O''Reilly');

Named arguments are supported too using%(name)s placeholders in thequery and specifying the values into a mapping. Using named arguments allowsto specify the values in any order and to repeat the same value in severalplaces in the query:

>>>cur.execute("""...    INSERT INTO some_table (an_int, a_date, another_date, a_string)...    VALUES (%(int)s,%(date)s,%(date)s,%(str)s);...    """,...{'int':10,'str':"O'Reilly",'date':datetime.date(2005,11,18)})

Using characters%,(,) in the argument names is not supported.

When parameters are used, in order to include a literal% in the query youcan use the%% string:

>>>cur.execute("SELECT (%s % 2) = 0 AS even",(10,))# WRONG>>>cur.execute("SELECT (%s%% 2) = 0 AS even",(10,))# correct

While the mechanism resembles regular Python strings manipulation, there are afew subtle differences you should care about when passing parameters to aquery.

  • The Python string operator%must not be used: theexecute()method accepts a tuple or dictionary of values as second parameter.Never use% or+ to merge valuesinto queries:

    >>>cur.execute("INSERT INTO numbers VALUES (%s,%s)"%(10,20))# WRONG>>>cur.execute("INSERT INTO numbers VALUES (%s,%s)",(10,20))# correct

  • For positional variables binding,the second argument must always be asequence, even if it contains a single variable (remember that Pythonrequires a comma to create a single element tuple):

    >>>cur.execute("INSERT INTO foo VALUES (%s)","bar")# WRONG>>>cur.execute("INSERT INTO foo VALUES (%s)",("bar"))# WRONG>>>cur.execute("INSERT INTO foo VALUES (%s)",("bar",))# correct>>>cur.execute("INSERT INTO foo VALUES (%s)",["bar"])# correct

  • The placeholdermust not be quoted. Psycopg will add quotes where needed:

    >>>cur.execute("INSERT INTO numbers VALUES ('%s')",(10,))# WRONG>>>cur.execute("INSERT INTO numbers VALUES (%s)",(10,))# correct

  • The variables placeholdermust always be a%s, even if a differentplaceholder (such as a%d for integers or%f for floats) may lookmore appropriate:

    >>>cur.execute("INSERT INTO numbers VALUES (%d)",(10,))# WRONG>>>cur.execute("INSERT INTO numbers VALUES (%s)",(10,))# correct

  • Only query values should be bound via this method: it shouldn’t be used tomerge table or field names to the query (Psycopg will try quoting the tablename as a string value, generating invalid SQL). If you need to generatedynamically SQL queries (for instance choosing dynamically a table name)you can use the facilities provided by thepsycopg2.sql module:

    >>>cur.execute("INSERT INTO%s VALUES (%s)",('numbers',10))# WRONG>>>cur.execute(# correct...SQL("INSERT INTO{} VALUES (%s)").format(Identifier('numbers')),...(10,))

The problem with the query parameters

The SQL representation of many data types is often different from their Pythonstring representation. The typical example is with single quotes in strings:in SQL single quotes are used as string literal delimiters, so the onesappearing inside the string itself must be escaped, whereas in Python singlequotes can be left unescaped if the string is delimited by double quotes.

Because of the difference, sometime subtle, between the data typesrepresentations, a naïve approach to query strings composition, such as usingPython strings concatenation, is a recipe forterrible problems:

>>>SQL="INSERT INTO authors (name) VALUES ('%s');"# NEVER DO THIS>>>data=("O'Reilly",)>>>cur.execute(SQL%data)# THIS WILL FAIL MISERABLYProgrammingError: syntax error at or near "Reilly"LINE 1: INSERT INTO authors (name) VALUES ('O'Reilly')                                              ^

If the variables containing the data to send to the database come from anuntrusted source (such as a form published on a web site) an attacker couldeasily craft a malformed string, either gaining access to unauthorized data orperforming destructive operations on the database. This form of attack iscalledSQL injection and is known to be one of the most widespread forms ofattack to database servers. Before continuing, please printthis page as amemo and hang it onto your desk.

Psycopg canautomatically convert Python objects to and from SQLliterals: using this feature your code will be more robust andreliable. We must stress this point:

Warning

Never,never,NEVER use Python string concatenation (+) orstring parameters interpolation (%) to pass variables to a SQL querystring. Not even at gunpoint.

The correct way to pass variables in a SQL command is using the secondargument of theexecute() method:

>>>SQL="INSERT INTO authors (name) VALUES (%s);"# Note: no quotes>>>data=("O'Reilly",)>>>cur.execute(SQL,data)# Note: no % operator

Values containing backslashes and LIKE

Unlike in Python, the backslash (\) is not used as an escapecharacterexcept in patterns used withLIKE andILIKE where theyare needed to escape the% and_ characters.

This can lead to confusing situations:

>>>path=r'C:\Users\Bobby.Tables'>>>cur.execute('INSERT INTO mytable(path) VALUES (%s)',(path,))>>>cur.execute('SELECT * FROM mytable WHERE path LIKE%s',(path,))>>>cur.fetchall()[]

The solution is to specify anESCAPE character of'' (empty string)in yourLIKE query:

>>>cur.execute("SELECT * FROM mytable WHERE path LIKE%s ESCAPE ''",(path,))

Adaptation of Python values to SQL types

Many standard Python types are adapted into SQL and returned as Pythonobjects when a query is executed.

The following table shows the default mapping between Python and PostgreSQLtypes:

Python

PostgreSQL

See also

None

NULL

Constants adaptation

bool

bool

float

real
double

Numbers adaptation

int
long
smallint
integer
bigint

Decimal

numeric

str
unicode
varchar
text

Strings adaptation

buffer
Buffer protocol

bytea

Binary adaptation

date

date

Date/Time objects adaptation

time

time
timetz

datetime

timestamp
timestamptz

timedelta

interval

list

ARRAY

Lists adaptation

tuple
namedtuple
Composite types
IN syntax

dict

hstore

Hstore data type

Psycopg’sRange

range

Range data types

Anything™

json

JSON adaptation

UUID

uuid

UUID data type

ipaddressobjects

inet
cidr

Networking data types

The mapping is fairly customizable: seeAdapting new Python types to SQL syntax andType casting of SQL types into Python objects. You can also find a few otherspecialized adapters in thepsycopg2.extras module.

Constants adaptation

PythonNone and boolean valuesTrue andFalse are converted into theproper SQL literals:

>>>cur.mogrify("SELECT%s,%s,%s;",(None,True,False))'SELECT NULL, true, false;'

Numbers adaptation

Python numeric objectsint,long,float,Decimal areconverted into a PostgreSQL numerical representation:

>>>cur.mogrify("SELECT%s,%s,%s,%s;",(10,10L,10.0,Decimal("10.00")))'SELECT 10, 10, 10.0, 10.00;'

Reading from the database, integer types are converted intoint, floatingpoint types are converted intofloat,numeric/decimal areconverted intoDecimal.

Note

Sometimes you may prefer to receivenumeric data asfloatinstead, for performance reason or ease of manipulation: you can configurean adapter tocast PostgreSQL numeric to Python float.This of course may imply a loss of precision.

Strings adaptation

Pythonstr andunicode are converted into the SQL string syntax.unicode objects (str in Python 3) are encoded in the connectionencoding before sending to the backend: trying to send acharacter not supported by the encoding will result in an error. Data isusually received asstr (i.e. it isdecoded on Python 3, leftencodedon Python 2). However it is possible to receiveunicode on Python 2 too:seeUnicode handling.

Unicode handling

Psycopg can exchange Unicode data with a PostgreSQL database. Pythonunicode objects are automaticallyencoded in the client encodingdefined on the database connection (thePostgreSQL encoding, available inconnection.encoding, is translated into aPython encoding using theencodings mapping):

>>>print(u,type(u))àèìòù€ <type 'unicode'>>>>cur.execute("INSERT INTO test (num, data) VALUES (%s,%s);",(74,u))

When reading data from the database, in Python 2 the strings returned areusually 8 bitstr objects encoded in the database client encoding:

>>>print(conn.encoding)UTF8>>>cur.execute("SELECT data FROM test WHERE num = 74")>>>x=cur.fetchone()[0]>>>print(x,type(x),repr(x))àèìòù€ <type 'str'> '\xc3\xa0\xc3\xa8\xc3\xac\xc3\xb2\xc3\xb9\xe2\x82\xac'>>>conn.set_client_encoding('LATIN9')>>>cur.execute("SELECT data FROM test WHERE num = 74")>>>x=cur.fetchone()[0]>>>print(type(x),repr(x))<type 'str'> '\xe0\xe8\xec\xf2\xf9\xa4'

In Python 3 instead the strings are automaticallydecoded in the connectionencoding, as thestr object can represent Unicode characters.In Python 2 you must register atypecaster in order to receiveunicode objects:

>>>psycopg2.extensions.register_type(psycopg2.extensions.UNICODE,cur)>>>cur.execute("SELECT data FROM test WHERE num = 74")>>>x=cur.fetchone()[0]>>>print(x,type(x),repr(x))àèìòù€ <type 'unicode'> u'\xe0\xe8\xec\xf2\xf9\u20ac'

In the above example, theUNICODE typecaster isregistered only on the cursor. It is also possible to register typecasters onthe connection or globally: see the functionregister_type() andType casting of SQL types into Python objects for details.

Note

In Python 2, if you want to uniformly receive all your database input inUnicode, you can register the related typecasters globally as soon asPsycopg is imported:

importpsycopg2.extensionspsycopg2.extensions.register_type(psycopg2.extensions.UNICODE)psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)

and forget about this story.

Note

In some cases, on Python 3, you may want to receivebytes instead ofstr, without undergoing to any decoding. This is especially the case ifthe data in the database is in mixed encoding. TheBYTES caster is what you neeed:

importpsycopg2.extensionspsycopg2.extensions.register_type(psycopg2.extensions.BYTES,conn)psycopg2.extensions.register_type(psycopg2.extensions.BYTESARRAY,conn)cur=conn.cursor()cur.execute("select%s::text",(u"€",))cur.fetchone()[0]b'\xe2\x82\xac'

Binary adaptation

Python types representing binary objects are converted into PostgreSQL binarystring syntax, suitable forbytea fields. Such types arebuffer(only available in Python 2),memoryview,bytearray, andbytes (only inPython 3: the name is available in Python 2 but it’s only an alias for thetypestr). Any object implementing theRevised Buffer Protocol shouldbe usable as binary type. Received data is returned asbuffer (in Python 2)ormemoryview (in Python 3).

Changed in version 2.4:only strings were supported before.

Changed in version 2.4.1:can parse the ‘hex’ format from 9.0 servers without relying on theversion of the client library.

Note

In Python 2, if you have binary data in astr object, you can pass themto abytea field using thepsycopg2.Binary wrapper:

mypic=open('picture.png','rb').read()curs.execute("insert into blobs (file) values (%s)",(psycopg2.Binary(mypic),))

Warning

Since version 9.0 PostgreSQL uses by defaulta new “hex” format toemitbytea fields. Starting from Psycopg 2.4.1 the format iscorrectly supported. If you use a previous version you will need someextra care when receiving bytea from PostgreSQL: you must have at leastlibpq 9.0 installed on the client or alternatively you can set thebytea_output configuration parameter toescape, either in theserver configuration file or in the client session (using a query such asSETbytea_outputTOescape;) before receiving binary data.

Date/Time objects adaptation

Python builtindatetime,date,time,timedelta are converted into PostgreSQL’stimestamp[tz],date,time[tz],interval data types.Time zones are supported too.

>>>dt=datetime.datetime.now()>>>dtdatetime.datetime(2010, 2, 8, 1, 40, 27, 425337)
>>>cur.mogrify("SELECT%s,%s,%s;",(dt,dt.date(),dt.time()))"SELECT '2010-02-08T01:40:27.425337', '2010-02-08', '01:40:27.425337';"
>>>cur.mogrify("SELECT%s;",(dt-datetime.datetime(2010,1,1),))"SELECT '38 days 6027.425337 seconds';"

Time zones handling

The PostgreSQL typetimestampwithtimezone (a.k.a.timestamptz) is converted into Pythondatetime objects.

>>>cur.execute("SET TIME ZONE 'Europe/Rome'")# UTC + 1 hour>>>cur.execute("SELECT '2010-01-01 10:30:45'::timestamptz")>>>cur.fetchone()[0]datetime.datetime(2010, 1, 1, 10, 30, 45,    tzinfo=datetime.timezone(datetime.timedelta(seconds=3600)))

Note

Before Python 3.7, thedatetime module only supported timezones with aninteger number of minutes. A few historical time zones had seconds in theUTC offset: these time zones will have the offset rounded to the nearestminute, with an error of up to 30 seconds, on Python versions before 3.7.

>>>cur.execute("SET TIME ZONE 'Asia/Calcutta'")# offset was +5:21:10>>>cur.execute("SELECT '1900-01-01 10:30:45'::timestamptz")>>>cur.fetchone()[0].tzinfo# On Python 3.6: 5h, 21mdatetime.timezone(datetime.timedelta(0, 19260))# On Python 3.7 and following: 5h, 21m, 10sdatetime.timezone(datetime.timedelta(seconds=19270))

Changed in version 2.2.2:timezones with seconds are supported (with rounding). Previously suchtimezones raised an error.

Changed in version 2.9:timezones with seconds are supported without rounding.

Changed in version 2.9:usedatetime.timezone as default tzinfo object instead ofFixedOffsetTimezone.

Infinite dates handling

PostgreSQL can store the representation of an “infinite” date, timestamp, orinterval. Infinite dates are not available to Python, so these objects aremapped todate.max,datetime.max,interval.max. Unfortunately themapping cannot be bidirectional so these dates will be stored back into thedatabase with their values, such as9999-12-31.

It is possible to create an alternative adapter for dates and other objectsto mapdate.max toinfinity, for instance:

classInfDateAdapter:def__init__(self,wrapped):self.wrapped=wrappeddefgetquoted(self):ifself.wrapped==datetime.date.max:returnb"'infinity'::date"elifself.wrapped==datetime.date.min:returnb"'-infinity'::date"else:returnpsycopg2.extensions.DateFromPy(self.wrapped).getquoted()psycopg2.extensions.register_adapter(datetime.date,InfDateAdapter)

Of course it will not be possible to write the value ofdate.max in thedatabase anymore:infinity will be stored instead.

Time handling

The PostgreSQLtime and Pythontime types are notfully bidirectional.

Within PostgreSQL, thetime type’s maximum value of24:00:00 istreated as 24-hours later than the minimum value of00:00:00.

>>>cur.execute("SELECT '24:00:00'::time - '00:00:00'::time")>>>cur.fetchone()[0]datetime.timedelta(days=1)

However, Python’stime only supports times until23:59:59.Retrieving a value of24:00:00 results in atime of00:00:00.

>>>cur.execute("SELECT '24:00:00'::time, '00:00:00'::time")>>>cur.fetchone()(datetime.time(0, 0), datetime.time(0, 0))

Lists adaptation

Python lists are converted into PostgreSQLARRAYs:

>>>cur.mogrify("SELECT%s;",([10,20,30],))'SELECT ARRAY[10,20,30];'

Note

You can use a Python list as the argument of theIN operator usingthe PostgreSQL ANY operator.

ids=[10,20,30]cur.execute("SELECT * FROM data WHERE id = ANY(%s);",(ids,))

FurthermoreANY can also work with empty lists, whereasIN()is a SQL syntax error.

Note

Reading back from PostgreSQL, arrays are converted to lists of Pythonobjects as expected, but only if the items are of a known type.Arrays of unknown types are returned as represented by the database (e.g.{a,b,c}). If you want to convert the items into Python objects you caneasily create a typecaster forarray of unknown types.

Tuples adaptation

Python tuples are converted into a syntax suitable for the SQLINoperator and to represent a composite type:

>>>cur.mogrify("SELECT%s IN%s;",(10,(10,20,30)))'SELECT 10 IN (10, 20, 30);'

Note

SQL doesn’t allow an empty list in theIN operator, so your codeshould guard against empty tuples. Alternatively you canuse aPython list.

If you want PostgreSQL composite types to be converted into a Pythontuple/namedtuple you can use theregister_composite()function.

Added in version 2.0.6:the tupleIN adaptation.

Changed in version 2.0.14:the tupleIN adapter is always active. In previous releases itwas necessary to import theextensions module to have itregistered.

Changed in version 2.3:namedtuple instances are adapted like regular tuples andcan thus be used to represent composite types.

Transactions control

In Psycopg transactions are handled by theconnection class. Bydefault, the first time a command is sent to the database (using one of thecursors created by the connection), a new transaction is created.The following database commands will be executed in the context of the sametransaction – not only the commands issued by the first cursor, but the onesissued by all the cursors created by the same connection. Should any commandfail, the transaction will be aborted and no further command will be executeduntil a call to therollback() method.

The connection is responsible for terminating its transaction, calling eitherthecommit() orrollback() method. Committedchanges are immediately made persistent in the database. If the connectionis closed (using theclose() method) or destroyed (usingdelor by letting it fall out of scope) while a transaction is in progress, theserver will discard the transaction. However doing so is not advisable:middleware such asPgBouncer may see the connection closed uncleanly anddispose of it.

It is possible to set the connection inautocommit mode: this way all thecommands executed will be immediately committed and no rollback is possible. Afew commands (e.g.CREATEDATABASE,VACUUM,CALL onstored procedures using transaction control…) require to be runoutside any transaction: in order to be able to run these commands fromPsycopg, the connection must be in autocommit mode: you can use theautocommit property.

Warning

By default even a simpleSELECT will start a transaction: inlong-running programs, if no further action is taken, the session willremain “idle in transaction”, an undesirable condition for severalreasons (locks are held by the session, tables bloat…). For long livedscripts, either make sure to terminate a transaction as soon as possible oruse an autocommit connection.

A few other transaction properties can be set session-wide by theconnection: for instance it is possible to have read-only transactions orchange the isolation level. See theset_session() method for allthe details.

with statement

Starting from version 2.5, psycopg2’s connections and cursors arecontextmanagers and can be used with thewith statement:

withpsycopg2.connect(DSN)asconn:withconn.cursor()ascurs:curs.execute(SQL)

When a connection exits thewith block, if no exception has been raised bythe block, the transaction is committed. In case of exception the transactionis rolled back.

When a cursor exits thewith block it is closed, releasing any resourceeventually associated with it. The state of the transaction is not affected.

A connection can be used in more than onewith statementand eachwith block is effectively wrapped in a separate transaction:

conn=psycopg2.connect(DSN)withconn:withconn.cursor()ascurs:curs.execute(SQL1)withconn:withconn.cursor()ascurs:curs.execute(SQL2)conn.close()

Warning

Unlike file objects or other resources, exiting the connection’swith blockdoesn’t close the connection, but only the transactionassociated to it. If you want to make sure the connection is closed aftera certain point, you should still use a try-catch block:

conn=psycopg2.connect(DSN)try:# connection usagefinally:conn.close()

Changed in version 2.9:withconnection starts a transaction also on autocommit connections.

Server side cursors

When a database query is executed, the Psycopgcursor usually fetchesall the records returned by the backend, transferring them to the clientprocess. If the query returns a huge amount of data, a proportionally largeamount of memory will be allocated by the client.

If the dataset is too large to be practically handled on the client side, it ispossible to create aserver side cursor. Using this kind of cursor it ispossible to transfer to the client only a controlled amount of data, so that alarge dataset can be examined without keeping it entirely in memory.

Server side cursor are created in PostgreSQL using theDECLARE command andsubsequently handled usingMOVE,FETCH andCLOSE commands.

Psycopg wraps the database server side cursor innamed cursors. A namedcursor is created using thecursor() method specifying thename parameter. Such cursor will behave mostly like a regular cursor,allowing the user to move in the dataset using thescroll()method and to read the data usingfetchone() andfetchmany() methods. Normally you can only scroll forward in acursor: if you need to scroll backwards you should declare your cursorscrollable.

Named cursors are alsoiterable like regular cursors.Note however that before Psycopg 2.4 iteration was performed fetching onerecord at time from the backend, resulting in a large overhead. The attributeitersize now controls how many records are fetched at timeduring the iteration: the default value of 2000 allows to fetch about 100KBper roundtrip assuming records of 10-20 columns of mixed number and strings;you may decrease this value if you are dealing with huge records.

Named cursors are usually createdWITHOUTHOLD, meaning they live onlyas long as the current transaction. Trying to fetch from a named cursor afteracommit() or to create a named cursor when the connectionis inautocommit mode will result in an exception.It is possible to create aWITHHOLD cursor by specifying aTruevalue for thewithhold parameter tocursor() or by setting thewithhold attribute toTrue before callingexecute() onthe cursor. It is extremely important to alwaysclose() such cursors,otherwise they will continue to hold server-side resources until the connectionwill be eventually closed. Also note that whileWITHHOLD cursorslifetime extends well aftercommit(), callingrollback() will automatically close the cursor.

Note

It is also possible to use a named cursor to consume a cursor createdin some other way than using theDECLARE executed byexecute(). For example, you may have a PL/pgSQL functionreturning a cursor:

CREATEFUNCTIONreffunc(refcursor)RETURNSrefcursorAS$$BEGINOPEN$1FORSELECTcolFROMtest;RETURN$1;END;$$LANGUAGEplpgsql;

You can read the cursor content by calling the function with a regular,non-named, Psycopg cursor:

cur1=conn.cursor()cur1.callproc('reffunc',['curname'])

and then use a named cursor in the same transaction to “steal the cursor”:

cur2=conn.cursor('curname')forrecordincur2:# or cur2.fetchone, fetchmany...# do something with recordpass

Thread and process safety

The Psycopg module and theconnection objects arethread-safe: manythreads can access the same database either using separate sessions andcreating aconnection per thread or using the sameconnection and creating separatecursors. InDB API 2.0 parlance, Psycopg islevel 2 thread safe.

The difference between the above two approaches is that, using differentconnections, the commands will be executed in different sessions and will beserved by different server processes. On the other hand, using many cursors onthe same connection, all the commands will be executed in the same session(and in the same transaction if the connection is not inautocommit mode), but they will be serialized.

The above observations are only valid for regular threads: they don’t apply toforked processes nor to green threads.libpq connectionsshouldn’t be used by aforked processes, so when using a module such asmultiprocessing or aforking web deploy method such as FastCGI make sure to create the connectionsafter the fork.

Connections shouldn’t be shared either by different green threads: seeSupport for coroutine libraries for further details.

Using COPY TO and COPY FROM

Psycopgcursor objects provide an interface to the efficientPostgreSQLCOPY command to move data from files to tables and back.

Currently no adaptation is provided between Python and PostgreSQL types onCOPY: the file can be any Python file-like object but its format must be inthe format accepted byPostgreSQL COPY command (data format, escapedcharacters, etc).

The methods exposed are:

copy_from()

Reads datafrom a file-like object appending them to a database table(COPYtableFROMfile syntax). The source file must provide bothread() andreadline() method.

copy_to()

Writes the content of a tableto a file-like object (COPYtableTOfile syntax). The target file must have awrite() method.

copy_expert()

Allows to handle more specific cases and to use all theCOPYfeatures available in PostgreSQL.

Please refer to the documentation of the single methods for details andexamples.

Access to PostgreSQL large objects

PostgreSQL offers support forlarge objects, which provide stream-styleaccess to user data that is stored in a special large-object structure. Theyare useful with data values too large to be manipulated conveniently as awhole.

Psycopg allows access to the large object using thelobject class. Objects are generated using theconnection.lobject() factory method. Data can be retrieved either as bytesor as Unicode strings.

Psycopg large object support efficient import/export with file system filesusing thelo_import() andlo_export() libpq functions.

Changed in version 2.6:added support for large objects greater than 2GB. Note that the support isenabled only if all the following conditions are verified:

  • the Python build is 64 bits;

  • the extension was built against at least libpq 9.3;

  • the server version is at least PostgreSQL 9.3(server_version must be >=90300).

If Psycopg was built with 64 bits large objects support (i.e. the firsttwo conditions above are verified), thepsycopg2.__version__ constantwill contain thelo64 flag. If any of the condition is not metseverallobject methods will fail if the arguments exceed 2GB.

Two-Phase Commit protocol support

Added in version 2.3.

Psycopg exposes the two-phase commit features available since PostgreSQL 8.1implementing thetwo-phase commit extensions proposed by the DB API 2.0.

The DB API 2.0 model of two-phase commit is inspired by theXA specification,according to which transaction IDs are formed from three components:

  • a format ID (non-negative 32 bit integer)

  • a global transaction ID (string not longer than 64 bytes)

  • a branch qualifier (string not longer than 64 bytes)

For a particular global transaction, the first two components will be the samefor all the resources. Every resource will be assigned a different branchqualifier.

According to the DB API 2.0 specification, a transaction ID is created using theconnection.xid() method. Once you have a transaction id, a distributedtransaction can be started withconnection.tpc_begin(), prepared usingtpc_prepare() and completed usingtpc_commit() ortpc_rollback(). Transaction IDs can also be retrieved from thedatabase usingtpc_recover() and completed using the abovetpc_commit() andtpc_rollback().

PostgreSQL doesn’t follow the XA standard though, and the ID for a PostgreSQLprepared transaction can be any string up to 200 characters long.Psycopg’sXid objects can represent both XA-styletransactions IDs (such as the ones created by thexid() method) andPostgreSQL transaction IDs identified by an unparsed string.

The format in which the Xids are converted into strings passed to thedatabase is the same employed by thePostgreSQL JDBC driver: this shouldallow interoperation between tools written in Python and in Java. For examplea recovery tool written in Python would be able to recognize the components oftransactions produced by a Java program.

For further details see the documentation for the above methods.