Creating a Python PostgreSQL connection for absolute beginners

How your Python script can communicate with your Postgres database

Mike Huls

Jul 4, 2022 — 5 min read

Postgres has arrived (image by April Pethybridge on Unsplash)

Having Python connect to your database and exchanging information is a very basic skill but creating the actual connection might be a bit weird at first. In this article we’ll check out how to create and use a connection in an efficient an safe way. We’ll use a package called psycopg2 and demonstrate how to interact with your database in some practical examples. Let’s code!

Before we begin..

Want to connect to a database that is not PostgreSQL? Check out the article below.

Generating SQL queries

This article focusses on creating a connection with a database using pyodbc. This connection can then be used to execute SQL. Some databases use other syntax than others:

MS SQL Server
SELECT TOP 1 colname FROM tablename LIMIT 10
PostgreSQL
SELECT colname FROM tablename LIMIT 10.

This means that you have to write the SQL statements for a specific database.

There is a database-agnostic approach where you define your query in a more Pythonic way and then compile if for a certain database. This will generate the SQL specific for that database so that you are not bound to the specifics of the database you’re currently using. This means that if you decide to switch from MySQL to Postgres in the future you won’t have to change your code.

I’m currently writing this article so make sure to follow me!

About psycopg2

Psycopg2 is a PostgreSQL database adapter written for Python. It was designed for heavily multi-threaded applications, is written in C and focusses on effective and secure communication.

If you’re like me and think “psycopg2.. that’s a weird name”: this is because the author made a little mistake (source):

I wanted to call it psychopg (a reference to their psychotic driver) but I typed the name wrong.

Despite this funny background story psycopg2 is a very optimized, safe and efficient package. In the next few parts we’ll use it to set up a connection to our Postgres database and execute some SQL.

Let’s plug into our database (image by Clint Patterson on Unsplash)

Creating the database connection

Below we’ll go through how the connection is made and used. First we’ll prepare by installing our dependencies:

Prep: dependencies

First let’s create a virtual environment and install our only dependency. Check out the article below to learn why using a venv is very important and how to create them.pip install psycopg2

Step 1. Creating a connection

The firsts goal is to create a connection (usually you have one connection per application).

First we’ll get our credentials by loading them from the environment so that our application has access to the credentials without hardcoding them into our script.

Prevent your passwords from leaking by learning how to apply env files in the article below:

Step 2. Creating a cursor and executing

Now that we have the connection we can create a cursor. This is an object that’s used to iterate over a result-set (resulting from a query).

The code above creates a cursor in a context-manager (the with) statement which has two main advantages:

All queries in the context-manager are handled as a transaction. Any cursor.execute that fails will cause the previous ones to roll back
The cursor will be closed automatically when we exit the block

Step 3. Example queries

Not that our database connection is made we can start querying our database. In the article below we configure a connection to (among other databases) MS SQL Server. This article also contains some very useful example queries that you can apply to our psycopg2 connection as well. Make sure to check them out to get a running start.

Important

Always make sure to clean the variables you put into your SQL statements in order to prevent SQL injection attacks, especially if you allow user input into your statements. This is beautifully explained by this famous XKCD comic.

Next steps

Now that your script can connect to the database you can start writing SQL like the article below. Check out this link for an overview of many handy queries. Also check out this article that details how to implement database migrations; this way you can use Python to version control the structure of your database! Very useful!

Conclusion

I hope to have shed some light on how to connect to your Postgres database with psycopg2 and use that connection to work with the database. I hope everything was as clear as I hope it to be but if this is not the case please let me know what I can do to clarify further. In the meantime, check out my other articles on all kinds of programming-related topics like these:

Happy coding!

— Mike

P.S: like what I’m doing? Follow me!