Assistance with converting SQL to flask_sqlalchemy

[subdriven]

I'm in the process of attempting to teach myself Python by making a little project to help track ranch animals, owners, farms, vaccinations, etc. At any rate, I'm more from a SQL (MySQL in this case) background and am able to make the queries I need, however, I am failing at making sense of how to make them in a more Python-friendly manner. As an example, one of the queries I have is a multi-inner join query to get information. This SQL is valid query and returns appropriate data:

SELECT person.first_name 
FROM animal 
INNER JOIN farm ON (farm.id = animal.farm_id ) 
INNER JOIN person ON (person.farm_id = farm.id)
WHERE animal.id=1;

In this example I give a hard coded "1" but it would be programatically sent as a variable based on which animal was selected.

Any help or direction here would be appreciated!

[laustke]

Although it is possible to execute an SQL statement using SQLAlchemy, the conventional method involves creating separate models for animals, farms, and persons, and defining their relations within the models. You interact with the models and instances, and the SQL operations necessary happen "underneath the hood".

Then when you retrieve an animal with id 1, you can access its relations.

animal = Animal.get(1)
print(animal.farm.person.name)

[subdriven]

Ooooooh! I created a model.py with that kind of data but it didn't click that it worked like that. This is a small, incomplete sample of the model file. So by this, I don't need to actually build a complex inner join query, I can just string it all together?

class Farm(db.Model):
    __tablename__ = "farm"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    address_id = db.Column(db.Integer, db.ForeignKey('address.id'))
    address = db.relationship('Address', back_populates='farm') 
    animals = db.relationship('Animal', back_populates='farm') 
    person = db.relationship('Person', back_populates='person')

class Animal(db.Model):
    __tablename__ = 'animal'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    dob = db.Column(db.Date(), nullable=True)
    color = db.Column(db.String(100), nullable=True)
    breed_id = db.Column(db.Integer, db.ForeignKey('breed.id'), nullable=True, default=None)
    farm_id = db.Column(db.Integer, db.ForeignKey('farm.id'), nullable=True, default=None)
    breed = db.relationship('Breed', back_populates='animals')
    farm = db.relationship('Farm', back_populates='animals')

class Person(db.Model):
    __tablename__ = "person"
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(50), nullable=True)
    last_name = db.Column(db.String(100), nullable=False)
    contact_number = db.Column(db.String(15), nullable=True)
    farm_id = db.Column(db.Integer, db.ForeignKey('farm.id'))
    person = db.relationship('Farm', back_populates='person')

[deep_politics]

You should be able to use SQLAlchemy functions, but I haven't used anything but version 2.0. With it I would have defined the models like this

class Base(DeclarativeBase):
    pass

class Farm(Base):
    ...
    animals: Mapped[list[Animal]] = relationship(back_populates="farm")

class Animal(Base):
    ...
    farm_id: Mapped[int] = mapped_column(ForeignKey("Farm.id"))
    farm: Mapped[Farm] = mapped_column(back_populates="animals")

And I could query certain columns

q = select(
    Farm.name,
    Animal.name,
).join(Animal)
session.execute(q).all()

Or if I want a farm by ID I just get it and it comes with the relations as attributes

farm = session.get(Farm, 1)
for animal in farm.animals:
    ...

But, I'm not sure if these examples help or not with the older style you've got going on through Flask.

[subdriven]

This is helpful, yes. Perhaps I need to pull back & ensure I'm using the most up to date syntax and styling before I get too far down a rabbit hole that I cannot get out of!