Python SQLite connection.execute() Function

The Python connection.execute() function allows us to execute SQL commands directly on the database without creating a cursor.

Connection refers to an object that represents the database. The connection object is used for executing SQL commands. This function provides methods to create a cursor, and then this closes the connection.

Execute is used to run SQL commands from the cursor object. This function takes SQL statements as its first argument and optional parameters as the second argument.

Syntax

Following is the syntax for the connection.execute() function.

connection.execute(sql[,optional parameters])

Parameter

This function contains SQL commands to be executed.

Return Value

Returns a cursor object that can be iterated from the result.

Example

Consider the following EMPLOYEES table which stores employees ID, Name, Age, Salary, City and Country −

Example 1

In the below example, we are inserting a new Employee row using connection.execute() function and then closes the connection.

import sqlite3
conn = sqlite3.connection('res.db')
conn.execute('INSERT INTO employees(Name, Age, Salary, City, Country)VALUES(?,?,?,?,?)',('Sharon', 22, 6000.0, 'Maharashtra', 'India'))
conn.commit()
conn.close()

Output

We will get the updated table from the above given values −

Example 2

Here, we are updating Kaushik's salary to 7000.0 using connection.execute() function and then we will close the database.

import sqlite3
conn = sqlite3.connect('res.db')
conn.execute('UPDATE Employees SET SAlary = ? WHERE Name = ?',(7000.0,'Kaushik'))
conn.commit()
conn.close()

Output

We will get the output as follows −

Kaushik's salary is updated to 7000.0

Example 3

Now, we are deleting the komal row from the Employees table using the connection.execute() function and the output confirms that the data has been deleted.

import sqlite3
conn = sqlite3.connect('res.db')
conn.execute('DELETE FROM employees WHERE Name = ?',('Komal'))
conn.commit()
conn.close()

Output

Output displays the updated employee table −

The record for Komal is deleted from the employees table.

Example 4

Now, we are executing an integer as an SQL statement using connection.execute() function throws a TypeError.

import sqlite3
try:
   conn = sqlite3.connect('res.db')
   conn.execute(12345)
   conn.commit()
except TypeError as e:
   print(f"TypeError:{e}")
finally:
   conn.close()

Output

When we run the above code we will get the following result −

TypeError: execute() argument 1 must be str, not int