Python/FAQ/Доступ к базам данных

# 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@@