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()
app.py

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.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)