Connecting to Databases in Python (SQLite, MySQL)

 

Connecting to Databases in Python (SQLite, MySQL)

Python provides various libraries for interacting with different types of databases, including SQLite and MySQL. In this tutorial, we will cover how to connect to these databases using Python, execute queries, and handle results. We will use the SQLite3 module for SQLite and the MySQL Connector for MySQL.

---

1. Connecting to an SQLite Database

SQLite is a lightweight, disk-based database that doesn’t require a separate server process. It is built into Python, so you don’t need to install any external libraries to use it.

1.1 Install SQLite

SQLite is part of the standard Python library, so you do not need to install it separately. You can start using it right away.

1.2 Connecting to SQLite

To connect to an SQLite database, you need to use the sqlite3 module. If the database file doesn’t exist, SQLite will create it for you.

import sqlite3

# Connect to the SQLite database (creates a new database if it doesn't exist)
connection = sqlite3.connect("example.db")

# Create a cursor object to interact with the database
cursor = connection.cursor()

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS Users
                (ID INTEGER PRIMARY KEY, Name TEXT, Age INTEGER)''')

# Commit changes and close the connection
connection.commit()

In the above code:

• We connect to the SQLite database using sqlite3.connect(). If the database file (example.db) doesn’t exist, it will be created.
• We use a cursor object to execute SQL queries.

1.3 Inserting Data into SQLite

To insert data into the database, we use the INSERT INTO SQL statement:

# Insert a new user into the Users table
cursor.execute("INSERT INTO Users (Name, Age) VALUES (?, ?)", ("John Doe", 25))

# Commit the transaction
connection.commit()

Here, ? is used as a placeholder for the values to be inserted, which helps prevent SQL injection attacks.

1.4 Querying Data from SQLite

You can retrieve data using the SELECT statement:

# Retrieve all users from the Users table
cursor.execute("SELECT * FROM Users")

# Fetch all results
rows = cursor.fetchall()

# Print the results
for row in rows:
    print(row)

1.5 Closing the SQLite Connection

Once you are done working with the database, make sure to close the connection:

# Close the cursor and connection
cursor.close()
connection.close()

---

2. Connecting to a MySQL Database

MySQL is a popular open-source relational database management system (RDBMS). To connect to a MySQL database from Python, you need to install the mysql-connector-python library, which allows you to interact with MySQL databases.

2.1 Installing MySQL Connector

If you don’t have MySQL Connector installed, you can install it using pip:

pip install mysql-connector-python

2.2 Connecting to MySQL

Once the MySQL connector is installed, you can use it to connect to a MySQL server. You will need the following information to establish a connection:

• host: The MySQL server address (e.g., localhost for a local server).
• user: The username with which to connect.
• password: The password for the user.
• database: The database you want to work with.

import mysql.connector

# Connect to the MySQL database
connection = mysql.connector.connect(
    host="localhost",         # Database server address
    user="root",              # MySQL username
    password="password",      # MySQL password
    database="test_db"        # Name of the database
)

# Create a cursor object
cursor = connection.cursor()

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS Users
                (ID INT AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(255), Age INT)''')

# Commit changes
connection.commit()

2.3 Inserting Data into MySQL

To insert data into MySQL, use the INSERT INTO statement just like we did for SQLite.

# Insert a new user into the Users table
cursor.execute("INSERT INTO Users (Name, Age) VALUES (%s, %s)", ("Jane Doe", 28))

# Commit the transaction
connection.commit()

Notice that in MySQL, we use %s as placeholders, not ?.

2.4 Querying Data from MySQL

Retrieving data works similarly to SQLite. Use SELECT and then fetch the results:

# Select all rows from the Users table
cursor.execute("SELECT * FROM Users")

# Fetch all results
rows = cursor.fetchall()

# Print each row
for row in rows:
    print(row)

2.5 Closing the MySQL Connection

After finishing operations on the database, you should close both the cursor and the connection:

# Close the cursor and connection
cursor.close()
connection.close()

---

3. Error Handling and Best Practices

When interacting with databases, it is important to implement error handling to catch any issues that may arise during the connection or query execution process.

3.1 Using Try-Except for Error Handling

You can use try-except blocks to handle exceptions while working with databases:

try:
    # Connect to SQLite
    connection = sqlite3.connect("example.db")
    cursor = connection.cursor()
    
    # Execute a query
    cursor.execute("SELECT * FROM Users")
    rows = cursor.fetchall()
    
    # Process the data
    for row in rows:
        print(row)
        
except sqlite3.Error as e:
    print("Error occurred:", e)
    
finally:
    # Ensure the connection is closed
    if connection:
        cursor.close()
        connection.close()

3.2 Using Context Managers (with Statement)

Python’s with statement ensures that resources like database connections are properly cleaned up after use:

import sqlite3

# Use 'with' to automatically close the connection
with sqlite3.connect("example.db") as connection:
    cursor = connection.cursor()
    
    # Execute queries within the context
    cursor.execute("SELECT * FROM Users")
    rows = cursor.fetchall()
    
    # Process the results
    for row in rows:
        print(row)

For MySQL, the concept is the same. You can also use the with statement if you define a context manager.

---

4. Summary

In this tutorial, we covered how to connect to both SQLite and MySQL databases from Python:

• SQLite: A lightweight, serverless database that is built into Python and used for local storage.
• MySQL: A more robust, server-based database system requiring a connection via the mysql-connector-python library.

Key concepts included:

• Connecting to databases.
• Executing queries such as SELECT, INSERT, UPDATE, and DELETE.
• Error handling and best practices for interacting with databases.

By understanding how to connect to and interact with databases in Python, you can efficiently work with large datasets and build data-driven applications.