Python and PostgreSQL without ORM

With a wide range of databases, MySQL, PostgreSQL, SQLite, MongoDB, to mention but a few, and the need to switch between each of them in different states of the software development, Object Relational Mappers such as SQLAlchemy, ORMLite e.t.c always comes to the rescue. You may at some point find yourself in a position where you are required to implement a feature without an ORM at hand. In this tutorial, I am assuming you have the basic knowledge of the following:

1. Python — the language. :)
2. PIP — a package management system to manage and install software packages.
3. Creating virtual environments with Python virtualenv
4. You have already installed PostgreSQL. If you are running on Ubuntu, check my previous post here.

Getting Started

Create a virtual environment and activate it in your project folder, if you have not already done so.

# On Windows
py -3 -m venv venv
source venv/Scripts/activate
# On Linux
$ python3 -m venv venv;
$ source venv/bin/activate

Installing psycopg2

When you think of Python, PostgreSQL and no ORM, psycopg2 should come in mind. It is the most popular adapter for Python with the core functionality to the Python DB API 2.0. It generally helps you execute your SQL statements and query the database.

With the virtual environment activated, run the following command to install psycopg2 to your environment.

pip install psycopg2

Okay, good.. Now let’s dive in into the code.

Integrating psycopg2 into your python application.

We will first need to import it to our python file. Remember to follow the PEP8 style guide. :)

import psycopg2

Connecting to a database.

Before connecting to your database, make sure the following conditions are satisfied:

1. The postgreSQL server is up and running.
2. You have a database user created. (You may also use the ‘postgres’ user)
3. You have set the database password for the user are going to use. You can use the following commands to change the password for the postgres user.

\password postgres

To change the password for any other user:

\password <user_name>

Okay, that’s done. Let’s connect to the database. Add the following line to your code:

con = psycopg2.connect('dbname='testdb' user='postgres' host='localhost' password='my_password')

1. Replace testdb with your database name.
2. Replase postgres with the user you will chose.
3. Replace ‘localhost’ with the IP address of the database, keep it that way if the database is local.
4. Replace my_password with your user password.

Executing SQL statements

You will need to create a cursor object to execute SQL statements to the database.

cur = con.cursor()

We are now ready to execute calls. Here is an example to create a simple table.

cur.execute("CREATE TABLE test(id serial PRIMARY KEY, name varchar, email varchar)")

Here is a simple example to query items from the table and obtain the result as a python object(s).

cur.execute("SELECT * FROM test")
items = cur.fetchall()

Finally, after all is done, make the changes persistent to the database by calling the commit function from the connection object.

con.commit()

Close the database

Once your application has successfully completed it’s need for the database connection, always remember to clean up :).

cur.close()
con.close()

Psycopg2 Documentation

This article helps you get started on creating the database connection and learning how to execute commands to the database. The full documentation for psycopg2 can be found here.

Gracias :)