Duckdb: SQLAlchemy dialect

[xhochy]

This is probably also useful for #302 when integrating with Ibis but also in general a feature people are using. The important part here is to have an SQLAlchemy dialect to render queries, connect to the database and get metadata about the tables. The acutal ORM that comes with SQLAlchemy is totally useless for data analytics (it's good when developing a blog) as it creates a lot of Python objects.

SQLAlchemy is often used as a more convinient access layer for databases in Python than the DB-API 2.0.

As duckdb is based off Postgres SQL-dialect wise, you probably can build upon that dialect.

[hannesmuehleisen]

I agree, its probably a good idea to support this. We need to look int result set construction performance though.

[xhochy]

I agree, its probably a good idea to support this. We need to look int result set construction performance though.

That is too slow when you have large results. Dialect and metadata are the great wins. SQLAlchemy needs someone to write an extension in future so that the sqlalchemy.Engine can return pandas/numpy/.. but for now I always render the query to a string and use raw_connection() for the result heavy queries.

[antoinecarme]

Thanks to the team for this project.

It would be nice indeed to have an embedded in-process/in-memory version of one of the highest quality databases on the market. SQLAlchemy support is real added value and PostgreSQL SQLAlchemy is already an excellent one.

To be precise, I am interested in an equivalent of SQLite(":memory:") with full SQLAlchemy support and pandas input/output.

[hannesmuehleisen]

We already have pandas input output in DuckDB, check out the relational API

[totalhack]

I'm also interested in testing DuckDB out as an alternative datasource in an analytics/warehousing project I'm working on called Zillion. I'm using the SQLAlchemy core expression language to build datasource queries. There is also a "combined layer" database that is simply an in-memory SQLite DB right now; DuckDB would be interesting to test there too. The latter doesn't use SQLAlchemy now but I was planning on moving towards that to open up the list of supported combined layer DBs.

Has there been any further discussion on the timeline for a SQLAlchemy plugin?

Thanks, just heard about this project yesterday and it looks great!

EDIT: I haven't tried this yet, and don't see a link to the source files, but it looks like someone is giving this a go: https://pypi.org/project/duckdb-engine/

[Mause]

Please be aware that that implementation is as basic as I have said in the readme