Connecting to SQLite and Cursor Objects

SQLite, as mentioned in previous posts, is a software package for managing databases. It does so mainly through SQL, Standard Query Language. There are other database software packages that use SQL, namely MySQL (I didn't like it but you may have better luck), PostgreSQL and Oracle. SQLite has the nice feature that it integrates with programming languages such as Python, C++, Perl, Java and JavaScript.

So how do you use it? First you need to install it (my experience described here). Then you write a Python program that imports the sqlite3 module, connects to the database, and creates a virtual cursor. For example, if my SQLite is installed at C:\\sqlite and the database is at C:\\sqlite\db\base1.db then I create a connection by

import sqlite3 # only needed once at the start
conn = sqlite3.connect("C:\\SQLite\db\base1.db")

Remember when I looked at files, and I talked about creating a file object? This is basically what the conn variable represents. I think of it as a specialised file object, and the sqlite3 module allows us to interact in more varied ways with this file than just the usual read, write or append. 

Once you have made the connection with your chosen database you create what is called a cursor object. This is a virtual cursor - you don't see it flashing on your screen, but it enables you to pass SQL commands from Python to the SQLite program, and retrieve answers. 

So if we call our virtual cursor object curs (just a variable, but one I can recognise), we create it by
curs = conn.cursor()

Now we can tell Python to pass SQL commands to the SQLite program which will interact with the database. 
The bad news is SQL is a language in itself (that's what the L stands for). So the SQLite module lets you use a language within another language. 
The good news is SQL is not particularly difficult. 

One quick way of finding out the tables in a database is by querying the SQLite_master table. So the python command to run the SQL command is

curs.execute("SELECT * FROM sqlite_master WHERE type = 'table';")

Everything within the double quotes is being passed by the cursor object (curs) to the SQLite3 program. Also note the semi-colon at the end of each SQL command. This command on its own does not produce much on screen. If you are doing this on the IDLE command line rather than in a script, you might get a reply like
<sqlite3.Cursor object at 0x038DD360>
Not very informative. It's actually saying that a memory location has something from the cursor object. If execute() sends instructions from Python to SQL, then fetchall() and fetchone() bring back the reply.
I find it best to assign these replies to variables within Python. So
dump = curs.fetchall()
will retrieve whatever reply the curs object has received from SQL and pass it to Python, holding it in the variable dump. So what does dump hold?
>>> print(dump)
[('table', 'Location', 'Location', 2, 'CREATE TABLE Location(Scientific_name TEXT, Nation TEXT, Biome TEXT)')]
>>>
This might look like a mess, but a lot of queries will return in this format, and Python understands what this is. It is a list (denoted by the square brackets) that contains a tuple (denoted by the outer set of round brackets). The list is all the lines returned, with each line returned as a tuple - here it is just one line, so just one tuple within the list. Within the tuple, each element is the contents of the column (or field) for that row. In spreadsheet terms each element in the tuple would be a cell.

I admit this example is helped by the fact that I have already done some work on base1.db, so there is something to show for the queries.  It's like one of those "how to..." programmes on the TV where rather than going through the whole process, the presenter moves to a different workbench and announces "And here's one I did earlier" much to the frustration of the audience trying to follow.
So what tables are in this database? Just one, called Location (hence just one tuple in the response), and the final element in the tuple is actually the  SQL command used to create the table (here 'CREATE TABLE Location(Scientific_name TEXT, Nation TEXT, Biome TEXT)'. I won't go into a detailed explanation here.
So what if I then run the same two commands (execute() and fetchall()) for this table Location?
>>> curs.execute("SELECT * FROM Location;")
<sqlite3.Cursor object at 0x038DD360>
>>> dump2 = curs.fetchall()
>>> print(str(dump2))
[('Homo sapiens', 'United Kingdom', 'Urban'), ('Homo sapiens', 'United Kingdom', 'Rural'), ('Rattus norvegicus', 'United Kingdom', 'Urban'), ('Rattus norvegicus', 'United Kingdom', 'Rural'), ('Carcharodon carcharias', 'South Africa', 'Ocean'), ('Homo sapiens', 'South Africa', 'Urban'), ('Homo sapiens', 'South Africa', 'Ocean'), ('Carcharodon carcharias', 'Australia', 'Ocean'), ('Homo sapiens', 'Australia', 'Ocean')]

That's a mess (hence my choice of variable name).
But if I then tell Python to loop through the list (leaving the tuples as tuples) we get:
>>> for line in dump2:
 print(line)

('Homo sapiens', 'United Kingdom', 'Urban')
('Homo sapiens', 'United Kingdom', 'Rural')
('Rattus norvegicus', 'United Kingdom', 'Urban')
('Rattus norvegicus', 'United Kingdom', 'Rural')
('Carcharodon carcharias', 'South Africa', 'Ocean')
('Homo sapiens', 'South Africa', 'Urban')
('Homo sapiens', 'South Africa', 'Ocean')
('Carcharodon carcharias', 'Australia', 'Ocean')
('Homo sapiens', 'Australia', 'Ocean')
>>>
which is more clearly a table of results, with each tuple containing three elements (here Scientific Name, Nation and Biome respectively - biome is a biological term for what sort of habitat). This table actually was part of an early draft of the project I have been working on.

Finally, when finishing with SQLite, it is sensible to close any connections (again similar to closing a file object). This also closes the cursor object. So:
>>> conn.close()
>>> 
More later...