On Tue, Jan 2, 2018 at 11:24 AM, Jevgenij Kusakovskij <kush...@gmail.com> 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 <kus...@gmail.com>
>> 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 <kus...@gmail.com>
>> >> 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.
>> >> > To post to this group, send email to sqlal...@googlegroups.com.
>> >> > 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+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > 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.
--
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.
