Show HN: Facata – Connect to SQL Databases from Python | Hacker News Reader

Show HN: Facata – Connect to SQL Databases from Python

github.com

24 pointstlocke11 comments

Creator of Facata here. DBAPI 2 is the standard for accessing SQL databases from Python, but since it's about 20 years old now, I thought I'd have a go at updating it in the form of a Python library that's a wrapper for DBAPI 2 driver. The main differences with DBAPI 2 are:

* DBAPI 2 has five ways of writing parameters in SQL, but Facata has just one.

* In DAPI 2 parameter values can be provided as a sequence or mapping, but in Facata parameter values are provided as keyword arguments.

* DBAPI 2 has four levels of threadsafety, but Facata just has one.

* DBAPI 2 has a 'cursor' object, but in Facata there is no cursor object and statements are executed using the connection.

* DBAPI 2 overrides the autocommit mode of the underlying database by silently sending START TRANSACTION statements if a transaction isn’t already in progress. Facata never silently sends SQL statements.

All feedback gratefully received :-)

What's the benefit over using SQL alchemy connection pool and passing it raw sql ?

You could do that, but Facata works at a lower level, so for example you can use prepared statements, which SQLAlchemy doesn't support. Also, Facata has better performance and fewer dependencies if you only want to use raw SQL.

SQLA automatically creates prepared statements and cache them: https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#p...

I get that Facata is a smaller dependency chain. I'm also curioujs about the performance side, what areas is Facata more performant in, and how was it measure?

That prepared statement caching is a feature of a particular DBAPI driver rather than SQLAlchemy itself. I take your point though, and I'd say that Facata is designed to be an alternative to DBAPI 2 rather than an alternative to SQLAlchemy.

On the performance, I'm basing this on the fact that SQLAlchemy does a lot more than Facata. You're right though, actual measurements would be interesting.

Actually, I retract my comment that Facata has better performance than SQLAlchemy with raw SQL.

Really, the only reason I can think of for using Facata over SQLAlchemy is if you don't want the extra functionality that SQLAlchemy gives you.

This looks really interesting! I do appreciate how clunky the DB-API is, this looks like a really nice alternative.

You should consider writing a full abstract implementation so that it's easy to implement this API for new DBs, Python's abstract base classes are great. I did something similar with the DB API PEP: https://github.com/thesketh/pep249

That's a good idea. I'll look into it, thanks!

Nice project! Is it planned to use the with statement for transactions or having something like psycopg2.sql.Literal to do proper escaping in complex queries?

Thanks! Facata is really a low-level API, so the only SQL that's sent is what's explicitly sent. That means it wouldn't have a 'with' for transactions because that would require SQL to be sent 'behind the scenes'.

Thanks for pointing me in the direction of psycopg2.sql, it looks interesting and I'll give it some thought.

like things to be simplified. nice project.

Reply on news.ycombinator.com