From mboxrd@z Thu Jan 1 00:00:00 1970 From: Colin Hall Subject: Round-tripping data between org-mode table and sqlite db using python Date: Fri, 27 Sep 2013 15:52:12 +0100 Message-ID: <87y56ixqoz.fsf@gmail.com> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:43115) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1VPZP8-0006NY-T3 for emacs-orgmode@gnu.org; Fri, 27 Sep 2013 10:52:35 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1VPZP0-0000yk-D2 for emacs-orgmode@gnu.org; Fri, 27 Sep 2013 10:52:26 -0400 Received: from mail-wi0-x233.google.com ([2a00:1450:400c:c05::233]:40516) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1VPZOz-0000ya-S9 for emacs-orgmode@gnu.org; Fri, 27 Sep 2013 10:52:18 -0400 Received: by mail-wi0-f179.google.com with SMTP id hm2so956858wib.6 for ; Fri, 27 Sep 2013 07:52:16 -0700 (PDT) List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: org-mode --=-=-= Content-Type: text/plain 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 --=-=-= Content-Type: text/x-org Content-Disposition: attachment; filename=python-sqlite-round-trip.org #+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 --=-=-= Content-Type: text/plain -- Colin Hall --=-=-=--