Skip to content
Related Articles

Related Articles

Insert Python Dictionary in PostgreSQL using Psycopg2

View Discussion
Improve Article
Save Article
  • Last Updated : 21 Oct, 2021
View Discussion
Improve Article
Save Article

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. 

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!