On Thu, Aug 29, 2019, at 10:27 PM, Varun Madiath wrote: > Hi Mike. > > Thank you for taking the time to respond. Responses inline > > On Thu, Aug 29, 2019 at 10:35 AM Mike Bayer <mike...@zzzcomputing.com> wrote: >> __ >> >> hi there, responses inline >> >> On Wed, Aug 28, 2019, at 11:39 PM, Varun Madiath wrote: >>> Hi. >>> >>> I'm working to extend the Turbodbc >>> <https://turbodbc.readthedocs.io/en/latest/> dialect of the >>> sqlalchemy_exasol <https://github.com/blue-yonder/sqlalchemy_exasol/> >>> project to support executing against a pandas dataframe. >>> >>> I'm aware that SqlAlchemy can use the executemany method of the DBAPI, and >>> I've seen that the MSSql dialect can even use PyODBC's fast_executemany >>> feature to speed up execution. However these all rely on the conversion of >>> the data into a list of dictionaries that is then passed to the execute >>> method of sqlalchmey. >>> >>> Turbodbc has the ability to execute against a PyArrow table directly >>> <https://turbodbc.readthedocs.io/en/latest/pages/advanced_usage.html#using-apache-arrow-tables-as-query-parameters>. >>> I was hoping to get some advice on the best way to be able to expose this >>> optimisation to users. Here are two approaches I'm considering >> >> what I dont understand about this API is what the contents of the SQL string >> need to be. Is this method parsing the SQL string "INSERT INTO my_table >> VALUES (?, ?)" ? that's not exactly how a SQLAlchemy INSERT statement looks, >> for example, we include the column names. > I'm sorry but I don't understand what you don't understand. The SQL string is > the same as any other DBAPI driver that using the qmark paramstyle.
Here are the two forms: INSERT INTO my_table (id, data) VALUES (?, ?) INSERT INTO my_table VALUES (?, ?) the first has the column names we are inserting towads listed out explicitly, this is the form that a SQLAlchemy insert() construct for example will generate. > I know that the sqlalchemy text type can convert from a named parameter style > to a qmark style based on the driver, is that what you're referring to when > you say "we include the column names" >> >> >> >> >>> 1. Expose an execute_pandas method on a custom Connection object. >> >> SQLAlchemy's Connection class is not a subclassing target. The internal >> Dialect on the other hand is, and that's where you can extend all kinds of >> various execution hooks. >> >> One reason is makes no sense to subclass Connection is that custom DBAPI >> features that require special execution styles are much more simply exposed >> using the DBAPI directly, based on the patterns documented at >> https://docs.sqlalchemy.org/en/13/core/connections.html#working-with-raw-dbapi-connections >> . Another is that Connection accepts an execution_options() dictionary >> which is where dialect-specific directives should be placed, where they can >> be interpreted by the Dialect. > > Glad to know that I should not be subclassing Connection. Given how hard I > had to look to find a way to get create_engine to return a Connection > subclass, I had a feeling that this might not be the right way to go about > this. > As for using raw DBAPI connections, it's something I considered, but I was > hoping to have an API that still allowed for me to use the SQLAlchemy events, > and also to keep DB execution functions under one namespace. > I used to have a function called `execute_with_dataframe` in my own > `sqlalchemy-helper` package, but that required that I compile any Statement > to a string, and then pass that string and the associated table to > `execute_with_dataframe` and I was hoping to create a less disconnected API. > >>> 1. Extend the execute function in a custom Connection class to check if >>> the parameter is a pandas DataFrame and if it is, use executemanycolumns >>> method of turbodbc, and otherwise defer to Connection.execute. >> >> The most straightforward approach is to support an execution_option, so that >> you only need to change your dialect's do_executemany() method to look in >> context._execution_options for the key/value pairs that you've documented as >> being meaningful to your dialect. >> >> conn.execution_options(exasol_pyarrow_table=my_table).execute("insert into >> table foo bar") > > I'm going to look into this, it appears very promising. > > Thank you for your help > > Varun Madiath > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CADm-oGmAbZu-Wa-kL16BbUXd0UEk4QCmf1_Y0RzFCW0wfSC4XA%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CADm-oGmAbZu-Wa-kL16BbUXd0UEk4QCmf1_Y0RzFCW0wfSC4XA%40mail.gmail.com?utm_medium=email&utm_source=footer>. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/5a05665f-fcbb-4de4-8fff-289987cdc528%40www.fastmail.com.
