Insert arrays through the dictionary

Method 1: Naive method

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)

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.,

Tags:

#Python PostgreSQL #Python Pyscopg2 #Python #python

Method 1: Naive method

Insert arrays through the dictionary

Python3

Python Psycopg2 – Inserting array of strings

Similar Reads

Contact Us