How to Connect and Work with a SQL Database in Python

Relational databases, or SQL databases, are the backbone of most modern application’s data infrastructure. Utilizing the data stored in these databases, you can produce actionable insights that can help you improve business performance.

Although there are multiple ways in which you can perform analytics, Python offers enhanced flexibility to perform advanced data transformations. If you are having doubts about how to use Python for SQL databases, you have come to the right place.

This article will comprehensively discuss the process of using SQL database in Python, highlighting the steps to optimize performance when working with large datasets.

Why Python Is Ideal for SQL Database Operations?

An extensive set of libraries makes Python a popular choice for performing SQL operations, especially in use cases requiring data analysis and engineering tasks. Some of the most common analytics libraries Python offers include Pandas, NumPy, Matplotlib, scikit-learn, and TensorFlow. These libraries allow you to model and visualize complex datasets.

To leverage these libraries, you must first integrate data from the required source into the Python environment. Most organizations use relational databases like MySQL, Oracle, PostgreSQL, and more to store data efficiently. Python supports various database drivers that you can import into your local system to connect and work with these databases.

Due to its simplicity in handling data, you can efficiently perform create, read, update, and delete operations on your database.

When to Use SQL Database with Python?

SQL databases are commonly used with Python when you require robust data storage, transformation, and analytics capabilities. Here are a few use cases for Python-SQL integration:

• ETL Operations: With the powerful features of Python, you can extract and transform raw data from different sources, including CRMs and ERPs. This processed data can then be bulk stored in your SQL database within the same development environment.‍
• Advanced Analytics: You can also use Python to extract data from your SQL database and perform advanced analytics to produce actionable insights.‍
• Robust Data Structures: By leveraging various Python data structures, you can organize and store data for efficient manipulation and enhanced accessibility.‍
• AI Application Development: Using SQL databases in Python makes it easier for you to train large language models on your stored organizational data.

How to Connect to SQL Database Using Python?

In order to connect to a SQL database in Python, here are the steps you must follow:

Installing Libraries

First, you must install the necessary libraries to work with your preferred SQL database. For example, if your task requires you to work with Pandas and MySQL, execute the following code in your terminal:

pip install mysql-connector-python
pip install pandas

Importing Necessary Libraries

After installation, you can now import the SQL library with other necessary libraries like Pandas, which will assist you in working with the data.

import mysql.connector
import pandas

Connecting to SQL Database 

Now, inside your code editor, connect to the MySQL server using the given code:

def create_server_connection(host_name, user_name, user_password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

Calling the create_server_connection function with proper host_name, user_name, and user_password arguments establishes the connection to the MySQL server.

Creating an SQL Database

After connecting to the MySQL server, you must create a SQL database to store data. To accomplish this, execute the following code:

def create_database(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as err:
        print(f"Error: '{err}'")

If you need to create a new database, you can call the create_database function with:

• connection as create_server_connection function’s result.‍
• query as CREATE DATABASE <placeholder>. Remove the <placeholder> with a database name.

Connecting to SQL Database

After creating a database, you can now learn how to connect SQL database in Python and start working with it. To establish a database connection, execute the given code:

def create_db_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

By calling the create_db_connection with appropriate arguments, you can create a connection to the SQL database in a Python environment.

Create a Query Execution Function

Writing execution code for every single query can increase redundancy when working with an SQL database in Python. As you might be performing multiple queries on data, it is often considered beneficial to define a query execution function. This function will enable you to run SQL commands that are stored as strings in Python.

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Error as err:
        print(f"Error: '{err}'")

You can call this function with connection and query arguments, outlining the database connection and the query you want to execute. The connection.commit() method ensures the execution of SQL commands.

Performing Basic SQL Operations with Python

After performing all the prerequisites of creating a connection with the SQL database, it’s time to perform some basic operations with Python. This section describes the most crucial database operations performed for modern applications.

Creating Tables

To create a table in your database, you can write a query using the CREATE TABLE command and store the query as a string in a variable. For example, you can create a customer table by executing the code below:

create_customer_table = """
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(40) NOT NULL,
    last_name VARCHAR(40) NOT NULL,
    address VARCHAR(100) NOT NULL,
    dob DATE,
    phone_no VARCHAR(20)
    );
"""
connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, create_customer_table)

By running this code, you can create a table ‘customers’ in your database with multiple columns for storing customer details. Ensure you replace the credentials with your database details. Similarly, you can create multiple tables, like products, orders, etc., and define data relationships between these tables using the primary and foreign keys.

CRUD Operations

Persistent data storage and retrieval involves four key operations: create, read, update, and delete (CRUD).

• Use the create operation to insert new data into a database.
• With the read operation, you can read through the data.
• The update operation allows you to upgrade the existing data record according to your requirements.
• The delete operation eliminates data rows.

To perform CRUD operations on your table, follow the next steps.

Inserting Data

You can perform create operation using the INSERT INTO statement.

customer_data = """
INSERT INTO customers VALUES
(1,  'James', 'Smith', 1540 Todds Lane, '1985-04-20', '+491774553676'),
(2, 'Stefanie',  'Martin', '1615 Hazelwood Avenue', '1970-02-17', '+491234567890'), 
(3, 'Steve', 'Wang', '4634 Camden Street', '1990-11-12', '+447840921333'),
(4, 'Friederike',  'Müller-Rossi', '389 Walnut Avenue', '1987-07-07', '+492345678901'),
(5, 'Isobel', 'Ivanova', '2217 Centennial Farm Road', '1963-05-30', '+491772635467'),
(6, 'Niamh', 'Murphy', '4661 Park Street', '1995-09-08', '+491231231232');
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, customer_data)

Executing the above query allows you to insert six data rows into the ‘customers’ table.

Read Data

With the read operation, you can extract data from an existing database table. Let’s define a function that efficiently displays data from a table.

def read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as err:
        print(f"Error: '{err}'")

This function uses the fetchall method to read data without making any changes to the existing table. As an example, to print all the rows from the ‘customers’ table, execute the code below:

q1 = """
SELECT *
FROM customers;
"""

connection = create_db_connection("localhost", "root", pw, db)
results = read_query(connection, q1)

for result in results:
  print(result)

When you have multiple tables, you can also perform read operations with advanced SQL concepts like JOIN statements. To further enhance data analytics capabilities, convert the data in Pandas DataFrame.

Update Records

If you wish to change an existing record, writing a query with the UPDATE command can be beneficial. You can update the records in the existing table by executing the update statement with the execute_query function. The WHERE command helps you update the record based on certain conditions.

update = """
UPDATE customers 
SET address = '223 Fingiertweg, 134 Berlin' 
WHERE customer_id = 2;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, update)

This code will update the address of the second customer, ‘Stefanie,’ from '1615 Hazelwood Avenue' to ‘223 Fingiertweg, 134 Berlin.’

Delete Records

Keeping the database updated might also require you to remove the customers who no longer subscribe to your products. To delete a record from the ‘customers’ table in Python, create a DELETE statement with a WHERE clause that removes the record based on your specific requirements. For example, the given code removes the third row from the table.

remove_customer = """
DELETE FROM customers 
WHERE customer_id = 3;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, remove_customer)

Change Management with SQL & Python Integration

Managing changes in a database is a crucial component for generating updated business insights. You can consider an example where a database stores records for an e-commerce website. As product ‘A’ leaves the inventory, the database must be updated by reducing the total quantity of ‘A.’ This helps keep the database up-to-date with the changes.

However, addressing changes in a table can be a challenging process and might lead to errors. To overcome the difficulties associated with change management, you can create Python scripts defining SQL objects like triggers, stored procedures, and functions. Provide these variables and connection details as arguments to the execute_query function to implement the triggers on your database table.

Let’s create a trigger to decrease the product count in the inventory based on the order information.

trigger_request = """
CREATE TRIGGER updating_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;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, trigger_request)

Before creating a custom script using these SQL objects, you must thoroughly understand the business use case and how the logic will affect your table.

Handling Large-Scale SQL Databases in Python

When you are working with large datasets, it becomes crucial to follow a few rules to get improved results. You can use specific Python libraries and methods to handle the data effectively. NumPy is a scientific computing Python library that lets you store data in a multidimensional array, which significantly reduces computational time.

Performing complex operations on large amounts of data requires advanced computational resources. Dividing large datasets into smaller chunks can be beneficial in reducing the data management challenge. The chunksize method offered by Pandas enables you to define how you wish to divide the dataset into smaller components.

Another way to handle large-scale SQL databases in Python is by utilizing database-specific methods for partitioning and caching. For instance, MySQL provides statements like PARTITION BY and a list of variables like query_cache_limit to specify caching techniques to manage large datasets.

How to Optimize Queries When Using Python?

By following a set of best practices, you can optimize queries for enhanced performance. Let’s look at a few of them:

• Selecting the right database engine is the first and most important step. Assessing factors like scalability, reliability, security, and compatibility can help you choose the best-fitting database for your application.
• With Object-relational mapping (ORM) tools, you can interact with databases using Python objects instead of SQL queries. With features like lazy loading, connection pooling, batch operations, and query caching, ORM tools simplify and automate database management tasks.
• Indexing can improve performance for frequently used columns and expressions by reducing scan time and enhancing data quality.
• Another way to optimize query performance in Python is by writing effective queries. For example, instead of using SELECT *, you can specify specific data columns and use the WHERE command to select a subset of data rows. You can use LIMIT to reduce the amount of data to be queried.

Challenges You Might Face While Connecting SQL Database to Python & Solutions

Although there are multiple benefits of using Python with SQL databases, you must also consider the drawbacks that come with the advantages. Here are some limitations of working with a SQL database in Python:

• Using any SQL database driver requires a thorough availability check, as the driver might not be available for every database.
• Invalid database authentication credentials can cause configuration issues in your local Python environment.
• When updating the database, the data must have SQL-compatible data types. If the data is incompatible with the column type, the record values might not be revised, leading to data inconsistency.

Leveraging Airbyte for Simplified Data Integration

Consolidating data from various sources and inserting it into the SQL database can be a time-consuming task. Airbyte, an AI-powered integration tool, simplifies this process by allowing you to extract data from a range of sources and load it into SQL databases like Postgres, SQL Server, and more.

To speed up this process, it offers no-code UI and over 400 pre-built connectors. These connectors allow you to migrate data between different platforms. If the connector you seek is unavailable, you can build a custom connector from scratch using Airbyte’s Connector Development Kits (CDKs) and Connector Builder. Once the data is loaded into your SQL database, you can connect it with Python to perform further analysis and processing.

Key features of Airbyte:

• Schema Management: Using schema change management, you can mention how Airbyte must handle source schema changes for each connection. For cloud users, Airbyte automatically performs regular schema checks every 15 minutes and every 24 hours for self-hosted accounts.‍
• Change Data Capture (CDC): The CDC feature enables you to identify incremental changes in the source systems and automatically replicate them in the destination. With this feature, you can ensure that the data remains consistent and up-to-date.‍
• AI-Powered Connector Builder: The AI-assist functionality that comes with the Connector Builder reads through your preferred platform’s API documentation and auto-fills most configuration entries. This feature simplifies the development of connectors for you, allowing you to create connectors within minutes.‍
• PyAirbyte: As an open-source Python library, PyAirbyte allows you to extract data from multiple sources using Airbyte connectors and load it into SQL caches. These caches are compatible with most Python libraries and AI frameworks like LangChain and LlamaIndex, facilitating the development of LLM-powered applications.‍
• Vector Database Compatibility: Airbyte supports prominent vector databases, including Pinecone, Weaviate, and Milvus. You can store vector embeddings in these databases, which can then be used to train AI applications almost effortlessly.

Conclusion

Using a SQL database in Python is essential for performing advanced analytics that yield impactful insights. Python’s robust data transformation capabilities allow you to transform the extracted data into an analysis-ready format.

By considering certain best practices like partitioning and caching, you can optimize your data management workflow. However, it is also crucial to understand the challenges that might come when you use Python to manage SQL databases for better performance.