14. Database Access

# See http://www.python.org/doc/topics/database/ for Database Interfaces details.
# currently listed on http://www.python.org/doc/topics/database/modules/
#
#  DB/2, Informix, Interbase, Ingres, JDBC, MySQL, pyodbc, mxODBC, ODBC Interface,
#  DCOracle, DCOracle2, PyGresQL, psycopg, PySQLite, sapdbapi, Sybase, ThinkSQL.
#

#-------------------------------------
import anydbm
filename = "test.db"
try:
    db = anydbm.open(filename)
except anydbm, err:
    print "Can't open %s: %s!" % (filename, err)

db["key"] = "value"        # put value into database
if "key" in db:            # check whether in database
    val = db.pop("key")    # retrieve and remove from database
db.close()                 # close the database
#-------------------------------------
# download the following standalone program
#!/usr/bin/python
# userstats - generates statistics on who logged in.
# call with an argument to display totals

import sys, os, anydbm, re

db_file = '/tmp/userstats.db'       # where data is kept between runs

try:
    db = anydbm.open(db_file,'c')   # open, create if it does not exist
except:
    print "Can't open db %s: %s!" % (db_file, sys.exc_info()[1])
    sys.exit(1)

if len(sys.argv) > 1:
    if sys.argv[1] == 'ALL':
        userlist = db.keys()
    else:
        userlist = sys.argv[1:]
    userlist.sort()
    for user in userlist:
        if db.has_key(user):
            print "%s\t%s" % (user, db[user])
        else:
            print "%s\t%s" % (user, 0)
else:
    who = os.popen('who').readlines()  # run who(1)
    if len(who)<1:
        print "error running who"       # exit
        sys.exit(1)
    # extract username (first thin on the line) and update
    user_re = re.compile("^(\S+)")
    for line in who:
        fnd = user_re.search(line)
        if not fnd:
            print "Bad line from who: %s" % line
            sys.exit(1)
        user = fnd.groups()[0]
        if not db.has_key(user):
            db[user] = "0"
        db[user] = str(int(db[user])+1) # only strings are allowed
db.close()
    

# Emptying a DBM File

import anydbm

try:
    db = anydbm.open(FILENAME,'w')   # open, for writing
except anydbm.error, err:
    print "Can't open db %s: %s!" % (filename, err)
    raise SystemExit(1)

db.clear()
db.close()
# -------------------------------
try:
    db = anydbm.open(filename,'n')   # open, always create a new empty db
except anydbm.error, err:
    print "Can't open db %s: %s!" % (filename, err)
    raise SystemExit(1)

db.close()
# -------------------------------
import os
try:
    os.remove(FILENAME)
except OSError, err:
    print "Couldn't remove %s to empty the database: %s!" % (FILENAME,
        err)
    raise SystemExit

try:
    db = anydbm.open(FILENAME,'n')   # open, flways create a new empty db
except anydbm.error, err:
    print "Couldn't create %s database: %s!" % (FILENAME, err)
    raise SystemExit

# Converting Between DBM Files

# download the following standalone program
#!/usr/bin/python
# db2gdbm: converts DB to GDBM

import sys
import dbm, gdbm

if len(sys.argv)<3:
    print "usage: db2gdbm infile outfile"
    sys.exit(1)

(infile, outfile) = sys.argv[1:]

# open the files
try:
    db_in = dbm.open(infile)
except:
    print "Can't open infile %s: %s!" % (infile, sys.exc_info()[1])
    sys.exit(1)
try:
    db_out = dbm.open(outfile,"n")
except:
    print "Can't open outfile %s: %s!" % (outfile, sys.exc_info()[1])
    sys.exit(1)

# copy (don't use db_out = db_in because it's slow on big databases)
# is this also so for python ?
for k in db_in.keys():
    db_out[k] = db_in[k]

# these close happen automatically at program exit
db_out.close()
db_in.close()

OUTPUT.update(INPUT1)
OUTPUT.update(INPUT2)

OUTPUT = anydbm.open("OUT","n")
for INPUT in (INPUT1, INPUT2, INPUT1):
    for key, value in INPUT.iteritems():
        if OUTPUT.has_key(key):
            # decide which value to use and set OUTPUT[key] if necessary
            print "key %s already present: %s, new: %s" % (
                    key, OUTPUT[key], value )
        else:
            OUTPUT[key] = value

# On systems where the Berkeley DB supports it, dbhash takes an
# "l" flag:
import dbhash
dbhash.open("mydb.db", "cl") # 'c': create if doesn't exist

# @@INCOMPLETE@@

# @@INCOMPLETE@@
# @@INCOMPLETE@@

# @@INCOMPLETE@@
# @@INCOMPLETE@@

# shelve uses anydbm to access and chooses between DBMs.
# anydbm detect file formats automatically.
import shelve
db = shelve.open("celebrities.db")

name1 = "Greg Stein"
name2 = "Greg Ward"

# shelve uses pickle to convert objects into strings and back.
# This is automatic.
db[name1] = ["of ViewCVS fame", "gstein@lyra.org"]
db[name2] = ["of Distutils fame", "gward@python.net"]

greg1 = db[name1]
greg2 = db[name2]

print "Two Gregs: %x %x" % (id(greg1), id(greg2))

if greg1 == greg2:
    print "You're having runtime fun with one Greg made two."
else:
    print "No two Gregs are ever alike."

# Changes to mutable entries are not written back by default.
# You can get the copy, change it, and put it back.
entry = db[name1]
entry[0] = "of Subversion fame"
db[name1] = entry

# Or you can open shelve with writeback option. Then you can
# change mutable entries directly. (New in 2.3)
db = shelve.open("celebrities.db", writeback=True)
db[name2][0] = "of Optik fame"

# However, writeback option can consume vast amounts of memory
# to do its magic. You can clear cache with sync().
db.sync()
#-----------------------------

# DON'T DO THIS.
import os as _os, shelve as _shelve

_fname = "persist.db"
if not _os.path.exists(_fname):
    var1 = "foo"
    var2 = "bar"
_d = _shelve.open("persist.db")
globals().update(_d)

print "var1 is %s; var2 is %s"%(var1, var2)
var1 = raw_input("New var1: ")
var2 = raw_input("New var2: ")

for key, val in globals().items():
    if not key.startswith("_"):
        _d[key] = val
# @@INCOMPLETE@@

#-----------------------------
import dbmodule

dbconn = dbmodule.connect(arguments...)

cursor = dbconn.cursor()
cursor.execute(sql)

while True:
   row = cursor.fetchone()
   if row is None:
       break
   ...

cursor.close()
dbconn.close()

#-----------------------------
import MySQLdb
import pwd

dbconn = MySQLdb.connect(db='dbname', host='mysqlserver.domain.com',
                        port=3306, user='user', passwd='password')

cursor = dbconn.cursor()
cursor.execute("CREATE TABLE users (uid INT, login CHAR(8))")

# Note: some databases use %s for parameters, some use ? or other
# formats
sql_fmt = "INSERT INTO users VALUES( %s, %s )"

for userent in pwd.getpwall():
   # the second argument contains a list of parameters which will
   # be quoted before being put in the query
   cursor.execute(sql_fmt, (userent.pw_uid, userent.pw_name))

cursor.execute("SELECT * FROM users WHERE uid < 50")

for row in cursor.fetchall():
   # NULL will be displayed as None
   print ", ".join(map(str, row))

cursor.execute("DROP TABLE users")
cursor.close()
dbconn.close()
#-----------------------------

# @@INCOMPLETE@@
# @@INCOMPLETE@@