sqlalchemy `.fetchmany()` vs `.limit()`

I have found fetchmany to be very useful when you need to get a very large dataset from the database but you do not want to load all of those results into memory. It allows you to process the results in smaller batches.

result = conn.execution_options(stream_results = True).execute(
   SomeLargeTable.__table__.select()
)
while chunk: = result.fetchmany(10000) # # only get 10 K rows at a time
for row in chunk:
   # # process each row before moving onto the next chunk

Suggestion : 2

I have found fetchmany to be very useful anycodings_sqlalchemy when you need to get a very large anycodings_sqlalchemy dataset from the database but you do not anycodings_sqlalchemy want to load all of those results into anycodings_sqlalchemy memory. It allows you to process the anycodings_sqlalchemy results in smaller batches.,limit will be a part of the sql query anycodings_sqlalchemy sent to the database server.,With fetchmany the query is executed anycodings_sqlalchemy without any limit, but the client anycodings_sqlalchemy (python code) requests only certain anycodings_sqlalchemy number of rows.,Therefore using limit should be faster anycodings_sqlalchemy in most cases.

Example usage would be:

query = select([census.columns.state, (census.columns.pop2008 - census.columns.pop2000).label("pop_change")]).group_by(census.columns.state).order_by(desc("pop_change"))
results_1 = query.limit(5).fetchall()
results_2 = connection.execute(query).fetchmany(n) #`results_2` = `results_1`

result = conn.execution_options(stream_results = True).execute(
   SomeLargeTable.__table__.select()
)
while chunk: = result.fetchmany(10000) # # only get 10 K rows at a time
for row in chunk:
   # # process each row before moving onto the next chunk

Suggestion : 3

This section describes how to use transactions when working directly with Engine and Connection objects. When using the SQLAlchemy ORM, the public API for transaction control is via the Session object, which makes usage of the Transaction object internally. See Managing Transactions for further information.,The Connection object is procured by calling the Engine.connect() method of the Engine object, and provides services for execution of SQL statements as well as transaction control.,To use the schema translation feature with the ORM Session, set this option at the level of the Engine, then pass that engine to the Session. The Session uses a new Connection for each transaction:,The most common way to access the raw DBAPI connection is to get it from an already present Connection object directly. It is present using the Connection.connection attribute:

engine = create_engine('mysql://scott:tiger@localhost/test')

from sqlalchemy
import text

with engine.connect() as connection:
   result = connection.execute(text("select username from users"))
for row in result:
   print("username:", row['username'])

with engine.connect() as connection:
   with connection.begin():
   r1 = connection.execute(table1.select())
connection.execute(table1.insert(), {
   "col1": 7,
   "col2": "this is some data"
})

# runs a transaction
with engine.begin() as connection:
   r1 = connection.execute(table1.select())
connection.execute(table1.insert(), {
   "col1": 7,
   "col2": "this is some data"
})

# method_a starts a transaction and calls method_b
def method_a(connection):
   with connection.begin(): # open a transaction
method_b(connection)

# method_b also starts a transaction
def method_b(connection):
   with connection.begin(): # open a transaction - this runs in the
# context of method_a 's transaction
connection.execute(text("insert into mytable values ('bat', 'lala')"))
connection.execute(mytable.insert(), {
   "col1": "bat",
   "col2": "lala"
})

# open a Connection and call method_a
with engine.connect() as conn:
   method_a(conn)

# method_a calls method_b
def method_a(connection):
   method_b(connection)

# method_b uses the connection and assumes the transaction
# is external
def method_b(connection):
   connection.execute(text("insert into mytable values ('bat', 'lala')"))
connection.execute(mytable.insert(), {
   "col1": "bat",
   "col2": "lala"
})

# open a Connection inside of a transaction and call method_a
with engine.begin() as conn:
   method_a(conn)