basecursor.py

python web programming 部分

#
# baseCursor.py: base class for database adaptation layer
#

# XXX Add DATE/TIME/TIMESTAMP, maybe others?

from types import StringType

class baseCursor:

    def __init__(self, cursor, conn):
        self.cursor = cursor
        self.conn = conn

#
# Connection operations
#
    def commit(self):
        return self.conn.commit()

    def rollback(self):
        return self.conn.rollback()

#
# Data Manipulation language SQL statement generators
#
    def DELETE(self, tname, WHERE=None):
        result = "DELETE FROM %s" % tname
        if WHERE:
            result += " WHERE %s" % WHERE
        return result

    def INSERT(self, tname, fnames=None, VALUES=None):
        if VALUES is None:
            raise ValueError, "Value list not provided for INSERT"
        if fnames is not None:
            if isinstance(fnames, StringType):
                fnames = [fnames]
            flist = "(%s) " % ", ".join(fnames)
        else:
            flist = ""
        if isinstance(VALUES, StringType):
            values = VALUES
        else:
            values = ", ".join(map(str,VALUES))
        return "INSERT INTO %s %sVALUES(%s)" % (tname, flist, values)

    def SELECT(self, fields, FROM=None, WHERE=None, REST=None):
        if isinstance(fields, StringType):
            result = "SELECT " + fields
        else:
            ff = []
            for f in fields:
                if isinstance(f, StringType):
                    ff.append(f)
                else:
                    ff.append(self.fldalias(f))
            result = "SELECT " + ", ".join(ff)
        if FROM:
            result += "\n    FROM %s" % self.tables(FROM)
        if WHERE:
            result += "\n    WHERE %s" % WHERE
        if REST:
            result += "\n     %s" % REST
        return result

    def UNION(self, *stmts, **kywds):
        union = "UNION"
        if kywds.has_key("DISTINCT") and kywds["DISTINCT"]:
            union += " DISTINCT"
        union = "\n    %s\n" % union
        result = union.join(stmts)
        if kywds.has_key("REST"):
            result = "%s\n    %s" % (result, kywds["REST"])
        return result

    def UPDATE(self, table, cols, vals, WHERE=None):
        if len(cols) != len(vals):
            raise ValueError, "UPDATE column count differs from value count"
        result = "UPDATE %s SET %s" % (table, ", ".join(map(lambda x,y: x+"="+y, cols, vals)))
        if WHERE:
            result += " WHERE " + WHERE
        return result
#
# Data definition language SQL statement generators
#
    def CREATE_INDEX(self, ixname, tbl, fields, UNIQUE=0):
        if isinstance(fields, StringType):
            fields = [fields]
        if UNIQUE:
            unique = "UNIQUE "
        else:
            unique = ""
        return "CREATE %sINDEX %s ON %s(%s)" % (unique, ixname, tbl, ", ".join(fields))

    def CREATE_TABLE(self, tname, *fields):
        ff = []
        for f in fields:
            ff.append("%s %s" % (f[0], f[1]))
        return "CREATE TABLE %s (\n    %s)" % (tname, ",\n    ".join(ff))

    def CREATE_VIEW(self, vname, fields, select):
        return "CREATE VIEW %s (%s) AS\n    %s" % (vname, ", ".join(fields), select)

    def DROP_INDEX(self, i, t):
        return "DROP INDEX %s" % i

    def DROP_TABLE(self, t):
        return "DROP TABLE %s" % t

    def DROP_VIEW(self, v):
        return "DROP VIEW %s" % v

#
# Datatype generation statements and constants
#
    def VARCHAR(self, n):
        return "VARCHAR(%d)" % n
            
    INTEGER = "INTEGER"
    FLOAT = "FLOAT"

#
# Local cursor operations.
# Other cursor operations are delegated to
# the cursor supplied as an argument
#
    def __getattr__(self, attrname):
        "Delegate unrecognised methods/attributes to the cursor object."
        return getattr(self.cursor, attrname)

#
# Utility functions: may be overridden to modify SQL grammar
#
    def fldalias(self, f):
        if len(f) != 2:
            raise ValueError, "Field alias should be exactly two elements"
        else:
            return " AS ".join(f)

    def tables(self, FROM):
        if isinstance(FROM[0], StringType):
            FROM = [FROM]
        result = []
        for t in FROM:
            if isinstance(t, StringType):
                result.append(t)
            else:
                result.append(self.tblalias(t))
        return ", ".join(result)

    def tblalias(self, t):
        if len(t) != 2:
            raise ValueError, "Table alias should be exactly two elements"
        else:
            return " AS ".join(t)