Combining SQL and Python: A Comprehensive Guide for Efficient Data Management

Introduction to SQL Databases

SQL databases are the gold standard for information storage in most IT departments. SQL is generally preferred because the databases are both relational and normalized.

The Popularity of MySQL

MySQL is the most common SQL database system due to its open-source build. Its base is completely free, but there are many other add-ons. MySQL is easy to install, fast, and renowned for its security features.

Other SQL Database Systems

Oracle, PostgreSQL, and SQL Server are also very popular. The only possible downside of MySQL is that it doesn’t fully support every possible SQL Query, such as a FULL JOIN.

The Ultimate Python Ethical Hacking Course

=> Join the Waitlist for Early Access.

By subscribing, you agree to get emails from me, Tanner Abraham. I'll respect your privacy and you can unsubscribe any time.

The Versatility of Python Programming

Python is one of the most popular current programming languages. It is an object-oriented, interpreted, high-level programming language.

Python is known for its no compilation requirement, making the process of building, editing, testing, and debugging much faster than with other high-level programming languages.

IDEs for Python Development

Python can be worked on in several IDEs, the most popular being PyCharm. PyCharm has features designed to make it easy to use, offering both a professional version and a community version.

Python’s Standard Library and Applications

Python has many different applications, like data visualization tools (such as pandas and SciPy).

Different Python frameworks are useful for various tasks, such as web development, internet packages, desktop GUIs, and software and business applications.

Python’s standard library supports protocols like HTML, JSON, FTP, IMAP, and XML.

Linking Python and MySQL

SQL Syntax for Database Interaction

MySQL databases are operated by querying. For example, if you have a table named “Cook Book”, your code to select everything in the table using SQL syntax would be:

SELECT * FROM Cook Book;

Python Code for Interacting with MySQL

In some projects, Java, Python, or another language will be joined to a separate MySQL database. For example, in Python, the code to interact with a table named “Cook Book” would be:

select_food_query = "SELECT * FROM Cook Book"

with connection.cursor() as cursor:
    cursor.execute(select_food_query)
    result = cursor.fetchall()
    for row in result:
        print(row)

Setting Up the Connection Between Python and MySQL

Installing MySQL on Your System

The official MySQL documentation provides instructions on how to install MySQL on the most popular operating systems.

There are instructions for Windows, macOS, and Linux.

MySQL Connector Python Installation

To connect a Python program to a MySQL database, you need a connector, also called a database driver. In this case, a MySQL driver would be called MySQL Connector Python.

This step requires pip to be installed on your computer. From there, you can install it by using:

$ pip install mysql-connector-python

Test the connection by typing this command into your Python terminal:

import mysql.connector

Manipulating Databases with Python

Connecting to a Database

To connect to a database, you’ll need something called a cursor. You can build one for yourself by using the method “connection” on a cursor variable.

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter user: "),
        password=getpass("Enter passcode: "),
        database="Cook Book",
    ) as connection:
        print(connection)
except Error as f:
    print(f)

Data Definition Language (DDL)

To create, drop, or alter a table, you have to use a Data Definition Language (DDL). Some of the basic DDL commands are CREATE, DROP, and ALTER.

Working with Tables in Python and MySQL

Creating Tables

To create a table, input DDL code in MySQL. For example, creating a “breakfast” table would look like this:

CREATE TABLE breakfast(
    breakfast_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    Food_group VARCHAR(100),
    Calories INT
);

You can combine SQL with Python like so:

create_breakfast_table_query = """
CREATE TABLE movies(
     breakfast_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    Food_group VARCHAR(100),
    Calories INT
)
"""
with connection.cursor() as cursor:
    cursor.execute(create_breakfast_table_query)
    connection.commit()

Inserting Records

To insert values into a table, you can use .execute() or .executemany() depending on context. .executemany() is more commonly used since it can apply to multiple items at once.

insert_lunch_query = """
INSERT INTO lunch
(name, food_group)
VALUES ( %s )
"""
lunch_records = [
    ("bread", "grain"),
    ("cheese", "dairy"),
    ("yogurt", "dairy"),
    ("lettuce", "vegetable"),
    ("sausage", "meat"),
    ("tofu", "meat"),
    ("orange", "fruit"),
    ("avocado", "vegetable"),
    ("salmon", "meat"),
    ("kale", "vegetable"),
    ("kiwi", "fruit"),
    ("black beans", "protein"),
    ("dates", "fruit"),
    ("rice", "grain"),
    ("blackberries", "fruit"),
    ("melon", "fruit"),
    ("almond", "protein"),
    ("hazelnut", "protein"),
    ("prunes", "fruit"),
    ("goat cheese", "dairy"),
    ("agave nectar", "dressing"),
    ("mint", "dressing"),
]

with connection.cursor() as cursor:
    cursor.executemany(insert_lunch_query, lunch_records)
    connection.commit()

Reading Records

Reading records is important in certain situations and can be done with a simple code. Use a select query, send it to cursor.execute(), and submit cursor.fetchall() to retrieve the table.

select_lunch_query = "SELECT * FROM lunch LIMIT 5"
with connection.cursor() as cursor:
    cursor.execute(select_lunch_query)
    result = cursor.fetchall()
    for row in result:
        print(row)

Conclusion

Queries, specifically SQL queries in Python, are not as complicated as they may seem.

The most important parts are the wrapper for the SQL Query and a variable that represents the query.

With these tools combined, querying SQL within a Python application is a simple process. What will you use SQL and Python together to accomplish?

The organization of SQL and the power of Python is a combination that cannot be easily overstated.

Tanner Abraham

Data Scientist and Software Engineer with a focus on experimental projects in new budding technologies that incorporate machine learning and quantum computing into web applications.