Member-only story
Creating a Python PostgreSQL connection for absolute beginners
How your Python script can communicate with your Postgres database
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):
