> > @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 <kus...@gmail.com > <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 sqlalchemy+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.