Is it possible in SQLAlchemy to filter by a database function or stored procedure?
- by Rico Suave
We're using SQLalchemy in a project with a legacy database. The database has functions/stored procedures. In the past we used raw SQL and we could use these functions as filters in our queries.
I would like to do the same for SQLAlchemy queries if possible. I have read about the @hybrid_property, but some of these functions need one or more parameters, for example;
I have a User model that has a JOIN to a bunch of historical records. These historical records for this user, have a date and a debit and credit field, so we can look up the balance of a user at a specific point in time, by doing a SUM(credit) - SUM(debit) up until the given date.
We have a database function for that called dbo.Balance(user_id, date_time). I can use this to check the balance of a user at a given point in time.
I would like to use this as a criterium in a query, to select only users that have a negative balance at a specific date/time.
selection = users.filter(coalesce(Users.status, 0) == 1,
coalesce(Users.no_reminders, 0) == 0,
dbo.pplBalance(Users.user_id, datetime.datetime.now()) < -0.01).all()
This is of course a non-working example, just for you to get the gist of what I'd like to do. The solution looks to be to use hybrd properties, but as I mentioned above, these only work without parameters (as they are properties, not methods).
Any suggestions on how to implement something like this (if it's even possible) are welcome.
Thanks,
