Python Guide to Connect with SQL

Connecting Python with an SQL database is easy. First, you need to choose a database library like mysql-connector-python or sqlite3. Next, import the library into your Python code and make a connection to your database by passing details like host, user, and password.

Once the connection is established, create a cursor object to run SQL queries. With the cursor, you can execute all types of queries, for example, SELECT (read) and UPDATE (write) queries. By the way, don’t forget to commit if you make any changes in the database. Lastly, at the end of your script, don’t miss to close the connection to free up resources.

How to Use SQL with Python

Python’s flexibility makes it simple to connect with different databases, letting your apps work effortlessly with database systems. Below is a simple diagram showing how to connect Python with MySQL database.

We can get Python to work with any database. However, in this tutorial, we have provided steps to connect with the MySQL database. So, to begin, you will need a MySQL instance to use with Python. If it is not installed, then check this to install MySQL on Ubuntu. If you already have it running either on your local or remote system, then you are good to begin with the next section.

Also Read: Python MongoDB Tutorial

Steps to Connect Python With SQL Database

To connect Python with an SQL database, read and follow these steps carefully. They will guide you with what to do and help with any error you might face in between.

1. Install Database Library

Firstly, you must set up a suitable database library like mysql-connector-python, pymysql or sqlite3. You can install it using the package manager like pip. If you don’t have this tool handy, check how to install pip in Python.

Before picking on the package, first check if you already have it, mysql-connector-python. There is another package as well like: mysql-connector, but it may fail in connecting to the SQL database as we faced. So, it’s better to avoid and remove it.

pip list | grep mysql-

If the above command doesn’t list any results, run the below command to install the MySQL Python package as it works best with MySQL.

pip install mysql-connector-python

Once the above command completes, you can check whether the package is successfully installed or not. Run the below instruction by passing the package name. You can tally its output to confirm the Python MySQL package installation.

pip show mysql-connector-python

Now, it is also possible that you have installed another popular package mysql-connector. But we have seen it does not work well with some MySQL versions as happened with us while testing the scripts. So, you can remove it and add the one mentioned above. Also, if there is a mix of packages on your system, then a conflict may arise, and your script could fail with the error shown below: You would see this error when you run the Python script making a connection to the SQL database. We have not yet reached that point to run the script but it is important to mention the error here as we faced it.

AttributeError: module 'mysql.connector' has no attribute 'connect'

In such a case, clean up and re-install the correct package.

pip uninstall mysql-connector
pip uninstall mysql-connector-python
pip install mysql-connector-python

2. Import Library

Secondly, in your Python script, import the database library. The module name is the same in both the mysql-connector and mysql-connector-python packages. So, don’t worry or be confused about this. Check from below.

import mysql.connector

3. Establish Connection

Thirdly, call the SQL connect method to interact with the database. Provide necessary details such as host, user, and password to start a connection.

connection = mysql.connector.connect(
   host="db_host",
   user="db_user",
   password="db_password",
   database="db_name"
)

Our demo Python script will use “temp” as the username and password. The database name is “techbeamers” and “127.0.0.1” is the host. You can however replace the host with the “localhost”.

4. Create Cursor

Create a cursor object to execute SQL queries.

cursor = connection.cursor()

5. Execute Queries

Use the cursor to execute SQL queries.

cursor.execute("SELECT * FROM your_table")

6. Fetch Results

If your query returns results, fetch them.

results = cursor.fetchall()

7. Commit Changes

If you modify the database, remember to commit changes.

connection.commit()

8. Close Connection

Close the connection to free up resources.

connection.close()

Connecting Python with SQL databases allows seamless integration and offers efficient data management for your applications.

Python SQL Simple Connection Flow

Below is a simple sequence diagram that captures the Python method calls to make a connection with the SQL database.

Python SQL Query Execution Flow

Once Python is connected with the SQL database, you can pull a cursor object bound to the connection. With the help of this cursor, you can execute queries and fetch records from the database.

Python SQL Database Connection End-to-End Flow

Below is a sequence diagram that covers the full end-to-end flow of how Python connects with the MySQL database. It visually explains how the database connection takes place using Python. Next, it also captures requesting data from the SQL tables using cursors.

Python SQL Database Operations

So far, you must have thoroughly understood how to set up Python for connecting to a SQL database. The next step is to learn to perform different database operations that can be done using Python.

Pre-requisites

To ensure that the Python code in this tutorial runs smoothly, you have to follow some preparatory steps. However, if you are good at using MySQL / want to use a different user, then feel free to use the same and replace it in the Python code.

The first step is to create a temporary MySQL user which our Python script will use to connect to the SQL database.

-- Connect to MySQL with sudo / provide a password if needed
sudo mysql -u root

-- Create a temporary user with 'mysql_native_password' authentication
CREATE USER 'temp'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'temp';
GRANT ALL PRIVILEGES ON *.* TO 'temp'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Secondly, you will remove the temp user after your job is done. So, follow the below command to delete the user.

-- Connect to MySQL with sudo
sudo mysql -u root

-- Revoke privileges and delete the temporary user
REVOKE ALL PRIVILEGES ON *.* FROM 'temp'@'localhost';
DROP USER 'temp'@'localhost';
FLUSH PRIVILEGES;

The above steps ensure you don’t run into database connection errors when you execute the Python script. However, if it still does, let us know, and we’ll resolve it.

Now, it’s time to get everything we learned and set up to actual action – which means let’s prepare the Python code to connect to an SQL database, create tables, and run different types of queries.

1. Common Database Connection Script

To simplify database access and reduce duplication, we’ll keep the db connection code inside the, db_utils.py. Import it into your other scripts to eliminate redundant connection code. It has one more utility function to print a star pattern just before the result.

# filename: db_utils.py

import mysql.connector

def connect_db(db_name="techbeamers"):

    if db_name == "":
        # Connect to MySQL server without specifying a database
        db = mysql.connector.connect(
           host="127.0.0.1",
           user="temp",
           password="temp"
        )
    else:
        # Reconnect to the specific database
        db = mysql.connector.connect(
            host="127.0.0.1",
            user="temp",
            password="temp",
            database=db_name
        )

    return db

def print_separator():
    print("*" * 50)

By importing this script, you ensure a steady connection for your Python applications, promoting code reuse and keeping things consistent.

2. Python Create Database

Let’s set up a new database effortlessly using Python. In this example, the script establishes a connection and creates a database named “techbeamers.”

from db_utils import *

def create_db(db_name="techbeamers"):

    db = connect_db("")

    # Create a cursor object to execute SQL queries
    cur = db.cursor()

    # Create the database if it doesn't exist
    cur.execute(f"CREATE DATABASE IF NOT EXISTS {db_name}")

    # Close the cursor
    db.close()
    print_separator()
    print(f"Database created as {db_name}")

# Call the function create the database
create_db()

3. Python Create Tables

Now, we’ll create tables within our database. This Python script connects to the database and creates a “clients” table with columns for id, name, and address.

from db_utils import *

# Create tables
def create_tables():
    db = connect_db()
    cur = db.cursor()

    # Create 'clients' table
    cur.execute("CREATE TABLE clients (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
    print_separator()
    print("Table 'clients' created")

    # Create 'orders' table
    cur.execute("CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY, client_id INT, product VARCHAR(255))")
    print_separator()
    print("Table 'orders' created")

    # Create 'products' table
    cur.execute("CREATE TABLE products (product_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), price DECIMAL(10, 2))")
    print_separator()
    print("Table 'products' created")

    db.commit()

# Call the function create the tables
create_tables()

4. Python Insert Data

Inserting data is simple with Python. This script connects to the database and adds a new client to the “clients” table.

from db_utils import *

# Insert dummy data
def insert_data():
    db = connect_db()
    cur = db.cursor()

    # Insert data into 'clients' table
    sql_clients = "INSERT INTO clients (name, address) VALUES (%s, %s)"
    val_clients = [("Jason Woo", "103 Main St"), ("Alice Johnson", "201 Oak Ave")]

    cur.executemany(sql_clients, val_clients)
    print_separator()
    print("Data inserted into 'clients' table")

    # Insert data into 'orders' table
    sql_orders = "INSERT INTO orders (client_id, product) VALUES (%s, %s)"
    val_orders = [(1, "Laptop"), (2, "Phone"), (1, "Tablet")]

    cur.executemany(sql_orders, val_orders)
    print_separator()
    print("Data inserted into 'orders' table")

    # Insert data into 'products' table
    sql_products = "INSERT INTO products (name, price) VALUES (%s, %s)"
    val_products = [("Laptop", 999.99), ("Phone", 499.99), ("Tablet", 299.99)]

    cur.executemany(sql_products, val_products)
    print_separator()
    print("Data inserted into 'products' table")

    db.commit()

# Call the function insert dummy data
insert_data()

5. Python Select Data

Now, let’s retrieve data. This script connects to the database, selects all records from the “clients” table, and prints the results.

from db_utils import *

def select_data(tbl_name="clients"):

    db = connect_db()
    cur = db.cursor()
    cur.execute(f"SELECT * FROM {tbl_name}")
    myresult = cur.fetchall()

    print_separator()
    print(f"Selecting data from {tbl_name}")
    for row in myresult:
        print(row)

# Call the function to fetch records
select_data()

6. Python Update Data

Updating records is a breeze. Connect to the database and modify the address for the client with ID 1 in the “clients” table.

from db_utils import *

# Update data in 'clients' table
def update_data(tbl_name="clients"):
    db = connect_db()
    cur = db.cursor()

    # Update address for the client with ID 1 in the 'clients' table
    sql_update = f"UPDATE {tbl_name} SET address = '456 Oak St' WHERE id = 1"
    cur.execute(sql_update)
    print_separator()
    print(f"Data updated in '{tbl_name}' table")

    db.commit()

# Call the function to update data
update_data()

These Python snippets guide you through basic SQL operations, making it easy to manage databases with Python. Remember to keep the common database connection code in a separate file for reusability.

7. Python SQL Join Operations

Combining data from different tables is seamless with Python SQL Join operations. Import our common connection script, connect to the database, and execute a JOIN query to fetch related data from multiple tables.

from db_utils import *

# Perform SQL JOIN operation
def sql_join_ops():
    db = connect_db()
    cur = db.cursor()

    # Example of INNER JOIN
    cur.execute("SELECT clients.name, orders.product FROM clients INNER JOIN orders ON clients.id = orders.client_id")

    result = cur.fetchall()

    print_separator()
    print("Results of SQL JOIN operation:")
    for row in result:
        print(row)

# Call the function to update data
sql_join_ops()

8. Python SQL Aggregate Functions

Aggregating data with Python enhances data analysis. Sum, average, and count functions are pivotal.

from db_utils import *

# Perform SQL Aggregate Functions
def sql_aggr_ops():
    db = connect_db()
    cur = db.cursor()

    # Sum
    cur.execute("SELECT SUM(price) FROM products")
    sum_result = cur.fetchone()[0]
    print_separator()
    print("Sum of prices in 'products' table:", sum_result)

    # Average
    cur.execute("SELECT AVG(price) FROM products")
    avg_result = cur.fetchone()[0]
    print_separator()
    print("Avg price in 'products' table:", avg_result)

    # Count
    cur.execute("SELECT COUNT(*) FROM products")
    count_result = cur.fetchone()[0]
    print_separator()
    print("No. of records in 'products' table:", count_result)

# Call the above method to demo mysql aggregate functions
sql_aggr_ops()

This Python code demonstrates how to leverage SQL aggregate functions to perform calculations on your data. Sum, average, and count functions offer valuable insights into your dataset’s characteristics.

9. Python SQL Delete Table

Deleting a table is straightforward in Python. Connect to the database and remove the “clients” table.

from db_utils import *

def drop_table(table_name):
    db = connect_db()
    cur = db.cursor()

    # Drop the given table if it exists
    cur.execute(f"DROP TABLE IF EXISTS {table_name}")

    print_separator()
    print(f"Table '{table_name}' dropped")

    db.commit()
    db.close()

# Call the above method to drop the table
drop_table()

10. Python SQL Delete Database

Deleting a database is as simple as creating one. Connect to the server and drop the “techbeamers” database.

from db_utils import *

def drop_db(db_name="techbeamers"):
    db = connect_db()
    cur = db.cursor()

    # Drop the given database if it exists
    cur.execute(f"DROP DATABASE IF EXISTS {db_name}")

    print_separator()
    print(f"Database '{db_name}' dropped")

    db.close()

# Call the above method to drop the database
drop_db()

The above two Python examples showcase how to delete tables and databases using Python. With these operations, you get flexibility in managing your database structures. However, always be responsible and mindful when performing delete operations to avoid unintentional data loss.

Check GitHub for More Engaging Stuff

Please note that all the above examples after thorough testing are carefully published to the following GitHub repo: https://github.com/techbeamers/python-sql-examples

Here you can find the examples saved in the form of Python scripts. In addition, you will get scripts related to dedicated DB operation with a launcher script and a single Python script to run and test the whole stuff.

Summary – Connect Python to a SQL Database

The above tutorial made it simple to connect to an SQL database from Python. To learn further, you can practice with the examples provided and start using them in your project.

The given Python scripts are practical and effectively demonstrate important SQL commands. Keep coding simple and use these examples in your Python SQL database projects.

Before you leave, render your support for us to continue. If you like our tutorials, share this post on social media like Facebook/Twitter.

Happy coding,
TechBeamers.