Connect an application

The Yugabyte Psycopg2 smart driver is a Python driver for YSQL built on the PostgreSQL psycopg2 driver, with additional connection load balancing features.

YugabyteDB Managed

To use smart driver load balancing features when connecting to clusters in YugabyteDB Managed, applications must be deployed in a VPC that has been peered with the cluster VPC. For applications that access the cluster from outside the VPC network, use the upstream PostgreSQL driver instead; in this case, the cluster performs the load balancing. Applications that use smart drivers from outside the VPC network fall back to the upstream driver behaviour automatically. For more information, refer to Using smart drivers with YugabyteDB Managed.

The Yugabyte Psycopg2 smart driver does not support SSL mode verify-full for clusters in YugabyteDB Managed. Use verify-ca or the upstream psycopg2 driver.

CRUD operations

The following sections demonstrate how to perform common tasks required for Python application development using the YugabyteDB Psycopg2 smart driver.

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

Step 1: Add the YugabyteDB driver dependency

Building Psycopg2 requires a few prerequisites (a C compiler and some development packages). Check the installation instructions and the FAQ for details.

The YugabyteDB Psycopg2 requires PostgreSQL version 12 or later (preferably 14).

After you've installed the prerequisites, install psycopg2-yugabytedb like any other Python package, using pip to download it from PyPI:

$ pip install psycopg2-yugabytedb

Or, you can use the setup.py script if you've downloaded the source package locally:

$ python setup.py build
$ sudo python setup.py install

Step 2: Set up the database connection

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

Parameter Description Default
host Host name of the YugabyteDB instance. You can also enter multiple addresses. localhost
port Listen port for YSQL 5433
database/dbname Database name yugabyte
user User connecting to the database yugabyte
password User password yugabyte
load_balance Uniform load balancing Defaults to upstream driver behavior unless set to 'true'
topology_keys Topology-aware load balancing If load_balance is true, uses uniform load balancing unless set to comma-separated geo-locations in the form cloud.region.zone.

You can provide the connection details in one of the following ways:

  • Connection string:

    "dbname=database_name host=hostname port=5433 user=username password=password load_balance=true topology_keys=cloud.region.zone1,cloud.region.zone2"
    
  • Connection dictionary:

    user = 'username', password='xxx', host = 'hostname', port = '5433', dbname = 'database_name', load_balance='true', topology_keys='cloud.region.zone1,cloud.region.zone2'
    

The following is an example connection string for connecting to YugabyteDB:

conn = psycopg2.connect(dbname='yugabyte',host='localhost',port='5433',user='yugabyte',password='yugabyte',load_balance='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.

Tip

To obtain a list of available hosts, you can connect to any cluster node and use the yb_servers() YSQL function.

Delimit the addresses using commas, as follows:

conn = psycopg2.connect(dbname='yugabyte',host='host1,host2,host3',port='5433',user='yugabyte',password='yugabyte',load_balance='true')

The hosts are only used during the initial connection attempt. If the first host is down when the driver is connecting, the driver attempts to connect to the next host in the string, and so on.

Use SSL

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

Parameter Description Default
sslmode SSL mode prefer
sslrootcert path to the root certificate on your computer ~/.postgresql/

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

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

The Yugabyte Psycopg2 smart driver does not support SSL mode verify-full for clusters in YugabyteDB Managed. Use verify-ca or the upstream psycopg2 driver. If your cluster is on YugabyteDB Managed, use the cluster credentials for user and password, and download the SSL Root certificate.

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 psycopg2

# Create the database connection.

connString = "host=127.0.0.1 port=5433 dbname=yugabyte user=yugabyte password=yugabyte load_balance=True"

conn = psycopg2.connect(connString)

# Open a cursor to perform database operations.
# The default mode for psycopg2 is "autocommit=false".

conn.set_session(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.set_session(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 there is no output or you get an error, verify the parameters included in the connection string.

Limitations

Currently, PostgreSQL psycopg2 driver and Yugabyte Psycopg2 smart driver cannot be used in the same environment.

Learn more