Why I Created DBRows

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 Database class
• the database class has a query() method
• 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.

The Database.query() function returns a RecordCollection object. It has two functions all() and first() which rather should be properties. It also has a property dataset, so we have all them mixed here.

The Record class has a couple of other functions which should rather be properties like keys(), values(), as_dict().

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: Database, RowCollection, and 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'])

Final Remarks

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.