Sqlalchemy

Foreign Key
Details
python
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Person(Base):
    __tablename__ = "people"

    ssn = Column('ssn', Integer, primary_key=True)
    firstname = Column('firstname', String)
    lastname = Column('lastname', String)
    gender = Column('gender', CHAR)
    age = Column('age', Integer)

    def __init__(self,ssn,first,last,gender,age):
        self.ssn = ssn
        self.firstname = first
        self.lastname = last
        self.gender = gender
        self.age = age
    
    def __repr__(self):
        return f"({self.ssn} {self.firstname} {self.lastname} {self.gender} {self.age})"

class Thing(Base):
    __tablename__ = "things"
    thing_id = Column('thing_id', Integer, primary_key=True)

    description = Column("description", String)

    owner = Column(Integer, ForeignKey('people.ssn'))

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

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


engine = create_engine('sqlite:///data.db', echo=True)
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()

person = Person(12345,"Mike","Smith","M",34)
session.add(person)
session.commit()

p1 = Person(54234,"Jason","Henry","M",50)
p2 = Person(53642,"Sam","Adams","M",64)
p3 = Person(64234,"Rachel","Lee","F",35)
p4 = Person(75234,"Erica","Brown","F",18)
p5 = Person(64323,"John","Granger","M",42)
p5 = Person(43236,"Janet","Lee","M",25)

session.add(p1)
session.add(p2)
session.add(p3)
session.add(p4)
session.add(p5)
session.commit()

t1 = Thing(1,"Car",p1.ssn)
t2 = Thing(2,"Laptop",p1.ssn)
t3 = Thing(3,"PS5",p2.ssn)
t4 = Thing(4,"Tool",p3.ssn)
t5 = Thing(5,"Book",p4.ssn)

session.add(t1)
session.add(t2)
session.add(t3)
session.add(t4)
session.add(t5)
session.commit()

results = session.query(Person,Thing).filter(Thing.owner == Person.ssn).filter(Person.firstname == "Jason")

print([r for r in results])

# results = session.query(Person).all()
# print(results)

# results = session.query(Person).filter(Person.lastname == "Lee")
# print(results)
# print([r for r in results])

# results = session.query(Person).filter(Person.age > 30)
# print(results)
# print([r for r in results])

# results = session.query(Person).filter(Person.firstname.like('J%'))
# print(results)
# print([r for r in results])

results = session.query(Person).filter(Person.firstname.in_(["Jason","Sam"]))
print(results)
print([r for r in results])
Sqlalchemy ​

Sqlalchemy
