cursor execute in python sqlite3 with code examples

Introduction:

Python is an object-oriented programming language that is widely used in software development, data science, artificial intelligence, and machine learning. SQLite3 is a lightweight, compact, and efficient relational database management system. Cursor execute in Python SQLite3 is a statement that allows you to execute SQL queries in SQLite databases using Python.

This article provides an in-depth guide to cursor execute in Python SQLite3 with code examples. It covers everything from how to connect to a SQLite3 database, how to create a cursor, and how to execute various types of SQL queries.

Connecting to a SQLite3 Database:

Before you can execute SQL queries in a SQLite3 database, you need to connect to the database using Python. You can do this using the sqlite3 library that comes standard with Python. The first step is to import the sqlite3 library as shown below:

import sqlite3

Once you have imported the sqlite3 library, you can connect to the SQLite3 database using the connect() function. The connect() function takes the name of the database file as its argument. If the database file does not exist, it will be created automatically.

conn = sqlite3.connect('example.db')

Once you have connected to the database, you can create a cursor object using the cursor() function as shown below:

cursor = conn.cursor()

Executing SQL Queries:

After you have created a cursor, you can execute SQL queries in the SQLite3 database using the execute() function. The execute() function takes an SQL query string as its argument.

To execute a simple SQL query that returns all rows from a table, you can use the following code example:

cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
for row in rows:
    print(row)

In the above code example, we execute the SELECT statement that selects all rows from the employees table. We then fetch all the rows using the fetchall() function and iterate over each row to print the row data.

Executing Parameterized SQL Queries:

Parameterized SQL queries are SQL queries that have placeholders for the variables that will be provided at runtime. Parameterized SQL queries make it easier and safer to insert data into the database.

To execute a parameterized SQL query, you can use the following code example:

name = 'John'
age = 25
cursor.execute("INSERT INTO employees (name, age) VALUES (?, ?)", (name, age))

In the above code example, we execute an INSERT statement that inserts the name and age data into the employees table. We use placeholders ? for the name and age data that will be provided at runtime.

Executing SQL Transactions:

SQL transactions are a series of SQL statements that are executed together as a single unit. SQL transactions ensure that the database remains consistent and that data is not lost or corrupted due to errors or crashes.

To execute an SQL transaction, you can use the following code example:

try:
    cursor.execute("BEGIN")
    cursor.execute("INSERT INTO employees (name, age) VALUES ('John', 25)")
    cursor.execute("INSERT INTO employees (name, age) VALUES ('Mary', 35)")
    cursor.execute("COMMIT")
except:
    cursor.execute("ROLLBACK")

In the above code example, we execute a series of INSERT statements that insert data into the employees table. We wrap the INSERT statements between a BEGIN and COMMIT statement to specify that this is an SQL transaction. If any of the INSERT statements fail, we execute the ROLLBACK statement to cancel the transaction.

Conclusion:

In conclusion, the cursor execute function in Python SQLite3 allows you to execute SQL queries in SQLite databases using Python. This article covered how to connect to a SQLite3 database, create a cursor, and execute various types of SQL queries using code examples. With this knowledge, you can start building powerful database-driven applications using Python and SQLite3.

Connecting to a SQLite3 Database:

To connect to a SQLite3 database using Python, we need to use the sqlite3 library that comes with Python. The first step is to import the sqlite3 library using the import statement:

import sqlite3

Once we have imported the sqlite3 library, we can connect to the SQLite3 database using the connect function of sqlite3 module. The connect function takes a string argument, which is the path to the SQLite3 database file.

conn = sqlite3.connect('example.db')

The above code creates a connection object named conn. If the SQLite3 file doesn't exist, it will create a new one with the given file name. The .connect() function will return a connection object.

Creating a Cursor Object:

After establishing a connection to the database, we need to create a cursor object using the cursor method of the connection object. The cursor object allows us to execute SQL queries on the database.

cursor = conn.cursor()

Executing SQL Queries:

Once we have created a cursor object, we can execute SQL queries using the execute method of the cursor object. The execute method executes a single SQL statement. To execute a multi-line SQL statement, we can add a triple-quote before and after the statement or split the statement with the semicolon ;.

query = '''SELECT * from students WHERE first_name = 'John';'''
cursor.execute(query)

Fetching Data:

After executing a query, we can retrieve data using the cursor object. There are three methods to retrieve data from the database, they are fetchone, fetchmany, and fetchall.

fetchone: This method retrieves the next row of the query result as a tuple. If there is no more row left in the result set, it returns an empty tuple.

row = cursor.fetchone()

fetchmany: This method retrieves the specified number of rows of the query result as a list of tuples. If there is no more row left in the result set, it returns an empty list.

rows = cursor.fetchmany(2) # retrieves the first two rows of the result set

fetchall: This method retrieves all the remaining rows of the query result as a list of tuples. If there is no more row left in the result set, it returns an empty list.

rows = cursor.fetchall()

Executing Parameterized SQL Queries:

Parameterized SQL queries are used to prevent SQL injection attacks and to execute a query repeatedly with different values efficiently. In parameterized SQL queries, we use placeholders ? for the values we need to pass.

We can pass values to the query using a tuple or a dictionary.

Tuples

name = 'David'
age = 27
query = '''SELECT * from students WHERE first_name = ? AND age = ?;'''
cursor.execute(query, (name, age))

Dictionaries

data = {'name': 'David', 'age': 27}
query = '''SELECT * from students WHERE first_name = :name AND age = :age;'''
cursor.execute(query, data)

Executing SQL Transactions:

Transactions are used in databases to ensure that multiple operations that are performed together as a single unit succeed or fail together. The SQLite3 database system automatically commits a transaction when a command is executed. To explicitly specify the start and end of a transaction, we use the BEGIN, COMMIT, and ROLLBACK statements.

try:
    cursor.execute("BEGIN")
    cursor.execute("INSERT INTO students VALUES ('Jenny', 'Brown', 26)")
    cursor.execute("INSERT INTO students VALUES ('Jack', 'White', 28)")
    cursor.execute("COMMIT")
except:
    cursor.execute("ROLLBACK")

In the above code block, we have started a transaction using the BEGIN statement. We have executed two INSERT statements using the cursor object. If one of the INSERT statements failed, we catch the exception and undo the transaction using the ROLLBACK statement. If both the INSERT statements completed successfully, we commit the changes to the database using the COMMIT statement.

cursor execute in python sqlite3 with code examples

Popular questions

Tag

Leave a Reply Cancel reply

Popular questions

Tag

Leave a Reply Cancel reply

Related Posts