56. Working with SQL Databases

Here are 10 Python code snippets demonstrating how to work with SQL databases using sqlite3 and SQLAlchemy:

1. Basic SQLite Database Connection

Connecting to a SQLite database and creating a table.

Copy

import sqlite3

# Connect to a SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('example.db')
cursor = conn.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
conn.commit()
conn.close()

This snippet connects to a SQLite database, creates a table if it doesn't exist, and then closes the connection.

---

2. Inserting Data into SQLite

Inserting data into a table in SQLite.

Copy

import sqlite3

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

# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('John Doe', 30))

# Commit and close
conn.commit()
conn.close()

This example shows how to insert a record into the users table in SQLite.

---

3. Querying Data in SQLite

Querying data from an SQLite database.

Copy

import sqlite3

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

# Retrieve data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

# Display results
for row in rows:
    print(row)

conn.close()

This queries all rows from the users table and prints them out.

---

4. Updating Data in SQLite

Updating a record in the SQLite database.

Copy

import sqlite3

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

# Update a record
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (35, 'John Doe'))

# Commit and close
conn.commit()
conn.close()

This snippet updates the age of the user John Doe to 35.

---

5. Deleting Data in SQLite

Deleting a record from the SQLite database.

Copy

import sqlite3

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

# Delete a record
cursor.execute("DELETE FROM users WHERE name = ?", ('John Doe',))

# Commit and close
conn.commit()
conn.close()

This deletes the record with the name John Doe from the users table.

---

6. Using SQLAlchemy for Database Connection

Setting up SQLAlchemy for database connection and defining a model.

Copy

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

# Set up database engine and session
engine = create_engine('sqlite:///example.db', echo=True)
Base = declarative_base()

# Define a model
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Create table
Base.metadata.create_all(engine)

# Session maker
Session = sessionmaker(bind=engine)
session = Session()

# Add a new user
new_user = User(name='Jane Doe', age=28)
session.add(new_user)
session.commit()

session.close()

This demonstrates how to use SQLAlchemy to connect to an SQLite database, define a model, and insert a record.

---

7. Querying Data with SQLAlchemy

Querying data using SQLAlchemy.

Copy

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import select

# Connect to database
engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

# Query data
users = session.query(User).all()

# Print all users
for user in users:
    print(user.name, user.age)

session.close()

This snippet retrieves all records from the users table using SQLAlchemy and prints them.

---

8. Filtering Data with SQLAlchemy

Using filters to query specific records with SQLAlchemy.

Copy

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Connect to database
engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

# Query with filter
user = session.query(User).filter(User.name == 'Jane Doe').first()
if user:
    print(f"Found user: {user.name}, {user.age}")

session.close()

This filters the users table to find a specific user, Jane Doe.

---

9. Updating Records with SQLAlchemy

Updating records using SQLAlchemy.

Copy

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Connect to database
engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

# Update a record
user = session.query(User).filter(User.name == 'Jane Doe').first()
if user:
    user.age = 30
    session.commit()

session.close()

This updates the age of Jane Doe to 30 in the database.

---

10. Deleting Records with SQLAlchemy

Deleting records with SQLAlchemy.

Copy

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Connect to database
engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

# Delete a record
user = session.query(User).filter(User.name == 'Jane Doe').first()
if user:
    session.delete(user)
    session.commit()

session.close()

This deletes the record for Jane Doe from the database using SQLAlchemy.

---

These snippets show how to interact with SQL databases in Python using both the sqlite3 module for direct SQL queries and SQLAlchemy for ORM-based interaction, covering key operations like creating tables, inserting, updating, deleting, and querying data.