Python With SQL Databases - Full Crash Course | Python Database Connectivity Tutorial

SQLite is a popular, open-source, embedded relational database management system that is used in many applications and devices. It is lightweight, easy to use, and requires minimal setup, making it a good choice for storing and manipulating data in Python.

In this tutorial, we will explore how to connect Python with SQLite, the most widely deployed SQL database engine in the world. We will cover everything from installation and setup to basic CRUD operations (create, read, update, delete) and more advanced concepts such as prepared statements and transaction management.

To get started, you will need to have Python and SQLite installed on your computer. If you do not already have Python installed, you can download it from the official Python website (https://www.python.org/). For this tutorial, we will be using Python 3.9, but earlier versions should work as well.

SQLite is included with Python by default, so you do not need to install it separately. You can access the SQLite API in Python through the sqlite3 module, which is part of the standard library.

To connect to an SQLite database in Python, we can use the connect() function of the sqlite3 module, which takes the name of the database file as an argument. If the file does not exist, it will be created automatically.

import sqlite3

# Connect to the database
cnx = sqlite3.connect("my_database.db")

With the connection established, we can now start executing SQL queries. To do this, we can use the cursor() method of the connection object to create a new cursor, which allows us to execute SQL commands and retrieve results.

# Create a cursor
cursor = cnx.cursor()

# Execute a SQL query
cursor.execute("SELECT * FROM users")

# Fetch the results
results = cursor.fetchall()

# Print the results
print(results)

This code will execute a SELECT * FROM users query on the users table and print the results to the console.

In addition to basic CRUD operations (create, read, update, delete), we can also use the execute() method to perform other operations such as INSERT, UPDATE, and DELETE. For example, the following code will insert a new record into the users table:

# Insert a new record
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("John Smith", "john@example.com"))

# Commit the changes to the database
cnx.commit()

It is also possible to use prepared statements to protect against SQL injection attacks and improve performance. A prepared statement is a SQL query that is pre-compiled and stored in the database server, allowing it to be reused multiple times with different parameter values.

To use prepared statements in Python, we can use the execute() method with placeholders (represented by ?) for the parameter values. The placeholders are then replaced with the actual values when the statement is executed.

# Create a prepared statement
stmt = "INSERT INTO users (name, email) VALUES (?, ?)"

# Execute the prepared statement with different parameter values
cursor.execute(stmt, ("Bob Smith", "bob@example.com"))
cursor.execute(stmt, ("Alice Smith", "alice@example.com"))

# Commit the changes to the database
cnx.commit()

Finally, it is important to properly manage transactions when working with databases. A transaction is a sequence of SQL statements that are treated as a single unit of work. Either all of the statements in the transaction are executed, or none of them are, ensuring the integrity of the data.

To use transactions in Python, we can use the autocommit attribute of the connection object. By default, this attribute is set to True, which means that each SQL statement is committed automatically. To use transactions, we can set autocommit to False and use the commit() and rollback() methods to control the transaction.

# Disable autocommit
cnx.autocommit = False

try:
    # Execute multiple statements as a single transaction
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("John Smith", "john@example.com"))
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Jane Doe", "jane@example.com"))

    # Commit the changes
    cnx.commit()
except Exception as e:
    # Rollback the changes on error
    cnx.rollback()

# Enable autocommit
cnx.autocommit = True

This code will insert two records into the users table as a single transaction. If any of the statements fail, the entire transaction will be rolled back and the data will remain unchanged.

I hope this tutorial has helped you understand the basics of connecting Python with SQLite databases. For more information and advanced topics, you can refer to the official documentation and other online resources.