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 packagesimport psycopg2# forming connectionconn = psycopg2.connect( database="Classroom", user='postgres', password='sherlockedisi', host='127.0.0.1', port='5432')conn.autocommit = True# creating a cursorcursor = conn.cursor()# list of rows to be insertedvalues = [17, 'samuel', 95]# executing the sql statementcursor.execute("INSERT INTO classroom VALUES(%s,%s,%s) ", values)# select statement to display outputsql1 = '''select * from classroom;'''# executing sql statementcursor.execute(sql1)# fetching rowsfor i in cursor.fetchall(): print(i)# committing changesconn.commit()# closing connectionconn.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 packagesimport psycopg2import pandas as pdfrom sqlalchemy import create_engine# creating a connectiondb = create_engine(conn_string)conn = db.connect()#creating a tablesql = '''CREATE TABLE details(Name char(20), Age int);'''# initialise data of lists.data = {'Name':['sam', 'richie', 'harry'], 'Age':[18, 20, 19]}# Create DataFramedf = pd.DataFrame(data)df.to_sql('data', con=conn, if_exists='replace', index=False)conn = psycopg2.connect(conn_string )conn.autocommit = Truecursor = conn.cursor()# fetching datasql1='''select * from data;'''cursor.execute(sql1)for i in cursor.fetchall(): print(i)# conn.commit()conn.close() |
Output:
('sam', 18)
('richie', 20)
('harry', 19)
