Python Drivers

• PostgreSQL Psycopg2
• YugabyteDB Psycopg2

Psycopg is the most popular PostgreSQL database adapter for the Python programming language. 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.

Fundamentals of PostgreSQL Psycopg driver

Learn how to establish a connection to YugabyteDB database and begin simple CRUD operations using the steps in Build an Application in the Quick Start section.

Let us break down the quick start example and understand how to perform the common tasks required for Python App development using the PostgreSQL Psycopg driver.

Download the Driver Dependency

Building Psycopg requires a few prerequisites (a C compiler, some development packages): please check the install and the faq documents in the doc dir or online for the details.

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 advised to use the package built from sources.

Connect to YugabyteDB Database

Python Apps can connect to and query the YugabyteDB database. To do that first import the psycopg2 package.

import psycopg2

The Connection details can be provided as a string or a dictionary. 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'

Example URL for connecting to YugabyteDB can be seen below.

conn = psycopg2.connect(dbname='yugabyte',host='localhost',port='5433',user='yugabyte',password='yugabyte')

Create a Cursor

To execute any sql commands, a cursor needs to be created after connection is made. It allows Python code to execute PostgreSQL command in a database session. Cursors are created by the connection.cursor() method: they are bound to the connection for the entire lifetime and all the commands are executed in the context of the database session wrapped by the connection.

cur = conn.cursor()

Create Table

Tables can be created in YugabyteDB by passing the CREATE TABLE DDL statement to the cursor.execute(statement) method.

For example

CREATE TABLE IF NOT EXISTS employee (id int PRIMARY KEY, name varchar, age int, language text)

conn = psycopg2.connect(dbname='yugabyte',host='localhost',port='5433',user='yugabyte',password='yugabyte')
cur = conn.cursor()
cur.execute('CREATE TABLE IF NOT EXISTS employee (id int PRIMARY KEY, name varchar, age int, language varchar)')

Read and Write Data

Insert Data

In order to write data into YugabyteDB, execute the INSERT statement using the cursor.execute(statement) method.

For example

INSERT INTO employee VALUES (1, 'John', 35, 'Java')

conn = psycopg2.connect(dbname='yugabyte',host='localhost',port='5433',user='yugabyte',password='yugabyte')
cur = conn.cursor()
cur.execute('INSERT INTO employee VALUES (1, 'John', 35, 'Java')')

Query Data

In order to query data from YugabyteDB tables, execute the SELECT statement using cursor.execute(statement) method followed by cursor.fetchall() method. fetchall() fetches all the rows of a query result, returning them as a list of tuples. An empty list is returned if there is no more record to fetch.

For example

SELECT * from employee;

conn = psycopg2.connect(dbname='yugabyte',host='localhost',port='5433',user='yugabyte',password='yugabyte')
cur = conn.cursor()
cur.execute('SELECT * from employee')
rows = cur.fetchall()
for row in rows:
  print("\nQuery returned: %s, %s, %s" % (row[0], row[1], row[2]))

Configure SSL/TLS

Psycopg2 supports several SSL modes, as follows:

By default, the driver supports the prefer SSL mode. And in the require mode, a root CA certificate isn't required to be configured.

To enable verify-ca or verify-full, you need to provide the path to the root CA certificate in the connection string using the sslrootcert parameter. The default location is ~/.postgresql/root.crt. If the root certificate is in a different file, specify it in the sslrootcert parameter:

conn = psycopg2.connect("host=<hostname> port=5433 dbname=yugabyte user=<username> password=<password> sslmode=verify-full sslrootcert=/Users/my-user/Downloads/root.crt")

The difference between verify-ca and verify-full depends on the policy of the root CA. If you're using a public CA, verify-ca allows connections to a server that somebody else may have registered with the CA. Because of this behavior, you should always use verify-full with a public CA. If you're using a local CA, or even a self-signed certificate, using verify-ca may provide enough protection, but the best security practice is to always use verify-full.