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

Most upvoted comments

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:

class ConnectableSession(Session):
    def connect(self):
       return self

sess = ConnectableSession()
pandas.read_sql(statement, sess)

because with_loader_criteria does all of that where/from stuff that is not very easy to do manually.