123. Interfacing with Relational Databases

Here are 10 Python code snippets demonstrating how to interface with relational databases using libraries like sqlite3 or SQLAlchemy:

---

1. Connecting to a SQLite Database

Copy

import sqlite3

# Connect to an SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Close the connection
conn.close()

---

2. Creating a Table in SQLite

Copy

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

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

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

---

3. Inserting Data into SQLite

Copy

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

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

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

---

4. Querying Data from SQLite

Copy

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Query data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close the connection
conn.close()

---

5. Updating Data in SQLite

Copy

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Update data
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, 'Alice'))

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

---

6. Deleting Data from SQLite

Copy

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Delete data
cursor.execute("DELETE FROM users WHERE name = ?", ('Alice',))

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

---

7. Using SQLAlchemy for ORM

Copy

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

# Define the database and the base class
engine = create_engine('sqlite:///my_database.db')
Base = declarative_base()

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

# Create the table
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Close the session
session.close()

---

8. Inserting Data Using SQLAlchemy ORM

Copy

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import IntegrityError

# Define the database and create a session
engine = create_engine('sqlite:///my_database.db')
Session = sessionmaker(bind=engine)
session = Session()

# Inserting data using ORM
try:
    user = User(name='Bob', age=25)
    session.add(user)
    session.commit()
except IntegrityError:
    session.rollback()

# Close the session
session.close()

---

9. Querying Data with SQLAlchemy ORM

Copy

from sqlalchemy.orm import sessionmaker

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Query data using SQLAlchemy ORM
users = session.query(User).filter_by(name='Bob').all()
for user in users:
    print(f"Name: {user.name}, Age: {user.age}")

# Close the session
session.close()

---

10. Updating Data with SQLAlchemy ORM

Copy

from sqlalchemy.orm import sessionmaker

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Query and update data using SQLAlchemy ORM
user = session.query(User).filter_by(name='Bob').first()
if user:
    user.age = 26
    session.commit()

# Close the session
session.close()

---

These code snippets demonstrate basic operations like connecting to a database, creating tables, inserting, querying, updating, and deleting data using both sqlite3 (Python's built-in SQLite interface) and SQLAlchemy (an ORM for managing database operations).