search

Mause / duckdb_engine

SQLAlchemy driver for DuckDB
MIT License
358 stars 41 forks source link

Question: Any way to create engine from existing connection? #1143

Open NickCrews opened 1 month ago

NickCrews commented 1 month ago

What happened?

See https://github.com/duckdb/duckdb/discussions/14549#discussion-7364961. Any way to go from duckdb.DuckDBPyConnection to a sqlalchemy.engine? I have to make my own little wrapper class that provides the needed methods for sqlalchemy? Thanks!

DuckDB Engine Version

NA

DuckDB Version

NA

SQLAlchemy Version

No response

Relevant log output

No response

Code of Conduct

Mause commented 4 weeks ago

I can't say I'd really recommend this, but you can do this to work around that error: 

from duckdb_engine import ConnectionWrapper

engine = sa.create_engine("duckdb://", creator=lambda: ConnectionWrapper(con))

this class just patches the duckdb connection to be what sqlalchemy expects from postgres/psycopg2

NickCrews commented 4 weeks ago

Thanks!

I can't say I'd really recommend this,

because you'd like to keep ConnectionWrapper as non-public, and you might make breaking changes at any time? That seems like a good idea.

What would you think about if you encapsulated that into a more simple API, and then exposed that publicly:

def create_engine(con: duckdb.DuckDBPyConnection) -> sqlalchemy.engine.Engine:
    return sa.create_engine("duckdb://", creator=lambda: ConnectionWrapper(con))

I'm not that familiar with sqlalchemy's API though, are users then gonna want to pass in various kwargs into creat_engine?