Open In App

Python Psycopg2 – Inserting array of strings

Improve
Improve
Like Article
Like
Save
Share
Report

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:

Python3




# 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

Python3




# 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)



Last Updated : 07 Feb, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads