This article is about inserting multiple rows in our table of a specified database with one query. There are multiple ways of executing this task, let’s see how we can do it from the below approaches.
Method 1: Inserting Values through Naive method
In this method, we import the psycopg2 package and form a connection using the psycopg2.connect() method, we connect to the ‘Classroom’ database. after forming a connection we create a cursor using the connect().cursor() method, it’ll help us fetch rows. after that we execute the insert SQL statement, which is of the form :
insert into table_name ( column1, column2, …. column_n) values (…) (…) (…) ;
The SQL statement is executed by cursor.execute() method. we fetch rows using the cursor.fetchall() method.
CSV Used:
Example:
Python3
# importing packagesimport psycopg2# forming connectionconn = psycopg2.connect( database="Classroom", user='postgres', password='pass', host='127.0.0.1', port='5432')conn.autocommit = True# creating a cursorcursor = conn.cursor()# sql statement to be executedsql = '''insert into classroom(enrollment_no, name , science_marks)values(12, 'sarah', 90),(13,'Ray',81); '''# executing the sql statementcursor.execute(sql)# 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:
Output in Python:
(4, 'Linnett', 79) (5, 'Jayden', 45) (6, 'Sam', 63) (7, 'Zooey', 82) (8, 'Robb', 97) (9, 'Jon', 38) (10, 'Sansa', 54) (11, 'Arya', 78) (12, 'sarah', 90) (13, 'Ray', 81)
Method 2: Inserting Values through cursor.mogrify()
The code is the same as the previous example, but the difference is cursor.mogrify() method.
cursor.mogrify() method:
After the arguments have been bound, return a query string. The string returned is the same as what would be sent to the database if you used the execute() method or anything similar. The string that is returned is always a bytes string and this is faster than executemany() method.
Syntax:
cur.mogrify(“INSERT INTO test (col) VALUES (%s, %s….)”, (val1, val2,…)(……))
cursor.mogrify() returns a bytes string but we want it to be in string format so we just need to decode the result of mogrify back to a string by using the decode(‘utf-8’) hack.
Example:
Python3
# importing packagesimport psycopg2# forming connectionconn = psycopg2.connect( database="Classroom", user='postgres', password='pass', host='127.0.0.1', port='5432')conn.autocommit = True# creating a cursorcursor = conn.cursor()# list of rows to be insertedvalues = [(14, 'Ian', 78), (15, 'John', 88), (16, 'Peter', 92)]# cursor.mogrify() to insert multiple valuesargs = ','.join(cursor.mogrify("(%s,%s,%s)", i).decode('utf-8') for i in values)# executing the sql statementcursor.execute("INSERT INTO classroom VALUES " + (args))# 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:
Method 3: Inserting Values through executemany() method
The approach of this example is the same as before but instead of using cursor.mogrify() we use cursor.executemany() method. executemany() is slower compared to mogrify() method.
executemany():
It is used to Apply a database action (query or command) to all parameter tuples or mappings in the vars list sequence. The function is especially useful for database update instructions because it discards any result set produced by the query. The query’s parameters are bound using the same principles as the execute() function.
Syntax
executemany(query, variable_list)
Example:
Python3
# importing packagesimport psycopg2# forming connectionconn = psycopg2.connect( database="Classroom", user='postgres', password='pass', host='127.0.0.1', port='5432')conn.autocommit = True# creating a cursorcursor = conn.cursor()# list of rows to be insertedvalues = [(17, 'rachel', 67), (18, 'ross', 79), (19, 'nick', 95)]# executing the sql statementcursor.executemany("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:
