Connect PostgreSQL using Python and Jdbc Driver- Example

  • Post author:
  • Post last modified:October 4, 2019
  • Post category:General
  • Reading time:6 mins read

PostgreSQL is one of the widely used open source relational database management system (RDBMS). Sometimes, it is simply called Postgres. Many modern day databases such as Redshift, Netezza, Vertica,etc are based on the PostgreSQL. Postgres supports both JDBC and OBDC drivers. You can use those drivers from any programming language to connect. In this article, we will check how to connect PostgreSQL using Python and Jdbc driver with a working example.

PostgreSQL JDBC Driver

PostgreSQL offers drivers for the programming languages and tools that are compatible with JDBC API. You can use a JDBC connection to connect to your Postgres database from many third-party SQL client tools or from your favorite programming language that supports JDBC connection.

Download latest version of PostgreSQL JDBC connector from official website.

You can use any python module that supports JDBC connection. We will be using the Jaydebeapi module to demonstrate in this article. Jaydebeapi is one of the easiest module that I have been using.

You can also use other python modules such as psycopg2. For demonstration, we will use Jaydebeapi module.

Install Jaydebeapi

The JayDeBeApi module allows you to connect from Python code to various databases using Java JDBC drivers and a connection string. It provides a Python DB-API v2.0 to that database.

You can install it using pip:

# Python
pip install Jaydebeapi

Set CLASSPATH to Driver Location

If there are dependent jar files for any JDBC jar file, you have to provide path of those jar files in CLASSPATH shell or environment variable.

The PostgreSQL provided JDBC driver does not have any dependent jars, you can directly refer this driver in your jaydebeapi module.

Alternatively, if you are on Linux operating system, you can export jar location to CLASSPATH shell variable and run your python program without needing to set jar the location in your module.

You can read on how to set CLASSPATH variable in my other post – Set and Use Environment Variable inside Python Script.

PostgreSQL JDBC Connection String

Connection string differs from database to database. The PostgreSQL jdbc connection string is slightly different from databases like Netezza, Vertica, etc. It is almost similar to other Postgres systems like Redshift.

Below is the JDBC connection string that you can use to connect PostgreSQL database:

jdbc:postgresql://host:port/database

For example, consider below connection string as an example.

jdbc:postgresql://postgres_host:13013/dev

How to Connect PostgreSQL using JDBC Driver from Python?

Now we have PostgreSQL jdbc driver downloaded and a classpath variable or jar location is set. In this section, we will check how to connect PostgreSQL using JDBC driver from Python program.

Note that, PostgreSQL jdbc driver class name is “org.postgresql.Driver“.

Here is the code that can help you: 

import jaydebeapi, os

dsn_database = "dev"
dsn_hostname = "postgres_host"
dsn_port = "13013"
dsn_uid = "username"
dsn_pwd = "password"
jdbc_driver_name = "org.postgresql.Driver"
jdbc_driver_loc = os.path.join(r'D:\Jar\postgresql-9.3-1100-jdbc41.jar')

sql_str = "select version()"

connection_string='jdbc:postgresql://'+ dsn_hostname+':'+ dsn_port +'/'+ dsn_database

url = '{0}:user={1};password={2}'.format(connection_string, dsn_uid, dsn_pwd)
print("Connection String: " + connection_string)

conn = jaydebeapi.connect(jdbc_driver_name, connection_string, {'user': dsn_uid, 'password': dsn_pwd},
jars=jdbc_driver_loc)

curs = conn.cursor()
curs.execute(sql_str)
result = curs.fetchall()

print(result[0])

Here is the sample output: 

Connection String: jdbc:postgresql://postgres_host:13013/dev
(u'PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit',)

Hope this helps 🙂

Tags: , ,