Why I Created DBRows

Author: Szymon Lipiński
Published at: 2017-02-15

Some time ago I was using the Records, a library loved by so many people. This library is simple, it’s just a proxy to some other libraries. That’s fine.

I was really not impressed with the library interface.

After some time of using the library I got the feeling that the interface is not too good, it looked like some random ad hoc bunch of functions.

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.

DBRows Basic Ideas

So the ideas were simple: make some nice interface for the database operations using all the things we have in Python: 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 beings.

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 being. 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, 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:

The Wrong Requests Interface

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 the antipattern that I named:

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

and 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 rather used internally.

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()

or just using the things Python gives us, and closing 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 comments are disabled. If you want to write something to me, you can use e.g. Twitter.