Python Psycopg2 – Inserting array of strings

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
conn_string = 'postgres://postgres:sherlockedisi@127.0.0.1/data1'

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)