Creating a PostgreSQL Database in Python

This article is the first in an ongoing series covering the use of the PostgreSQL RDBMS with Python, and firstly I will cover creating a database.

Python itself provides no database access functionality but it does provide the Python Database API 2.0 standard (or DB-API) which is implemented by various third-party modules. The module I will be using for this and subsequent posts is psycopg2, the most popular Python/PostgreSQL DB-API module.

In this post I will introduce psycopg2 with a simple program which creates a database and then adds a few tables and views.

Relational database management systems and SQL are large and complex areas so I will not attempt to give an introduction to them here — this article will assume that you understand at least the basics of tables, column types, primary keys and foreign keys, as well as creating them using SQL. The code for this post assumes you have PostgreSQL installed, and ideally pgAdmin as well.

The need to programatically insert, update, delete and query data is obvious, but actually creating a database by running a program is rather less common. For purely in-house projects a typical workflow would be to create the schema on a development database (perhaps using data modelling tools and/or a GUI such as pgAdmin) and then generating a script to run on the production server.

However, if you are writing database-driven software to be distributed to various sites or external clients then manually running scripts becomes too cumbersome to be practical. Many companies will want to distribute a setup program (or suite of programs) to their customers which can be run with little or no sys admin or DBA expertise.

Python is the ideal choice for this, even if the software being distributed is written in another language. It is straightforward to create one or more standard setup programs to create the database and carry out all other installation and setup tasks, perhaps tweaking them for individual client’s requirements.

For this project I will be hard-coding the various CREATE statements, but for a later project I’ll look…

Related posts:

A house on the beach, an expensive car, going on holiday four times a year — these and much more are dreams that people in our society cherish. Every day they get up reluctantly, go to work…

We recently had an AMA in official Polygon Telegram group, on May 13th. As some of you might have not participated, we made sure no one missed out on the good stuff! We made an AMA recap of the asked…

When you start your self-publishing journey — whether it be in romance, science fiction, erotica, or another genre — you will come across Amazon and KDP Select. Publishing on Amazon is almost…