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:
Python Psycopg2 – Insert multiple rows with one query
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.