Insert Python Dictionary in PostgreSQL using Psycopg2

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