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: