sqlalchemy: sqlalchemy "do_orm_execute" event does not trigger for pandas.read_sql
Describe the bug
When using the pd.read_sql function in pandas, the SQLAlchemy “do_orm_execute” event is not triggered, so logic tied to that event will not run. If for example, the application is using the do_orm_execute event to enforce security rules, this can be a problem as no errors are triggered and all results are returned.
SQLAlchemy recommends using this event for public filters and other use cases. See: https://docs.sqlalchemy.org/en/14/_modules/examples/extending_query/filter_public.html
It is possible this is due to some logic missing in SQLAlchemy, but I noticed it only for queries using this pandas function.
To Reproduce
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Column, Integer, String, event
from sqlalchemy.orm import Session
import pandas as pd
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://[insert connection here]'
db = SQLAlchemy(app)
@event.listens_for(Session, "do_orm_execute")
def _add_filtering_criteria(execute_state):
print("*** The do_orm_execute event was triggered ***")
class Data(db.Model):
__tablename__ = 'testdata'
id = Column(Integer, primary_key=True, autoincrement=True, index=True)
name = Column(String(255))
info = Column(String(255))
if __name__ == '__main__':
# setup some test data
print("insert test data")
data = Data(
name="Alice",
info="Apple")
db.session.add(data)
db.session.commit()
data = Data(
name="Bill",
info="Banana")
db.session.add(data)
db.session.commit()
# results from orm query
print("\norm test")
orm_data = Data.query.all()
for result in orm_data:
print(result.id, result.name, result.info)
# results from pandas query
print("\npandas test")
pd_data = Data.query
df = pd.read_sql(pd_data.statement, pd_data.session.bind)
print(df)
-------------------------------------------------------------------------
Generates this result:
-------------------------------------------------------------------------
insert test data
orm test
*** The do_orm_execute event was triggered ***
1 Alice Apple
2 Bill Banana
pandas test
id name info
0 1 Alice Apple
1 2 Bill Banana
Process finished with exit code 0
Error
# Copy complete stack trace and error message here, including SQL log output if applicable.
Versions
- OS: Mac OS, Linux
- Python: 3.9
- SQLAlchemy: 1.4.31
- Database: MariaDB 10.4.22
- DBAPI (eg: psycopg, cx_oracle, mysqlclient): PyMySQL
Additional context
Also logged with pandas, however it was determined this is not a pandas bug: https://github.com/pandas-dev/pandas/issues/45978
About this issue
- Original URL
- State: closed
- Created 5 years ago
- Comments: 26 (16 by maintainers)
Commits related to this issue
- add note reiterating do_orm_execute() is for ORM statements only Fixes: #7707 Change-Id: I7216f63d9f4269059438e41ad8dd3dffc25a5d03 (cherry picked from commit 619abb52b6f1ee023db0f85fd96ba9f88c8efa7b) — committed to sqlalchemy/sqlalchemy by zzzeek 5 years ago
- add note reiterating do_orm_execute() is for ORM statements only Fixes: #7707 Change-Id: I7216f63d9f4269059438e41ad8dd3dffc25a5d03 — committed to relsunkaev/sqlalchemy by zzzeek 5 years ago
so one thing I think is relevant here is that when someone makes a statement and then invokes pd.read_sql(), that is already something that happens in your application, so in theory you could forego using events entirely and instead make sure that pd.read_sql() isn’t called directly, rather, it goes through a library function that applies the rules you want to statements that are incoming.
another thing about read_sql is that the “con” argument is listed as a “connectable”, which refers to an object that has a .connect() method. If they are in fact calling .connect() on the object passed, that would make it more difficult to pass a Session instead of a connection to it, but you could make a Session subclass with a connect() method that returns “self”, then they’d be using session.execute().
Still another thing is, pandas read_sql should have an option to accept a Session object directly since session.execute() is now an important API that people are going to be using quite a lot. I’d request this feature from them.
re: select with join(), doing manipulation of select() is not that convenient which is why the whole with_loader_criteria() thing were added. I did not anticipate users would be stymied from using this API because Pandas doesn’t allow it. Digging into the select().froms and getting a Join object, you can add criteria to the ON clause of it, like join.onclause = and_(join.onclause, new_criteria). that’s fine. But it would be a whoile lot better if you could use the API that I wrote for this use case as I put a ton of effort into making this very common case possible.
If pandas is looking for a “connectable” see if you can do this:
because with_loader_criteria does all of that where/from stuff that is not very easy to do manually.