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.
pip install pipenv pipenv shell pipenv install psycopg2
Note: We have installed everything for you on our platform.
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) print("Name = ", row) print("Department = ", row, "\n") # Closing the connection conn.close()
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.
View all Courses