Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Insert Python Dictionary in PostgreSQL using Psycopg2

  • Last Updated : 21 Oct, 2021

In this article, we are going to see how to insert a Python dictionary in PostgreSQL using Psycopg2.

We can insert a python dictionary by taking the dictionary keys as column names and values as dictionary values. We import the Psycopg2 package and form a connection to the PostgreSQL database using psycopg2.connect() method. First, let’s create a table and then insert the python dictionary values into it. We create the table by executing the SQL statement using the cursor.execute() method. 

 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

Create table SQL command:

'''CREATE TABLE DETAILS(employee_id int NOT NULL, employee_name char(20),
         employee_email varchar(30), employee_salary float);'''

After creating an empty table, values in the table are taken from the dictionary by using the .values() method.



empty table

We loop through the dictionary values and insert them into the table. The below SQL statement is executed for each insertion.

'''insert into DETAILS(employee_id , employee_name ,
         employee_email , employee_salary) VALUES{};'''

Below is the implementation:

Python3




import psycopg2
 
# connection establishment
conn = psycopg2.connect(
   database="geeks",
    user='postgres',
    password='root',
    host='localhost',
    port= '5432'
)
   
 
conn.autocommit = True
cursor = conn.cursor()
 
sql = '''CREATE TABLE DETAILS(employee_id int NOT NULL,\
          employee_name char(20),
          employee_email varchar(30), employee_salary float);'''
 
 
cursor.execute(sql)
dictionary ={ 'empl1' : (187643,'sarah',
                       'sarahpaul@gmail.com',65000),
              'empl2' : (187644,'rahul',
                       'rahul101@gmail.com',75000),
              'empl3' : (187645,'arjun',
                       'arjun234@gmail.com',70000)
}
columns= dictionary.keys()
for i in dictionary.values():
    sql2='''insert into DETAILS(employee_id , employee_name ,
          employee_email , employee_salary) VALUES{};'''.format(i)
 
    cursor.execute(sql2)
 
sql3='''select * from DETAILS;'''
cursor.execute(sql3)
for i in cursor.fetchall():
    print(i)
 
conn.commit()
conn.close()

Output:

(187643, 'sarah               ', 'sarahpaul@gmail.com', 65000.0)
(187644, 'rahul               ', 'rahul101@gmail.com', 75000.0)
(187645, 'arjun               ', 'arjun234@gmail.com', 70000.0)

PostgreSQL table after insertion:

final table after insertion




My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!