Connect an application
This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.
Psycopg is the most popular PostgreSQL database adapter for Python. Its main features are the complete implementation of the Python DB API 2.0 specification and the thread safety (several threads can share the same connection). YugabyteDB has full support for Psycopg2.
CRUD operations
The following sections demonstrate how to perform common tasks required for Python application development using the PostgreSQL Psycopg2 driver.
To start building your application, make sure you have met the prerequisites.
Step 1: Download the driver dependency
Building Psycopg requires a few prerequisites (a C compiler, some development packages). Refer to Installation and the FAQ in the Psycopg documentation.
If prerequisites are met, you can install psycopg like any other Python package, using pip
to download it from PyPI:
$ pip install psycopg2
or, using setup.py
, if you have downloaded the source package locally:
python setup.py build
sudo python setup.py install
You can also obtain a stand-alone package, not requiring a compiler or external libraries, by installing the psycopg2-binary package from PyPI:
pip install psycopg2-binary
The binary package is a practical choice for development and testing, but in production it is recommended to use the package built from sources.
Step 2: Connect to your cluster
The following table describes the connection parameters required to connect.
Parameter | Description | Default |
---|---|---|
host | Hostname of the YugabyteDB instance | localhost |
port | Listen port for YSQL | 5433 |
database/dbname | Database name | yugabyte |
user | User connecting to the database | yugabyte |
password | User password | yugabyte |
You can provide the connection details in one of the following ways:
-
Connection string
"dbname=database_name host=hostname port=port user=username password=password"
-
Connection dictionary
user = 'username', password='xxx', host = 'hostname', port = 'port', dbname = 'database_name'
The following is an example connection string for connecting to YugabyteDB.
conn = psycopg2.connect(dbname='yugabyte',host='localhost',port='5433',user='yugabyte',password='yugabyte')
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 YugabyteDB with SSL encryption enabled:
conn = psycopg2.connect("host=<hostname> port=5433 dbname=yugabyte user=<username> password=<password> sslmode=verify-full sslrootcert=/Users/my-user/Downloads/root.crt")
If you have created a cluster on YugabyteDB Aeon, use the cluster credentials 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"
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 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.