In this article, we will be looking at the multiple approaches to inserting an array of strings in pyscopg2/Postgres in the python programming language.,
Method 1: Naive method
In this example, we form a connection to the classroom database using psycopg2.connect() method, we then create a cursor using conn.cursor() method. We then insert the values of the list directly by executing the SQL insert command, using the execute() method using placeholders %s. we finally fetch all the rows using the fetchall() method.
CSV Used:
# importing packages import psycopg2
# forming connection conn = psycopg2.connect(
database = "Classroom" ,
user = 'postgres' ,
password = 'sherlockedisi' ,
host = '127.0.0.1' ,
port = '5432'
) conn.autocommit = True
# creating a cursor cursor = conn.cursor()
# list of rows to be inserted values = [ 17 , 'samuel' , 95 ]
# executing the sql statement cursor.execute( "INSERT INTO classroom VALUES(%s,%s,%s) " , values)
# select statement to display output sql1 = '''select * from classroom;'''
# executing sql statement cursor.execute(sql1) # fetching rows for i in cursor.fetchall():
print (i)
# committing changes conn.commit() # closing connection conn.close() |
Output:
Before inserting:
After inserting:
Method 2: Insert arrays through the dictionary
We can insert an array of strings by storing them in a dictionary. The dictionary can further be updated into the table we created or in a table that already exists. in this example, we use the to_sql() method.
We use the to_sql() method to insert a pandas data frame into our database table.
Syntax of to_sql:
df.to_sql(‘data’, con=conn, if_exists=’replace’, index=False)
Arguments:
- Data: Name of the table
- con: connection
- if_exists: if the table already exists the function we want to apply. ex: ‘append’ help us add data instead of replacing the data.
- index: True or False
# import packages import psycopg2
import pandas as pd
from sqlalchemy import create_engine
# creating a connection db = create_engine(conn_string)
conn = db.connect()
#creating a table sql = '''CREATE TABLE details(Name char(20),
Age int);'''
# initialise data of lists. data = { 'Name' :[ 'sam' , 'richie' , 'harry' ],
'Age' :[ 18 , 20 , 19 ]}
# Create DataFrame df = pd.DataFrame(data)
df.to_sql( 'data' , con = conn, if_exists = 'replace' , index = False )
conn = psycopg2.connect(conn_string
)
conn.autocommit = True
cursor = conn.cursor()
# fetching data sql1 = '''select * from data;'''
cursor.execute(sql1) for i in cursor.fetchall():
print (i)
# conn.commit() conn.close() |
Output:
('sam', 18) ('richie', 20) ('harry', 19)