[HN Gopher] Show HN: Facata - Connect to SQL Databases from Python
       ___________________________________________________________________
        
       Show HN: Facata - Connect to SQL Databases from Python
        
       Author : tlocke
       Score  : 14 points
       Date   : 2021-08-21 16:47 UTC (6 hours ago)
        
 (HTM) web link (github.com)
 (TXT) w3m dump (github.com)
        
       | tlocke wrote:
       | 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 :-)
        
         | thesketh wrote:
         | 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
        
           | tlocke wrote:
           | That's a good idea. I'll look into it, thanks!
        
         | BiteCode_dev wrote:
         | What's the benefit over using SQL alchemy connection pool and
         | passing it raw sql ?
        
           | tlocke wrote:
           | 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.
        
         | frafra wrote:
         | 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?
        
           | tlocke wrote:
           | 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.
        
       ___________________________________________________________________
       (page generated 2021-08-21 23:02 UTC)