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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s