PostgreSQL – Insert Data Into a Table using Python

Last Updated : 30 Aug, 2020

In this article we will look into the process of inserting data into a PostgreSQL Table using Python. To do so follow the below steps:

Step 1: Connect to the PostgreSQL database using the connect() method of psycopg2 module.

conn = psycopg2.connect(dsn)

Step 2: Create a new cursor object by making a call to the cursor() method

cur = conn.cursor()

Step 3: Now execute the INSERT statement by running the execute() method

cur.execute(sql, (value1,value2))

Step 4: After inserting the data call the commit() method to make the changes permanent.

conn.commit()

Step 5: Now terminate the cursor and the connection to the database.

cur.close()
conn.close()

Example:

Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course

For example we will use the Student table of the school database that we created in the earlier sections of the article series.

Here we will create a insert_student() function to insert student_name row to the student table:

Python3

#!/usr/bin/python
  
import psycopg2
from config import config
  
  
def insert_student(student_name):
    """ insert a new vendor into the vendors table """
    sql = """INSERT INTO students(student_name)
             VALUES(%s) RETURNING student_id;"""
    conn = None
    student_id = None
    try:
        # read database configuration
        params = config()
        # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
        # create a new cursor
        cur = conn.cursor()
        # execute the INSERT statement
        cur.execute(sql, (student_name,))
        # get the generated id back
        student_id = cur.fetchone()[0]
        # commit the changes to the database
        conn.commit()
        # close communication with the database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
  
    return student_id