In SQLite using the executescript() method, we can execute multiple SQL statements/queries at once. The basic execute() method allows us to only accept one query at a time, so when you need to execute several queries we need to arrange them like a script and pass that script to the executescript() method.
executescript() can be able to execute series of several SQL/SQLite queries in a row. While executescript() operation is running, the other threads/operations are not able to access the database until executescript() has finished executing all its queries. In case we need to provide some runtime parameter for each of the queries in executescript() we cannot add an external parameter at runtime as executescript() method doesn’t accept any parameter for the query in runtime so you need to give a set of static queries as a script. In the execution of executescript() method, first, it issues a COMMIT statement and then executes the SQL/SQLite script.
Syntax:
cursor_obj.executescript("""
SQLite Statement/Query . . . 1
SQLite Statement/Query . . . 2
SQLite Statement/Query . . . 3
.
.
.
SQLite Statement/Query . . . n
""")
The below code shows how to execute multiple SQLite statements/queries in python:
Python3
import sqlite3 # make the database connection and cursor object connection = sqlite3.connect("CollegeData.db") cursor = connection.cursor() # create a set of queries in executescript() # below set of queries will create and insert # data into table cursor.executescript(""" CREATE TABLE department( deptId INTEGER, deptName VARCHAR(20), deptScore INTEGER); INSERT INTO department VALUES ( 01,'IT', 850 ); INSERT INTO department VALUES ( 02,'COMP', 840 ); INSERT INTO department VALUES ( 03,'CIVIL', 500 ); INSERT INTO department VALUES ( 04,'E&TC', 650 ); """) # fetch the table data print("Table data :") cursor.execute("SELECT * FROM department") print(cursor.fetchall()) # below set of queries will update the data # of in the table cursor.executescript(""" UPDATE department set deptScore = 900 where deptId = 01; UPDATE department set deptScore = 890 where deptId = 02; UPDATE department set deptScore = 660 where deptId = 03; UPDATE department set deptScore = 790 where deptId = 04; """) # fetch the table data after updation print("Table data after updation :") cursor.execute("SELECT * FROM department") print(cursor.fetchall()) # commit the changes and close the database # connection connection.commit() connection.close() |
Output
Table data :
[(1, ‘IT’, 850), (2, ‘COMP’, 840), (3, ‘CIVIL’, 500), (4, ‘E&TC’, 650)]
Table data after updation :
[(1, ‘IT’, 900), (2, ‘COMP’, 890), (3, ‘CIVIL’, 660), (4, ‘E&TC’, 790)]
The above code executes multiple SQLite statements at once. The first executescript() method in the code creates and inserts the data into a table in one instance. And then second executescript() method updates all the records in one instance. In this way, a set of many queries can be executed in SQLite using executescript().
