How to insert a Python list into PostgreSQL database
PostgreSQL is an object-relational database that uses and extends the SQL language to scale and store big, complicated data in the database.
In this shot, we learn how to insert a Python list into PostgreSQL.
Installation
pip install pipenv
pipenv shell
pipenv install psycopg2
Note: We have installed everything for you on our platform.
Code
Create a file and name it app.py. Now click the “Run” button below to print the inserted data on the console.
import psycopg2
#from mlxtend import psycopg2
# connection establishment
conn = psycopg2.connect(
database="postgres",
user='postgres',
password='12345',
host='localhost',
port= '5432'
)
# Creating a cursor object
cursor = conn.cursor()
# query to create a table in the database
sql = '''CREATE TABLE school(
id SERIAL NOT NULL,
name varchar(60) not null,
department varchar(200) not null
)'''
# executing the query inorder to create the table
cursor.execute(sql)
print("Database has been created successfully !!")
# list to be inserted into table
data = [('Gerddald', 'Biochemistry'), ('Wisdom', 'matritime'),
('Donald', 'material and metallogical engineering '), ('Andrew', 'Food and Science Technology'),
('Alexander', 'Agricultural and Bioresources engineering')]
# inserting record into school table
for d in data:
cursor.execute("INSERT into school(name, department) VALUES (%s, %s)", d)
print("List has been inserted to school table successfully...")
# Commit your changes in the database
conn.commit()
print("Retrieving records from table")
cursor.execute("select * from school")
records = cursor.fetchall()
for row in records:
print("ID = ", row[0])
print("Name = ", row[1])
print("Department = ", row[2], "\n")
# Closing the connection
conn.close()
Explanation
In the code above:
-
Line 1: We import
psycopg2, which is the most popular PostgreSQL database adapter for the Python programming language. -
Lines 5-11: We start establishing a connection to the database.
-
Line 15: We do
cursor = conn.cursor()whereby creating the cursor object. -
Lines 18-22: We create a table in the database.
-
Line 26: We execute the query in order to create the table.
-
Line 32-34: We create the data to be inserted into the table.
-
Lines 37-38: We looped the data and start inserting it into the database.
-
Line 43: We commit the changes to the database.
-
Line 46: We retrieve records from the table.
-
Lines 50-53: We print records on the console.
-
Line 55: We close the connection to the database.
