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.
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.
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...
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...
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...
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.
DBplus.__mro__
(__main__.DBplus, __main__.DB, object)
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...
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...
Here's another database we'd like to study. It has only the one table: Shapes.
with DBplus('polyhedrons.db') as db:
print(db.dbname)
db.schema()
Connecting... polyhedrons.db getting schema CREATE TABLE Shapes (shape_id int PRIMARY KEY, shape text, abbrev text, shape_v int, shape_f int, shape_e int, shape_dual_id int, shape_volume float, updated_at int, updated_by text) ('index', 'sqlite_autoindex_Shapes_1', 'Shapes', 3, None) 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
:
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.
ctx = decimal.getcontext()
ctx
Context(prec=28, rounding=ROUND_HALF_EVEN, Emin=-999999, Emax=999999, capitals=1, clamp=0, flags=[], traps=[InvalidOperation, DivisionByZero, Overflow])
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.