Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

How to Execute many SQLite Statements in Python?

  • Last Updated : 30 Jun, 2021

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.

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course

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




My Personal Notes arrow_drop_up
Recommended Articles
Page :