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