On Tue, Jan 2, 2018 at 9:54 AM, Jevgenij Kusakovskij <kush...@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+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.
