PostgreSQL is an advanced, open-source, highly stable DBMS that extends the standard SQL language. It supports all major programming languages, such as Python, Java, C++, etc. Due to its extensive features, Postgres has become most programmers/developers' first choice.

In this blog, we will present a step-wise guide on inserting data into a Postgres table using Python. So, let’s begin!

How to Insert Data Into a Postgres Using Python Programming?

Follow the below instructions to insert the data into a table using python:

● Import psycopg
● Establish a Connection With Postgres
● Create a Cursor Object
● Execute the INSERT Query
● Save Changes
● Close the Connection With the Database

Import psycopg

Firstly, you need to import the “psycopg” module in your python program via the import keyword:

import psycopg2

Establish a Connection With Postgres

Use the “connect()” method of “psycopg” module to establish a connection with the Postgres database using python:

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

In the above snippet, the “postgres” represents a targeted database, the “postgres” represents a user name, and the “password” field shows a valid password for the selected database. Finally, specify the host/IP address and the port number in the respective fields.

Create a Cursor Object

Creating the cursor object enables us to execute the Postgres queries from python. To create a cursor object in Postgres, you need to use the cursor() method as follows:

curs_obj = con.cursor()

Execute the INSERT Query

Now, execute the INSERT query with the help of the cursor object:

curs_obj.execute("INSERT INTO emp_data(emp_name, emp_age) VALUES('Joseph', 26), ('Joe', 29);")
print("Data Inserted")

Save Changes

Use the “commit()” method to commit all the desired changes to the Postgres database permanently:

con.commit()

Close the Connection With the Database

Use the “close()” method to terminate the cursor and the connection to the selected database:

curs_obj.close()
con.close()

Complete Code

Output

Verification

Let’s verify the inserted data using the execute() method and fetchall() method:

curs_obj.execute("SELECT * FROM emp_data")
result = curs_obj.fetchall()
print("Table's Data:", "\n", result)

In the above code block, we utilize the execute() method to execute the SELECT query. Using the fetchall() method, we fetch the data from the “emp_data” table.

Code

Output

This is how you can insert and fetch data to or from a table in Postgres.

Conclusion

Use the “connect()” method of “psycopg” module to establish a connection with the Postgres database using python. Next, create a cursor object and execute the INSERT query via the cursor object. After that, use the “commit()” method to permanently commit all the desired changes to the Postgres database. Finally, execute the “close()” method to terminate the cursor and the connection to the selected database. This blog presented a detailed guide on inserting data into a Postgres table via Python programming.