Mastering Database Interaction in Python: A Comprehensive Guide with SQLite and MySQL
Introduction to Python Database Handling
In this blog, we dive deep into how Python can interact with two widely used database systems: SQLite and MySQL. By understanding how to perform common database operations such as creating tables, inserting records, updating data, and deleting records, you’ll gain the foundational skills necessary to work with databases in your Python projects.
import sqlite3
conn = sqlite3.connect("Database/example.db")
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
)
''')
conn.commit()
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Someone", 100))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Someone Else", 84))
conn.commit()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (101, "Someone"))
conn.commit()
cursor.execute("DELETE FROM users WHERE name = ?", ("Someone Else",))
conn.commit()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
conn.close()
import mysql.connector
conn = mysql.connector.connect(
host = "localhost",
user = "test_user",
password = "my_password",
database = "example_db",
)
cursor = conn.cursor()
# cursor.execute("CREATE DATABASE IF NOT EXISTS example_db")
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
age INT
)
''')
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Someone', 100))
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Someone Else', 84))
conn.commit()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
cursor.execute("UPDATE users SET age = %s WHERE name = %s", (101, "Someone"))
conn.commit()
cursor.execute("DELETE FROM users WHERE name = %s", ("Someone Else",))
conn.commit()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
conn.close()We’ll explore both SQLite, which is built into Python, and MySQL, a popular relational database management system.
Working with SQLite in Python
SQLite is an embedded database, which makes it perfect for small to medium-sized applications where the overhead of setting up a separate database server is not needed. It’s a serverless database that stores all its data in a single file.
Setting Up SQLite
First, we need to import the sqlite3 module and establish a connection to the SQLite database. If the database file doesn’t exist, SQLite will automatically create it.
import sqlite3
conn = sqlite3.connect("Database/example.db")
cursor = conn.cursor()This creates a connection to the example.db database file, located in the Database directory. If the file doesn’t exist, it will be created automatically.
Creating a Table
We create a table named users with three columns: id, name, and age. The id column is the primary key and will auto-increment.
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
)
''')
conn.commit()The CREATE TABLE SQL statement is executed, and the changes are committed to the database.
Inserting Data into the Table
Next, we insert some initial data into the users table. We use parameterized queries (?) to safely insert data into the table and avoid SQL injection.
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Someone", 100))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Someone Else", 84))
conn.commit()We commit the changes to ensure they are saved to the database.
Querying Data from the Table
Now we can fetch and display all the records from the users table using a SELECT statement.
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)This will print all rows in the users table.
Get Someone’s stories in your inbox
Join Medium for free to get updates from this writer.
Output:
(1, 'Someone', 100)
(2, 'Someone Else', 84)Updating Data in the Table
To update an existing record, we use the UPDATE statement. Here, we change the age of the user with the name Someone.
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (101, "Someone"))
conn.commit()Deleting Data from the Table
Similarly, we can delete a record. Here, we delete the user with the name Someone Else.
cursor.execute("DELETE FROM users WHERE name = ?", ("Someone Else",))
conn.commit()Querying the Updated Data
Finally, we query the users table again to see the changes we made.
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)Output:
(1, 'Someone', 101)Closing the SQLite Connection
Once the operations are complete, it’s essential to close the connection to the SQLite database.
conn.close()Transitioning to MySQL with Python
In this section, we explore how to interact with a MySQL database using Python. MySQL is a more scalable solution, often used for web applications or large-scale systems.
Setting Up MySQL
Before we begin, ensure that you have MySQL running on your local machine and that the Python MySQL connector (mysql-connector-python) is installed. You can install it using pip:
pip install mysql-connector-pythonOnce the connector is installed, we can proceed to establish a connection to the MySQL database.
import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="test_user",
password="my_password",
database="example_db",
)
cursor = conn.cursor()Here, we use the mysql.connector.connect function to connect to the MySQL database with the provided credentials.
Creating a MySQL Table
Just like with SQLite, we need to create a table in MySQL. We use the CREATE TABLE SQL command to define the structure.
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
age INT
)
''')In MySQL, we define the id column as an AUTO_INCREMENT to automatically generate unique IDs for each row.
Inserting Data into MySQL
We insert data into the MySQL table using parameterized queries, just like with SQLite.
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Someone', 100))
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Someone Else', 84))
conn.commit()Querying Data from MySQL
Once data is inserted, we can fetch and display it using a SELECT statement.
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)Output:
(1, 'Someone', 100)
(2, 'Someone Else', 84)Updating Data in MySQL
Updating records in MySQL works similarly to SQLite.
cursor.execute("UPDATE users SET age = %s WHERE name = %s", (101, "Someone"))
conn.commit()Deleting Data from MySQL
To delete records, we use the DELETE statement.
cursor.execute("DELETE FROM users WHERE name = %s", ("Someone Else",))
conn.commit()Querying the Updated Data in MySQL
After the update and delete operations, we query the table again to verify the changes.
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)Output:
(1, 'Someone', 101)Closing the MySQL Connection
Finally, close the MySQL connection to ensure that all resources are released.
conn.close()Conclusion: Key Takeaways
This tutorial provided a thorough overview of working with two different types of databases in Python: SQLite and MySQL. By learning how to:
- Establish connections to both SQLite and MySQL.
- Create, read, update, and delete data in the database.
- Use parameterized queries to avoid SQL injection.
You are now equipped with the necessary skills to integrate Python into database-driven applications, both small and large-scale.
For my ongoing learning journey, you can view the code and follow my updates on:
Thanks for following along, and I hope this blog has helped you improve your Python and database skills!
