Python SQLite connection.excutescript() Function
The Python connection.executescript() function executes the provided SQL script from the connection object. This function is used for specifying database and batch operations. It can handle multiple statements for batch processing.
A Connection object represents the database and allows us to interact with the database by executing SQL commands and managing transactions in the database.
Execute is part of the connection object in SQLite. It takes a single argument containing one or more SQL statements separated by semicolons and executes all the SQL statements in the string one by one.
Syntax
Following is the syntax for the connection.executescript() function.
connection.executescript(sql_script)
Parameters
A string containing one or more SQL statements is separated by semicolons, and this script can include any SQL commands.
Return Value
This function returns None after executing the provided SQL script.
Example
Consider the following EMPLOYEES table, which stores employees ID, Name, Age, Salary, City and Country −
| ID | Name | Age | Salary | City | Country |
|---|---|---|---|---|---|
| 1 | Ramesh | 32 | 2000.00 | Maryland | USA |
| 2 | Mukesh | 40 | 5000.00 | New York | USA |
| 3 | Sumit | 45 | 4500.00 | Muscat | Oman |
| 4 | Kaushik | 25 | 2500.00 | Kolkata | India |
| 5 | Hardik | 29 | 3500.00 | Bhopal | India |
| 6 | Komal | 38 | 3500.00 | Saharanpur | India |
| 7 | Ayush | 25 | 3500.00 | Delhi | India |
Example 1
Consider the above table, this represents employees from India who are 30 years old or younger, using the connection.executescript() function.
import sqlite3
conn = sqlite3.connect('res.db')
conn.executescript("""
UPDATE employees SET Salary = Salary * 2.2 WHERE Country = 'India';
DELETE FROM employees WHERE Age > 35;
""")
conn.commit()
conn.close()
Output
The result is obtained as follows −
| ID | Name | Age | Salary | City | Country |
|---|---|---|---|---|---|
| 1 | Ramesh | 32 | 2000.00 | Maryland | USA |
| 4 | Kaushik | 25 | 2500.00 | Kolkata | India |
| 5 | Hardik | 29 | 3500.00 | Bhopal | India |
| 7 | Ayush | 25 | 3500.00 | Delhi | India |
Example 2
In the below example, we are deleting multiple rows from the SQLite table using the connection.executescript() function.
import sqlite3
conn = sqlite3.connect('res.db')
conn.executescript("""
DELETE FROM employees WHERE Salary < 4000;
""")
conn.commit()
conn.close()
Output
When we run the above code, we will get the following result −
| ID | Name | Age | Salary | City | Country |
|---|---|---|---|---|---|
| 2 | Mukesh | 40 | 5000.00 | New York | USA |
| 3 | Sumit | 45 | 4500.00 | Muscat | Oman |
Example 3
Here, this example inserts a new row into the employee table using connection.executescript() function.
import sqlite3
conn = sqlite3.connect('res.db')
conn.executescript("""
INSERT INTO employees (ID, Name, Age, Salary, City, Country) VALUES (8, 'Sharon', 22, 4300.00, 'Mumbai', 'India');
""")
conn.commit()
conn.close()
Output
The result is produced as follows −
(8, 'Sharon', 22, 4300.00, 'Mumbai', 'India')
Example 4
In this example, a list is passed to this connection.executescript function, which throws a TypeError because the expected datatype is not given to this function.
import sqlite3
conn = sqlite3.connect(':data:')
conn.executescript("CREATE TABLE employees (ID INTEGER, Name TEXT);")
conn.executescript(["INSERT INTO employees VALUES(2, 'Mukesh');"])
Output
We will get the result as follows −
TypeError: executescript() argument 2 must be str, not list