Psycopg

https://www.psycopg.org/docs
Admin
Psycopg is a popular, efficient, and thread-safe PostgreSQL database adapter for Python, fully...
Tokens:39,787
Snippets:532
Trust Score:9.6
Update:2 months ago
Show doc for...
Tokens:
Raw
### Install from source

Source: https://www.psycopg.org/docs/install.html

Build and install the package from the local source directory.

```bash
$ python setup.py build
$ python setup.py install
```

--------------------------------

### Install Psycopg from source

Source: https://www.psycopg.org/docs/install.html

Use the --no-binary option to force installation from source, which is recommended for production environments.

```bash
$ pip install --no-binary :all: psycopg2
```

--------------------------------

### Install via pip

Source: https://www.psycopg.org/docs/install.html

Standard installation method using the Python package manager.

```bash
$ pip install psycopg2
```

--------------------------------

### Install Psycopg binary package

Source: https://www.psycopg.org/docs/install.html

Use this command to install the pre-compiled binary version of Psycopg, which is suitable for development and beginners.

```bash
$ pip install psycopg2-binary
```

--------------------------------

### start_replication_expert

Source: https://www.psycopg.org/docs/extras.html

Starts replication on the connection using a provided START_REPLICATION command.

```APIDOC
## start_replication_expert(command, decode=False, status_interval=10)

### Description
Start replication on the connection using the provided START_REPLICATION command.

### Parameters
- **command** (string or Composable) - Required - The full replication command.
- **decode** (boolean) - Optional - A flag indicating that unicode conversion should be performed on messages received from the server.
- **status_interval** (float) - Optional - Time in seconds between feedback packets sent to the server.
```

--------------------------------

### Import and use Psycopg

Source: https://www.psycopg.org/docs/install.html

Basic example showing how to connect to a database, create a cursor, and execute a query.

```python
import psycopg2

# Connect to your postgres DB
conn = psycopg2.connect("dbname=test user=postgres")

# Open a cursor to perform database operations
cur = conn.cursor()

# Execute a query
cur.execute("SELECT * FROM my_data")

# Retrieve query results
records = cur.fetchall()
```

--------------------------------

### tpc_prepare()

Source: https://www.psycopg.org/docs/connection.html

Performs the first phase of a transaction started with tpc_begin().

```APIDOC
## tpc_prepare()

### Description
Performs the first phase of a transaction started with tpc_begin(). After calling this, no statements can be executed until tpc_commit() or tpc_rollback() is called.
```

--------------------------------

### ReplicationCursor.start_replication

Source: https://www.psycopg.org/docs/extras.html

Starts the replication stream on the connection.

```APIDOC
## ReplicationCursor.start_replication(slot_name=None, slot_type=None, start_lsn=0, timeline=0, options=None, decode=False, status_interval=10)

### Parameters
- **slot_name** (string) - Optional - Name of the replication slot to use
- **slot_type** (constant) - Optional - Type of replication (REPLICATION_LOGICAL or REPLICATION_PHYSICAL)
- **start_lsn** (int/string) - Optional - LSN position to start replicating from
- **timeline** (int) - Optional - WAL history timeline (physical only)
- **options** (dict) - Optional - Options for logical replication slot
- **decode** (bool) - Optional - Whether to perform unicode conversion
- **status_interval** (int) - Optional - Time between feedback packets
```

--------------------------------

### Run Psycopg2 test suite

Source: https://www.psycopg.org/docs/install.html

Execute the test suite from the source directory to verify the installation.

```bash
$ python -c "import tests; tests.unittest.main(defaultTest='tests.test_suite')" --verbose
```

--------------------------------

### Example output for notification listener

Source: https://www.psycopg.org/docs/advanced.html

Sample console output observed when receiving notifications via the listener script.

```text
Waiting for notifications on channel 'test'
Timeout
Timeout
Got NOTIFY: 6535 test hello
Timeout
...
```

--------------------------------

### Configure requirements.txt for source installation

Source: https://www.psycopg.org/docs/install.html

Specify the --no-binary option within a requirements file to ensure the package is compiled from source.

```text
psycopg2>=2.7,<2.8 --no-binary psycopg2
```

--------------------------------

### Batch execution with execute_batch

Source: https://www.psycopg.org/docs/extras.html

Examples of using execute_batch to perform multiple inserts efficiently.

```python
>>> nums = ((1,), (5,), (10,))
>>> execute_batch(cur, "INSERT INTO test (num) VALUES (%s)", nums)

>>> tuples = ((123, "foo"), (42, "bar"), (23, "baz"))
>>> execute_batch(cur, "INSERT INTO test (num, data) VALUES (%s, %s)", tuples)
```

--------------------------------

### Create custom adapter for infinite dates

Source: https://www.psycopg.org/docs/usage.html

Provides an example of a custom adapter to map Python's date.max to PostgreSQL's infinity value.

```python
class InfDateAdapter:
    def __init__(self, wrapped):
        self.wrapped = wrapped
    def getquoted(self):
        if self.wrapped == datetime.date.max:
            return b"'infinity'::date"
        elif self.wrapped == datetime.date.min:
            return b"'-infinity'::date"
        else:
            return psycopg2.extensions.DateFromPy(self.wrapped).getquoted()

psycopg2.extensions.register_adapter(datetime.date, InfDateAdapter)
```

--------------------------------

### Bulk operations with execute_values

Source: https://www.psycopg.org/docs/extras.html

Examples of using execute_values for bulk inserts and updates.

```python
>>> cur.execute(
... "create table test (id int primary key, v1 int, v2 int)")

>>> execute_values(cur,
... "INSERT INTO test (id, v1, v2) VALUES %s",
... [(1, 2, 3), (4, 5, 6), (7, 8, 9)])

>>> execute_values(cur,
... """UPDATE test SET v1 = data.v1 FROM (VALUES %s) AS data (id, v1)
... WHERE test.id = data.id""",
... [(1, 20), (4, 50)])

>>> cur.execute("select * from test order by id")
>>> cur.fetchall()
[(1, 20, 3), (4, 50, 6), (7, 8, 9)])
```

--------------------------------

### Configure Session Transaction Characteristics

Source: https://www.psycopg.org/docs/connection.html

Examples of setting session transaction characteristics using set_session. The first example sets a read-only transaction without affecting server defaults, while the second sets autocommit mode and modifies server defaults.

```python
conn.set_session(readonly=True)
```

```python
conn.set_session(readonly=True, autocommit=True)
```

--------------------------------

### Logical Replication Quick-Start

Source: https://www.psycopg.org/docs/advanced.html

Demonstrates setting up a logical replication connection and consuming a stream using a custom consumer class. Requires PostgreSQL 9.4+ with specific configuration settings.

```python
from __future__ import print_function
import sys
import psycopg2
import psycopg2.extras

conn = psycopg2.connect('dbname=psycopg2_test user=postgres',
   connection_factory=psycopg2.extras.LogicalReplicationConnection)
cur = conn.cursor()
try:
    # test_decoding produces textual output
    cur.start_replication(slot_name='pytest', decode=True)
except psycopg2.ProgrammingError:
    cur.create_replication_slot('pytest', output_plugin='test_decoding')
    cur.start_replication(slot_name='pytest', decode=True)

class DemoConsumer(object):
    def __call__(self, msg):
        print(msg.payload)
        msg.cursor.send_feedback(flush_lsn=msg.data_start)

democonsumer = DemoConsumer()

print("Starting streaming, press Control-C to end...", file=sys.stderr)
try:
   cur.consume_stream(democonsumer)
except KeyboardInterrupt:
   cur.close()
   conn.close()
   print("The slot 'pytest' still exists. Drop it with "
      "SELECT pg_drop_replication_slot('pytest'); if no longer needed.",
      file=sys.stderr)
   print("WARNING: Transaction logs will accumulate in pg_xlog "
      "until the slot is dropped.", file=sys.stderr)
```

--------------------------------

### Format SQL templates

Source: https://www.psycopg.org/docs/sql.html

Demonstrates using positional and named arguments to populate SQL templates.

```python
>>> query = sql.SQL("select {0} from {1}").format(
...    sql.SQL(', ').join([sql.Identifier('foo'), sql.Identifier('bar')]),
...    sql.Identifier('table'))
>>> print(query.as_string(conn))
select "foo", "bar" from "table"
```

```python
>>> print(sql.SQL("select * from {} where {} = %s")
...     .format(sql.Identifier('people'), sql.Identifier('id'))
...     .as_string(conn))
select * from "people" where "id" = %s

>>> print(sql.SQL("select * from {tbl} where {pkey} = %s")
...     .format(tbl=sql.Identifier('people'), pkey=sql.Identifier('id'))
...     .as_string(conn))
select * from "people" where "id" = %s
```

--------------------------------

### Connect to a database using psycopg2

Source: https://www.psycopg.org/docs/module.html

Establishes a new database session using either a libpq connection string or keyword arguments.

```python
conn = psycopg2.connect("dbname=test user=postgres password=secret")
```

```python
conn = psycopg2.connect(dbname="test", user="postgres", password="secret")
```

--------------------------------

### Use Binary adapter for binary data

Source: https://www.psycopg.org/docs/extensions.html

Demonstrates using the Binary adapter to escape non-printable characters and format binary data for SQL.

```python
>>> Binary("\x00\x08\x0F").getquoted()
"'\\000\\010\\017'"
```

--------------------------------

### connection.readonly

Source: https://www.psycopg.org/docs/connection.html

Property to get or set the read-only status for the current session.

```APIDOC
## connection.readonly

### Description
Property to return or set the read-only status. Values: True (read-only), False (writable), or None (server default).
```

--------------------------------

### connection.isolation_level

Source: https://www.psycopg.org/docs/connection.html

Property to get or set the transaction isolation level for the current session.

```APIDOC
## connection.isolation_level

### Description
Property to return or set the transaction isolation level. Accepts constants from `psycopg2.extensions` or literal strings like 'READ COMMITTED' or 'SERIALIZABLE'.
```

--------------------------------

### Perform basic database operations with Psycopg2

Source: https://www.psycopg.org/docs/usage.html

Demonstrates the standard workflow of connecting to a database, executing commands, handling data with placeholders, and closing connections.

```python
>>> import psycopg2

# 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()
```

--------------------------------

### Initialize Asynchronous Connection

Source: https://www.psycopg.org/docs/advanced.html

Establish a connection in asynchronous mode and wait for it to be ready.

```python
>>> aconn = psycopg2.connect(database='test', async=1)
>>> wait(aconn)
>>> acurs = aconn.cursor()
```

--------------------------------

### connection.deferrable

Source: https://www.psycopg.org/docs/connection.html

Get or set the deferrable status for the current session. Values can be True, False, or None.

```APIDOC
## connection.deferrable

### Description
Return or set the deferrable status for the current session. Available values are True (new transactions will be in deferrable mode), False (new transactions will be in non deferrable mode), or None (use the default configured for the server).
```

--------------------------------

### Adapt Python constants to SQL literals

Source: https://www.psycopg.org/docs/usage.html

Demonstrates the conversion of Python None, True, and False into SQL NULL, true, and false literals using mogrify.

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

--------------------------------

### Interrupting a long-running query

Source: https://www.psycopg.org/docs/faq.html

Example of configuring the wait callback to allow Ctrl-C interrupts during long-running queries in an interactive shell.

```python
>>> psycopg2.extensions.set_wait_callback(psycopg2.extras.wait_select)
>>> cnn = psycopg2.connect('')
>>> cur = cnn.cursor()
>>> cur.execute("select pg_sleep(10)")
^C
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  QueryCanceledError: canceling statement due to user request

>>> cnn.rollback()
>>> # You can use the connection and cursor again from here
```

--------------------------------

### Open a Logical Replication Connection

Source: https://www.psycopg.org/docs/extras.html

Use the LogicalReplicationConnection factory to establish a connection specifically for logical replication.

```python
from psycopg2.extras import LogicalReplicationConnection
log_conn = psycopg2.connect(dsn, connection_factory=LogicalReplicationConnection)
log_cur = log_conn.cursor()
```

--------------------------------

### Implement a Logical Replication Consumer

Source: https://www.psycopg.org/docs/extras.html

A sketch implementation of a callable for logical replication, demonstrating message processing and feedback reporting.

```python
class LogicalStreamConsumer(object):

    # ...

    def __call__(self, msg):
        self.process_message(msg.payload)
        msg.cursor.send_feedback(flush_lsn=msg.data_start)

consumer = LogicalStreamConsumer()
cur.consume_stream(consumer)
```

--------------------------------

### Open a Physical Replication Connection

Source: https://www.psycopg.org/docs/extras.html

Use the PhysicalReplicationConnection factory to establish a connection specifically for physical replication.

```python
from psycopg2.extras import PhysicalReplicationConnection
phys_conn = psycopg2.connect(dsn, connection_factory=PhysicalReplicationConnection)
phys_cur = phys_conn.cursor()
```

--------------------------------

### Use connection and cursor as context managers

Source: https://www.psycopg.org/docs/usage.html

Shows the standard pattern for using connections and cursors within 'with' blocks to manage transactions and resource cleanup.

```python
with psycopg2.connect(DSN) as conn:
    with conn.cursor() as curs:
        curs.execute(SQL)
```

--------------------------------

### Use AsIs adapter for SQL representation

Source: https://www.psycopg.org/docs/extensions.html

Demonstrates using the AsIs adapter to return the string representation of an object as valid SQL.

```python
>>> AsIs(42).getquoted()
'42'
```

--------------------------------

### Mapping a custom Point class to PostgreSQL point type

Source: https://www.psycopg.org/docs/advanced.html

Demonstrates defining a custom class and an adapter function, then registering it to allow the object to be used directly in execute() calls.

```python
>>> from psycopg2.extensions import adapt, register_adapter, AsIs

>>> class Point(object):
...    def __init__(self, x, y):
...        self.x = x
...        self.y = y

>>> def adapt_point(point):
...     x = adapt(point.x).getquoted()
...     y = adapt(point.y).getquoted()
...     return AsIs("'(%s, %s)'" % (x, y))

>>> register_adapter(Point, adapt_point)

>>> cur.execute("INSERT INTO atable (apoint) VALUES (%s)",
...             (Point(1.23, 4.56),))
```

```sql
INSERT INTO atable (apoint) VALUES ('(1.23, 4.56)');
```

--------------------------------

### tpc_begin(xid)

Source: https://www.psycopg.org/docs/connection.html

Begins a TPC transaction with the given transaction ID.

```APIDOC
## tpc_begin(xid)

### Description
Begins a TPC transaction with the given transaction ID xid. This method should be called outside of a transaction.
```

--------------------------------

### Retrieve and encode string data

Source: https://www.psycopg.org/docs/usage.html

Shows how to check connection encoding and retrieve string data, including changing the client encoding.

```python
>>> 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'
```

--------------------------------

### Mogrify a query string

Source: https://www.psycopg.org/docs/cursor.html

Returns the query string after argument binding, representing exactly what would be sent to the database.

```python
>>> cur.mogrify("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar'))
"INSERT INTO test (num, data) VALUES (42, E'bar')"
```

--------------------------------

### Configure pg_config path

Source: https://www.psycopg.org/docs/install.html

Add the directory containing the pg_config program to the system PATH to facilitate compilation.

```bash
$ export PATH=/usr/lib/postgresql/X.Y/bin/:$PATH
```

--------------------------------

### Create a connection string with make_dsn

Source: https://www.psycopg.org/docs/extensions.html

Constructs a valid connection string by merging a base DSN string with additional keyword arguments.

```python
>>> from psycopg2.extensions import make_dsn
>>> make_dsn('dbname=foo host=example.com', password="s3cr3t")
'host=example.com password=s3cr3t dbname=foo'
```

--------------------------------

### Compose a query with identifiers and placeholders

Source: https://www.psycopg.org/docs/sql.html

Use sql.SQL with format() to merge identifiers into a query template while keeping %s-style placeholders for execute().

```python
query = sql.SQL("select {field} from {table} where {pkey} = %s").format(
    field=sql.Identifier('my_name'),
    table=sql.Identifier('some_table'),
    pkey=sql.Identifier('id'))
```

--------------------------------

### cursor.copy_from(file, table, sep='\t', null='\\N', size=8192, columns=None)

Source: https://www.psycopg.org/docs/cursor.html

Efficiently copies data from a file-like object into a database table.

```APIDOC
## copy_from(file, table, sep='\t', null='\\N', size=8192, columns=None)

### Description
Read data from the file-like object file appending them to the table named table.

### Parameters
- **file** (file-like object) - Required - Source data.
- **table** (str) - Required - Target table name.
- **sep** (str) - Optional - Column separator.
- **null** (str) - Optional - Null representation.
- **size** (int) - Optional - Buffer size.
- **columns** (list) - Optional - List of columns.
```

--------------------------------

### Using correct placeholder types

Source: https://www.psycopg.org/docs/usage.html

Always use %s as the placeholder, regardless of the underlying data type.

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

--------------------------------

### Correct Query Parameter Usage

Source: https://www.psycopg.org/docs/usage.html

Demonstrates the safe way to pass variables to SQL queries using the second argument of the execute() method without manual string formatting.

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

--------------------------------

### copy_from(file, table, sep='\t', null='\\N', size=8192, columns=None)

Source: https://www.psycopg.org/docs/cursor.html

Reads data from a file-like object and imports it into a database table.

```APIDOC
## copy_from

### Description
Reads data from a file-like object and imports it into a database table.

### Parameters
- **file** (file-like object) - Required - File to read data from. Must have read() and readline() methods.
- **table** (string) - Required - Name of the table to copy data into.
- **sep** (string) - Optional - Columns separator. Defaults to a tab.
- **null** (string) - Optional - Textual representation of NULL. Defaults to '\\N'.
- **size** (int) - Optional - Size of the buffer used to read from the file.
- **columns** (iterable) - Optional - Iterable with names of the columns to import.
```

--------------------------------

### mogrify(operation[, parameters])

Source: https://www.psycopg.org/docs/cursor.html

Returns a query string after argument binding, representing exactly what would be sent to the database.

```APIDOC
## mogrify(operation[, parameters])

### Description
Return a query string after arguments binding. The string returned is exactly the one that would be sent to the database running the execute() method.

### Parameters
- **operation** (string) - Required - The SQL query string.
- **parameters** (sequence or dict) - Optional - The parameters to bind to the query.
```

--------------------------------

### Manage multiple transactions with one connection

Source: https://www.psycopg.org/docs/usage.html

Illustrates how a single connection can be reused across multiple 'with' blocks, each creating a separate transaction.

```python
conn = psycopg2.connect(DSN)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL1)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL2)

conn.close()
```

--------------------------------

### Passing named parameters to SQL queries

Source: https://www.psycopg.org/docs/usage.html

Use %(_name_)s placeholders and a dictionary to map values, allowing flexible ordering and repetition of parameters.

```python
>>> 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)})
```

--------------------------------

### Inspect linked libpq library

Source: https://www.psycopg.org/docs/faq.html

Use this command to identify the specific libpq dynamic library being loaded by the Psycopg2 extension at runtime.

```bash
$ ldd /path/to/packages/psycopg2/_psycopg.so | grep libpq
```

--------------------------------

### Asynchronous replication message consumption

Source: https://www.psycopg.org/docs/extras.html

Demonstrates using select() with a replication cursor to manage message consumption and feedback intervals.

```python
from select import select
from datetime import datetime

def consume(msg):
    # ...
    msg.cursor.send_feedback(flush_lsn=msg.data_start)

status_interval = 10.0
while True:
    msg = cur.read_message()
    if msg:
        consume(msg)
    else:
        now = datetime.now()
        timeout = status_interval - (now - cur.feedback_timestamp).total_seconds()
        try:
            sel = select([cur], [], [], max(0, timeout))
        except InterruptedError:
            pass  # recalculate timeout and continue
```

--------------------------------

### psycopg2.connect

Source: https://www.psycopg.org/docs/module.html

Creates a new database session and returns a connection object. Parameters can be provided via a DSN string or keyword arguments.

```APIDOC
## psycopg2.connect(dsn=None, connection_factory=None, cursor_factory=None, async=False, **kwargs)

### Description
Creates a new database session and returns a new connection object. You can specify connection parameters using a libpq connection string or keyword arguments.

### Parameters
- **dsn** (string) - Optional - A libpq connection string.
- **connection_factory** (callable) - Optional - A callable object to create the connection.
- **cursor_factory** (callable) - Optional - A factory for creating cursors.
- **async** (boolean) - Optional - If True, creates an asynchronous connection.
- **kwargs** (dict) - Optional - Additional connection parameters (e.g., dbname, user, password, host, port).
```

--------------------------------

### Import data using copy_from

Source: https://www.psycopg.org/docs/cursor.html

Reads data from a file-like object into a database table.

```python
>>> f = StringIO("42\tfoo\n74\tbar\n")
>>> cur.copy_from(f, 'test', columns=('num', 'data'))
>>> cur.execute("select * from test where id > 5;")
>>> cur.fetchall()
[(6, 42, 'foo'), (7, 74, 'bar')]
```

--------------------------------

### Insert binary data using Binary wrapper

Source: https://www.psycopg.org/docs/usage.html

Uses the psycopg2.Binary wrapper to insert binary data from a file into a bytea field.

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

--------------------------------

### reset()

Source: https://www.psycopg.org/docs/connection.html

Reset the connection to the default state.

```APIDOC
## reset()

### Description
Rolls back any pending transaction and executes the PostgreSQL RESET and SET SESSION AUTHORIZATION commands to revert the session to default values.
```

--------------------------------

### Suboptimal SQL string formatting

Source: https://www.psycopg.org/docs/sql.html

Shows a manual string formatting approach that is functional but lacks proper identifier quoting and security.

```python
# This works, but it is not optimal
table_name = 'my_table'
cur.execute(
    "insert into %s values (%%s, %%s)" % table_name,
    [10, 20])
```

--------------------------------

### psycopg2.extensions.adapt(obj)

Source: https://www.psycopg.org/docs/extensions.html

Returns the SQL representation of an object as an ISQLQuote. Raises a ProgrammingError if the adaptation method is unknown.

```APIDOC
## psycopg2.extensions.adapt(obj)

### Description
Returns the SQL representation of the provided object as an ISQLQuote. This is the entry point for the adaptation mechanism.

### Parameters
- **obj** (object) - Required - The Python object to adapt to SQL syntax.

### Returns
- **ISQLQuote** - An object conforming to the ISQLQuote protocol.
```

--------------------------------

### Implement a wait callback for coroutine support

Source: https://www.psycopg.org/docs/advanced.html

A sample implementation of a wait callback using select() to handle connection polling states.

```python
def wait_select(conn):
    while True:
        state = conn.poll()
        if state == extensions.POLL_OK:
            break
        elif state == extensions.POLL_READ:
            select.select([conn.fileno()], [], [])
        elif state == extensions.POLL_WRITE:
            select.select([], [conn.fileno()], [])
        else:
            raise OperationalError("bad state from poll: %s" % state)
```

--------------------------------

### psycopg2.extensions.make_dsn(dsn=None, **kwargs)

Source: https://www.psycopg.org/docs/extensions.html

Creates a valid PostgreSQL connection string from provided arguments.

```APIDOC
## psycopg2.extensions.make_dsn(dsn=None, **kwargs)

### Description
Creates a valid connection string from arguments. If `dsn` is specified, it merges the arguments from `kwargs` into it, with `kwargs` taking precedence.

### Parameters
- **dsn** (string) - Optional - A base connection string.
- **kwargs** (dict) - Optional - Keyword arguments to include in the connection string.
```

--------------------------------

### connection.lobject(oid, mode, new_oid, new_file, lobject_factory)

Source: https://www.psycopg.org/docs/connection.html

Returns a new database large object as a lobject instance.

```APIDOC
## connection.lobject(oid, mode, new_oid, new_file, lobject_factory)

### Description
Return a new database large object as a lobject instance.

### Parameters
- **oid** (integer) - Optional - The OID of the object to read or write. 0 to create a new large object.
- **mode** (string) - Optional - Access mode to the object.
- **new_oid** (integer) - Optional - Create a new object using the specified OID.
- **new_file** (string) - Optional - The name of a file to be imported in the database.
- **lobject_factory** (class) - Optional - Subclass of lobject to be instantiated.
```

--------------------------------

### Adapt Python tuples for SQL IN operator

Source: https://www.psycopg.org/docs/usage.html

Demonstrates how Python tuples are converted into SQL-compatible syntax for the IN operator.

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

--------------------------------

### Insert Unicode data into PostgreSQL

Source: https://www.psycopg.org/docs/usage.html

Demonstrates inserting a Unicode string into a database table using a cursor.

```python
>>> print(u, type(u))
àèìòù€ <type 'unicode'>

>>> cur.execute("INSERT INTO test (num, data) VALUES (%s,%s);", (74, u))
```

--------------------------------

### sql.SQL.join

Source: https://www.psycopg.org/docs/sql.html

Joins a sequence of Composable objects using the current SQL object as a separator.

```APIDOC
## sql.SQL.join

### Description
Joins a sequence of Composable objects into a single Composed object, using the SQL instance as the separator.

### Parameters
- **seq** (Iterable[Composable]) - Required - A sequence of Composable objects to join.
```

--------------------------------

### Specify alternate pg_config location

Source: https://www.psycopg.org/docs/install.html

Use this command to point the build process to a specific pg_config executable when it is not in the system PATH.

```bash
$ python setup.py build_ext --pg-config /path/to/pg_config build
```

--------------------------------

### fetchone()

Source: https://www.psycopg.org/docs/cursor.html

Fetches the next row of a query result set.

```APIDOC
## fetchone()

### Description
Fetch the next row of a query result set, returning a single tuple, or None when no more data is available.
```

--------------------------------

### Accessing internal PGconn via ctypes

Source: https://www.psycopg.org/docs/connection.html

Demonstrates how to retrieve the internal PGconn pointer as an integer and use it with ctypes to call libpq functions.

```python
>>> import ctypes
>>> import ctypes.util
>>> libpq = ctypes.pydll.LoadLibrary(ctypes.util.find_library('pq'))
>>> libpq.PQserverVersion.argtypes = [ctypes.c_void_p]
>>> libpq.PQserverVersion.restype = ctypes.c_int
>>> libpq.PQserverVersion(conn.pgconn_ptr)
90611
```

--------------------------------

### Retrieve DSN parameters from a connection

Source: https://www.psycopg.org/docs/connection.html

Returns a dictionary of effective DSN parameters. Requires libpq version 9.3 or higher.

```python
>>> conn.get_dsn_parameters()
{'dbname': 'test', 'user': 'postgres', 'port': '5432', 'sslmode': 'prefer'}
```

--------------------------------

### Handling Backslashes in LIKE Queries

Source: https://www.psycopg.org/docs/usage.html

Illustrates how backslashes in paths can cause issues with LIKE queries and provides the solution using the ESCAPE clause.

```python
>>> 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()
[]
```

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

--------------------------------

### sql.SQL.format

Source: https://www.psycopg.org/docs/sql.html

Merges Composable objects into an SQL template using positional or keyword arguments.

```APIDOC
## sql.SQL.format

### Description
Merges Composable objects into a template string. Supports auto-numbered ({}), numbered ({0}), and named ({name}) placeholders.

### Parameters
- **args** (Composable) - Optional - Positional parameters to replace numbered placeholders.
- **kwargs** (Composable) - Optional - Keyword parameters to replace named placeholders.

### Returns
- **Composed** - The resulting composed SQL object.
```

--------------------------------

### copy_to(file, table, sep='\t', null='\\N', columns=None)

Source: https://www.psycopg.org/docs/cursor.html

Writes the content of a database table to a file-like object.

```APIDOC
## copy_to

### Description
Writes the content of the table named table to the file-like object file.

### Parameters
- **file** (file-like object) - Required - File to write data into. Must have a write() method.
- **table** (string) - Required - Name of the table to copy data from.
- **sep** (string) - Optional - Columns separator. Defaults to a tab.
- **null** (string) - Optional - Textual representation of NULL. Defaults to '\\N'.
- **columns** (iterable) - Optional - Iterable with names of the columns to export.
```

--------------------------------

### Avoiding manual string formatting

Source: https://www.psycopg.org/docs/usage.html

Never use Python's % operator or string concatenation to build queries; always pass parameters as the second argument to execute().

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

--------------------------------

### Execute custom COPY statements with copy_expert

Source: https://www.psycopg.org/docs/cursor.html

Submits a user-composed COPY statement for advanced configuration.

```python
>>> cur.copy_expert("COPY test TO STDOUT WITH CSV HEADER", sys.stdout)
id,num,data
1,100,abc'def
2,,dada
...
```

--------------------------------

### Adapt Python lists to PostgreSQL arrays

Source: https://www.psycopg.org/docs/usage.html

Demonstrates converting Python lists to PostgreSQL arrays and using them with the ANY operator.

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

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

--------------------------------

### psycopg2.extensions.libpq_version()

Source: https://www.psycopg.org/docs/extensions.html

Returns the version number of the loaded libpq dynamic library as an integer.

```APIDOC
## psycopg2.extensions.libpq_version()

### Description
Returns the version number of the `libpq` dynamic library loaded as an integer. Raises `NotSupportedError` if the `psycopg2` module was compiled with a `libpq` version lesser than 9.1.
```

--------------------------------

### Join identifiers in a query

Source: https://www.psycopg.org/docs/sql.html

Use sql.SQL.join() to handle variable sequences of arguments like field lists.

```python
query = sql.SQL("select {fields} from {table}").format(
    fields=sql.SQL(',').join([
        sql.Identifier('field1'),
        sql.Identifier('field2'),
        sql.Identifier('field3'),
    ]),
    table=sql.Identifier('some_table'))
```

--------------------------------

### psycopg2.extras.register_hstore

Source: https://www.psycopg.org/docs/extras.html

Registers adapter and typecaster for dict-hstore conversions.

```APIDOC
## register_hstore(conn_or_curs, globally=False, unicode=False, oid=None, array_oid=None)

### Description
Register adapter and typecaster for dict-hstore conversions. The hstore contrib module must be installed in the database.

### Parameters
- **conn_or_curs** (connection or cursor) - Required - The connection or cursor to register the typecaster on.
- **globally** (bool) - Optional - Register the adapter globally.
- **unicode** (bool) - Optional - If True, keys and values returned will be unicode (not available on Python 3).
- **oid** (int) - Optional - The OID of the hstore type if known.
- **array_oid** (int) - Optional - The OID of the hstore array type if known.
```

--------------------------------

### Registering byte-based type handlers

Source: https://www.psycopg.org/docs/faq.html

Use these adapters to force the return of bytes instead of decoded strings, useful for mixed encoding databases.

```python
psycopg2.extensions.register_type(psycopg2.extensions.BYTES)
psycopg2.extensions.register_type(psycopg2.extensions.BYTESARRAY)
```

--------------------------------

### psycopg2.extras.LogicalReplicationConnection

Source: https://www.psycopg.org/docs/extras.html

A connection factory class used to open a connection for logical replication.

```APIDOC
## psycopg2.extras.LogicalReplicationConnection

### Description
Opens a special connection for logical replication.

### Usage
```python
from psycopg2.extras import LogicalReplicationConnection
log_conn = psycopg2.connect(dsn, connection_factory=LogicalReplicationConnection)
log_cur = log_conn.cursor()
```
```

--------------------------------

### Safe SQL composition with psycopg2.sql

Source: https://www.psycopg.org/docs/sql.html

Recommended approach using the sql module to clearly separate identifiers from parameters for secure and dynamic SQL generation.

```python
from psycopg2 import sql

cur.execute(
    sql.SQL("insert into {} values (%s, %s)")
        .format(sql.Identifier('my_table')),
    [10, 20])
```

--------------------------------

### Adapt Python numeric types to SQL

Source: https://www.psycopg.org/docs/usage.html

Shows how Python int, long, float, and Decimal objects are converted into PostgreSQL numerical representations.

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

--------------------------------

### Handle PostgreSQL time type limitations

Source: https://www.psycopg.org/docs/usage.html

Illustrates the discrepancy between PostgreSQL's 24:00:00 time support and Python's 23:59:59 limit.

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

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

--------------------------------

### Create a Replication Slot

Source: https://www.psycopg.org/docs/extras.html

Create streaming replication slots using the ReplicationCursor. The slot type can be inferred from the connection or specified explicitly.

```python
log_cur.create_replication_slot("logical1", "test_decoding")
phys_cur.create_replication_slot("physical1")

# either logical or physical replication connection
cur.create_replication_slot("slot1", slot_type=REPLICATION_LOGICAL)
```

--------------------------------

### Implement Asynchronous Polling Loop

Source: https://www.psycopg.org/docs/advanced.html

A helper function to wait for a connection state to become ready using select() and poll().

```python
def wait(conn):
    while True:
        state = conn.poll()
        if state == psycopg2.extensions.POLL_OK:
            break
        elif state == psycopg2.extensions.POLL_WRITE:
            select.select([], [conn.fileno()], [])
        elif state == psycopg2.extensions.POLL_READ:
            select.select([conn.fileno()], [], [])
        else:
            raise psycopg2.OperationalError("poll() returned %s" % state)
```

--------------------------------

### Adapt Python datetime objects to PostgreSQL

Source: https://www.psycopg.org/docs/usage.html

Demonstrates the conversion of Python datetime objects into PostgreSQL timestamp, date, and interval types.

```python
>>> dt = datetime.datetime.now()
>>> dt
datetime.datetime(2010, 2, 8, 1, 40, 27, 425337)
```

```python
>>> 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';"
```

```python
>>> cur.mogrify("SELECT %s;", (dt - datetime.datetime(2010,1,1),))
"SELECT '38 days 6027.425337 seconds';"
```

--------------------------------

### Accessing PostgreSQL error code constants

Source: https://www.psycopg.org/docs/errorcodes.html

Demonstrates accessing symbolic constants for error classes and specific error conditions.

```python
>>> errorcodes.CLASS_SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION
'42'
>>> errorcodes.UNDEFINED_TABLE
'42P01'
```

--------------------------------

### cursor.execute(query, vars=None)

Source: https://www.psycopg.org/docs/cursor.html

Executes a database operation (query or command). Parameters can be provided as a sequence or mapping to be bound to placeholders in the query.

```APIDOC
## execute(query, vars=None)

### Description
Executes a database operation (query or command). Parameters may be provided as a sequence or mapping and will be bound to variables in the operation using positional (%s) or named (%(_name_)s) placeholders.

### Parameters
- **query** (string) - Required - The SQL query or command to execute.
- **vars** (sequence or mapping) - Optional - Parameters to bind to the query placeholders.

### Returns
- **None** - The method returns None. Results can be retrieved using fetch*() methods.
```

--------------------------------

### Passing numeric parameters to queries

Source: https://www.psycopg.org/docs/faq.html

Always use %s placeholders for query parameters, even when passing numeric types.

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

--------------------------------

### Incorrect SQL execution attempt

Source: https://www.psycopg.org/docs/sql.html

Demonstrates an invalid approach where a table name is passed as a query argument, which will fail.

```python
# This will not work
table_name = 'my_table'
cur.execute("insert into %s values (%s, %s)", [table_name, 10, 20])
```

--------------------------------

### xid(format_id, gtrid, bqual)

Source: https://www.psycopg.org/docs/connection.html

Returns a Xid instance to be passed to the tpc_*() methods of the connection.

```APIDOC
## xid(format_id, gtrid, bqual)

### Description
Returns a Xid instance to be passed to the tpc_*() methods of this connection. The values passed will be available as members format_id, gtrid, and bqual on the returned object.
```

--------------------------------

### copy_expert(sql, file, size=8192)

Source: https://www.psycopg.org/docs/cursor.html

Submits a user-composed COPY statement to handle advanced PostgreSQL COPY parameters.

```APIDOC
## copy_expert

### Description
Submit a user-composed COPY statement. Useful to handle all parameters that PostgreSQL makes available.

### Parameters
- **sql** (string) - Required - The COPY statement to execute.
- **file** (file-like object) - Required - A file-like object to read or write.
- **size** (int) - Optional - Size of the read buffer to be used in COPY FROM.
```

--------------------------------

### psycopg2.extras.execute_batch

Source: https://www.psycopg.org/docs/extras.html

Executes groups of statements in fewer server roundtrips by joining statements into multi-statement commands.

```APIDOC
## execute_batch(cur, sql, argslist, page_size=100)

### Description
Executes a SQL statement multiple times against a list of parameters. It reduces server roundtrips by grouping statements into batches.

### Parameters
- **cur** (cursor) - Required - The cursor to use for execution.
- **sql** (str) - Required - The SQL statement to execute.
- **argslist** (list) - Required - A sequence of sequences or mappings containing parameters.
- **page_size** (int) - Optional - Maximum number of statements per batch (default: 100).
```

--------------------------------

### psycopg2.extras.PhysicalReplicationConnection

Source: https://www.psycopg.org/docs/extras.html

A connection factory class used to open a connection for physical replication.

```APIDOC
## psycopg2.extras.PhysicalReplicationConnection

### Description
Opens a special connection for physical replication.

### Usage
```python
from psycopg2.extras import PhysicalReplicationConnection
phys_conn = psycopg2.connect(dsn, connection_factory=PhysicalReplicationConnection)
phys_cur = phys_conn.cursor()
```
```

--------------------------------

### Create a PL/pgSQL function returning a cursor

Source: https://www.psycopg.org/docs/usage.html

Define a database function that opens a cursor for a query and returns the cursor name.

```sql
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
$$ LANGUAGE plpgsql;
```

--------------------------------

### Passing positional parameters to SQL queries

Source: https://www.psycopg.org/docs/usage.html

Use %s placeholders in the SQL string and pass a sequence of values as the second argument to cursor.execute().

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

```sql
INSERT INTO some_table (an_int, a_date, a_string)
VALUES (10, '2005-11-18', 'O''Reilly');
```

--------------------------------

### cursor(name=None, cursor_factory=None, scrollable=None, withhold=False)

Source: https://www.psycopg.org/docs/connection.html

Creates and returns a new cursor object associated with the connection. Supports both client-side and server-side (named) cursors.

```APIDOC
## cursor(name=None, cursor_factory=None, scrollable=None, withhold=False)

### Description
Returns a new cursor object using the connection. If a name is provided, it creates a server-side named cursor; otherwise, it creates a standard client-side cursor.

### Parameters
- **name** (string) - Optional - The name for a server-side cursor.
- **cursor_factory** (class) - Optional - A subclass of psycopg2.extensions.cursor to create non-standard cursors.
- **scrollable** (boolean) - Optional - Defines if the named cursor is scrollable.
- **withhold** (boolean) - Optional - Defines if the named cursor should persist after transaction commit.
```

--------------------------------

### Handle PostgreSQL time zones

Source: https://www.psycopg.org/docs/usage.html

Shows how timestamptz is converted to Python datetime objects and how historical time zone offsets are handled.

```python
>>> 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)))
```

```python
>>> 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, 21m
datetime.timezone(datetime.timedelta(0, 19260))
# On Python 3.7 and following: 5h, 21m, 10s
datetime.timezone(datetime.timedelta(seconds=19270))
```

--------------------------------

### Prepared statement batch execution

Source: https://www.psycopg.org/docs/extras.html

Using execute_batch with PostgreSQL prepared statements for improved performance.

```python
execute_batch(cur,
    "big and complex SQL with %s %s params",
    params_list)
```

```python
cur.execute("PREPARE stmt AS big and complex SQL with $1 $2 params")
execute_batch(cur, "EXECUTE stmt (%s, %s)", params_list)
cur.execute("DEALLOCATE stmt")
```

--------------------------------

### sql.Composable.as_string

Source: https://www.psycopg.org/docs/sql.html

Returns the string representation of the Composable object evaluated within a specific context.

```APIDOC
## sql.Composable.as_string

### Description
Returns the string value of the object. This method is automatically invoked by cursor methods like execute() if a Composable is passed.

### Parameters
- **context** (connection or cursor) - Required - The database context used to evaluate the string.
```

--------------------------------

### Parse connection strings with parse_dsn

Source: https://www.psycopg.org/docs/extensions.html

Converts a connection string or URI into a dictionary of parameters using libpq parsing rules.

```python
>>> from psycopg2.extensions import parse_dsn
>>> parse_dsn('dbname=test user=postgres password=secret')
{'password': 'secret', 'user': 'postgres', 'dbname': 'test'}
>>> parse_dsn("postgresql://someone@example.com/somedb?connect_timeout=10")
{'host': 'example.com', 'user': 'someone', 'dbname': 'somedb', 'connect_timeout': '10'}
```

--------------------------------

### Exception Inheritance Hierarchy

Source: https://www.psycopg.org/docs/module.html

Visual representation of the Psycopg2 exception class inheritance structure.

```text
StandardError
|__ Warning
|__ Error
    |__ InterfaceError
    |__ DatabaseError
        |__ DataError
        |__ OperationalError
        |__ IntegrityError
        |__ InternalError
        |__ ProgrammingError
        |__ NotSupportedError
```

--------------------------------

### Call a cursor-returning function

Source: https://www.psycopg.org/docs/usage.html

Execute a stored procedure using a standard Psycopg cursor to initialize a named cursor on the server.

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

--------------------------------

### psycopg2.extensions.ISQLQuote

Source: https://www.psycopg.org/docs/extensions.html

Protocol class for SQL adaptation. Custom adapters should implement the getquoted() method.

```APIDOC
## class psycopg2.extensions.ISQLQuote(wrapped_object)

### Description
Represents the SQL adaptation protocol. Objects conforming to this protocol must implement a getquoted() method.

### Methods
- **getquoted()** - Returns a valid SQL string representing the wrapped object.
- **prepare(conn)** - Optional. Invoked before getquoted() to prepare the adapter based on connection-specific parameters.
```

--------------------------------

### Use QuotedString adapter for string escaping

Source: https://www.psycopg.org/docs/extensions.html

Demonstrates using the QuotedString adapter to escape single quotes and backslashes for SQL string constants.

```python
>>> QuotedString(r"O'Reilly").getquoted()
"'O''Reilly'"
```

--------------------------------

### Register dictionary adapter

Source: https://www.psycopg.org/docs/extras.html

Register the Json adapter for all Python dictionaries globally.

```python
psycopg2.extensions.register_adapter(dict, psycopg2.extras.Json)
```
Tokens:
Raw