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