The main aim of this page is to discover SQLite3 features its general functionality. All tests implemented in venv with:

#+BEGIN_SRC sh :results output :exports both
python --version
# sqlite installed by default
sqlite3 --version
# host description
uname -a

: Python 3.4.3
: 3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d
: Linux antony-ThinkPad 3.13.0-93-generic #140-Ubuntu SMP Mon Jul 18 21:20:08 UTC 2016 i686 i686 i686 GNU/Linux

Tip Copy the code snippet above in your own org file, allocate point inside this code and type C-c C-c to evaluate it on your host.


Python stdlib library

Detailed interaction SQLite3 and Python

SQLite3 official documentation

from this site


Manual testing

SQLite3 is equipped with extremeley powerful innate shell and it's might be useful to use it in many cases. Just open your terminal tab and type

john@john-Thinkpad:~$ sqlite3
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

RDBMS is ready to listen your input

   sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.
                         With no args, it turns EXPLAIN on.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices ?TABLE?       Show names of all indices
                         If TABLE specified, only show indices for tables
                         matching LIKE pattern TABLE.
.load FILE ?ENTRY?     Load an extension library
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.open ?FILENAME?       Close existing database and reopen FILENAME
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.stats ON|OFF          Turn stats on or off
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.trace FILE|off        Output each SQL statement as it is run
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
.timer ON|OFF          Turn the CPU timer measurement on or off

With no doubt it's a comprehensive list of commands and feel free to review it in a much precise manner. This interaction through native SQLite3 shell allows you all possible operations in a manual mode, but our goal is to reach much literate level of interaction. We take an attempt to include and intersect emacs org-mode documents, SQLite3 tables and Python flexibility.

Tip To evaluate code in the snippet below you need to activate Python support in Babel.

#+BEGIN_SRC python :results output :session stdlib :exports both

: Hi

Tip It's easy to evaluate code snippets just copy it out from browser page and paste it into your org-file. After that it would possible to evaluate the snippet when you allocate point on it and type C-c C-c The results should appear below the snippet.

The latter output proves that Python was activated in Babel already. In case when you're got another result - don't worry, we will fix this issue in a very next chapter.

Preparing Emacs

Language support

Now, when it is clear that SQLite3 and Python3 are installed properly on our host and accessible in terminal it's time to pull it inside emacs org file. First of all you need ensure in supported languages inside org-mode.

#+BEGIN_SRC elisp :results output :exports both
(print org-babel-load-languages)

: ((python . t) (emacs-lisp . t) (shell . t))

Obviously that:

  • in our case SQLite3 not supported;
  • Python, elisp and shell was activated and ready to serve inside org-mode files.

Lets activate all required languages support by evaluation this elisp code snippet:

#+BEGIN_SRC elisp
'((python . t)
(emacs-lisp . t)
(shell . t)
(sqlite . t)

: ((python . t) (emacs-lisp . t) (shell . t) (sqlite . t))

Integrity inside Emacs

Ensure yourself, evaluate this, please:

#+BEGIN_SRC elisp :results output :exports both
(print org-babel-load-languages)

: ((python . t) (emacs-lisp . t) (shell . t) (sqlite . t))

Now it's clear that all required languages supported inside org-mode. Lets test how SQLite3 works. As well-described on this page:

#+name: sqlite-populate-test
#+header: :results silent
#+header: :dir ./
#+header: :db test-sqlite.db
#+begin_src sqlite
drop table if exists greeting;
create table greeting(one varchar(10), two varchar(10));
insert into greeting values('Hello', 'world!');

#+name: sqlite-hello
#+header: :list
#+header: :separator \ 
#+header: :results raw
#+header: :dir ./
#+header: :db test-sqlite.db
#+begin_src sqlite
select * from greeting;

#+RESULTS: sqlite-hello
Hello world!

#+BEGIN_SRC sh :exports both
ls | grep .db

: test-sqlite.db

SQLite3 DB file was successfully created, initial data store in it.

Emacs + Python + SQLite3

Test existing DB

Python connect and select from DB

By means studying this manual

#+BEGIN_SRC python :results output :session stdlib
import sqlite3
conn = sqlite3.connect('test-sqlite.db') # created in previous section
c = conn.cursor()
c.execute('SELECT * FROM greeting')

: >>> >>> <sqlite3.Cursor object at 0xb718c5a0>
: ('Hello', 'world!')

It proves the read access from python. Lets up-to-date the existing table. As a start point we should know the schema:

Tip You need to close connection when you finish all interactions with DB in Python. Thus conn.close() is the last string in our snippet.

Tip Python supports sessions in org-mode. In many cases it might be reasonable to share sqlite3 connection among the snippets in the same session. In our case the session name is stdlib.

SQLite3 insertion test

#+BEGIN_SRC sqlite :echo on :db test-sqlite.db :results output
.schema greeting

: .tables
: greeting
: .schema greeting
: CREATE TABLE greeting(one varchar(10), two varchar(10));

It is its schema. Try to fulfill it by data from org-table:

#+NAME: tableexample
 | one           | two    |
 | Hi            | Tony!  |
 | Hello         | guys!  |
 | Good morning  | Vikky! |
 | How are you   | doing? |
 | Suppose it    | clear? |
#+BEGIN_SRC sqlite :db test-sqlite.db :results output :colnames yes :var orgtable=tableexample

drop table if exists greeting;
.mode csv
.import $orgtable greeting
select * from greeting;

: Hi,Tony!
: Hello,guys!
: "Good morning",Vikky!
: "How are you",doing?
: "Suppose it",clear?

Combine approach

Right now we have one table greeting with 4 rows there:

#+BEGIN_SRC sqlite :echo on :db test-sqlite.db :results output
select * from greeting;

: select * from greeting;
: Hello,guys!
: "Good morning",Vikky!
: "How are you",doing?
: "Suppose it",clear?

Try to do a similar operation in Python:

import sqlite3
conn = sqlite3.connect('test-sqlite.db') # created in the section above
c = conn.cursor()
c.execute('SELECT * FROM greeting')
>>> >>> >>> Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such table: greeting

Tip As you might noticed the snippet above leave a connection open. Hence it might be convenient to use it (and cursor also) in the next code evaluation in stdlib Python session.

#+BEGIN_SRC python :results output :session stdlib :exports both
c.execute('SELECT count(*) FROM greeting')
print("Our database have %i rows now" % c.fetchone()[0])

: <sqlite3.Cursor object at 0xb71c10e0>
: Our database have 4 rows now

#+BEGIN_SRC python :results output :session stdlib :exports both
c.execute('select * from greeting limit 2')

: <sqlite3.Cursor object at 0xb726c0e0>
: [('Hello', 'guys!'), ('Good morning', 'Vikky!')]
: <class 'list'>

fetchall method compose a list as an output type.

Lets insert some values into our table greeting in pure Python:

#+BEGIN_SRC python :results output :session stdlib :exports both pp
c.execute("INSERT INTO greeting VALUES ('Insertion', 'test')")
c.execute('select * from greeting')

: <sqlite3.Cursor object at 0xb726c0e0>
: >>> <sqlite3.Cursor object at 0xb726c0e0>
: [('Hello', 'guys!'), ('Good morning', 'Vikky!'), ('How are you', 'doing?'), ('Suppose it', 'clear?'), ('Insertion', 'test'), ('Insertion', 'test')]

Now it's time to close our connection to SQLite3 db and move further for more complex examples.

#+BEGIN_SRC python :results none :session stdlib :exports both

Tip Org-mode opened your stdlib session in a separate buffer. Its name is *stdlib* and feel free to interact with all session variables there directly and a straightforward manner.

Full-fledged example

Goal definition

Now, when you're whetted your appetite, try to explain what results you expect to achieve by SQLite3.

  • Create DB schema
    • tables;
    • relations;
  • Write data into tables;
  • Update data in DB;
  • Delete particular data out from DB;
    • Restriction test;

RDBMS theory

Possible solution




blog comments powered by Disqus


27 August 2016


org-mode Python SQLite3 literate programming