Hi Mike. Thank you for taking the time to respond. Responses inline
On Thu, Aug 29, 2019 at 10:35 AM Mike Bayer <[email protected]> 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. 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CADm-oGmAbZu-Wa-kL16BbUXd0UEk4QCmf1_Y0RzFCW0wfSC4XA%40mail.gmail.com.
