CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Eli
Eli

Reputation: 38929

Can SQLAlchemy Use MySQL's SSCursor For Only Some Queries?

I have a query that fetches a lot of data from my MySQL db, where loading all of the data into memory isn't an option. Luckily, SQLAlchemy lets me create an engine using MySQL's SSCursor, so the data is streamed and not fully loaded into memory. I can do this like so:

create_engine(connect_str, connect_args={'cursorclass': MySQLdb.cursors.SSCursor})

That's great, but I don't want to use SSCursor for all my queries including very small ones. I'd rather only use it where it's really necessary. I thought I'd be able to do this with the stream_results setting like so:

conn.execution_options(stream_results=True).execute(MyTable.__table__.select())

Unfortunately, when monitoring memory usage when using that, it seems to use the exact same amount of memory as if I don't do that, whereas using SSCursor, my memory usage goes down to nil as expected. What am I missing? Is there some other way to accomplish this?

Upvotes: 2

Views: 883

Answers (1)

Jack
Jack

Reputation: 21163

From the docs:

stream_results – Available on: Connection, statement. Indicate to the dialect that results should be “streamed” and not pre-buffered, if possible. This is a limitation of many DBAPIs. The flag is currently understood only by the psycopg2 dialect.

I think you just want to create multiple sessions one for streaming and one for normal queries, like:

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

def create_session(engine):
  # configure Session class with desired options
  Session = sessionmaker()

  # associate it with our custom Session class
  Session.configure(bind=engine)

  # work with the session
  session = Session()

  return session


#streaming
stream_engine = create_engine(connect_str, connect_args={'cursorclass': MySQLdb.cursors.SSCursor})
stream_session = create_session(stream_engine)

stream_session.execute(MyTable.__table__.select())

#normal
normal_engine = create_engine(connect_str)
normal_session = create_session(normal_engine)

normal_session.execute(MyTable.__table__.select())

Upvotes: 2

Related Questions