CONNECTING PYTHON TO YOUR PostgreSQL Database

Before starting this simple program, you need to have Python and PostgreSQL installed on your local machine. If you would like you can download PyCharm too, which is an IDE for your python and pgAdmin4, a GUI administrating tool for your PostgreSQL database. You can download it from the following websites.

PostgreSQL
pgAdmin4
python
PyCharm

Open pgAdmin4 and create a new server. After creating a new server, a database named postgres comes default. However you can create a new database. I have named it as school where I have created two tables named as students and teacher.

I have already some data inserted there and I will be fetching those data into my PyCharm terminal.

Open a new python file and name it anything you like. Now before you can work with database, you need a python package called psycopg2 to work with postgresql database. Open your terminal and type pip install psycopg2.

I already have it installed. Now lets fetch the values into the same.

import psycopg2

con = psycopg2.connect(
    host="localhost",
    database="school",
    user="postgres",
    password="****",
    port="5432"
)

cur.execute("select id,name,age from teacher")
con.commit()

rows = cur.fetchall()
for i in rows:
    print(i)

con.close()

First you need to import pyscopg2 and then connect withyour database with above five parameters. Everything except database name and password is default. Enter the database you created and the password you have created during installation. The next step is to create a cursor that will help us to interact with database. After creating a cursor then you can execute any SQL queries you like. After all execution and operations you need to close the connection. If you run the queries in your PyCharm terminal, this is the output:

The output is same in the pgAdmin4 too:

You can do anything you like. For example: inserting, updating, deleting all the joins operation and so on. For changing the table value like in updating, inserting, you need to commit the changes and should place this code con.commit() just after the execution of SQL queries.