Open In App

PostgreSQL – Insert Data Into a Table using Python

Improve
Improve
Like Article
Like
Save
Share
Report

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:

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


Now to verify the insertion use the following command in the psql shell:

SELECT * FROM student;

Output:



Last Updated : 30 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads