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.
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.
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.
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
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.
11 comments
[ 3.2 ms ] story [ 35.4 ms ] thread* 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 :-)
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?
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.
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.
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
Thanks for pointing me in the direction of psycopg2.sql, it looks interesting and I'll give it some thought.