#+TITLE:     Round trip table of data through an ob-python block
#+AUTHOR:    Colin Hall
#+EMAIL:     address@hidden
#+DATE:      <2013-09-27 Fri>
#+DESCRIPTION: An example of round tripping data from org through an Sqlite database
#+LANGUAGE:  en
#+OPTIONS:   H:1 num:nil toc:nil \n:nil @:t ::t |:t ^:{} -:t f:nil *:t <:nil
#+OPTIONS:   TeX:t LaTeX:t skip:nil d:nil todo:nil pri:nil tags:nil
#+STARTUP: overview
#+STARTUP: hidestars

* Round trip table of data through an ob-python block
  
  I'd like to edit values in tables, store them to an Sqlite database,
  update the table from the Sqlite database, make changes, and store
  them back to the database.

  Here is an example table (I don't know any Hungarian):

  #+attr_html: :border 2 :rules all :frame border
  |-----------+---------|
  | Hungarian | English |
  |-----------+---------|
  | alpha     | matches |
  | beta      | station |
  | gamma     | tobacco |
  |-----------+---------|

* Data entry and Retrieval from Sqlite database
  Before making any edits run retrieveFromSqliteDb to update the data
  entry table.

  # Note that the following code block does not have a
  # name. This is because when I did add a name it caused the results
  # table to be given the same name

  #+caption: Retrieve records from database on disk, update data entry table
  #+name: retrieveFromSqliteDb
  #+begin_src python :eval never-export :exports both :results output raw
    #!/usr/bin/env python
    import sqlite3
    con = sqlite3.connect('/var/tmp/test.db')
    cur = con.cursor()    
    cur.execute("SELECT * FROM translations")
    rows = cur.fetchall()
    print '|-+-|'
    print '| Hungarian | English |'
    print '|-+-|'
    for row in rows:
        id, hungarian, english = row
        print '| {hungarian} | {english} |'.format(hungarian=hungarian,english=english)
        
    print '|-+-|'
    con.close()
  #+end_src

  #+caption: Data entry table for Hungarian-English translation
  #+attr_html: :border 2 :rules all :frame border
  #+name: translations
  #+results: retrieveFromSqliteDb
  |-----------+---------|
  | Hungarian | English |
  |-----------+---------|
  | one       | two     |
  | three     | four    |
  |-----------+---------|

  I placed a calls here to make it a bit handier for running the write
  to database block.
  
  #+call: writeToSqliteDb() :exports code :results silent

* Writing data to the Sqlite database
  Run this ob-python block to update the Sqlite database

  #+caption: Write records from table to database on disk
  #+name: writeToSqliteDb
  #+headers: :var inData=translations[2:-1]
  #+begin_src python :eval never-export :exports code :results silent
    #!/usr/bin/env python
    import sqlite3
    con = sqlite3.connect('/var/tmp/test.db')
    c = con.cursor()    
    c.execute("DROP TABLE IF EXISTS translations")
    c.execute('''CREATE TABLE translations (
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          hungarian TEXT,
                          english TEXT);
                          ''')
    c.executemany('INSERT INTO translations (hungarian,english) VALUES (?,?)', inData)
    con.commit()
    con.close()
  #+end_src

* Utilities for working with the database

  #+caption: Creating an empty database
  #+name: createSqliteDb
  #+begin_src python :eval never-export :exports code :results silent
    #!/usr/bin/env python
    import sqlite3
    con = sqlite3.connect('/var/tmp/test.db')
    c = con.cursor()    
    c.execute("DROP TABLE IF EXISTS translations")
    c.execute('''CREATE TABLE translations (
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          hungarian TEXT,
                          english TEXT);
                          ''')
    con.commit()
    con.close()
  #+end_src

  #+caption: Dump records from the database on disk
  #+name: dumpSqliteDb
  #+headers: :db "/var/tmp/test.db"
  #+begin_src sqlite :eval never-export :exports both :results output table
    .mode column
    .headers on
    select * from translations;
  #+end_src

  #+attr_html: :border 2 :rules all :frame border
  #+results: dumpSqliteDb