Jul 16, 2024
pip install SQLAlchemy
.from sqlalchemy import create_engine, Column, String, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
engine = create_engine('sqlite:///mydb.db', echo=True)
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()
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}"
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}"
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()
results = session.query(Person).all()
for person in results:
print(person)
filter
results = session.query(Person).filter(Person.last_name == 'Blue').all()
for person in results:
print(person)
filter
with numbersresults = session.query(Person).filter(Person.age > 25).all()
for person in results:
print(person)
results = session.query(Person).filter(Person.first_name.like('%a%')).all()
for person in results:
print(person)
in_
results = session.query(Person).filter(Person.first_name.in_(['Anna', 'Mike'])).all()
for person in results:
print(person)
results = session.query(Thing, Person).filter(Thing.owner == Person.ssn, Person.first_name == 'Anna').all()
for r in results:
print(r)
[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
Troubleshooting Tips
Good Practices
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:
Keep pushing your coding skills to the next level! π
End of Lecture Notes
Bye! βΊοΈ