Python Records: Doc Extension.

This is the unfinished story started by someone I would like to have coffee with, while unwinding on how the tech eco system has been for us all.

His name is Kenneth Reitz and I have got so much love and respect for him.

tl;dr you have been warned. Just kidding, its not that long.

Incase you don’t know what this library is used for, here is a quick summary:

Records is a python library designed to quickly connect to database tables using raw sql and with just a few lines of code you are set.

The above code is an example gotten from the official github page, I made a little modification. Now the documentation goes on to describe how to access data from the rows object that was created and how easy it can be used.

I particularly enjoy the dataset method, where he used tablib to display records and give the data a well aligned table set, it kind of makes me feel like I was looking at the table records the way its displayed when connected the the db server from the terminal.

Here is where my issue began, I have been wondering on how to use records for actual production quality code development. I wanted to sort of control how my applications accessed the database. i.e be able to commit changes when all things goes right and rollback to a previous table state when a few things fail, obeying the atomic property of RDBMS within my application.

Unfortunately the documentation didn’t specify that. So I did what every other out of the box thinking problem solver would do. I browsed the record lib source code and I was a bit relieved that it was a small code base, less than 500 lines in total. Here are my findings, I hope that Kenneth will finally oblige for that coffee.

Lets start with the `query` method.

Look at line three on the above code snippet. on the right hand side you will notice a small select query that is being sent to the database for a response.

If you wanted to run a query with filters ` where clause ` or you need to pass parameters to your SQL query before it gets executed, here is a neat way to do it.

I was formally using Python string format method for this operation and it was a very terrible approach, as it required me to do the data type casting manually.

Now, normally using the query object means that you don’t have control of when the connection to the db is open and when its closed. If you require to loop through a long list of values that is meant to be passed into the database you probably will be faced with so many errors in your application, because your connection to the database will be closed before your loop goes for the next cycle.

So to control when the application connection to the database is open and closed, here is how to implement it.

The above example will ensure that you use the db connection instance for as long as you want to use it. However should there be any errors, you wont be able to get the errors logged as this transaction method uses a try except and finally internally to silence the errors.

If you want to be able to log the errors that might occur here is how to implement it.

With the above code snippet, if there is an error in your application, your logger function will keep record of it immediately, and you will be able to see it and do something about it.

One more thing before I drop the mic, records was built on top of sqlalchemy, which means that the you get the full power of sqlalchemy while using records for db connections. The databases that sqlalchemy supports which includes MSSQL, PostgresSQL, Oracle, Mysql, Firebird, Sqlite, DB2, SysBase etc, you can also interact with it as well.

I finally can now use records on my production environment, the same way i will use sqlalchemy or Django Models. I there say that its faster to setup and also easier too.

Another library I would love to use is peewee but for now I think am okay with the way records works, besides this is Lagos, I need to pay some bills before my landlord comes after me.

ps: only Lagosians will understand the above joke.

I run a software company here in Lagos, Nigeria. I train individuals and corporate bodies on Python, Javascript (javascript, reactjs, vuejs, backbonejs, angularjs, html and css) and SQL for any RDBMS. (website https://www.ecardex.com)

Lastly I create software products for businesses and cooperates.

Do you have interesting projects, hit me up.