Great! Thanks a lot for all your help!
On Tuesday, January 2, 2018 at 6:48:35 PM UTC+2, Mike Bayer wrote:
>
> On Tue, Jan 2, 2018 at 11:24 AM, Jevgenij Kusakovskij <kus...@gmail.com
> <javascript:>> 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+...@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.
