Chapter 11 ■ the World Wide Web

191

Reading and Writing to a Database

Imagine a simple bank application that wants to allow account holders to send each other payments using a web
application. At the very least, such an application will need a table of payments, a way of inserting a new payment, and
a way of fetching all of the payments that have involved the account of the currently logged-in user so that they can be
displayed.
Listing 11-1 presents a simple library demonstrating all three of these features, and it is backed by the SQLite
database that comes built-in to the Python Standard Library. So, the listing should work anywhere that you have
Python installed!

Listing 11-1. A Routine for Building and Talking to a Database

#!/usr/bin/env python3

Foundations of Python Network Programming, Third Edition

https://github.com/brandon-rhodes/fopnp/blob/m/py3/chapter11/bank.py

A small library of database routines to power a payments application.

import os, pprint, sqlite3
from collections import namedtuple

def open_database(path='bank.db'):
new = not os.path.exists(path)
db = sqlite3.connect(path)
if new:
c = db.cursor()
c.execute('CREATE TABLE payment (id INTEGER PRIMARY KEY,'
' debit TEXT, credit TEXT, dollars INTEGER, memo TEXT)')
add_payment(db, 'brandon', 'psf', 125, 'Registration for PyCon')
add_payment(db, 'brandon', 'liz', 200, 'Payment for writing that code')
add_payment(db, 'sam', 'brandon', 25, 'Gas money-thanks for the ride!')
db.commit()
return db

def add_payment(db, debit, credit, dollars, memo):
db.cursor().execute('INSERT INTO payment (debit, credit, dollars, memo)'
' VALUES (?, ?, ?, ?)', (debit, credit, dollars, memo))

def get_payments_of(db, account):
c = db.cursor()
c.execute('SELECT FROM payment WHERE credit =? or debit = ?'
' ORDER BY id', (account, account))
Row = namedtuple('Row', [tup[0] for tup in c.description])
return [Row(row) for row in c.fetchall()]

if name == 'main':
db = open_database()
pprint.pprint(get_payments_of(db, 'brandon'))

The SQLite engine fits each database inside a single file on disk, so the open_database() function can check for
the existence of the file to determine whether the database is being created or merely reopened. When creating the
database, it builds a single payment table and adds three example payments so that your web application will have
something to display besides an empty list of payments.