Home

Database Fun

We're going to do something sophisticated: define a context manager for connecting to and disconnecting from any SQLite database. A context manager is triggered by the Python keyword with.

When we connect to a database, successfully, we get a "connection object". From said connection object, we get an object called a "cursor". The cursor is our go-between, accepting SQL directives, such as SELECT, and providing fetch methods to get the results.

When you're finished with the database, close the connection. The connection is also used to "commit" changes (after UPDATE and DELETE).

The class below is triggered to run through __enter__ when used with with. Whatever __enter__ returns becomes a nameable object, the result of the "as", is in

  with DB("airports.db") as some_object:  
      print(some_object.dbname)

The connection and cursor objects get created by __enter__ and remain accessible through some_object, usuable with the indented scope (context) that follows.

In [1]:
import sqlite3 as sql

class DB:
    
    def __init__(self, the_db): # triggered by DB("airports.db")
        self.dbname = the_db
    
    def __enter__(self):
        """
        connect and get conn, curs
        """
        print("Connecting...")
        self.conn = sql.connect(self.dbname)
        self.curs = self.conn.cursor()  
        return self  # as db
        
    def __exit__(self, *oops): # triggered by leaving the context scope
        if self.conn:
            print("Disconnecting...")
            self.conn.close()
        if oops[0]:  # exception occurred, else None
            print("Exception raised...")
            return False  # not handling it...
        return True       # nothing to handle

Perhaps the first thing you'll want to do, when connecting to a database, is get a read-out of the tables and structure. What are the tables, the column names, and what types of data go in the columns (integers? text? what?).

Typically, that information is stored in additional tables. In SQLite, you will find this info in sqlite_schema. Here's some documentation.

In [2]:
with DB("airports.db") as db:
    db.curs.execute("SELECT * FROM sqlite_schema")  # contains metadata
    for row in db.curs.fetchall():
        print(row[4])
    #=== scope ends, triggering __exit__ ===
Connecting...
CREATE TABLE Airports
         (iata text,
          iso text,
          name text,
          continent text,
          type text,
          lat float,
          lon float,
          size text,
          status int)
Disconnecting...
In [3]:
with DB("airports.db") as db:
    db.curs.execute("SELECT * FROM Airports where iata='PDX'")
    for row in db.curs.fetchall():
        print(row)
Connecting...
('PDX', 'US', 'Portland International Airport', 'NA', 'airport', 45.588997, -122.5929, 'large', 1)
Disconnecting...
In [4]:
with DB("airports.db") as db:
    db.curs.execute("SELECT * FROM Airports where iata='SFO'")
    for row in db.curs.fetchall():
        print(row)
Connecting...
('SFO', 'US', 'San Francisco International Airport', 'NA', 'airport', 37.615215, -122.38988, 'large', 1)
Disconnecting...
In [5]:
class DBplus(DB):

    def schema(self):
        print("getting schema")
        self.curs.execute("SELECT * FROM sqlite_schema")  # contains metadata
        for row in self.curs.fetchall():
            if row[0] == 'table':
                print(row[4])
            else:
                print(row)

This ability to dump out metadata looks useful in a generic sense. Lets define a class DBplus that does everything DB does, in addition to incorporating the schema behavior.

In [6]:
DBplus.__mro__
Out[6]:
(__main__.DBplus, __main__.DB, object)
In [7]:
with DBplus('bookmarks.db') as marks:
    print(marks.dbname)
    marks.schema()
Connecting...
bookmarks.db
getting schema
CREATE TABLE Bookmarks
            (bk_place text PRIMARY KEY,
             bk_url text,
             bk_tags text)
('index', 'sqlite_autoindex_Bookmarks_1', 'Bookmarks', 3, None)
Disconnecting...
In [8]:
with DB("bookmarks.db") as db:
    db.curs.execute("SELECT bk_place FROM Bookmarks WHERE bk_tags LIKE ? ", ("%comedy%",))
    for row in db.curs.fetchall():
        print(row)
Connecting...
("'New Math' by Tom Lehrer (animated)",)
('In Defense of Ada',)
('Warriors of the Net',)
('Will Geeks Rule? CBS News (world domination meme)',)
('World Domination meme',)
('XKCD',)
Disconnecting...

Keep in mind that the with keyword gets used to manage many contexts that have nothing to do with talking to a database.

For example, whereas the Decimal type works to 28 digits of precision by default, you might want to boost the precision temporarily, then revert to the usual level.

Here's a way to do that using with:

In [16]:
import decimal
from decimal import Decimal

decimal.localcontext is already set up to return a context manager. You may give it an argument, or not.

In [17]:
ctx = decimal.getcontext()
ctx
Out[17]:
Context(prec=28, rounding=ROUND_HALF_EVEN, Emin=-999999, Emax=999999, capitals=1, clamp=0, flags=[], traps=[InvalidOperation, DivisionByZero, Overflow])
In [18]:
with decimal.localcontext(ctx) as context:
    context.prec = 1000
    print("Super precision:")
    d = Decimal('1'+'0'*100) # 100000.... a hundred 0s
    e = ((1 + 1/d) ** d)
    print(str(e)[:42])       # truncate the result

print("Default precision:")    
print((1 + 1/d) ** d)        # 1/d doesn't even register
Super precision:
2.7182818284590452353602874713526624977572
Default precision:
1.000000000000000000000000000

From a published source:

2.7182818284 5904523536 0287471352 6624977572

In Excercises, we explore similar high precision computations using a 3rd party module instead of decimal.

Also in Exercises, you will find the source code for Castle Game is built around a context manager class.