>
> @event.listens_for(SomeEngine, 'before_cursor_execute')
> def receive_before_cursor_execute(conn, cursor, statement, parameters,
> context, executemany):
> if context.execution_options.get('pyodbc_fast_execute', False):
> cursor.fast_executemany = True
Maybe I am missing something, but should it be:
if context.execution_options.get('pyodbc_fast_execute', True):
cursor.fast_executemany = True
On Tuesday, January 2, 2018 at 4:27:53 PM UTC+2, Mike Bayer wrote:
>
> On Tue, Jan 2, 2018 at 6:46 AM, Jevgenij Kusakovskij <[email protected]
> <javascript:>> wrote:
> > I would like to send a large pandas.DataFrame to a remote server running
> MS
> > SQL. I am using pandas-0.20.3, pyODBC-4.0.21 and sqlalchemy-1.1.13.
> >
> > My first attempt of tackling this problem can be reduced to following
> code:
> >
> > import sqlalchemy as sa
> >
> >
> >
> > engine = sa.create_engine("mssql+pyodbc:///?odbc_connect=%s" %
> cnxn_str)
> > data_frame.to_sql(table_name, engine, index=False)
> >
> >
> > Simple, but very slow... Took about 2.5 minutes to insert 1000 rows.
>
> that's really weird, 1000 rows is very few. I'm pretty sure if I ran
> 1000 rows over pyodbc into SQL server here it would take about 300 ms
> tops. 2.5 minutes is more like you're trying to send 800K rows.
> that alone is kind of concerning, and if pandas is not sending all the
> rows to connection.execute() at once and is instead running one row at
> a time, then the fast_executemany flag will have no effect for you.
>
> >
> > Using the following code, that does not involve SQLAlchemy, the same
> task is
> > performed in less than a second:
> >
> > import pyodbc as pdb
> >
> > list_of_tuples = convert_df(data_frame)
> >
> > connection = pdb.connect(cnxn_str)
> >
> > cursor = self.connection.cursor()
> > cursor.fast_executemany = True
> > cursor.executemany(sql_statement, list_of_tuples)
> > connection.commit()
> >
> > cursor.close()
> > connection.close()
> >
> >
> > Is there a way to flip the fast_executemany switch on when using
> SQLAlchemy?
>
> easiest would be to use cursor execution events:
>
>
> http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=before_cursor_execute#sqlalchemy.events.ConnectionEvents.before_cursor_execute
>
>
> you get the cursor right there, set the flag. You can set a custom
> execution_option:
>
> conn = conn.execution_options(pyodbc_fast_execute=True)
>
> then in your event you can look for it:
>
> @event.listens_for(SomeEngine, 'before_cursor_execute')
> def receive_before_cursor_execute(conn, cursor, statement, parameters,
> context, executemany):
> if context.execution_options.get('pyodbc_fast_execute', False):
> cursor.fast_executemany = True
>
>
> but....2.5 minutes for 1000 rows is much more wrong than that, you
> should figure out what's happening there.
>
>
>
>
>
>
> >
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > description.
> > ---
> > You received this message because you are subscribed to the Google
> Groups
> > "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an
> > email to [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
