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:
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.