Some time ago I was using the Records library. When I searched for some comments, I noticed only good reviews. The main goal of this library to simplify things. It’s a proxy to the SqlAlchemy library.
The only thing I was no impressed with was the library interface.
After some time of using it, I got the feeling that the interface is not good enough. It looked rather like some random ad hoc bunch of functions put into classes.
So I have decided to implement my own version. Mainly to find out if it is possible to create the kind of interface I described here.
That’s why the DBRows Project has been born.
The DBRows Basic Ideas
The idea was simple: make a nicer interface for the database operations. Implement it in Python using all the available things: decorators, context managers, iterators etc.
Why That Name?
Kenneth created Records, my version is named Rows. Why? Well, for me (and also many database experts like Joe Celko or Itzik Ben-Gan) those are two different things.
Basically, records and fields are some physical things. When we define a record in C, then we define the physical layout of the fields in the memory.
When we define a table with rows and columns, then we define some logical structure. When I insert a row into a table, I have no idea where and how it is stored. The database takes care of that. What’s more, there are some optimizations in the background. One row can be stored in multiple files, it can be divided, database can store multiple versions of that one row, etc.
I don’t know and I don’t care how the ROW is stored - that’s the main difference.
The Interface Similarities
The general architecture of those two projects is similar:
- there is some
- the database class has a
- this method returns a list like object with objects
- each of the objects in the list represents one row
The Wrong Requests Interface
I think that the current Requests interface breaks some of the rules of a good interface.
Database.query() function returns a
RecordCollection object. It has two functions
first() which rather should be properties. It also has a property
dataset, so we have all them mixed here.
Record class has a couple of other functions which should rather be properties like
So generally it’s the anti-pattern, which I name:
I remember the name, I have no idea how to write it
The context manager is implemented only for the
Database class so it can be used like:
with Database(...): #do something here
The database connection will be closed automatically after getting out of the
with block. It would be nice to have this also for transactions.
The DBRows Interface
It is quite similar, there are three basic classes:
Row. There is also
Transaction class, but that’s used internally to implement transaction operations.
All the members of the classes are either methods (named using verbs and doing some things) or properties (named using nouns and returning data).
You can use the library doing all the things on your own e.g. closing the connections and committing the transactions:
from dbrows import Database db = Database("postgresql://user:pass@localhost:5432/dbname") transaction = db.transaction rows = db.query("SELECT a, b, c FROM tab WHERE something > %s ORDER BY a, b", 30) for row in rows: print(row['a']) print(row['b']) transaction.rollback() db.close()
You can also use the Python goodies and close all the things automatically:
from dbrows import Database with Database("postgresql://user:pass@localhost:5432/dbname") as db: with db.transaction(rollback=True): for row in db.query("SELECT a, b, c FROM tab WHERE something > %s ORDER BY a, b", 30): print(row['a']) print(row['b'])
This project is not finished yet. It is tested only using PostgreSQL, so most probably it won’t work on other databases without changes.
The Records library is using SQLAlchemy internally which supports more than Postgres.