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 1. Expose an execute_pandas method on a custom Connection object. 2. 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. Do these approaches seem sane? Are there any other approaches that you'd recommend? I have a version of method one implemented in this commit <https://github.com/vamega/sqlalchemy_exasol/commit/02f5b6ea93af09d128261f5e1c9f674ac433783e>. It somewhat works, but relies on having a custom EngineStrategy to have create_engine return a Connection subclass. It also doesn't have knowledge of being in a transaction, and auto commit, and probably a lot of other things. The branch fetch-pandas will be tracking that approach. I like the second approach more, although from some initial investigation it's going to be a lot more work to get that working seamlessly across the execution and core levels of SqlAlchemy, and I'm not even sure if it's possible. I appreciate any feedback you might have. Finally, I'd also like to thank everyone here for a wonderful product. I don't use the ORM much, but I really love using SQLAlchemy! 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-oGk6NO%3DciTMrRzQ-ruez5_bcC-qBxuPWL2k5F%3DTUbNDMWw%40mail.gmail.com.
