Round-tripping data between org-mode table and sqlite db using python

Round-tripping data between org-mode table and sqlite db using python

[Colin Hall]

[-- Attachment #1: Type: text/plain, Size: 3971 bytes --]


Hi,

I've written an org file in which I use org tables and ob-python blocks
to manipulate an sqlite database. Updates to the database are a mix of
manual data entry and computed values. I'm using org-8.0.3 at the
moment.

I was going to post asking how to do this but I managed to develop a
working solution this morning, see the attached org file and the ascii
export below. I'm sure it could be improved, so I'm interested in advice
on how to go about that.

My main issue is that I wish to export the ob-python code as well as the
org-table showing the data, but present them under separate topics. So,
I would like the freedom to place the org-table anywhere within the org
file, preferably as a named table in a topic without an org-babel block
above it. Also, in my application the tables have about 100 rows and
having org-babel blocks hanging around add visual clutter when I'm
performing the edits.

I've not posted to this list much (at all?) so if there is a better way
to present the org example, let me know.

Cheers,
Colin.



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

  --------------------
   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.


  ,----
  | #!/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()
  `----
  Listing 1: Retrieve records from database on disk, update data entry
  table

  --------------------
   Hungarian  English 
  --------------------
   one        two     
   three      four    
  --------------------

  Table 1: Data entry table for Hungarian-English translation

  I placed a calls here to make it a bit handier for running the write
  to database block.


Writing data to the Sqlite database
===================================

  Run this ob-python block to update the Sqlite database

  ,----
  | #!/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()
  `----
  Listing 2: Write records from table to database on disk


Utilities for working with the database
=======================================

  ,----
  | #!/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()
  `----
  Listing 3: Creating an empty database

  ,----
  | .mode column
  | .headers on
  | select * from translations;
  `----
  Listing 4: Dump records from the database on disk



[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: python-sqlite-round-trip.org --]
[-- Type: text/x-org, Size: 4155 bytes --]

#+TITLE:     Round trip table of data through an ob-python block
#+AUTHOR:    Colin Hall
#+EMAIL:     colinghall@gmail.com
#+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

[-- Attachment #3: Type: text/plain, Size: 17 bytes --]



-- 
Colin Hall