CODEWITHSUNDEEP
pythonsqlsqlalchemy
user990726
user990726

Reputation: 21

sql alchemy python query query.first() _and

I am modifying some code I did not write. The problem with this code is that tracking numbers have started to recycle with the carrier. So for a given tracking number there may be two sometimes 3 rows in the database. This code apparently finds the first record in the query which in our case is the 'oldest created date'.

So I would like to understand how to change this to one of the 2 options I am thinking so the program only pulls the most recent tracking number.

  1. Retrieve the last record of the query and place it in manifest variable.
  2. use an 'AND' on the query filter. I have a created_date column that I can say: if tracking number = exception tracking number AND created date > 1/1/20011.

So if someone could provide the code that does #1 and #2 I would be grateful.

query = session.query(database.Manifest)
query = query.filter(database.Manifest.tracking_number==exception.TrackingNumber)

manifest = query.first()

I thought for number 2, the statement could be:

query = query.filter(_and(database.Manifest.tracking_number==exception.TrackingNumber, 
database.Manifest.created_date > '2011-01-01'))

ok?

Upvotes: 2

Views: 3669

Answers (1)

donkopotamus
donkopotamus

Reputation: 23206

Your first option cannot be done accurately unless you have a column, like an identity, that helps you understand the order in which rows were put into the table. (EDIT: From your attached comments it seems you have a created_at field ...)

So a query would be

query = (session.query(database.Manifest)
         .filter_by(tracking_number=exception.TrackingNumber)
         .order_by(desc(database.Manifest.created_at)))   
manifest = query.first()

If you don't, then you must rely on your database simply returning rows in the order they were first inserted by magic, then you will need to grab all of them and take the last one

query = (session.query(database.Manifest)
         .filter_by(tracking_number=exception.TrackingNumber))
manifest = query.all()[-1]

And frankly, you can't rely on your database to do that ... it will generally return them in whatever it assesses to be the fastest available way.

As for the second option you ask for, it would just be

query = (session.query(database.Manifest)
         .filter_by(tracking_number=exception.TrackingNumber)
         .filter(database.Manifest.date > datetime.date(2011, 1, 1)))

manifest = query.first()

Upvotes: 4

Related Questions