This article is all about creating and querying Python PostgreSQL database in a simple and easy way.
Additionally, the readers of this article are going to learn to set up the environment before they can create and query the PostgreSQL database in Python.
This article is suitable for both beginners and professionals who are keen to get their hands on these technologies provided they are familiar with relational databases and also possess some understanding of modern database tools and technologies.
About Python and PostgreSQL
Let us begin this article by getting a bit of a conceptual understanding of both Python and PostgreSQL since we are going to use and implement these in the upcoming sections of this article.
What is Python
Python is a powerful, object-oriented, and very famous programming language that ships with a large variety of out-of-the-box features. It is a first-hand choice for programmers for several things from helper scripts to supporting large-scale applications.
Python is open-source which means it is free to use for both commercial and non-commercial projects at the same it is very easy to write code in Python and it is very fast to get your code outcome in Python.
According to www.python.org some of the common applications where Python is used are as follows:
- Automating tasks ranging from simple to complex nature
- Developing web applications
- Accessing database backends
- For in-depth analysis of data as used by data scientists
- Accessing and querying databases in python (Python PostgreSQL)
- Network programming
- Game development
- Education
- Scientific computing
- Building e-commerce systems (business applications)
In short words, Python is opening new horizons for its developers and users along with improving and expanding its exposure and capabilities to the next level all these apart from the already attained badge of being free for commercial use (open source) and easy to program plus not to mention the architecture independence it offers.
What is PostgreSQL
PostgreSQL is yet another open-source product just like Python that belongs to the group of relational database systems.
PostgreSQL uses and extends the SQL language to get a lot more benefits than you get by simply using SQL.
PostgreSQL is not a stranger to SQL developers since it follows the same syntax apart from the additional things it offers thereby increasing the overall productivity of the database system.
What is Python PostgreSQL
Python and PostgreSQL together form a Python PostgreSQL combination which in most cases turns out to be very productive and efficient.
The point is to be able to manage and manipulate the PostgreSQL database inside Python thereby making it fully accessible and exposing it to the rich library of Python for several important tasks ranging from database access to data analytics.
The Plan for Python PostgreSQL database creation and querying
In this section, we are discussing the plan to create and query the PostgreSQL database in Python before we jump ahead.
Please remember we are going to create a PostgreSQL database natively using the PostgreSQL client tool (the tool offered by PostgreSQL that helps to build databases) and then call it inside a Python program followed by simple manipulation of the database objects but all in Python to prove that this combination not only works but worth doing for your professional endeavors.
So, the summary of the steps is as follows:
- Setup PostgreSQL database system locally
- Setup Python to use PostgreSQL locally (with the help of a tool)
- Build PostgreSQL database in PostgreSQL
- Connect to the PostgreSQL database in Python
- Create PostgreSQL database objects (tables) in Python
- Populate PostgreSQL database object in Python
- Update the PostgreSQL database object in Python
Setup PostgreSQL database system locally
PostgreSQL database management system needs to be set up locally on your machine first. This can be done by simply installing PostgreSQL from the official website and configuring it correctly to run on your machine.
To use the Python PostgreSQL database combination, we must first configure the PostgreSQL database server to create a database there.
Installing PostgreSQL
Please go to PostgreSQL: Downloads and start the installation (after the download is finished) based on your operating system.
In this article we are installing PostgreSQL on a windows PC so our choice is as follows:
Install the required components except for pgAdmin4 which is not required for this article:
Once the setup is finished, please get hold of the following things:
Server: localhost
Database: Postgres
Port: 5432
Username: postgres
Password: (you chose during the setup but keep it safe)
Please remember Python PostgreSQL first requires a PostgreSQL server to be installed and configured and that is exactly what we have done now successfully. PostgreSQL installation comes with a default postgres database.
However, we need to test that all is working fine by running the PostgreSQL command prompt also called psql terminal.
Testing PostgreSQL Command Prompt (psql)
Type psql in the windows search box and you are going to see the PostgreSQL shell ready to be started.
Next, start the psql terminal and provide the configurations to connect to it (making sure you are typing in the correct password):
Upon successful connection you are connected to the Postgres database:
Create Sample Database University
Let us now create a new sample database called University by typing the following code in the command prompt:
|
1 2 3 |
postgres=# Create Database University; |
Please note that the semi-colon must be used to end a SQL statement in PostgreSQL.
The following output shows you that the sample database has been created successfully.
Check PostgreSQL Databases
Get a list of all the databases in PostgreSQL by running the following command (\l):
|
1 2 3 |
postgres=# \l |
This shows us the currently present PostgreSQL databases:
So, we can see the sample database university has been successfully created in PostgreSQL and we are getting ready for Python PostgreSQL database access and management.
Setup Python to use PostgreSQL locally (with the help of a tool)
To setup, Python to use PostgreSQL we need to install a very handy open-source tool called Visual Studio Code by clicking the link Visual Studio Code – Code Editing. Redefined.
Install Python Extension in Visual Studio Code
Python is installed as an extension in Visual Studio Code.
Please open Visual Studio Code and install the Python extension from Visual Studio Marketplace by clicking the Extension tab and searching and installing Python (extension):
Install PostgreSQL Extension in Visual Studio Code (psycopg2 2.9.5)
Next for Python PostgreSQL database joint access, we need to install the PostgreSQL extension in Visual Studio Code.
This is done by installing psycopg2 which is a PostgreSQL database adaptor for Python programming language that can help possible Python PostgreSQL database connection and querying.
To install psycopg2 2.9.5 let us first create a new virtual environment in Visual Studio code.
Please open the command palette (CTRL+SHIFT+X) and type Python: Create Environment:
This will create a virtual environment for Python.
Next, open Python Terminal by once again opening the command palette and selecting the Create Terminal this time followed by typing the following code in the Terminal:
|
1 2 3 |
(.venv) PS C:\Source\python-posgresql-database\python-postgresql> pip install psycopg2 |
Upon successful installation, your Python code is ready to talk with the PostgreSQL database.
Connect to PostgreSQL database in Python
Next, we need to establish a connection with the PostgreSQL database in Python. Now, to do this, we have to first create a file that contains our PostgreSQL database server credentials that we used in the past to connect to it using the PostgreSQL command prompt.
Create postgresql-university-database.ini file
Please create a PostgreSQL-university-database.ini file that contains the connection details including the password of your PostgreSQL database server and save it locally to be accessible by the Python file running in Visual Studio Code.
It is recommended to create and copy this file in the same location of the Visual Studio Code workspace (the windows folder that you are currently working in with Visual Studio Code).
Create postgresql_config.py file
Once the PostgreSQL-university-database.ini file is saved the next step is to create a config (Python file) file called postgresql_config.py that calls your ini file.
The file can be located in the following GitHub repository:
Create postgresql_universitydb_connect.py file
After you have created the config file you have to create a file to connect in Python using Visual Studio Code in the same workspace (folder).
Create a file postgresql_universitydb_connect.py.
Please click the GitHub link below to access this file:
Run the file to see the following output:
Create PostgreSQL database objects (tables) in Python
We need to now create database objects for the PostgreSQL university database from within Python (Python PostgreSQL).
Create create_student_subject_tables.py file
After we have established the connection we need to create the following tables in the database:
- Student table
- Subject table
Please create another Python file in the same workspace called create_student_subject_tables.py by using the following link and run it in Visual Studio Code:
Data check via PostgreSQL command prompt
Let us check whether the tables have been created or not by running the following command in the PostgreSQL command prompt:
|
1 2 3 |
university=# \d |
The output is as follows:
Populate PostgreSQL database object in Python (Python PostgreSQL)
Let us populate the table in the PostgreSQL database.
Create insert_four_subjects.py file
Now create and run a file called insert_four_subjects.py with the help of the following GitHub link:
Data check
Check the data by running the following command against the PostgreSQL command shell:
|
1 2 3 |
university=# SELECT * FROM Subject; |
The results are shown below:
Update PostgreSQL database object in Python
Finally, we want to update the PostgreSQL database by changing the name of a subject.
Create update_datastructure_subject file
Please create and execute the update_datastructure_Subject.py file with the help of the following GithHub repository link:
We have replaced the subject name Data Structure with Data Structures.
Data check
Let us check it in the PostgreSQL command prompt:
Congratulations, you have successfully learned to create and query the Python PostgreSQL database.
He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.
He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.
His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.
View all posts by Haroon Ashraf
- How to create and query the Python PostgreSQL database - August 15, 2024
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023