API Reference
Connection
- asyncconnect(dsn=None,*,host=None,port=None,user=None,password=None,passfile=None,service=None,servicefile=None,database=None,loop=None,timeout=60,statement_cache_size=100,max_cached_statement_lifetime=300,max_cacheable_statement_size=15360,command_timeout=None,ssl=None,direct_tls=None,connection_class=<class'asyncpg.connection.Connection'>,record_class=<class'asyncpg.protocol.record.Record'>,server_settings=None,target_session_attrs=None,krbsrvname=None,gsslib=None)[source]
A coroutine to establish a connection to a PostgreSQL server.
The connection parameters may be specified either as a connectionURI indsn, or as specific keyword arguments, or both.If bothdsn and keyword arguments are specified, the latteroverride the corresponding values parsed from the connection URI.The default values for the majority of arguments can be specifiedusingenvironment variables.
Returns a new
Connectionobject.- Parameters:
dsn –
Connection arguments specified using as a single string in thelibpq connection URI format:
postgres://user:password@host:port/database?option=value.The following options are recognized by asyncpg:host,port,user,database(ordbname),password,passfile,sslmode,sslcert,sslkey,sslrootcert,andsslcrl. Unlike libpq, asyncpg will treat unrecognizedoptions asserver settings to be used for the connection.Note
The URI must bevalid, which means that all components mustbe properly quoted with
urllib.parse.quote_plus(), andany literal IPv6 addresses must be enclosed in square brackets.For example:postgres://dbuser@[fe80::1ff:fe23:4567:890a%25eth0]/dbname
host –
Database host address as one of the following:
an IP address or a domain name;
an absolute path to the directory containing the databaseserver Unix-domain socket (not supported on Windows);
a sequence of any of the above, in which case the addresseswill be tried in order, and the first successful connectionwill be returned.
If not specified, asyncpg will try the following, in order:
host address(es) parsed from thedsn argument,
the value of the
PGHOSTenvironment variable,on Unix, common directories used for PostgreSQL Unix-domainsockets:
"/run/postgresql","/var/run/postgresl","/var/pgsql_socket","/private/tmp", and"/tmp","localhost".
port –
Port number to connect to at the server host(or Unix-domain socket file extension). If multiple hostaddresses were specified, this parameter may specify asequence of port numbers of the same length as the host sequence,or it may specify a single port number to be used for all hostaddresses.
If not specified, the value parsed from thedsn argument is used,or the value of the
PGPORTenvironment variable, or5432ifneither is specified.user –
The name of the database role used for authentication.
If not specified, the value parsed from thedsn argument is used,or the value of the
PGUSERenvironment variable, or theoperating system name of the user running the application.database –
The name of the database to connect to.
If not specified, the value parsed from thedsn argument is used,or the value of the
PGDATABASEenvironment variable, or thecomputed value of theuser argument.password –
Password to be used for authentication, if the server requiresone. If not specified, the value parsed from thedsn argumentis used, or the value of the
PGPASSWORDenvironment variable.Note that the use of the environment variable is discouraged asother users and applications may be able to read it without needingspecific privileges. It is recommended to usepassfile instead.Password may be either a string, or a callable that returns a string.If a callable is provided, it will be called each time a new connectionis established.
passfile – The name of the file used to store passwords(defaults to
~/.pgpass, or%APPDATA%\postgresql\pgpass.confon Windows).service – The name of the postgres connection service stored in the postgresconnection service file.
servicefile – The location of the connnection service file used to storeconnection parameters.
loop – An asyncio event loop instance. If
None, the defaultevent loop will be used.timeout (float) – Connection timeout in seconds.
statement_cache_size (int) – The size of prepared statement LRU cache. Pass
0todisable the cache.max_cached_statement_lifetime (int) – The maximum time in seconds a prepared statement will stayin the cache. Pass
0to allow statements be cachedindefinitely.max_cacheable_statement_size (int) – The maximum size of a statement that can be cached (15KiB bydefault). Pass
0to allow all statements to be cachedregardless of their size.command_timeout (float) – The default timeout for operations on this connection(the default is
None: no timeout).ssl –
Pass
Trueor anssl.SSLContext instance torequire an SSL connection. IfTrue, a default SSL contextreturned byssl.create_default_context()will be used. The value can also be one of the following strings:'disable'- SSL is disabled (equivalent toFalse)'prefer'- try SSL first, fallback to non-SSL connectionif SSL connection fails'allow'- try without SSL first, then retry with SSL if the firstattempt fails.'require'- only try an SSL connection. Certificateverification errors are ignored'verify-ca'- only try an SSL connection, and verifythat the server certificate is issued by a trusted certificateauthority (CA)'verify-full'- only try an SSL connection, verifythat the server certificate is issued by a trusted CA andthat the requested server host name matches that in thecertificate.
The default is
'prefer': try an SSL connection and fallback tonon-SSL connection if that fails.Note
ssl is ignored for Unix domain socket communication.
Example of programmatic SSL context configuration that is equivalentto
sslmode=verify-full&sslcert=..&sslkey=..&sslrootcert=..:>>>importasyncpg>>>importasyncio>>>importssl>>>asyncdefmain():...# Load CA bundle for server certificate verification,...# equivalent to sslrootcert= in DSN....sslctx=ssl.create_default_context(...ssl.Purpose.SERVER_AUTH,...cafile="path/to/ca_bundle.pem")...# If True, equivalent to sslmode=verify-full, if False:...# sslmode=verify-ca....sslctx.check_hostname=True...# Load client certificate and private key for client...# authentication, equivalent to sslcert= and sslkey= in...# DSN....sslctx.load_cert_chain(..."path/to/client.cert",...keyfile="path/to/client.key",...)...con=awaitasyncpg.connect(user='postgres',ssl=sslctx)...awaitcon.close()>>>asyncio.run(main())
Example of programmatic SSL context configuration that is equivalentto
sslmode=require(no server certificate or host verification):>>>importasyncpg>>>importasyncio>>>importssl>>>asyncdefmain():...sslctx=ssl.create_default_context(...ssl.Purpose.SERVER_AUTH)...sslctx.check_hostname=False...sslctx.verify_mode=ssl.CERT_NONE...con=awaitasyncpg.connect(user='postgres',ssl=sslctx)...awaitcon.close()>>>asyncio.run(main())
direct_tls (bool) – Pass
Trueto skip PostgreSQL STARTTLS mode and perform a directSSL connection. Must be used alongsidesslparam.server_settings (dict) – An optional dict of server runtime parameters. Refer toPostgreSQL documentation foralist of supported options.
connection_class (type) – Class of the returned connection object. Must be a subclass of
Connection.record_class (type) – If specified, the class to use for records returned by queries onthis connection object. Must be a subclass of
Record.target_session_attrs (SessionAttribute) –
If specified, check that the host has the correct attribute.Can be one of:
"any"- the first successfully connected host"primary"- the host must NOT be in hot standby mode"standby"- the host must be in hot standby mode"read-write"- the host must allow writes"read-only"- the host most NOT allow writes"prefer-standby"- first try to find a standby host, but ifnone of the listed hosts is a standby server,return any of them.
If not specified, the value parsed from thedsn argument is used,or the value of the
PGTARGETSESSIONATTRSenvironment variable,or"any"if neither is specified.krbsrvname (str) – Kerberos service name to use when authenticating with GSSAPI. Thismust match the server configuration. Defaults to ‘postgres’.
gsslib (str) – GSS library to use for GSSAPI/SSPI authentication. Can be ‘gssapi’or ‘sspi’. Defaults to ‘sspi’ on Windows and ‘gssapi’ otherwise.
- Returns:
A
Connectioninstance.
Example:
>>>importasyncpg>>>importasyncio>>>asyncdefrun():...con=awaitasyncpg.connect(user='postgres')...types=awaitcon.fetch('SELECT * FROM pg_type')...print(types)...>>>asyncio.run(run())[<Record typname='bool' typnamespace=11 ...
Added in version 0.10.0:Added
max_cached_statement_use_countparameter.Changed in version 0.11.0:Removed ability to pass arbitrary keyword arguments to setserver settings. Added a dedicated parameter
server_settingsfor that.Added in version 0.11.0:Added
connection_classparameter.Added in version 0.16.0:Added
passfileparameter(and support for password files in general).Added in version 0.18.0:Added ability to specify multiple hosts in thedsnandhost arguments.
Changed in version 0.21.0:Thepassword argument now accepts a callable or an async function.
Changed in version 0.22.0:Added therecord_class parameter.
Changed in version 0.22.0:Thessl argument now defaults to
'prefer'.Changed in version 0.24.0:The
sslcert,sslkey,sslrootcert, andsslcrloptionsare supported in thedsn argument.Changed in version 0.25.0:The
sslpassword,ssl_min_protocol_version,andssl_max_protocol_versionoptions are supported in thedsnargument.Changed in version 0.25.0:Default system root CA certificates won’t be loaded when specifying aparticular sslmode, following the same behavior in libpq.
Changed in version 0.25.0:The
sslcert,sslkey,sslrootcert, andsslcrloptionsin thedsn argument now have consistent default values of files under~/.postgresql/as libpq.Changed in version 0.26.0:Added thedirect_tls parameter.
Changed in version 0.28.0:Added thetarget_session_attrs parameter.
Changed in version 0.30.0:Added thekrbsrvname andgsslib parameters.
Changed in version 0.31.0:Added theservicefile andservice parameters.
- classConnection(protocol,transport,loop,addr,config:ConnectionConfiguration,params:ConnectionParameters)[source]
A representation of a database session.
Connections are created by calling
connect().- asyncadd_listener(channel,callback)[source]
Add a listener for Postgres notifications.
- Parameters:
channel (str) – Channel to listen on.
callback (callable) – A callable or a coroutine function receiving the followingarguments:connection: a Connection the callback is registered with;pid: PID of the Postgres server that sent the notification;channel: name of the channel the notification was sent to;payload: the payload.
Changed in version 0.24.0:The
callbackargument may be a coroutine function.
- add_log_listener(callback)[source]
Add a listener for Postgres log messages.
It will be called when asyncronous NoticeResponse is receivedfrom the connection. Possible message types are: WARNING, NOTICE,DEBUG, INFO, or LOG.
- Parameters:
callback (callable) – A callable or a coroutine function receiving the followingarguments:connection: a Connection the callback is registered with;message: theexceptions.PostgresLogMessage message.
Added in version 0.12.0.
Changed in version 0.24.0:The
callbackargument may be a coroutine function.
- add_query_logger(callback)[source]
Add a logger that will be called when queries are executed.
- Parameters:
callback (callable) – A callable or a coroutine function receiving one argument:record, a LoggedQuery containingquery,args,timeout,elapsed,exception,conn_addr, andconn_params.
Added in version 0.29.0.
- add_termination_listener(callback)[source]
Add a listener that will be called when the connection is closed.
- Parameters:
callback (callable) – A callable or a coroutine function receiving one argument:connection: a Connection the callback is registered with.
Added in version 0.21.0.
Changed in version 0.24.0:The
callbackargument may be a coroutine function.
- asyncclose(*,timeout=None)[source]
Close the connection gracefully.
- Parameters:
timeout (float) – Optional timeout value in seconds.
Changed in version 0.14.0:Added thetimeout parameter.
- asynccopy_from_query(query,*args,output,timeout=None,format=None,oids=None,delimiter=None,null=None,header=None,quote=None,escape=None,force_quote=None,encoding=None)[source]
Copy the results of a query to a file or file-like object.
- Parameters:
query (str) – The query to copy the results of.
args – Query arguments.
output – Apath-like object,or afile-like object, oracoroutine functionthat takes a
bytesinstance as a sole argument.timeout (float) – Optional timeout value in seconds.
The remaining keyword arguments are
COPYstatement options,seeCOPY statement documentation for details.- Returns:
The status string of the COPY command.
Example:
>>>importasyncpg>>>importasyncio>>>asyncdefrun():...con=awaitasyncpg.connect(user='postgres')...result=awaitcon.copy_from_query(...'SELECT foo, bar FROM mytable WHERE foo > $1',10,...output='file.csv',format='csv')...print(result)...>>>asyncio.run(run())'COPY 10'
Added in version 0.11.0.
- asynccopy_from_table(table_name,*,output,columns=None,schema_name=None,timeout=None,format=None,oids=None,delimiter=None,null=None,header=None,quote=None,escape=None,force_quote=None,encoding=None)[source]
Copy table contents to a file or file-like object.
- Parameters:
table_name (str) – The name of the table to copy data from.
output – Apath-like object,or afile-like object, oracoroutine functionthat takes a
bytesinstance as a sole argument.columns (list) – An optional list of column names to copy.
schema_name (str) – An optional schema name to qualify the table.
timeout (float) – Optional timeout value in seconds.
The remaining keyword arguments are
COPYstatement options,seeCOPY statement documentation for details.- Returns:
The status string of the COPY command.
Example:
>>>importasyncpg>>>importasyncio>>>asyncdefrun():...con=awaitasyncpg.connect(user='postgres')...result=awaitcon.copy_from_table(...'mytable',columns=('foo','bar'),...output='file.csv',format='csv')...print(result)...>>>asyncio.run(run())'COPY 100'
Added in version 0.11.0.
- asynccopy_records_to_table(table_name,*,records,columns=None,schema_name=None,timeout=None,where=None)[source]
Copy a list of records to the specified table using binary COPY.
- Parameters:
table_name (str) – The name of the table to copy data to.
records – An iterable returning row tuples to copy into the table.Asynchronous iterablesare also supported.
columns (list) – An optional list of column names to copy.
schema_name (str) – An optional schema name to qualify the table.
where (str) –
An optional SQL expression used to filter rows when copying.
Note
Usage of this parameter requires support for the
COPYFROM...WHEREsyntax, introduced inPostgreSQL version 12.timeout (float) – Optional timeout value in seconds.
- Returns:
The status string of the COPY command.
Example:
>>>importasyncpg>>>importasyncio>>>asyncdefrun():...con=awaitasyncpg.connect(user='postgres')...result=awaitcon.copy_records_to_table(...'mytable',records=[...(1,'foo','bar'),...(2,'ham','spam')])...print(result)...>>>asyncio.run(run())'COPY 2'
Asynchronous record iterables are also supported:
>>>importasyncpg>>>importasyncio>>>asyncdefrun():...con=awaitasyncpg.connect(user='postgres')...asyncdefrecord_gen(size):...foriinrange(size):...yield(i,)...result=awaitcon.copy_records_to_table(...'mytable',records=record_gen(100))...print(result)...>>>asyncio.run(run())'COPY 100'
Added in version 0.11.0.
Changed in version 0.24.0:The
recordsargument may be an asynchronous iterable.Added in version 0.29.0:Added thewhere parameter.
- asynccopy_to_table(table_name,*,source,columns=None,schema_name=None,timeout=None,format=None,oids=None,freeze=None,delimiter=None,null=None,header=None,quote=None,escape=None,force_quote=None,force_not_null=None,force_null=None,encoding=None,where=None)[source]
Copy data to the specified table.
- Parameters:
table_name (str) – The name of the table to copy data to.
source – Apath-like object,or afile-like object, oranasynchronous iterablethat returns
bytes, or an object supporting thebuffer protocol.columns (list) – An optional list of column names to copy.
schema_name (str) – An optional schema name to qualify the table.
where (str) –
An optional SQL expression used to filter rows when copying.
Note
Usage of this parameter requires support for the
COPYFROM...WHEREsyntax, introduced inPostgreSQL version 12.timeout (float) – Optional timeout value in seconds.
The remaining keyword arguments are
COPYstatement options,seeCOPY statement documentation for details.- Returns:
The status string of the COPY command.
Example:
>>>importasyncpg>>>importasyncio>>>asyncdefrun():...con=awaitasyncpg.connect(user='postgres')...result=awaitcon.copy_to_table(...'mytable',source='datafile.tbl')...print(result)...>>>asyncio.run(run())'COPY 140000'
Added in version 0.11.0.
Added in version 0.29.0:Added thewhere parameter.
- cursor(query,*args,prefetch=None,timeout=None,record_class=None)[source]
Return acursor factory for the specified query.
- Parameters:
args – Query arguments.
prefetch (int) – The number of rows thecursor iteratorwill prefetch (defaults to
50.)timeout (float) – Optional timeout in seconds.
record_class (type) – If specified, the class to use for records returned by this cursor.Must be a subclass of
Record. If not specified,a per-connectionrecord_class is used.
- Returns:
A
CursorFactoryobject.
Changed in version 0.22.0:Added therecord_class parameter.
- asyncexecute(query:str,*args,timeout:float|None=None)→str[source]
Execute an SQL command (or commands).
This method can execute many SQL commands at once, when no argumentsare provided.
Example:
>>>awaitcon.execute('''... CREATE TABLE mytab (a int);... INSERT INTO mytab (a) VALUES (100), (200), (300);...''')INSERT 0 3>>>awaitcon.execute('''... INSERT INTO mytab (a) VALUES ($1), ($2)...''',10,20)INSERT 0 2
- Parameters:
args – Query arguments.
timeout (float) – Optional timeout value in seconds.
- Return str:
Status of the last SQL command.
Changed in version 0.5.4:Made it possible to pass query arguments.
- asyncexecutemany(command:str,args,*,timeout:float|None=None)[source]
Execute an SQLcommand for each sequence of arguments inargs.
Example:
>>>awaitcon.executemany('''... INSERT INTO mytab (a) VALUES ($1, $2, $3);...''',[(1,2,3),(4,5,6)])
- Parameters:
command – Command to execute.
args – An iterable containing sequences of arguments.
timeout (float) – Optional timeout value in seconds.
- Return None:
This method discards the results of the operations.
Added in version 0.7.0.
Changed in version 0.11.0:timeout became a keyword-only parameter.
Changed in version 0.22.0:
executemany()is now an atomic operation, which means thateither all executions succeed, or none at all. This is in contrastto prior versions, where the effect of already-processed iterationswould remain in place when an error has occurred, unlessexecutemany()was called in a transaction.
- asyncfetch(query,*args,timeout=None,record_class=None)→list[source]
Run a query and return the results as a list of
Record.- Parameters:
- Return list:
A list of
Recordinstances. If specified, theactual type of list elements would berecord_class.
Changed in version 0.22.0:Added therecord_class parameter.
- asyncfetchmany(query,args,*,timeout:float|None=None,record_class=None)[source]
Run a query for each sequence of arguments inargsand return the results as a list of
Record.- Parameters:
query – Query to execute.
args – An iterable containing sequences of arguments for the query.
timeout (float) – Optional timeout value in seconds.
record_class (type) – If specified, the class to use for records returned by this method.Must be a subclass of
Record. If not specified,a per-connectionrecord_class is used.
- Return list:
A list of
Recordinstances. If specified, theactual type of list elements would berecord_class.
Example:
>>>rows=awaitcon.fetchmany('''... INSERT INTO mytab (a, b) VALUES ($1, $2) RETURNING a;... ''',[('x',1),('y',2),('z',3)])>>>rows[<Record row=('x',)>, <Record row=('y',)>, <Record row=('z',)>]
Added in version 0.30.0.
- asyncfetchrow(query,*args,timeout=None,record_class=None)[source]
Run a query and return the first row.
- Parameters:
- Returns:
The first row as a
Recordinstance, or None ifno records were returned by the query. If specified,record_class is used as the type for the result value.
Changed in version 0.22.0:Added therecord_class parameter.
- asyncfetchval(query,*args,column=0,timeout=None)[source]
Run a query and return a value in the first row.
- Parameters:
- Returns:
The value of the specified column of the first record, orNone if no records were returned by the query.
- get_reset_query()[source]
Return the query sent to server on connection release.
The query returned by this method is used by
Connection.reset(),which is, in turn, used byPoolbefore makingthe connection available to another acquirer.Added in version 0.30.0.
- get_server_version()[source]
Return the version of the connected PostgreSQL server.
The returned value is a named tuple similar to that in
sys.version_info:>>>con.get_server_version()ServerVersion(major=9, minor=6, micro=1, releaselevel='final', serial=0)
Added in version 0.8.0.
- is_closed()[source]
Return
Trueif the connection is closed,Falseotherwise.- Return bool:
Trueif the connection is closed,Falseotherwise.
- is_in_transaction()[source]
Return True if Connection is currently inside a transaction.
- Return bool:
True if inside transaction, False otherwise.
Added in version 0.16.0.
- asyncprepare(query,*,name=None,timeout=None,record_class=None)[source]
Create aprepared statement for the specified query.
- Parameters:
query (str) – Text of the query to create a prepared statement for.
name (str) – Optional name of the returned prepared statement. If notspecified, the name is auto-generated.
timeout (float) – Optional timeout value in seconds.
record_class (type) – If specified, the class to use for records returned by theprepared statement. Must be a subclass of
Record. If not specified, a per-connectionrecord_class is used.
- Returns:
A
PreparedStatementinstance.
Changed in version 0.22.0:Added therecord_class parameter.
Changed in version 0.25.0:Added thename parameter.
- query_logger(callback)[source]
Context manager that addscallback to the list of query loggers,and removes it upon exit.
- Parameters:
callback (callable) – A callable or a coroutine function receiving one argument:record, a LoggedQuery containingquery,args,timeout,elapsed,exception,conn_addr, andconn_params.
Example:
>>>classQuerySaver: def __init__(self): self.queries = [] def __call__(self, record): self.queries.append(record.query)>>>withcon.query_logger(QuerySaver()):>>>awaitcon.execute("SELECT 1")>>>print(log.queries)['SELECT 1']
Added in version 0.29.0.
- asyncreload_schema_state()[source]
Indicate that the database schema information must be reloaded.
For performance reasons, asyncpg caches certain aspects of thedatabase schema, such as the layout of composite types. Consequently,when the database schema changes, and asyncpg is not able togracefully recover from an error caused by outdated schemaassumptions, an
OutdatedSchemaCacheErroris raised. To prevent the exception, this method may be used to informasyncpg that the database schema has changed.Example:
>>>importasyncpg>>>importasyncio>>>asyncdefchange_type(con):...result=awaitcon.fetch('SELECT id, info FROM tbl')...# Change composite's attribute type "int"=>"text"...awaitcon.execute('ALTER TYPE custom DROP ATTRIBUTE y')...awaitcon.execute('ALTER TYPE custom ADD ATTRIBUTE y text')...awaitcon.reload_schema_state()...forid_,infoinresult:...new=(info['x'],str(info['y']))...awaitcon.execute(...'UPDATE tbl SET info=$2 WHERE id=$1',id_,new)...>>>asyncdefrun():...# Initial schema:...# CREATE TYPE custom AS (x int, y int);...# CREATE TABLE tbl(id int, info custom);...con=awaitasyncpg.connect(user='postgres')...asyncwithcon.transaction():...# Prevent concurrent changes in the table...awaitcon.execute('LOCK TABLE tbl')...awaitchange_type(con)...>>>asyncio.run(run())
Added in version 0.14.0.
- asyncremove_listener(channel,callback)[source]
Remove a listening callback on the specified channel.
- remove_log_listener(callback)[source]
Remove a listening callback for log messages.
Added in version 0.12.0.
- remove_query_logger(callback)[source]
Remove a query logger callback.
- Parameters:
callback (callable) – The callable or coroutine function that was passed to
Connection.add_query_logger().
Added in version 0.29.0.
- remove_termination_listener(callback)[source]
Remove a listening callback for connection termination.
- Parameters:
callback (callable) – The callable or coroutine function that was passed to
Connection.add_termination_listener().
Added in version 0.21.0.
- asyncreset(*,timeout=None)[source]
Reset the connection state.
Calling this will reset the connection session state to a stateresembling that of a newly obtained connection. Namely, an opentransaction (if any) is rolled back, open cursors are closed,allLISTENregistrations are removed, all session configurationvariables are reset to their default values, and all advisory locksare released.
Note that the above describes the default query returned by
Connection.get_reset_query(). If one overloads the methodby subclassingConnection, then this method will do whateverthe overloaded method returns, except open transactions are alwaysterminated and any callbacks registered byConnection.add_listener()orConnection.add_log_listener()are removed.- Parameters:
timeout (float) – A timeout for resetting the connection. If not specified, defaultsto no timeout.
- asyncreset_type_codec(typename,*,schema='public')[source]
Resettypename codec to the default implementation.
- Parameters:
typename – Name of the data type the codec is for.
schema – Schema name of the data type the codec is for(defaults to
'public')
Added in version 0.12.0.
- asyncset_builtin_type_codec(typename,*,schema='public',codec_name,format=None)[source]
Set a builtin codec for the specified scalar data type.
This method has two uses. The first is to register a builtincodec for an extension type without a stable OID, such as ‘hstore’.The second use is to declare that an extension type or auser-defined type is wire-compatible with a certain builtindata type and should be exchanged as such.
- Parameters:
typename – Name of the data type the codec is for.
schema – Schema name of the data type the codec is for(defaults to
'public').codec_name – The name of the builtin codec to use for the type.This should be either the name of a known core type(such as
"int"), or the name of a supported extensiontype. Currently, the only supported extension type is"pg_contrib.hstore".format – Ifformat is
None(the default), all formats supportedby the target codec are declared to be supported fortypename.Ifformat is'text'or'binary', then only thespecified format is declared to be supported fortypename.
Changed in version 0.18.0:Thecodec_name argument can be the name of any knowncore data type. Added theformat keyword argument.
- asyncset_type_codec(typename,*,schema='public',encoder,decoder,format='text')[source]
Set an encoder/decoder pair for the specified data type.
- Parameters:
typename – Name of the data type the codec is for.
schema – Schema name of the data type the codec is for(defaults to
'public')format –
The type of the argument received by thedecoder callback,and the type of theencoder callback return value.
Ifformat is
'text'(the default), the exchange datum is astrinstance containing valid text representation of thedata type.Ifformat is
'binary', the exchange datum is abytesinstance containing valid _binary_ representation of thedata type.Ifformat is
'tuple', the exchange datum is a type-specifictupleof values. The table below lists supported datatypes and their format for this mode.Type
Tuple layout
interval(
months,days,microseconds)date(
dateordinalrelativetoJan12000,)-2^31for negative infinity timestamp2^31-1for positive infinity timestamp.timestamp(
microsecondsrelativetoJan12000,)-2^63for negative infinity timestamp2^63-1for positive infinity timestamp.timestampwithtimezone(
microsecondsrelativetoJan12000UTC,)-2^63for negative infinity timestamp2^63-1for positive infinity timestamp.time(
microseconds,)timewithtimezone(
microseconds,timezoneoffsetinseconds)any compositetype
Composite value elements
encoder – Callable accepting a Python object as a single argument andreturning a value encoded according toformat.
decoder – Callable accepting a single argument encoded according toformatand returning a decoded Python object.
Example:
>>>importasyncpg>>>importasyncio>>>importdatetime>>>fromdateutil.relativedeltaimportrelativedelta>>>asyncdefrun():...con=awaitasyncpg.connect(user='postgres')...defencoder(delta):...ndelta=delta.normalized()...return(ndelta.years*12+ndelta.months,...ndelta.days,...((ndelta.hours*3600+...ndelta.minutes*60+...ndelta.seconds)*1000000+...ndelta.microseconds))...defdecoder(tup):...returnrelativedelta(months=tup[0],days=tup[1],...microseconds=tup[2])...awaitcon.set_type_codec(...'interval',schema='pg_catalog',encoder=encoder,...decoder=decoder,format='tuple')...result=awaitcon.fetchval(..."SELECT '2 years 3 mons 1 day'::interval")...print(result)...print(datetime.datetime(2002,1,1)+result)...>>>asyncio.run(run())relativedelta(years=+2, months=+3, days=+1)2004-04-02 00:00:00
Added in version 0.12.0:Added the
formatkeyword argument and support for ‘tuple’format.Changed in version 0.12.0:The
binarykeyword argument is deprecated in favor offormat.Changed in version 0.13.0:The
binarykeyword argument was removed in favor offormat.Changed in version 0.29.0:Custom codecs for composite types are now supported with
format='tuple'.Note
It is recommended to use the
'binary'or'tuple'formatwhenever possible and if the underlying type supports it. Asyncpgcurrently does not support text I/O for composite and range types,and some other functionality, such asConnection.copy_to_table(), does not support types with textcodecs.
- transaction(*,isolation=None,readonly=False,deferrable=False)[source]
Create a
Transactionobject.Refer toPostgreSQL documentation on the meaning of transactionparameters.
- Parameters:
isolation – Transaction isolation mode, can be one of:‘serializable’,‘repeatable_read’,‘read_uncommitted’,‘read_committed’. If notspecified, the behavior is up to the server andsession, which is usually
read_committed.readonly – Specifies whether or not this transaction isread-only.
deferrable – Specifies whether or not this transaction isdeferrable.
Prepared Statements
Prepared statements are a PostgreSQL feature that can be used to optimize theperformance of queries that are executed more than once. When a queryisprepared by a call toConnection.prepare(), the server parses,analyzes and compiles the query allowing to reuse that work once there isa need to run the same query again.
>>>importasyncpg,asyncio>>>asyncdefrun():...conn=awaitasyncpg.connect()...stmt=awaitconn.prepare('''SELECT 2 ^ $1''')...print(awaitstmt.fetchval(10))...print(awaitstmt.fetchval(20))...>>>asyncio.run(run())1024.01048576.0
Note
asyncpg automatically maintains a small LRU cache for queries executedduring calls to thefetch(),fetchrow(),orfetchval() methods.
Warning
If you are using pgbouncer withpool_mode set totransaction orstatement, prepared statements will not work correctly. SeeWhy am I getting prepared statement errors? for more information.
- classPreparedStatement[source]
A representation of a prepared statement.
- executemany(args,*,timeout:float|None=None)[source]
Execute the statement for each sequence of arguments inargs.
- Parameters:
args – An iterable containing sequences of arguments.
timeout (float) – Optional timeout value in seconds.
- Return None:
This method discards the results of the operations.
Added in version 0.22.0.
- explain(*args,analyze=False)[source]
Return the execution plan of the statement.
- Parameters:
args – Query arguments.
analyze – If
True, the statement will be executed andthe run time statitics added to the return value.
- Returns:
An object representing the execution plan. This valueis actually a deserialized JSON output of the SQL
EXPLAINcommand.
- fetchmany(args,*,timeout=None)[source]
Execute the statement and return a list of
Recordobjects.- Parameters:
args – Query arguments.
timeout (float) – Optional timeout value in seconds.
- Returns:
A list of
Recordinstances.
Added in version 0.30.0.
- fetchval(*args,column=0,timeout=None)[source]
Execute the statement and return a value in the first row.
- Parameters:
- Returns:
The value of the specified column of the first record.
- get_attributes()[source]
Return a description of relation attributes (columns).
- Returns:
A tuple of
asyncpg.types.Attribute.
Example:
st=awaitself.con.prepare(''' SELECT typname, typnamespace FROM pg_type''')print(st.get_attributes())# Will print:# (Attribute(# name='typname',# type=Type(oid=19, name='name', kind='scalar',# schema='pg_catalog')),# Attribute(# name='typnamespace',# type=Type(oid=26, name='oid', kind='scalar',# schema='pg_catalog')))
- get_parameters()[source]
Return a description of statement parameters types.
- Returns:
A tuple of
asyncpg.types.Type.
Example:
stmt=awaitconnection.prepare('SELECT ($1::int, $2::text)')print(stmt.get_parameters())# Will print:# (Type(oid=23, name='int4', kind='scalar', schema='pg_catalog'),# Type(oid=25, name='text', kind='scalar', schema='pg_catalog'))
Transactions
The most common way to use transactions is through anasyncwith statement:
asyncwithconnection.transaction():awaitconnection.execute("INSERT INTO mytable VALUES(1, 2, 3)")
asyncpg supports nested transactions (a nested transaction context will createasavepoint.):
asyncwithconnection.transaction():awaitconnection.execute('CREATE TABLE mytab (a int)')try:# Create a nested transaction:asyncwithconnection.transaction():awaitconnection.execute('INSERT INTO mytab (a) VALUES (1), (2)')# This nested transaction will be automatically rolled back:raiseExceptionexcept:# Ignore exceptionpass# Because the nested transaction was rolled back, there# will be nothing in `mytab`.assertawaitconnection.fetch('SELECT a FROM mytab')==[]
Alternatively, transactions can be used without anasyncwith block:
tr=connection.transaction()awaittr.start()try:...except:awaittr.rollback()raiseelse:awaittr.commit()
See also theConnection.transaction()function.
Cursors
Cursors are useful when there is a need to iterate over the results ofa large query without fetching all rows at once. The cursor interfaceprovided by asyncpg supportsasynchronous iteration via theasyncforstatement, and also a way to read row chunks and skip forward over theresult set.
To iterate over a cursor using a connection object useConnection.cursor().To make the iteration efficient, the cursor will prefetch records toreduce the number of queries sent to the server:
asyncdefiterate(con:Connection):asyncwithcon.transaction():# Postgres requires non-scrollable cursors to be created# and used in a transaction.asyncforrecordincon.cursor('SELECT generate_series(0, 100)'):print(record)
Or, alternatively, you can iterate over the cursor manually (cursorwon’t be prefetching any rows):
asyncdefiterate(con:Connection):asyncwithcon.transaction():# Postgres requires non-scrollable cursors to be created# and used in a transaction.# Create a Cursor objectcur=awaitcon.cursor('SELECT generate_series(0, 100)')# Move the cursor 10 rows forwardawaitcur.forward(10)# Fetch one row and print itprint(awaitcur.fetchrow())# Fetch a list of 5 rows and print itprint(awaitcur.fetch(5))
It’s also possible to create cursors from prepared statements:
asyncdefiterate(con:Connection):# Create a prepared statement that will accept one argumentstmt=awaitcon.prepare('SELECT generate_series(0, $1)')asyncwithcon.transaction():# Postgres requires non-scrollable cursors to be created# and used in a transaction.# Execute the prepared statement passing `10` as the# argument -- that will generate a series or records# from 0..10. Iterate over all of them and print every# record.asyncforrecordinstmt.cursor(10):print(record)
Note
Cursors created by a call toConnection.cursor() orPreparedStatement.cursor()arenon-scrollable: they can only be read forwards. To create a scrollablecursor, use theDECLARE...SCROLLCURSOR SQL statement directly.
Warning
Cursors created by a call toConnection.cursor() orPreparedStatement.cursor()cannot be used outside of a transaction. Any such attempt will result inInterfaceError.
To create a cursor usable outside of a transaction, use theDECLARE...CURSORWITHHOLD SQL statement directly.
- classCursorFactory[source]
A cursor interface for the results of a query.
A cursor interface can be used to initiate efficient traversal of theresults of a large query.
- asyncforrowinc
Execute the statement and iterate over the results asynchronously.
- awaitc
Execute the statement and return an instance of
Cursorwhich can be used to navigate over andfetch subsets of the query results.
- classCursor[source]
An openportal into the results of a query.
- fetch(n,*,timeout=None)[source]
Return the nextn rows as a list of
Recordobjects.- Parameters:
timeout (float) – Optional timeout value in seconds.
- Returns:
A list of
Recordinstances.
Connection Pools
- create_pool(dsn=None,*,min_size=10,max_size=10,max_queries=50000,max_inactive_connection_lifetime=300.0,connect=None,setup=None,init=None,reset=None,loop=None,connection_class=<class'asyncpg.connection.Connection'>,record_class=<class'asyncpg.protocol.record.Record'>,**connect_kwargs)[source]
Create a connection pool.
Can be used either with an
asyncwithblock:asyncwithasyncpg.create_pool(user='postgres',command_timeout=60)aspool:awaitpool.fetch('SELECT 1')
Or to perform multiple operations on a single connection:
asyncwithasyncpg.create_pool(user='postgres',command_timeout=60)aspool:asyncwithpool.acquire()ascon:awaitcon.execute(''' CREATE TABLE names ( id serial PRIMARY KEY, name VARCHAR (255) NOT NULL) ''')awaitcon.fetch('SELECT 1')
Or directly with
await(not recommended):pool=awaitasyncpg.create_pool(user='postgres',command_timeout=60)con=awaitpool.acquire()try:awaitcon.fetch('SELECT 1')finally:awaitpool.release(con)
Warning
Prepared statements and cursors returned by
Connection.prepare()andConnection.cursor()become invalid once the connection is released. Likewise, allnotification and log listeners are removed, andasyncpgwillissue a warning if there are any listener callbacks registered on aconnection that is being released to the pool.- Parameters:
dsn (str) – Connection arguments specified using as a single string inthe following format:
postgres://user:pass@host:port/database?option=value.**connect_kwargs – Keyword arguments for the
connect()function.connection_class (Connection) – The class to use for connections. Must be a subclass of
Connection.record_class (type) – If specified, the class to use for records returned by queries onthe connections in this pool. Must be a subclass of
Record.min_size (int) – Number of connection the pool will be initialized with.
max_size (int) – Max number of connections in the pool.
max_queries (int) – Number of queries after a connection is closed and replacedwith a new connection.
max_inactive_connection_lifetime (float) – Number of seconds after which inactive connections in thepool will be closed. Pass
0to disable this mechanism.connect (coroutine) – A coroutine that is called instead of
connect()whenever the pool needs to make anew connection. Must return an instance of type specified byconnection_class orConnectionifconnection_class was not specified.setup (coroutine) – A coroutine to prepare a connection right before it is returnedfrom
Pool.acquire(). An example usecase would be to automatically set up notifications listeners forall connections of a pool.init (coroutine) – A coroutine to initialize a connection when it is created.An example use case would be to setup type codecs with
Connection.set_builtin_type_codec()orConnection.set_type_codec().reset (coroutine) –
A coroutine to reset a connection before it is returned to the pool by
Pool.release(). The function is supposedto reset any changes made to the database session so that the nextacquirer gets the connection in a well-defined state.The default implementation calls
Connection.reset(), which runs the following:SELECTpg_advisory_unlock_all();CLOSEALL;UNLISTEN*;RESETALL;
The exact reset query is determined by detected server capabilities,and a customreset implementation can obtain the default queryby calling
Connection.get_reset_query().loop – An asyncio event loop instance. If
None, the defaultevent loop will be used.
- Returns:
An instance of
Pool.
Changed in version 0.10.0:An
InterfaceErrorwill be raised on anyattempted operation on a released connection.Changed in version 0.13.0:An
InterfaceErrorwill be raised on anyattempted operation on a prepared statement or a cursor createdon a connection that has been released to the pool.Changed in version 0.13.0:An
InterfaceWarningwill be producedif there are any active listeners (added viaConnection.add_listener()orConnection.add_log_listener()) present on theconnection at the moment of its release to the pool.Changed in version 0.22.0:Added therecord_class parameter.
Changed in version 0.30.0:Added theconnect andreset parameters.
- classPool[source]
A connection pool.
Connection pool can be used to manage a set of connections to the database.Connections are first acquired from the pool, then used, and then releasedback to the pool. Once a connection is released, it’s reset to close allopen cursors and other resourcesexcept prepared statements.
Pools are created by calling
create_pool().- acquire(*,timeout=None)[source]
Acquire a database connection from the pool.
- Parameters:
timeout (float) – A timeout for acquiring a Connection.
- Returns:
An instance of
Connection.
Can be used in an
awaitexpression or with anasyncwithblock.asyncwithpool.acquire()ascon:awaitcon.execute(...)
Or:
con=awaitpool.acquire()try:awaitcon.execute(...)finally:awaitpool.release(con)
- asyncclose()[source]
Attempt to gracefully close all connections in the pool.
Wait until all pool connections are released, close them andshut down the pool. If any error (including cancellation) occursin
close()the pool will terminate by callingPool.terminate().It is advisable to use
asyncio.wait_for()to seta timeout.Changed in version 0.16.0:
close()now waits until all pool connections are releasedbefore closing them and the pool. Errors raised inclose()will cause immediate pool termination.
- asynccopy_from_query(query,*args,output,timeout=None,format=None,oids=None,delimiter=None,null=None,header=None,quote=None,escape=None,force_quote=None,encoding=None)[source]
Copy the results of a query to a file or file-like object.
Pool performs this operation using one of its connections. Other thanthat, it behaves identically to
Connection.copy_from_query().Added in version 0.24.0.
- asynccopy_from_table(table_name,*,output,columns=None,schema_name=None,timeout=None,format=None,oids=None,delimiter=None,null=None,header=None,quote=None,escape=None,force_quote=None,encoding=None)[source]
Copy table contents to a file or file-like object.
Pool performs this operation using one of its connections. Other thanthat, it behaves identically to
Connection.copy_from_table().Added in version 0.24.0.
- asynccopy_records_to_table(table_name,*,records,columns=None,schema_name=None,timeout=None,where=None)[source]
Copy a list of records to the specified table using binary COPY.
Pool performs this operation using one of its connections. Other thanthat, it behaves identically to
Connection.copy_records_to_table().Added in version 0.24.0.
- asynccopy_to_table(table_name,*,source,columns=None,schema_name=None,timeout=None,format=None,oids=None,freeze=None,delimiter=None,null=None,header=None,quote=None,escape=None,force_quote=None,force_not_null=None,force_null=None,encoding=None,where=None)[source]
Copy data to the specified table.
Pool performs this operation using one of its connections. Other thanthat, it behaves identically to
Connection.copy_to_table().Added in version 0.24.0.
- asyncexecute(query:str,*args,timeout:float|None=None)→str[source]
Execute an SQL command (or commands).
Pool performs this operation using one of its connections. Other thanthat, it behaves identically to
Connection.execute().Added in version 0.10.0.
- asyncexecutemany(command:str,args,*,timeout:float|None=None)[source]
Execute an SQLcommand for each sequence of arguments inargs.
Pool performs this operation using one of its connections. Other thanthat, it behaves identically to
Connection.executemany().Added in version 0.10.0.
- asyncexpire_connections()[source]
Expire all currently open connections.
Cause all currently open connections to get replaced on thenext
acquire()call.Added in version 0.16.0.
- asyncfetch(query,*args,timeout=None,record_class=None)→list[source]
Run a query and return the results as a list of
Record.Pool performs this operation using one of its connections. Other thanthat, it behaves identically to
Connection.fetch().Added in version 0.10.0.
- asyncfetchmany(query,args,*,timeout=None,record_class=None)[source]
Run a query for each sequence of arguments inargsand return the results as a list of
Record.Pool performs this operation using one of its connections. Other thanthat, it behaves identically to
Connection.fetchmany().Added in version 0.30.0.
- asyncfetchrow(query,*args,timeout=None,record_class=None)[source]
Run a query and return the first row.
Pool performs this operation using one of its connections. Other thanthat, it behaves identically to
Connection.fetchrow().Added in version 0.10.0.
- asyncfetchval(query,*args,column=0,timeout=None)[source]
Run a query and return a value in the first row.
Pool performs this operation using one of its connections. Other thanthat, it behaves identically to
Connection.fetchval().Added in version 0.10.0.
- get_idle_size()[source]
Return the current number of idle connections in this pool.
Added in version 0.25.0.
- get_max_size()[source]
Return the maximum allowed number of connections in this pool.
Added in version 0.25.0.
- get_min_size()[source]
Return the minimum number of connections in this pool.
Added in version 0.25.0.
- asyncrelease(connection,*,timeout=None)[source]
Release a database connection back to the pool.
- Parameters:
connection (Connection) – A
Connectionobject to release.timeout (float) – A timeout for releasing the connection. If not specified, defaultsto the timeout provided in the corresponding call to the
Pool.acquire()method.
Changed in version 0.14.0:Added thetimeout parameter.
- set_connect_args(dsn=None,**connect_kwargs)[source]
Set the new connection arguments for this pool.
The new connection arguments will be used for all subsequentnew connection attempts. Existing connections will remain untilthey expire. Use
Pool.expire_connections()to expedite the connectionexpiry.- Parameters:
Added in version 0.16.0.
Record Objects
Each row (or composite type value) returned by calls tofetch* methodsis represented by an instance of theRecord object.Record objects are a tuple-/dict-like hybrid, and allow addressing ofitems either by a numeric index or by a field name:
>>>importasyncpg>>>importasyncio>>>loop=asyncio.get_event_loop()>>>conn=loop.run_until_complete(asyncpg.connect())>>>r=loop.run_until_complete(conn.fetchrow('''... SELECT oid, rolname, rolsuper FROM pg_roles WHERE rolname = user'''))>>>r<Record oid=16388 rolname='elvis' rolsuper=True>>>>r['oid']16388>>>r[0]16388>>>dict(r){'oid': 16388, 'rolname': 'elvis', 'rolsuper': True}>>>tuple(r)(16388, 'elvis', True)
Note
Record objects currently cannot be created from Python code.
- classRecord
A read-only representation of PostgreSQL row.
- len(r)
Return the number of fields in recordr.
- r[field]
Return the field ofr with field name or indexfield.
- nameinr
Return
Trueif recordr has a field namedname.
- iter(r)
Return an iterator over thevalues of the recordr.
- get(name[,default])
Return the value forname if the record has a field namedname,else returndefault. Ifdefault is not given, return
None.Added in version 0.18.
- values()
Return an iterator over the record values.
- keys()
Return an iterator over the record field names.
- items()
Return an iterator over
(field,value)pairs.
- classConnectionSettings
A read-only collection of Connection settings.
- settings.setting_name
Return the value of the “setting_name” setting. Raises an
AttributeErrorif the setting is not defined.Example:
>>>connection.get_settings().client_encoding'UTF8'
Data Types
- classAttribute(name:ForwardRef('str'),type:ForwardRef('Type'))[source]
Database relation attribute.
- type:Type
Attribute data type
asyncpg.types.Type.
- classBitString(bitstring:builtins.bytes|None=None)[source]
Immutable representation of PostgreSQLbit andvarbit types.
- classmethodfrom_int(x:int,length:int,bitorder:_BitOrderType='big',*,signed:bool=False)→Self[source]
Represent the Python int x as a BitString.Acts similarly to int.to_bytes.
- Parameters:
x (int) – An integer to represent. Negative integers are represented in two’scomplement form, unless the argument signed is False, in which casenegative integers raise an OverflowError.
length (int) – The length of the resulting BitString. An OverflowError is raisedif the integer is not representable in this many bits.
bitorder – Determines the bit order used in the BitString representation. Bydefault, this function uses Postgres conventions for casting intsto bits. If bitorder is ‘big’, the most significant bit is at thestart of the string (this is the same as the default). If bitorderis ‘little’, the most significant bit is at the end of the string.
signed (bool) – Determines whether two’s complement is used in the BitStringrepresentation. If signed is False and a negative integer is given,an OverflowError is raised.
- Return BitString:
A BitString representing the input integer, in theform specified by the other input args.
Added in version 0.18.0.
- to_int(bitorder:Literal['big','little']='big',*,signed:bool=False)→int[source]
Interpret the BitString as a Python int.Acts similarly to int.from_bytes.
- Parameters:
bitorder – Determines the bit order used to interpret the BitString. Bydefault, this function uses Postgres conventions for casting bitsto ints. If bitorder is ‘big’, the most significant bit is at thestart of the string (this is the same as the default). If bitorderis ‘little’, the most significant bit is at the end of the string.
signed (bool) – Determines whether two’s complement is used to interpret theBitString. If signed is False, the returned value is alwaysnon-negative.
- Return int:
An integer representing the BitString. Information aboutthe BitString’s exact length is lost.
Added in version 0.18.0.
- classBox(high:Sequence[float],low:Sequence[float])[source]
Immutable representation of PostgreSQLbox type.
- classLineSegment(p1:Sequence[float],p2:Sequence[float])[source]
Immutable representation of PostgreSQLlseg type.
- classPath(*points:Sequence[float],is_closed:bool=False)[source]
Immutable representation of PostgreSQLpath type.
- classPoint(x:SupportsFloat|SupportsIndex|str|builtins.bytes|builtins.bytearray,y:SupportsFloat|SupportsIndex|str|builtins.bytes|builtins.bytearray)[source]
Immutable representation of PostgreSQLpoint type.
- classRange(lower:_RV|None=None,upper:_RV|None=None,*,lower_inc:bool=True,upper_inc:bool=False,empty:bool=False)[source]
Immutable representation of PostgreSQLrange type.