Meet SQLite3 in Babel Small, fast, reliable!

Detailed interaction SQLite3 and Python

from this site

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 ";"
sqlite>

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
sqlite>

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
print('Hi')
#+END_SRC

#+RESULTS:
: 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.

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;
#+END_SRC

#+RESULTS:
: 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')
c.fetchall()

>>> >>> >>> 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])
#+END_SRC

#+RESULTS:
: <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')
c.fetchall()
type(c.fetchall())
#+END_SRC

#+RESULTS:
: <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')")
conn.commit()
c.execute('select * from greeting')
c.fetchall()
#+END_SRC

#+RESULTS:
: <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
conn.close()
#+END_SRC

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.

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;