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
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
__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: # 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) #=== 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 == 'table': print(row) 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.
(__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...
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
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
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.