SQLAlchemy Overview Lecture Notes

Introduction to SQLAlchemy

SQLAlchemy is an SQL toolkit and Object-Relational Mapper (ORM) for Python.
Allows mapping Python classes and objects to database tables and entries.
Automates SQL tasks like Create, Insert, and Select, enabling focus on Python code.
Example: Person and Thing classes where a person can own multiple things.

Installation

Install SQLAlchemy using pip install SQLAlchemy.
SQL can be pronounced as either "SQL" or "sequel".

Basic Setup

Imports

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

Create Engine

Supports multiple database types (SQLite, MySQL etc.).
For SQLite: engine = create_engine('sqlite:///mydb.db', echo=True)
Create tables using Base.metadata.create_all(bind=engine)

Define Session

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

Defining Tables as Classes

Person Class

class Person(Base):
    __tablename__ = 'people'

    ssn = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name = Column(String)
    gender = Column(String)
    age = Column(Integer)

    def __init__(self, ssn, first_name, last_name, gender, age):
        self.ssn = ssn
        self.first_name = first_name
        self.last_name = last_name
        self.gender = gender
        self.age = age

    def __repr__(self):
        return f"{self.ssn}, {self.first_name}, {self.last_name}, {self.gender}, {self.age}"

Thing Class

class Thing(Base):
    __tablename__ = 'things'

    tid = Column(Integer, primary_key=True)
    description = Column(String)
    owner = Column(Integer, ForeignKey('people.ssn'))

    def __init__(self, tid, description, owner):
        self.tid = tid
        self.description = description
        self.owner = owner

    def __repr__(self):
        return f"{self.tid}, {self.description}, owned by {self.owner}"

Creating and Committing Records

p1 = Person(ssn=1, first_name='Mike', last_name='Smith', gender='M', age=35)
session.add(p1)
session.commit()
p2 = Person(ssn=2, first_name='Anna', last_name='Blue', gender='F', age=40)
session.add_all([p2, p3, p4])
session.commit()

Querying Records

Basic Query

results = session.query(Person).all()
for person in results:
    print(person)

Filtering with `filter`

results = session.query(Person).filter(Person.last_name == 'Blue').all()
for person in results:
    print(person)

Using `filter` with numbers

results = session.query(Person).filter(Person.age > 25).all()
for person in results:
    print(person)

Advanced Filtering

results = session.query(Person).filter(Person.first_name.like('%a%')).all()
for person in results:
    print(person)

Using `in_`

results = session.query(Person).filter(Person.first_name.in_(['Anna', 'Mike'])).all()
for person in results:
    print(person)

Combining Queries

results = session.query(Thing, Person).filter(Thing.owner == Person.ssn, Person.first_name == 'Anna').all()
for r in results:
    print(r)

Summary

SQLAlchemy allows mapping of Python objects to database tables without manual SQL.
Allows creation, update, deletion, and querying of database records using Python code.
Supports multiple database types including SQLite and MySQL.
Facilitates complex queries including joins and filters without writing SQL.

Conclusion

SQLAlchemy automates tedious SQL tasks, allowing focus on Python code.
Capable of handling complex relationships between tables through ORM.
Useful for developers who want to work with databases without writing SQL.

Next Steps

Experiment with more complex queries and relationships.
Explore additional SQLAlchemy features and extensions.
Consider using SQLAlchemy in upcoming projects to streamline database interactions.

References

Official Documentation: SQLAlchemy Documentation

[Music] -- Indication that this was a video tutorial.

Note: Always run your scripts in a safe environment to avoid data loss or corruption.

Common Errors/Issues

Foreign key issues if database isn’t properly initialized.
Unique constraint issues if trying to add duplicate records.
File locking issues if SQLite database file is open elsewhere.

Troubleshooting Tips

Ensure the database and tables are properly set up and committed.
Check for typos or mismatched column names and data types.
Make sure the database connection string is correct and the file or network location is accessible.

Good Practices

Always close sessions properly.
Handle exceptions and errors gracefully to avoid data corruption.
Regularly backup your database.

Resources

SQLAlchemy Quick Start Guide
SQL Tutorial for fundamentals.

Comments

Channel and likes calls indicate the video format.
Hands-on coding is emphasized for understanding of SQLAlchemy features.

Hope you found these notes helpful! Make sure to experiment with different queries and table relationships to master SQLAlchemy! 🚀

Enjoy your coding journey! 😊

Remember to always practice and explore beyond the basics to get the best out of SQLAlchemy in your projects! 😊

Resources:

That’s a wrap! 🎬

Keep pushing your coding skills to the next level! 🚀

End of Lecture Notes

Bye! ☺️

SQLAlchemy Overview