Open In App

How to Execute many SQLite Statements in Python?

Improve
Improve
Like Article
Like
Save
Share
Report

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().



Last Updated : 30 Jun, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads