On Tue, Jan 2, 2018 at 11:24 AM, Jevgenij Kusakovskij <[email protected]> wrote: > I see... I should have warned that I am new to Python and that questions of > this caliber could be expected. > > If I may ask one more thing, I would like to check with you if it is > possible to achieve the same effect > without any custom options by simply the executemany flag in the if clause. > It would be: > > @event.listens_for(SomeEngine, 'before_cursor_execute') > def receive_before_cursor_execute(conn, cursor, statement, parameters, > context, executemany): > if executemany: > cursor.fast_executemany = True > > > If I use this code, then there is not need for any custom options and the > code runs almost as fast as the > raw connection, i.e. in 2 seconds instead of 2.5 minutes.
oh yes, absolutely, do that way. good call. we have a built-in option for a similar feature in the psycopg2 driver and support for this option can be added as a feature to the pyodbc dialect. Feel free to add a ticket. > > Thank you kindly for the clarifications, for the quick responses and for > your patience! > > On Tuesday, January 2, 2018 at 5:15:52 PM UTC+2, Mike Bayer wrote: >> >> On Tue, Jan 2, 2018 at 9:54 AM, Jevgenij Kusakovskij <[email protected]> >> wrote: >> >> @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 >> >> dict.get('some_key', True) means if the key is not found, you get True >> back, e.g. True is the default. >> >> if you want the default to be False, then if some_key is present use that, >> it's >> >> dict.get('some_key', False) >> >> if key is present, you get key back, assuming it's True you get True >> key is not present, you get default back, e.g. False >> >> >> >> >> > >> > 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]> >> >> 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]. >> >> > 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. >> > >> > -- >> > 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. > > -- > 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. -- 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.
