MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Sqlite Programming’ tag

Sqlite on Ubuntu

without comments

We decided to include some existing Sqlite databases in our AWS Ubuntu learning lab because they’re used by the Data Science courses (specifically, DS 250). Installing Sqlite is quite simple:

sudo apt install -y sqlite

You can check the install by using the which utility, like:

which -a sqlite3

On Ubuntu, it should return:

/usr/bin/sqlite3

There is a friendly help document online that can provide insight in how to use Sqlite. You can create a new student.db database with the following syntax from the Ubuntu CLI (Command-Line Interface):

sqlite3 student.db

It would return the following:

SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" FOR usage hints.
sqlite>

A little warning about the simple example and how it opens only a transient in-memory database. If you want a persistent database, you must open sqlite3 without any arguments. Then, you must use the .open method to open a persistent student.db database create a file in the relative directory path where you launched sqlite3 executable. You can read more about persistent in Sqlite in the documentation.

.open student.db

The alternative opens the student.db file in a fully qualified path:

.open /home/student/Code/sqlite/db/student.db

If you type .databases at the sqlite> prompt it would return:

sqlite> .databases
main: /home/student/Code/sqlite/db/student.db r/w

Let’s create a script file that creates two tables, a foreign key reference from one of the tables to the other, and some data with the following create_sample.sql script:

-- Drop knight table if exists.
DROP TABLE IF EXISTS knight;
 
-- Drop kingdom table if exists.
DROP TABLE IF EXISTS kingdom;
 
-- Create normalized table kingdom
CREATE TABLE kingdom
( kingdom_id    INTEGER PRIMARY KEY
, kingdom_name  VARCHAR(20)
, population    INTEGER
, book          VARCHAR(40));
 
-- Insert kingdom into table.
INSERT INTO kingdom
( kingdom_id
, kingdom_name
, population
, book )
VALUES
 ( 1, 'Narnia', 42100, 'Prince Caspian' )
,( 2, 'Narnia', 77600, 'The Lion, The Witch and The Wardrobe' )
,( 3, 'Camelot', 15200, 'The Once and Future King' );
 
-- Create normalized knight table.
CREATE TABLE knight
( knight_id             INTEGER PRIMARY KEY
, knight_name           VARCHAR(22)
, kingdom_allegiance_id INTEGER
, allegiance_start_date text
, allegiance_end_date   text
, book                  VARCHAR(40)
, FOREIGN KEY (kingdom_allegiance_id)
  REFERENCES kingdom(kingdom_id));
 
-- Insert knights into table.  
INSERT INTO knight
( knight_id
, knight_name
, kingdom_allegiance_id
, allegiance_start_date
, allegiance_end_date
, book )
VALUES
 (  1, 'Peter the Magnificent', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' )
,(  2, 'Edmund the Just', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' )
,(  3, 'Susan the Gentle', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' )
,(  4, 'Lucy the Valiant', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' )
,(  5, 'Peter the Magnificent',	1, '1531-04-12', '1328-05-31', 'Prince Caspian' )
,(  6, 'Edmund the Just', 1, '1531-04-12', '1328-05-31', 'Prince Caspian' )
,(  7, 'Susan the Gentle', 1, '1531-04-12', '1328-05-31', 'Prince Caspian' )
,(  8, 'Lucy the Valiant', 1, '1531-04-12', '1328-05-31', 'Prince Caspian' )
,(  9, 'King Arthur', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 10, 'Sir Lionel', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 11, 'Sir Bors', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 12, 'Sir Bors', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 13, 'Sir Galahad', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 14, 'Sir Gawain', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 15, 'Sir Tristram', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 16, 'Sir Percival', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 17, 'Sir Lancelot', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' );

You can run the create_sample.sql script with the following syntax using an absolute path:

sqlite> .read /home/student/Code/sqlite/create_tables.sql

Then, you can write a query like this to retrieve the data from two tables:

SELECT k.kingdom_name
,      kn.knight_name
FROM   kingdom k INNER JOIN knight kn
ON     k.kingdom_id = kn.kingdom_allegiance_id
WHERE  k.book = 'Prince Caspian';

It will return the following:

Narnia|Peter the Magnificent
Narnia|Edmund the Just
Narnia|Susan the Gentle
Narnia|Lucy the Valiant

You can exit sqlite3 by entering .quit or Control+D (the system End-Of-File character). If you can determine whether you have a transient or persistent student.db database file with the long list (ll) command.

The following command:

ll /home/student/Code/sqlite/db/student.db

should return the following:

-rw-r--r-- 1 student student 12288 Feb  9 23:01 /home/student/Code/sqlite/db/student.db

If the command returned a 0 sized student.db database file, you created a transient Sqlite table. You’ll need to redo the creation of the student.db database file with the .open command as qualified above.

If you want to detach a database from your active Sqlite session, you can issue the following command to remove it:

sqlite> DETACH DATABASE student.db

Let’s jazz it up a bit with some Python. The first example verifies the ODBC driver’s ability to connect to Sqlite. Please note that it returns the same result for a transient and persistent database file. You can refer to the following documentation for Python examples.

#!/usr/bin/python
 
# Import sqlite3 ODBC library.
import sqlite3
 
try:
  # Open a connection to the student.db database
  db = sqlite3.connect('/home/student/Code/sqlite/db/student.db')
 
  # Print a string to say you've connected to the student.db database.
  print("Sqlite database connection success.")
 
except sqlite3.Error as e:
  print('SQLite error: %s' % (' '.join(e.args)))
  print("Exception class is: ", e.__class__)
  print('SQLite traceback: ')
  exc_type, exc_value, exc_tb = sys.exc_info()
  print(traceback.format_exception(exc_type, exc_value, exc_tb))
  sys.exit(1)
finally:
  # Close the connection when it is open.
  if db:
    db.close()

You can run the sqlite_connection.py script with the following syntax from its local directory:

Sqlite database connection success.

Assuming you have created a persistent Sqlite database, as qualified above with the .open command and fully qualified file name. A fully qualified file name as a path from a Linux mount point to the file.

You must use the fully qualified file name for a persistent Sqlite student.db database as the database parameter for the sqlite3.connect() method, as shown on line #9 of the sqlite_query.py program below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#!/usr/bin/python
 
# Import sys library.
import sqlite3
 
try:
  # Open a connection to a persistent database, which should use
  # a fully qualified file name, but may use a relative file
  # name when the Python code is in the same directory as a 
  # persistent student.db sqlite3 database.
  db = sqlite3.connect('/home/student/Code/sqlite/db/student.db')
 
  # Create a cursor.
  cursor = db.cursor()
 
  # Define a query.
  query = "SELECT k.kingdom_name "                           \
          ",      kn.knight_name "                           \
          "FROM   kingdom k INNER JOIN knight kn "           \
          "ON     k.kingdom_id = kn.kingdom_allegiance_id "  \
          "WHERE  k.book = 'Prince Caspian'"
 
  # Execute the cursor with the query.
  cursor.execute( query )
 
  # Display the rows returned by the query.
  for (kingdom_name, knight_name) in cursor:
    print('{0} has {1}'.format( kingdom_name.title(), knight_name.title()))
 
except sqlite3.Error as e:
  print('SQLite error: %s' % (' '.join(e.args)))
  print("Exception class is: ", e.__class__)
  print('SQLite traceback: ')
  exc_type, exc_value, exc_tb = sys.exc_info()
  print(traceback.format_exception(exc_type, exc_value, exc_tb))
  sys.exit(1)
finally:
  # Close the connection when it is open.
  if db:
    db.close()

As always, I hope this helps those trying to get up and running with Sqlite.

Written by maclochlainn

February 9th, 2024 at 8:47 pm