Day 29 - Working with Databases in Python - SQLite and SQLAlchemy

Welcome back to the #PythonForDevOps series! Today, on Day 29, we are going to talk about working with databases in Python. Understanding how to work with databases is crucial for any DevOps engineer, as it forms the backbone of data storage and retrieval in various applications. In this post, we'll focus on two powerful tools: SQLite and SQLAlchemy.

Getting Started with SQLite:

SQLite is a lightweight, serverless, and self-contained relational database engine. It's an excellent choice for small to medium-sized projects, offering simplicity without compromising functionality.

Let's start by installing the SQLite library in Python:

pip install sqlite3

Once installed, we can create a simple SQLite database and perform basic operations. Imagine we are managing user data for an application:

import sqlite3

# Connect to the database (or create it if it doesn't exist)
conn = sqlite3.connect('user_data.db')

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

# Create a table to store user information
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER
    )
''')

# Inserting data into the table
cursor.execute("INSERT INTO users (name, age) VALUES ('John Doe', 25)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Jane Smith', 30)")

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

In this example, we've created a database file named user_data.db, defined a table called users, and inserted two user records. SQLite is straightforward and perfect for quick setups.

Leveling Up with SQLAlchemy:

Now, let's step up our game with SQLAlchemy. SQLAlchemy is a powerful SQL toolkit and Object-Relational Mapping (ORM) library. It provides a higher level of abstraction, making database operations more Pythonic and developer-friendly.

To get started with SQLAlchemy, install it using:

pip install sqlalchemy

Now, let's rewrite our previous example using SQLAlchemy:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Define the database engine
engine = create_engine('sqlite:///user_data_sqlalchemy.db', echo=True)

# Create a base class for our models
Base = declarative_base()

# Define the User model
class User(Base):
    tablename = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    age = Column(Integer)

# Create the tables in the database
Base.metadata.create_all(engine)

# Create a session to interact with the database
Session = sessionmaker(bind=engine)
session = Session()

# Inserting data using SQLAlchemy
user1 = User(name='John Doe', age=25)
user2 = User(name='Jane Smith', age=30)

session.add_all([user1, user2])
session.commit()

With SQLAlchemy, we define our data model as a Python class, making it more readable and maintainable. The ORM handles the underlying SQL, allowing us to focus on the logic of our application.

Querying Data:

Both SQLite and SQLAlchemy make it easy to retrieve data. Let's query our user data:

SQLite:

conn = sqlite3.connect('user_data.db')
cursor = conn.cursor()

# Select all users
cursor.execute('SELECT * FROM users')
users = cursor.fetchall()

for user in users:
    print(user)

conn.close()

SQLAlchemy:

users = session.query(User).all()
for user in users:
    print(f"ID: {user.id}, Name: {user.name}, Age: {user.age}")

In both cases, we retrieve and print our user records. SQLAlchemy's syntax is more intuitive, reflecting the simplicity of Python.

In today's journey through databases in Python, we explored the basics of SQLite and took a leap forward with SQLAlchemy. While SQLite is excellent for quick setups and smaller projects, SQLAlchemy provides a more sophisticated and Pythonic approach to database management.

As a DevOps enthusiast, mastering these tools will empower you to handle data efficiently and contribute to the robustness of your applications.

Stay tuned for more exciting topics in our #PythonForDevOps series!

Happy coding!

*** Explore | Share | Grow ***