Having fun with context managers

Working with databases most of the time means working with transactions. You open a transaction, perform data manipulation and commit the transaction to finish up. If something goes wrong, instead of commit you’ll perform rollback that cleans everything you were doing inside of the transaction. Sometimes you want to commit the transaction after a simple database operation, sometimes you want to string lots of simple database operations together and commit only after all of them have been successfully performed. Nested transactions are one solution for this, another is to let the business logic dictate when the operation is finished. The later option means that data access layer should not care about transactions.

Python and Hy have very nice and elegant way of handling transactions with Sqlite databases. The following code is a function that is used to save a person into a database. Parameter person is a dict and connection is Sqlite connection. There is no transaction handling on this level.

(defn save-person [person connection]
  (let [[cursor (.cursor connection)]
        [params (, (:name person) (:id person))]
        [person-id (:id person)]]
    (if person-id (do (.execute cursor "update person set name=? where OID=?" params)
                      (load-person person-id connection))
        (do (.execute cursor "insert into person (name, OID) values (?, ?)" params)
            (let [[new-person-id cursor.lastrowid]]
              (load-person new-person-id connection))))))

The following code is a test that saves and loads person from a database (in-memory database in this case). The transaction handling is on this level, although it might not be apparent on a first glance.

(defn test-save-person []
  (with [connection (create-schema (get-in-memory-connection))]
        (let [[person {:id None :name "Pete"}]
              [saved-person (save-person person connection)]
              [loaded-person (load-person (:id saved-person) connection)]]
          (assert-that (:name loaded-person) (is- (equal-to "Pete"))))))

The magic is in with statement. Connection can be used as a context manager, which delegates transaction management to it. When with – block is entered a transaction is automatically started. When with block ends, the transaction is automatically commited, causing the changes to be written into the database. If there is an exception during the execution of with – block and the user code does not handle it, with catches it and performs a rollback.

The solution is really nice and elegant looking. The drawback is that it is not possible to call another function that uses with – block from within with – block, because that would cause problems with transaction management.


Working with database and hy

Recently I have been tinkering with Sqlite and hy. Both are new to me, so I decided to write a program that could simulate a small village or town. First I wanted to create and save a person to database. Hy has support for classes, but for now I opted to use a dictionary to hold person data. Following code demonstrates connecting to a database and saving a person there. I left out create-schema method, that just creates an empty database.

(defn load-person [id connection]
  (let [[cursor (.cursor connection)]
        [params (, id)]]
      (.execute cursor "select OID, * from person where OID=?" params)
      (let [[row (.fetchone cursor)]]
        (create-person-from-row row)))))

(defn save-person [person connection]
    (let [[cursor (.cursor connection)]
          [params (, (get person "name") (get person "id"))]
          [person-id (get person "id")]]
        (if person-id
            (.execute cursor "savepoint personsave")
            (.execute cursor "update person set name=? where OID=?" params)
            (.execute cursor "release personsave")
            (load-person person-id connection))
            (.execute cursor "savepoint personsave")
            (.execute cursor "insert into person (name, OID) values (?, ?)" params)
            (let [[new-person-id cursor.lastrowid]]
              (.execute cursor "release personsave")
              (load-person new-person-id connection))))
      (catch [e Exception] (do
        (.execute cursor "rollback to personsave")

(defn create-person-from-row [row]
  (dict {"id" (get row 0)
         "name" (get row 1)}))

(defn create-connection []
    (let [[connection (.connect sqlite3 ":memory:")]]
      (setv connection.row-factory sqlite3.Row)
      (setv connection.isolation-level None)
    (get [connection] 0))))

(defn create-database [connection]
  (create-schema connection))

(if (= __name__ "__main__")
  (let [[connection (create-connection)]]
    (do (create-database connection)
      (save-person (dict {"id" None
                          "name" "Jaska"}) connection))))

The code defines few functions: load-person, save-person, create-person-from-row, create-connection and create-database. It also has entry point to the program, defined in the familiar Pythonic-way. While executing, Python will see something along the lines of (notice how “-” gets automatically converted to “_”):

if __name__ == "__main__":
    connection = create_connection()
    save_person({"id": None,
                 "name": "Jaska"},

The save-person function isn’t anything too complex. It creates a cursor for manipulating the database and binds sql-parameters and existing person id. Then, depending on if the person-id is defined or is None, the function updates or inserts data in person table. The last step is to load the saved person, in case there are autogenerated values or triggers. Because load-person is the last to be executed, save-person will return the result of load-person (which is the loaded person).

THe code more or less works now, but it has some problems still. For example, mapping the row to person data is done by using indexes, while I would prefer using column names. Connection.row_factory has been set to sqlite3.Row, which should make that possible, but for some reason it is not working.