# 56. Working with SQL Databases

**1. Basic SQLite Database Connection**

Connecting to a SQLite database and creating a table.

```python
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.

```python
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.

```python
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.

```python
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.

```python
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.

```python
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.

```python
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.

```python
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.

```python
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.

```python
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.
