SQLAlchemy Overview

Jul 16, 2024

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

[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

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! ☺️