In this article, we will discuss how to Insert a Python list into PostgreSQL database using pyscopg2 module.
Psycopg2 is the most popular PostgreSQL adapter for the Python programming language. Psycopg2 is a DB API 2.0 compliant PostgreSQL driver that is actively developed. It is designed for multi-threaded applications and manages its own connection pool. This module can be installed using the given command:
pip install psycopg2
To insert all records the list is traversed and the values are inserted one by one.
Syntax :
list = [(),(),.....,()] for d in list: cursor.execute("INSERT into table_name( column1,column2, column3.....) VALUES (%s, %s, %s,.....)", d)
First import all the required libraries into the working space and establish database connection. Set auto-commit to false and create a cursor object. Now, create a list of data to be inserted into the table. Loop through the list and insert values. Commit and close connection.
Example: Inserting list values to database
# importing psycopg2 module import psycopg2
# establishing the connection conn = psycopg2.connect(
database = "postgres" ,
user = 'postgres' ,
password = 'password' ,
host = 'localhost' ,
port = '5432'
) # creating a cursor object cursor = conn.cursor()
# creating table sql = '''CREATE TABLE employee(
id SERIAL NOT NULL,
name varchar(20) not null,
state varchar(20) not null
)''' # list that contain records to be inserted into table data = [( 'Babita' , 'Bihar' ), ( 'Anushka' , 'Hyderabad' ),
( 'Anamika' , 'Banglore' ), ( 'Sanaya' , 'Pune' ),
( 'Radha' , 'Chandigarh' )]
# inserting record into employee table for d in data:
cursor.execute( "INSERT into employee(name, state) VALUES (%s, %s)" , d)
print ( "List has been inserted to employee table successfully..." )
# Commit your changes in the database conn.commit() # Closing the connection conn.close() |
Output:
List has been inserted into employee table successfully
Example: Check whether data is being shown in the employee table or not.
# importing psycopg2 module import psycopg2
# establishing the connection conn = psycopg2.connect(
database = "postgres" ,
user = 'postgres' ,
password = 'password' ,
host = 'localhost' ,
port = '5432'
) # creating cursor object cursor = conn.cursor()
# query to sort table by name sql2 = 'select * from employee;'
# executing query cursor.execute(sql2) # fetching the result print (cursor.fetchall())
# Commit your changes in the database conn.commit() # Closing the connection conn.close() |
Output
[(1, ‘Babita’, ‘Bihar’), (2, ‘Anushka’, ‘Hyderabad’), (3, ‘Anamika’, ‘Banglore’), (4, ‘Sanaya’, ‘Pune’), (5, ‘Radha’, ‘Chandigarh’)]