Connect an application

• YSQL
• YCQL

• YB Psycopg 2
• YB Psycopg 3
• PG Psycopg 2
• PG Psycopg 3
• aiopg

The Yugabyte Psycopg 3 smart driver is a Python driver for YSQL built on the PostgreSQL Psycopg 3 driver, with additional connection load balancing features. The import name is psycopg, so no changes are needed on existing application code; opting in requires a single parameter on the connection string.

CRUD operations

The following sections demonstrate how to perform common tasks required for Python application development using the Yugabyte Psycopg 3 smart driver.

To start building your application, make sure you have met the prerequisites.

Step 1: Add the YugabyteDB driver dependency

Install psycopg-yugabytedb from PyPI with pip, or pin the exact version:

pip install psycopg-yugabytedb
# or
pip install "psycopg-yugabytedb==3.3.4.1"

To use the upstream connection pool alongside the smart driver, install the [pool] extra. This pulls in an unmodified upstream psycopg-pool; the pool's connections still go through the smart-driver dispatcher.

pip install "psycopg-yugabytedb[pool]"

Prerequisites

• Python 3.10 or later
• libpq installed on the system (the C library underneath every PostgreSQL client). This is the same runtime prerequisite as upstream psycopg 3's pure-Python distribution.
  • macOS: brew install libpq && brew link --force libpq
  • Ubuntu / Debian: sudo apt install libpq5
  • RHEL / Fedora: sudo dnf install libpq

python3 -m venv ~/yb-venv
~/yb-venv/bin/pip install psycopg-yugabytedb

Verify the install:

python -c "import psycopg; print(psycopg.__version__)"
# Expected: 3.3.4.1

python -c "from psycopg.yb.registry import ClusterRegistry; print('smart driver ok')"
# Expected: smart driver ok

Step 2: Set up the database connection

To enable smart-driver load balancing, add load_balance_hosts=true to your connection string (or pass load_balance_hosts="true" to psycopg.connect()). If you omit it, the driver behaves like upstream Psycopg 3.

The smart driver extends the libpq conninfo string with four parameters. All take the standard libpq key=value form, and both dashed (load-balance-hosts) and underscore forms are accepted.

The following table describes the connection parameters required to connect, including smart driver parameters for uniform and topology load balancing.

The following is an example connection string:

import psycopg

conn = psycopg.connect(
"host=h1 port=5433 user=yugabyte dbname=yugabyte "
"load_balance_hosts=true"
)

You can also pass the parameters as keyword arguments to psycopg.connect:

conn = psycopg.connect(host="h1", port=5433, user="yugabyte", dbname="yugabyte", load_balance_hosts="true")

After the driver establishes the initial connection, it fetches the list of available servers from the cluster, and load-balances subsequent connection requests across these servers.

Use multiple addresses

You can specify multiple hosts in the connection string to provide alternative options during the initial connection in case the primary address fails.

Delimit the addresses using commas, as follows:

conn = psycopg.connect(
    "host=host1,host2,host3 port=5433 user=yugabyte dbname=yugabyte "
    "password=yugabyte load_balance_hosts=true"
)

The hosts are only used during the initial connection attempt. If the first host is down when the driver is connecting, libpq tries each host in turn and the smart driver bootstraps from the first one that responds. After bootstrap, the driver discovers the rest of the cluster via yb_servers().

Use SSL

The following table describes the connection parameters required to connect using SSL.

The following is an example for connecting to a YugabyteDB cluster with SSL enabled:

conn = psycopg.connect("host=<hostname> port=5433 dbname=yugabyte user=<username> password=<password> load_balance_hosts=true sslmode=verify-full sslrootcert=/path/to/root.crt")

SSL uses libpq's standard parameters — the same flags as upstream psycopg 3. For details, see the PostgreSQL libpq SSL documentation.

Step 3: Write your application

Create a new Python file called QuickStartApp.py in the base package directory of your project.

Copy the following sample code to set up tables and query the table contents. Replace the connection string connString with the cluster credentials and SSL certificate, if required.

import psycopg

# Create the database connection.

connString = "host=127.0.0.1 port=5433 dbname=yugabyte user=yugabyte password=yugabyte load_balance_hosts=true"

conn = psycopg.connect(connString)

# Open a cursor to perform database operations.

conn.autocommit = True
cur = conn.cursor()

# Create the table. (It might preexist.)

cur.execute(
  """
  DROP TABLE IF EXISTS employee
  """)

cur.execute(
  """
  CREATE TABLE employee (id int PRIMARY KEY,
                        name varchar,
                        age int,
                        language varchar)
  """)
print("Created table employee")
cur.close()

# Take advantage of ordinary, transactional behavior for DMLs.

conn.autocommit = False
cur = conn.cursor()

# Insert a row.

cur.execute("INSERT INTO employee (id, name, age, language) VALUES (%s, %s, %s, %s)",
            (1, 'John', 35, 'Python'))
print("Inserted (id, name, age, language) = (1, 'John', 35, 'Python')")

# Query the row.

cur.execute("SELECT name, age, language FROM employee WHERE id = 1")
row = cur.fetchone()
print("Query returned: %s, %s, %s" % (row[0], row[1], row[2]))

# Commit and close down.

conn.commit()
cur.close()
conn.close()

Run the application

Run the project QuickStartApp.py using the following command:

python3 QuickStartApp.py

You should see output similar to the following:

Created table employee
Inserted (id, name, age, language) = (1, 'John', 35, 'Python')
Query returned: John, 35, Python

If the connection fails, verify:

• YugabyteDB is running and reachable on the host/port in your DSN.
• The yugabyte user (or whichever user you used) exists and has the necessary permissions.
• For multi-host DSNs, at least one contact host responds.
• System libpq is installed (see Prerequisites).

Limitations

• Currently, PostgreSQL Psycopg 3 driver and YugabyteDB Psycopg 3 smart driver cannot be used in the same environment. Both write to site-packages/psycopg/. If a previous environment has upstream psycopg (or psycopg-binary, psycopg-c) installed, uninstall it before installing the fork, or use a fresh virtual environment.

• Pure Python distribution only. The C-accelerated (psycopg-yugabytedb-c) and pre-built binary (psycopg-yugabytedb-binary) distributions are not published yet. The pure-Python distribution requires system libpq.

• Strict topology filter in this release. If topology_keys matches no live nodes, connect raises OperationalError (no cluster-wide fallback in this release).

Learn more

YugabyteDB smart drivers for YSQL