CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
Tyler Wood
Tyler Wood

Reputation: 1967

Instantiation model class object from pure SQL query?

I'm working on my first proper web-app, following the 'Flask Mega-Tutorial'. It's helped me get started with SQLAlchemy ORM and given me some understanding of how to use this tool.

For instance, I have a class Pet. I can call

foo = Pet.query.filter_where(id=1).first()

this results in foo of class Pet. The definition of Pet has many attributes, but each is of they type 'db.Column', which makes sense because each Pet is a record in the Pets table.

Where I'm confused is now I want to execute more sophisticate SQL against the Pet table but still have access to those records as Pet objects.

pets = db.session.execute('SELECT * FROM pet WHERE blahblah = blahblah').fetch_all()

And now I have this pets List which all of the attributes for each Pet record returned by the query.

How do I now instantiate objects of class Pet for each of these records? When I query with the ORM, I get objects back. My Pet class as defined in app/models.py does not have an init that accepts any arguments.

Right now I am taking the list of results, grabbing the "id" value from the results, and then in a for loop creating all of them as objects

pets = db.session.execute('SELECT * FROM pet WHERE blahblah = blahblah').fetch_all()

pet_objects=[]

for p in pets:
  record_id = p.id
  pet_object = Pet.query.filter_where(id=record_id)
  pet_objects.append(pet_object)

From which point I can treat my list of Pet objects as I would the results of a query using the ORM which would return objects automatically.

My question is, am I doing this correctly? Seems incorrect because for each record I hit the db initially with my SQL and then again to instantiate the object by its 'id' but I am not sure of how else to go from record -> Object in this framework.

Sorry for the long question, but I've been grappling with this for a while.

Upvotes: 0

Views: 867

Answers (1)

Mehdi
Mehdi

Reputation: 1350

Correct me if I'm wrong, you want to achieve the result of this query SELECT * FROM pet WHERE blahblah = blahblah using sqlalchemy.

You can use filter_by method in the base query class:

for example for your query you can use this:

pet_objects = Pet.query.filter_by(blahblah='blahblah').all()

You can create even more sophisticated queries using a join or simply add more where constraints with adding more arguments to filter_by for example: query.filter_by(col_1= 'something', col_2 = 'another thing')

Upvotes: 1

Related Questions