Thursday, December 26, 2024
Google search engine
HomeLanguagesInsert Python list into PostgreSQL database

Insert Python list into PostgreSQL database

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

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

RELATED ARTICLES

Most Popular

Recent Comments