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 with PostgreSQL Psycopg2 driver
Learn how to establish a connection to YugabyteDB database and begin basic CRUD operations using the steps in the Build an application page under the Quick start section.
The following sections break down the quick start example to demonstrate how to perform common tasks required for Python application development using the PostgreSQL Psycopg2 driver.
After completing these steps, you should have a working Python application that uses Psycopg2 to connect to your cluster, set up tables, run queries, and print out results.
Step 1: Download the driver dependency
If prerequisites are met, you can install psycopg like any other Python package, using
pip to download it from PyPI:
$ pip install psycopg2
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 advised to use the package built from sources.
Step 2: Connect to your cluster
Python applications can connect to and query the YugabyteDB database. First, import the psycopg2 package.
The Connection details can be provided as a string or a dictionary.
"dbname=database_name host=hostname port=port user=username password=password"
user = 'username', password='xxx', host = 'hostname', port = 'port', dbname = 'database_name'
Example URL for connecting to YugabyteDB can be seen below.
conn = psycopg2.connect(dbname='yugabyte',host='localhost',port='5433',user='yugabyte',password='yugabyte')
|host||Hostname of the YugabyteDB instance||localhost|
|port||Listen port for YSQL||5433|
|user||User connecting to the database||yugabyte|
Example URL for connecting to YugabyteDB cluster enabled with on the wire SSL encryption:
conn = psycopg2.connect("host=<hostname> port=5433 dbname=yugabyte user=<username> password=<password> sslmode=verify-full sslrootcert=/Users/my-user/Downloads/root.crt")
|sslrootcert||Path to the root certificate on your computer||~/.postgresql/|
Step 3: Query the YugabyteDB cluster from your application
Create a new Python file called
QuickStartApp.pyin 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
yburlwith the cluster credentials and SSL certificate, if required.
import psycopg2 # Create the database connection. yburl = "host=127.0.0.1 port=5433 dbname=yugabyte user=yugabyte password=yugabyte" conn = psycopg2.connect(yburl) # 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, row, row)) # Commit and close down. conn.commit() cur.close() conn.close()
When you run the
QuickStartApp.py project, 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.