Querying SQL with Python

The Standard Workflow: Python and sqlite3

Interacting with an SQLite database from Python follows a standard, logical sequence of steps. Understanding this workflow is key to managing any database connection in your code.

1. Import: Bring the necessary library into your script with import sqlite3.
2. Connect: Establish a connection to a database file. If the file doesn't exist, Python will create it for you.
3. Cursor: Create a cursor object. Think of the cursor as a controller that allows you to execute commands and navigate through the results.
4. Execute: Use the cursor to run your SQL commands (e.g., CREATE TABLE, INSERT, SELECT).
5. Commit/Fetch:
   • For data-modifying commands (INSERT, UPDATE, DELETE), you must commit the transaction to save your changes.
   • For data-retrieval commands (SELECT), you fetch the results from the cursor.
6. Close: Close the connection to release the database file and its resources.

Let's see this entire process in action.

To recap the core Python code for these initial steps:

import sqlite3

# 1. Connect to a database (or create it)
conn = sqlite3.connect('mydatabase.db')

# 2. Create a cursor object
c = conn.cursor()

# 3. Execute a command (e.g., create a table)
c.execute("""CREATE TABLE employees (
            first text,
            last text,
            pay integer
            )""")

# 4. Commit the command to save the changes
conn.commit()

# 5. Close the connection
conn.close()

Writing and Reading Data

Now that we have a table, let's perform the two most common operations: inserting data into it and reading data from it.

Inserting Data and a Crucial Security Note

You can insert data using the INSERT INTO command. However, it's critically important to do this safely. A common mistake for beginners is to use Python's string formatting to insert variables into a query. This is extremely dangerous and makes your code vulnerable to SQL injection attacks.

The correct method is to use placeholders (?). The execute method will safely substitute the placeholder with your variable's value.

Here is a comparison of the wrong way and the right way:

# Assume conn and c are already created
emp_first = 'John'
emp_last = 'Doe'

# WRONG and DANGEROUS way (vulnerable to SQL injection)
# c.execute(f"SELECT * FROM employees WHERE first = '{emp_first}'") 

# RIGHT and SAFE way (using placeholders)
c.execute("SELECT * FROM employees WHERE first = ?", (emp_first,))

Notice that when passing a single variable, it must still be in a tuple, hence the comma (emp_first,).

Reading Data with SELECT

After executing a SELECT query, the results are held in the cursor object. You need to explicitly fetch them.

There are three ways to do this:

• c.fetchone(): Fetches the next single row from the results. Returns None if no more rows are available.
• c.fetchmany(size): Fetches the next size number of rows as a list.
• c.fetchall(): Fetches all remaining rows from the results as a list of tuples.

A common pattern is to fetch all results and loop through them:

c.execute("SELECT * FROM employees")

# Fetch all rows into a list
results = c.fetchall() 

# Loop through the list and print each row
for row in results:
    print(row)

# ('Corey', 'Schafer', 50000)
# ('Mary', 'Schafer', 70000)

A More "Pythonic" Way: The with Statement

Remembering to commit your changes and close the connection can be tedious. Python offers a cleaner, safer way to handle this using a context manager (the with statement). When you use with on a connection object, it will automatically commit your transaction if the code block completes successfully, or roll it back (undo it) if an error occurs.

This is a highly recommended practice.

Here's how it looks:

# The 'with' statement handles the commit and closing
with sqlite3.connect('mydatabase.db') as conn:
    c = conn.cursor()
    # No need for conn.commit() or conn.close() later
    c.execute("DELETE FROM employees WHERE last = ?", ('Doe',))

import sqlite3

# Data to be inserted
book_data = ('The Hitchhiker\'s Guide to the Galaxy', 'Douglas Adams', 1979)

# Use a 'with' statement for automatic commit/close
try:
    with sqlite3.connect('library.db') as conn:
        c = conn.cursor()

        # Create table (it's safe to run this even if it exists, using IF NOT EXISTS)
        c.execute("""CREATE TABLE IF NOT EXISTS books (
                    title text,
                    author text,
                    year integer
                    )""")

        # Insert a row of data safely
        c.execute("INSERT INTO books VALUES (?, ?, ?)", book_data)

        print("Book inserted successfully.")

        # Select all data and print
        print("\nAll books in the library:")
        c.execute("SELECT * FROM books")
        
        all_books = c.fetchall()
        for book in all_books:
            print(book)

except sqlite3.Error as e:
    print(f"Database error: {e}")

Bridging to Data Science: Integrating Pandas

While fetchall() is useful, for data analysis you'll almost always want your data in a Pandas DataFrame. The pandas library integrates beautifully with sqlite3, allowing you to read the results of an SQL query directly into a DataFrame.

The pd.read_sql_query() function is incredibly efficient. It takes an SQL query string and a connection object, and returns a fully populated DataFrame.

Here's an example:

import pandas as pd
import sqlite3

# Establish the connection
conn = sqlite3.connect('mydatabase.db')

# Define your SQL query
query = "SELECT first, last, pay FROM employees WHERE pay > 60000"

# Use pandas to execute the query and load data into a DataFrame
df = pd.read_sql_query(query, conn)

# Don't forget to close the connection
conn.close()

# Now you can use all the power of Pandas!
print(df.head())
print("\nAverage pay for high earners:", df['pay'].mean())

You can also write a DataFrame back to a new SQL table using the df.to_sql() method, which is very useful for saving processed data.

Conclusion

Congratulations on completing the "Database Systems and SQL" module! You've journeyed from the theoretical underpinnings of relational databases and normalization to the practical application of interacting with a database using Python.

Key Takeaways:

• The sqlite3 module provides a simple, file-based database system built into Python.
• The standard workflow is: Connect -> Cursor -> Execute -> Commit/Fetch -> Close.
• Using the with statement is a best practice for automatically managing commits and closing connections.
• Always use placeholders (?) in your execute calls to prevent SQL injection vulnerabilities.
• The pandas.read_sql_query() function is the most effective way to pull data from a database directly into a DataFrame for analysis.

Preview of the Next Lesson:
With your new skills in data retrieval and manipulation using Python, SQL, and Pandas, you are perfectly positioned to start the next phase of your journey. The next module, "Foundations of Machine Learning," begins with a lesson where you will learn to distinguish between supervised, unsupervised, and reinforcement learning. The data you've just learned to query will be the fuel for the models you're about to build.