From: Colin Hall <colinghall@gmail.com>
To: org-mode <emacs-orgmode@gnu.org>
Subject: Round-tripping data between org-mode table and sqlite db using python
Date: Fri, 27 Sep 2013 15:52:12 +0100 [thread overview]
Message-ID: <87y56ixqoz.fsf@gmail.com> (raw)
[-- 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
reply other threads:[~2013-09-27 14:52 UTC|newest]
Thread overview: [no followups] expand[flat|nested] mbox.gz Atom feed
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=87y56ixqoz.fsf@gmail.com \
--to=colinghall@gmail.com \
--cc=emacs-orgmode@gnu.org \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
Code repositories for project(s) associated with this external index
https://git.savannah.gnu.org/cgit/emacs.git
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is an external index of several public inboxes,
see mirroring instructions on how to clone and mirror
all data and code used by this external index.