Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Insert Python list into PostgreSQL database

  • Last Updated : 23 Sep, 2021

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:

 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

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, colum3.....) 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

Python3




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

Python3




# 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’)]




My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!