Working with SQL Databases in Python: A Detailed Guide

Technical writer

Python SQL

17.07.2024

Reading time: 12 min

In today's world, where the volume of information is rapidly growing, its effective processing becomes a key point in any field of human activity. In this context, databases act as a repository, allowing information not only to be stored but also to be managed efficiently. In the tutorial below, we will learn how to work with databases in Python.

SQL (Structured Query Language) plays a significant role in the organization of databases. This query language provides a convenient way to interact with data, allowing you to create, modify, and retrieve information. The combination of Python and SQL provides convenient tools for automating processes and will enable developers to create flexible and scalable applications.

Working with SQL in Python is done through database management systems (DBMS). There are many DBMS, each with its own characteristics and purposes. From classical relational databases such as MySQL and PostgreSQL to NoSQL solutions such as MongoDB, the choice of a suitable DBMS depends on the specific project requirements. In this guide, we will look at systems such as:

SQLite
MySQL
PostgreSQL

In this guide, we will go through the key steps of interacting with SQL databases in Python, providing detailed instructions on how to use basic operations with examples from different DBMS. Let's start with installing the libraries and connecting to the database.

Installing the Libraries

Before working with SQL databases in Python, you need to install the appropriate libraries. Each database has its own package; let's look at some of them.

To work with SQLite, you need the sqlite3 library. This library is included in the standard Python library, so there's no need to install it separately. However, for working with MySQL and PostgreSQL, we need to install the libraries.

For MySQL:

pip install mysql-connector-python

For PostgreSQL:

pip install psycopg2

A general way to install for most databases:

pip install SQLAlchemy

The SQLAlchemy library provides an abstraction layer over various DBMS, allowing developers to interact with different systems without changing the core code. Additionally, it includes an ORM (Object-Relational Mapping) mechanism, which is necessary for working with databases by representing information as Python objects. Instead of writing direct SQL queries, you can interact with the database using Python objects that map to tables in the database.

After installing the appropriate libraries, we will be able to establish a connection to the SQL database directly from the Python runtime environment.

Connecting to the Database

Establishing a connection to the SQL database is an important step before starting to work with the information repository. This step ensures interaction between your Python code and the physical information storage. The process of connecting depends on the DBMS used.

For interacting with SQLite, you need to import the sqlite3 library and then connect to the database stored in a file:

import sqlite3 as sq
connection = sq.connect('your_database_name.db')

If the database file does not exist, it will be created automatically.

For interacting with MySQL in Python, the mysql.connector library is used. Here is an example of using this library to establish a connection:

import mysql.connector as con

connection = con.connect(
      host='your_actual_host',
      user='your_actual_user',
      password='your_actual_password',
      database='your_actual_database'
)

In this example:

your_actual_host — your MySQL host address;
your_actual_user — your username for database access;
your_actual_password — your user password;
your_actual_database — the name of the database you want to interact with.

For interacting with PostgreSQL in Python, the psycopg2 library is used. Here is an example of using psycopg2 to create a connection:

import psycopg2 as ps

connection = ps.connect(
     host='your_actual_host',
     user='your_actual_user',
     password='your_actual_password',
     database='your_actual_database'
)

When using SQLAlchemy, the connection can be established as follows:

from sqlalchemy import create_engine

database_url = 'sqlite:///your_database.db'
engine = create_engine(database_url)

If you use SQLAlchemy, the URL is a standard way of representing connection parameters. Depending on the type of database, it can include information about the host, user, password, port, and other parameters.

After successfully creating the connection, the next step is to create a cursor object. The cursor provides the program with an interface to move through the results of a query, retrieve information, and make changes to records. This step is independent of the database used, as it provides a common interface for executing SQL queries:

cursor = connection.cursor()

Now everything is ready to execute SQL queries in Python and interact with the database.

When you finish working with the cursor, for example, after performing all operations, it is important to close both the cursor and the connection:

cursor.close()
connection.close()

This is critical for efficient resource management and preventing memory leaks.

After successfully establishing a connection to the database, you are ready to start executing SQL queries and various operations.

Creating Tables and Schemas in DBMS

Before interacting with the database, you will need to create an SQL table in Python. Tables are entities where each row corresponds to a specific record, and columns define various attributes of this record. This is an important stage of database design, where the structure and types of records that will be stored in the database are defined.

Here is an example of creating a table for SQLite:

products_tb = '''
CREATE TABLE IF NOT EXISTS products (
     product_id INTEGER PRIMARY KEY AUTOINCREMENT,
     product_nm TEXT NOT NULL,
     price REAL CHECK (price >= 0),
     stock_quantity INTEGER CHECK(stock_quantity >= 0)
)
'''

This SQL query defines the products table with fields:

product_id, a unique product identifier serving as the primary key;
product_nm, a text field for the product name. NOT NULL indicates that this field must contain information;
price, the product price, which can contain decimal values;
stock_quantity, the quantity of products in stock, this field can only contain integers.

The combination of all these fields creates a table where each record (row) represents a separate product, and each column contains information about that product.

After creating the table, you need to execute the SQL query in Python and apply the changes:

cursor.execute(products_tb)
connection.commit()

After completing these steps, you will have a products table created in the SQLite database, ready for use. We can open the database in an SQLite database browser, such as DB Browser for SQLite, and see the created table.

Note that creating a table needs to be done only once, which is why there is a check IF NOT EXISTS.

Similarly, here is an SQL query to create a table for MySQL:

products_tb = '''
CREATE TABLE IF NOT EXISTS products (
     product_id INT AUTO_INCREMENT PRIMARY KEY,
     product_nm VARCHAR(255) NOT NULL,
     price DECIMAL(10, 2),
     stock_quantity INT
)
'''

cursor.execute(products_tb)

connection.commit()

And here is the creation of the products table in PostgreSQL:

products_tb = '''
    CREATE TABLE IF NOT EXISTS products (
        product_id SERIAL PRIMARY KEY,
        product_nm VARCHAR(255) NOT NULL,
        price DECIMAL(10, 2),
        stock_quantity INT
    );
'''

cursor.execute(products_tb)

connection.commit()

All these examples demonstrate creating tables for different DBMS. You can adapt the SQL queries depending on your database structure requirements.

Executing SQL Queries with Python

After successfully creating tables, we proceed to perform basic operations known as CRUD (Create, Read, Update, Delete). These operations allow us to add, read, update, and delete information in the database. Let’s delve into each of these operations in detail.

CREATE Operation

The CREATE operation in SQL is responsible for adding new records to a table. We use the INSERT statement to add records by specifying values for each column. Here are examples of adding a new record for each database:

Add information about a new product to the table we created in the previous section:

new_product = ('Laptop', 99.999, 10)

SQL query to add records for SQLite:

request_to_insert_data = '''
INSERT INTO products (product_nm, price, stock_quantity) VALUES (?, ?, ?);
'''

SQL query to add records for MySQL and PostgreSQL:

request_to_insert_data = '''
INSERT INTO products (product_nm, price, stock_quantity) VALUES (%s, %s, %s);
'''

To execute the SQL query for any database:

cursor.execute(request_to_insert_data, new_product)
connection.commit()

The cursor.execute() command executes the query using the given SQL statement and data, adding a new record to the database. The connection.commit() command applies the changes.

READ Operation

The READ operation retrieves data from the table. We use the SELECT statement to perform this procedure. The syntax is the same for all three databases (SQLite, MySQL, and PostgreSQL):

request_to_read_data = "SELECT * FROM products"

cursor.execute(request_to_read_data)

data = cursor.fetchall()

for row in data:
     print(data)

This code forms an SQL query to select all data from the products table, executes the query with the cursor, fetches the result, and prints it.

UPDATE Operation

The UPDATE operation modifies existing records using the UPDATE statement. Let’s change the price of the product with product_id = 1 to a new value of 109.99:

new_price = 109.99
product_id_to_update = 1

SQL query for updating records for SQLite:

request_to_update_data = "UPDATE products SET price = ? WHERE product_id = ?"

cursor.execute(request_to_update_data, (new_price, product_id_to_update))

connection.commit()

SQL query for updating records for MySQL and PostgreSQL:

request_to_update_data = "UPDATE products SET price = %s WHERE product_id = %s"

cursor.execute(request_to_update_data, (new_price, product_id_to_update))

connection.commit()

DELETE Operation

The DELETE operation removes records from the table using the DELETE statement.

Example for SQLite:

product_id_to_delete = 1
request_to_delete_data = "DELETE FROM products WHERE product_id = ?"
cursor.execute(request_to_delete_data, (product_id_to_delete,))

For MySQL and PostgreSQL:

product_id_to_delete = 1
request_to_delete_data = "DELETE FROM products WHERE product_id = %s"
cursor.execute(request_to_delete_data, (product_id_to_delete,))

connection.commit()

As previously mentioned, it is essential to terminate the interaction with the database correctly. After completing all CRUD operations, remember to close the cursor and connection:

cursor.close()
connection.close()

Database Search Optimization

Indexes are data structures associated with values in one or more columns of a table. They provide fast access to specific values, significantly improving query performance. Creating an index adds a data structure to the database, representing a sorted list of unique values of selected columns. This reduces the number of rows to search.

Creating an index for optimizing the search by the product_nm column in the products table:

request_to_index = "CREATE INDEX idx_product_nm ON products (product_nm(255))"
cursor.execute(request_to_index)
connection.commit()

Ensuring Data Integrity

Transactions ensure data integrity. They guarantee that a series of operations are executed atomically, meaning all changes are applied successfully, or none are applied at all. Here’s how to work with transactions in SQLite:

try:
	update_query = "UPDATE products SET price = ? WHERE product_id = ?"
	new_price = 123.456
	product_id_to_update = 1
	cursor.execute(update_query, (new_price, product_id_to_update))
	
	connection.commit()
	
	print("Transaction completed successfully.")

except Exception as e:
	connection.rollback()
	print(f"Error occurred: {str(e)} Transaction is rolled back.")

finally:
	connection.close()

This example uses a try-except-finally block to handle transactions. If any operation in the try block raises an exception, the transaction is rolled back using connection.rollback(). Otherwise, changes are committed with commit().

Automating Reactions to Events

Triggers are a unique type of stored procedures that automatically activate when certain events occur in the database. They automate reactions to data changes, such as inserting new records, updating existing ones, or deleting records.

In SQLite, triggers can be activated for INSERT, UPDATE, and DELETE events. For example, we have an orders table with fields order_id, product_nm, and quantity, and we want to create a trigger that decreases the inventory in the inventory table each time a new order is added:

trigger_request = '''
CREATE TRIGGER decrease_inventory
AFTER INSERT ON orders
BEGIN
     UPDATE inventory
     SET stock_quantity = stock_quantity - NEW.quantity
     WHERE product_nm = NEW.product_nm;
END;
'''

In MySQL, the syntax for a similar trigger is the same as in SQLite, except for the FOR EACH ROW keyword:

trigger_request = '''
CREATE TRIGGER decrease_inventory
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
     UPDATE inventory
     SET stock_quantity = stock_quantity - NEW.quantity
     WHERE product_nm = NEW.product_nm;
END;
'''

In PostgreSQL, the syntax differs slightly. First, create a function for the trigger:

function_request = '''
CREATE OR REPLACE FUNCTION decrease_inventory()
RETURNS TRIGGER AS $$
BEGIN
     UPDATE inventory
     SET stock_quantity = stock_quantity - NEW.quantity
     WHERE product_nm = NEW.product_nm;
     RETURN NEW;
END;
$$ LANGUAGE plpgsql;
'''

Then create the trigger itself:

trigger_request = '''
CREATE TRIGGER decrease_inventory
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION decrease_inventory();
'''

Conclusion

In this guide, we explored the basics of interacting with databases using Python. You now know how to use Python to connect to an SQL database, create tables, and use CRUD operations to manage data effectively. We also covered indexing, transactions, and using triggers for automation.

This guide serves as an introduction to SQL with Python. Next steps might include learning more complex queries, optimizing performance, and using advanced features specific to each DBMS.