1from db import *
 2import hashlib
 3
 4def salt_password(user, unsalted):
 5    if user is None:
 6        return unsalted
 7    m = hashlib.sha512()
 8    m.update(user.username)
 9    m.update(unsalted)
10    return m.hexdigest()
11
12class User(DbObject):
13    dbo_tablename = "users"
14    primary_key = IntColumn("id", allow_none=True, mutable=False)
15    username = StringColumn("username", "")
16    password = PasswordColumn("password", salt_password, "")
17    display_name = StringColumn("display_name", "")
18    def __init__(self, **kwargs):
19        DbObject.__init__(self, **kwargs)
20    @classmethod
21    def load(self, cur, username):
22        selection = self.select('u')
23        selection[0].where(selection[1].username == username)
24        result = selection[0].execute(cur)
25        if len(result) == 0:
26            return None
27        else:
28            return result[0]
29    def match_password(self, password):
30        salted = salt_password(self, password)
31        return salted == self.password
32
33#assume there is a function get_db defined which returns a PEP-249
34#database object
35def main():
36    db = get_db()
37    cur = db.cursor()
38    user = User.load(cur, "some username")
39    user.password = "a new password!"
40    user.save(cur)
41    db.commit()
42
43    new_user = User(username="someone", display_name="Their name")
44    new_user.password = "A password that will be hashed"
45    new_user.save(cur)
46    db.commmit()
47
48    print new_user.primary_key # this will now have a database assigned id

  1class ColumnSet(object):
  2    """
  3    Object which is updated by ColumnInstances to inform changes
  4    """
  5    def __init__(self):
  6        self.__columns = {} # columns are sorted by name
  7        i_dict = type(self).__dict__
  8        for attr in i_dict:
  9            obj = i_dict[attr]
 10            if isinstance(obj, Column):
 11                # we get an instance of this column
 12                self.__columns[obj.name] = ColumnInstance(obj, self)
 13
 14    @property
 15    def mutated(self):
 16        """
 17        Returns the mutated columns for this tracker.
 18        """
 19        output = []
 20        for name in self.__columns:
 21            column = self.get_column(name)
 22            if column.mutated:
 23                output.append(column)
 24        return output
 25
 26    def get_column(self, name):
 27        return self.__columns[name]
 28
 29class ColumnInstance(object):
 30    """
 31    Per-instance column data. This is used in ColumnSet objects to hold data
 32    specific to that particular instance
 33    """
 34    def __init__(self, column, owner):
 35        """
 36        column: Column object this is created for
 37        initial: Initial value
 38        """
 39        self.__column = column
 40        self.__owner = owner
 41        self.update(column.default)
 42
 43    def update(self, value):
 44        """
 45        Updates the value for this instance, resetting the mutated flag
 46        """
 47        if value is None and not self.__column.allow_none:
 48            raise ValueError("'None' is invalid for column '" +
 49                             self.__column.name + "'")
 50        if self.__column.validate(value):
 51            self.__value = value
 52            self.__origvalue = value
 53        else:
 54            raise ValueError("'" + str(value) + "' is not valid for column '" +
 55                             self.__column.name + "'")
 56
 57    @property
 58    def column(self):
 59        return self.__column
 60
 61    @property
 62    def owner(self):
 63        return self.__owner
 64
 65    @property
 66    def mutated(self):
 67        return self.__value != self.__origvalue
 68
 69    @property
 70    def value(self):
 71        return self.__value
 72
 73    @value.setter
 74    def value(self, value):
 75        if value is None and not self.__column.allow_none:
 76            raise ValueError("'None' is invalid for column '" +
 77                             self.__column.name + "'")
 78        if not self.__column.mutable:
 79            raise AttributeError("Column '" + self.__column.name + "' is not" +
 80                                 " mutable")
 81        if self.__column.validate(value):
 82            self.__value = value
 83        else:
 84            raise ValueError("'" + value + "' is not valid for column '" +
 85                             self.__column.name + "'")
 86
 87class Column(object):
 88    """
 89    Column descriptor for a column
 90    """
 91    def __init__(self, name, default=None, allow_none=False, mutable=True):
 92        """
 93        Initializes a column
 94
 95        name: Name of the column this maps to
 96        default: Default value
 97        allow_none: Whether none (db null) values are allowed
 98        mutable: Whether this can be mutated by a setter
 99        """
100        self.__name = name
101        self.__allow_none = allow_none
102        self.__mutable = mutable
103        self.__default = default
104
105    def validate(self, value):
106        """
107        In a child class, this will validate values being set
108        """
109        raise NotImplementedError
110
111    @property
112    def name(self):
113        return self.__name
114
115    @property
116    def allow_none(self):
117        return self.__allow_none
118
119    @property
120    def mutable(self):
121        return self.__mutable
122
123    @property
124    def default(self):
125        return self.__default
126
127    def __get__(self, owner, ownertype=None):
128        """
129        Gets the value for this column for the passed owner
130        """
131        if owner is None:
132            return self
133        if not isinstance(owner, ColumnSet):
134            raise TypeError("Columns are only allowed on ColumnSets")
135        return owner.get_column(self.name).value
136
137    def __set__(self, owner, value):
138        """
139        Sets the value for this column for the passed owner
140        """
141        if not isinstance(owner, ColumnSet):
142            raise TypeError("Columns are only allowed on ColumnSets")
143        owner.get_column(self.name).value = value
144
145class StringColumn(Column):
146    def validate(self, value):
147        if value is None and self.allow_none:
148            print "nonevalue"
149            return True
150        if isinstance(value, basestring):
151            print "isstr"
152            return True
153        print "not string", value, type(value)
154        return False
155
156class IntColumn(Column):
157    def validate(self, value):
158        if value is None and self.allow_none:
159            return True
160        if isinstance(value, int) or isinstance(value, long):
161            return True
162        return False
163
164class PasswordColumn(Column):
165    def __init__(self, name, salt_function, default=None, allow_none=False,
166                 mutable=True):
167        """
168        Create a new password column which uses the specified salt function
169
170        salt_function: a function(self, value) which returns the salted string
171        """
172        Column.__init__(self, name, default, allow_none, mutable)
173        self.__salt_function = salt_function
174    def validate(self, value):
175        return True
176    def __set__(self, owner, value):
177        salted = self.__salt_function(owner, value)
178        super(PasswordColumn, self).__set__(owner, salted)

1class Table(ColumnSet):
2    some_column = StringColumn("column_1", "")
3    another = IntColumn("column_2", 0)
4a_variable = 5
5columns = Table.get_columns('x') # columns with a prefix 'x'
6query = DbQuery() # This base class just makes a where statement
7query.where((columns.some_column == "4") & (columns.another > a_variable)
8print query.sql

  1class DbQueryExpression(object):
  2    """
  3    Query expression created from columns, literals, and operators
  4    """
  5    def __and__(self, other):
  6        return DbQueryConjunction(self, other)
  7    def __or__(self, other):
  8        return DbQueryDisjunction(self, other)
  9
 10    def __str__(self):
 11        raise NotImplementedError
 12    @property
 13    def arguments(self):
 14        raise NotImplementedError
 15
 16class DbQueryConjunction(DbQueryExpression):
 17    """
 18    Query expression joining together a left and right expression with an
 19    AND statement
 20    """
 21    def __init__(self, l, r):
 22        DbQueryExpression.__ini__(self)
 23        self.l = l
 24        self.r = r
 25    def __str__(self):
 26        return str(self.l) + " AND " + str(self.r)
 27    @property
 28    def arguments(self):
 29        return self.l.arguments + self.r.arguments
 30
 31class DbQueryDisjunction(DbQueryExpression):
 32    """
 33    Query expression joining together a left and right expression with an
 34    OR statement
 35    """
 36    def __init__(self, l, r):
 37        DbQueryExpression.__init__(self)
 38        self.l = l
 39        self.r = r
 40    def __str__(self):
 41        return str(self.r) + " OR " + str(self.r)
 42    @property
 43    def arguments(self):
 44        return self.l.arguments + self.r.arguments
 45
 46class DbQueryColumnComparison(DbQueryExpression):
 47    """
 48    Query expression comparing a combination of a column and/or a value
 49    """
 50    def __init__(self, l, op, r):
 51        DbQueryExpression.__init__(self)
 52        self.l = l
 53        self.op = op
 54        self.r = r
 55    def __str__(self):
 56        output = ""
 57        if isinstance(self.l, DbQueryColumn):
 58            prefix = self.l.prefix
 59            if prefix is not None:
 60                output += prefix + "."
 61            output += self.l.name
 62        elif self.l is None:
 63            output += "NULL"
 64        else:
 65            output += "%s"
 66        output += self.op
 67        if isinstance(self.r, DbQueryColumn):
 68            prefix = self.r.prefix
 69            if prefix is not None:
 70                output += prefix + "."
 71            output += self.r.name
 72        elif self.r is None:
 73            output += "NULL"
 74        else:
 75            output += "%s"
 76        return output
 77    @property
 78    def arguments(self):
 79        output = []
 80        if not isinstance(self.l, DbQueryColumn) and self.l is not None:
 81            output.append(self.l)
 82        if not isinstance(self.r, DbQueryColumn) and self.r is not None:
 83            output.append(self.r)
 84        return output
 85
 86class DbQueryColumnSet(object):
 87    """
 88    Represents a set of columns attached to a specific DbOject type. This
 89    object dynamically builds itself based on a passed type. The columns
 90    attached to this set may be used in DbQueries
 91    """
 92    def __init__(self, dbo_type, prefix):
 93        d = dbo_type.__dict__
 94        self.__columns = {}
 95        for attr in d:
 96            obj = d[attr]
 97            if isinstance(obj, Column):
 98                column = DbQueryColumn(dbo_type, prefix, obj.name)
 99                setattr(self, attr, column)
100                self.__columns[obj.name] = column
101    def __len__(self):
102        return len(self.__columns)
103    def __getitem__(self, key):
104        return self.__columns[key]
105    def __iter__(self):
106        return iter(self.__columns)
107
108class DbQueryColumn(object):
109    """
110    Represents a Column object used in a DbQuery
111    """
112    def __init__(self, dbo_type, prefix, column_name):
113        self.dbo_type = dbo_type
114        self.name = column_name
115        self.prefix = prefix
116
117    def __lt__(self, other):
118        return DbQueryColumnComparison(self, "<", other)
119    def __le__(self, other):
120        return DbQueryColumnComparison(self, "<=", other)
121    def __eq__(self, other):
122        op = "="
123        if other is None:
124           op = " IS "
125       return DbQueryColumnComparison(self, op, other)
126    def __ne__(self, other):
127        op = "!="
128        if other is None:
129            op = " IS NOT "
130        return DbQueryColumnComparison(self, op, other)
131    def __gt__(self, other):
132        return DbQueryColumnComparison(self, ">", other)
133    def __ge__(self, other):
134        return DbQueryColumnComparison(self, ">=", other)

  1class DbQueryError(Exception):
  2    """
  3    Raised when there is an error constructing a query
  4    """
  5    def __init__(self, msg):
  6        self.message = msg
  7    def __str__(self):
  8        return self.message
  9
 10class DbQuery(object):
 11    """
 12    Represents a base SQL Query to a database based upon some DbObjects
 13
 14    All of the methods implemented here are valid on select, update, and
 15    delete statements.
 16    """
 17    def __init__(self, execute_filter=None):
 18        """
 19        callback: Function to call when the DbQuery is executed
 20        """
 21        self.__where = []
 22        self.__limit = None
 23        self.__orderby = []
 24        self.__execute_filter = execute_filter
 25    def where(self, expression):
 26        """Specify an expression to append to the WHERE clause"""
 27        self.__where.append(expression)
 28    def limit(self, value=None):
 29        """Specify the limit to the query"""
 30        self.__limit = value
 31    @property
 32    def sql(self):
 33        query = ""
 34        args = []
 35        if len(self.__where) > 0:
 36            where = self.__where[0]
 37            for clause in self.__where[1:]:
 38                where = where & clause
 39            args = where.arguments
 40            query += " WHERE " + str(where)
 41        if self.__limit is not None:
 42            query += " LIMIT " + self.__limit
 43        return query,args
 44    def execute(self, cur):
 45        """
 46        Executes this query on the passed cursor and returns either the result
 47        of the filter function or the cursor if there is no filter function.
 48        """
 49        query = self.sql
 50        cur.execute(query[0], query[1])
 51        if self.__execute_filter:
 52            return self.__execute_filter(self, cur)
 53        else:
 54            return cur
 55
 56class DbSelectQuery(DbQuery):
 57    """
 58    Creates a select query to a database based upon DbObjects
 59    """
 60    def __init__(self, execute_filter=None):
 61        DbQuery.__init__(self, execute_filter)
 62        self.__select = []
 63        self.__froms = []
 64        self.__joins = []
 65        self.__orderby = []
 66    def select(self, *columns):
 67        """Specify one or more columns to select"""
 68        self.__select += columns
 69    def from_table(self, dbo_type, prefix):
 70        """Specify a table to select from"""
 71        self.__froms.append((dbo_type, prefix))
 72    def join(self, dbo_type, prefix, on):
 73        """Specify a table to join to"""
 74        self.__joins.append((dbo_type, prefix, on))
 75    def orderby(self, *columns):
 76        """Specify one or more columns to order by"""
 77        self.__orderby += columns
 78    @property
 79    def sql(self):
 80        query = "SELECT "
 81        args = []
 82        if len(self.__select) == 0:
 83            raise DbQueryError("No selection in DbSelectQuery")
 84        query += ','.join([col.prefix + "." +
 85                 col.name for col in self.__select])
 86        if len(self.__froms) == 0:
 87            raise DbQueryError("No FROM clause in DbSelectQuery")
 88        for table in self.__froms:
 89            query += " FROM " + table[0].dbo_tablename + " " + table[1]
 90        if len(self.__joins) > 0:
 91            for join in self.__joins:
 92                query += " JOIN " + join[0].dbo_tablename + " " + join[1] +
 93                         " ON " + str(join[2])
 94        query_parent = super(DbSelectQuery, self).sql
 95        query += query_parent[0]
 96        args += query_parent[1]
 97        if len(self.__orderby) > 0:
 98           query += " ORDER BY " +
 99                    ','.join([col.prefix + "." +
100                    col.name for col in self.__orderby])
101        return query,args
102
103class DbInsertQuery(DbQuery):
104    """
105    Creates an insert query to a database based upon DbObjects. This does not
106    include any where or limit expressions
107    """
108    def __init__(self, dbo_type, prefix, execute_filter=None):
109        DbQuery.__init__(self, execute_filter)
110        self.table = (dbo_type, prefix)
111        self.__values = []
112    def value(self, column, value):
113        self.__values.append((column, value))
114    @property
115    def sql(self):
116        if len(self.__values) == 0:
117            raise DbQueryError("No values in insert")
118        tablename = self.table[0].dbo_tablename
119        query = "INSERT INTO {table} (".format(table=tablename)
120        args = [val[1] for val in self.__values
121                if val[0].prefix == self.table[1]]
122        query += ",".join([val[0].name for val in self.__values
123                          if val[0].prefix == self.table[1]])
124        query += ") VALUES ("
125        query += ",".join(["%s" for x in args])
126        query += ")"
127        return query,args
128
129class DbUpdateQuery(DbQuery):
130    """
131    Creates an update query to a database based upon DbObjects
132    """
133    def __init__(self, dbo_type, prefix, execute_filter=None):
134        """
135        Initialize the update query
136
137        dbo_type: table type to be updating
138        prefix: Prefix the columns are known under
139        """
140        DbQuery.__init__(self, execute_filter)
141        self.table = (dbo_type, prefix)
142        self.__updates = []
143    def update(self, left, right):
144        self.__updates.append((left, right))
145    @property
146    def sql(self):
147        if len(self.__updates) == 0:
148            raise DbQueryError("No update in DbUpdateQuery")
149        query = "UPDATE " + self.table[0].dbo_tablename + " " + self.table[1]
150        args = []
151        query += " SET "
152        for update in self.__updates:
153            if isinstance(update[0], DbQueryColumn):
154                query += update[0].prefix + "." + update[0].name
155            else:
156                query += "%s"
157                args.append(update[0])
158            query += "="
159            if isinstance(update[1], DbQueryColumn):
160                query += update[1].prefix + "." + update[1].name
161            else:
162                query += "%s"
163                args.append(update[1])
164        query_parent = super(DbUpdateQuery, self).sql
165        query += query_parent[0]
166        args += query_parent[1]
167        return query, args
168
169class DbDeleteQuery(DbQuery):
170    """
171    Creates a delete query for a database based on a DbObject
172    """
173    def __init__(self, dbo_type, prefix, execute_filter=None):
174        DbQuery.__init__(self, execute_filter)
175        self.table = (dbo_type, prefix)
176    @property
177    def sql(self):
178        query = "DELETE FROM " + self.table[0].dbo_tablename + " " +
179                self.table[1]
180        args = []
181        query_parent = super(DbDeleteQuery, self).sql
182        query += query_parent[0]
183        args += query_parent[1]
184        return query, args

  1class DbObject(ColumnSet):
  2    """
  3    A DbObject is a set of columns linked to a table in the database. This is
  4    synonomous to a row. The following class attributes must be set:
  5
  6    dbo_tablename : string table name
  7    primary_key : Column for the primary key
  8    """
  9    def __init__(self, **cols):
 10        ColumnSet.__init__(self)
 11        for name in cols:
 12            c = self.get_column(name)
 13            c.update(cols[name])
 14
 15    @classmethod
 16    def get_query_columns(self, prefix):
 17        return DbQueryColumnSet(self, prefix)
 18
 19    @classmethod
 20    def select(self, prefix):
 21        """
 22        Returns a DbSelectQuery set up for this DbObject
 23        """
 24        columns = self.get_query_columns(prefix)
 25        def execute(query, cur):
 26            output = []
 27            block = cur.fetchmany()
 28            while len(block) > 0:
 29                for row in block:
 30                    values = {}
 31                    i = 0
 32                    for name in columns:
 33                        values[name] = row[i]
 34                        i += 1
 35                    output.append(self(**values))
 36                block = cur.fetchmany()
 37            return output
 38        query = DbSelectQuery(execute)
 39        query.select(*[columns[name] for name in columns])
 40        query.from_table(self, prefix)
 41        return query, columns
 42
 43    def get_primary_key_name(self):
 44        return type(self).__dict__['primary_key'].name
 45
 46    def save(self, cur):
 47        """
 48        Saves any changes to this object to the database
 49        """
 50        if self.primary_key is None:
 51            # we need to be saved
 52            columns = self.get_query_columns('x')
 53            def execute(query, cur):
 54                self.get_column(self.get_primary_key_name()
 55                                ).update(cur.lastrowid)
 56                selection = []
 57                for name in columns:
 58                    if name == self.get_primary_key_name():
 59                        continue #we have no need to update the primary key
 60                    column_instance = self.get_column(name)
 61                    if not column_instance.column.mutable:
 62                        selection.append(columns[name])
 63                if len(selection) != 0:
 64                    # we get to select to get additional computed values
 65                    def execute2(query, cur):
 66                        row = cur.fetchone()
 67                        index = 0
 68                        for s in selection:
 69                            self.get_column(s.name).update(row[index])
 70                            index += 1
 71                        return True
 72                    query = DbSelectQuery(execute2)
 73                    query.select(*selection)
 74                    query.from_table(type(self), 'x')
 75                    query.where(columns[self.get_primary_key_name()] ==
 76                                self.get_column(self.get_primary_key_name()
 77                                                ).value)
 78                    return query.execute(cur)
 79                return True
 80            query = DbInsertQuery(type(self), 'x', execute)
 81            for name in columns:
 82                column_instance = self.get_column(name)
 83                if not column_instance.column.mutable:
 84                    continue
 85                query.value(columns[name], column_instance.value)
 86            print query.sql
 87            return query.execute(cur)
 88        else:
 89            # we have been modified
 90            modified = self.mutated
 91            if len(modified) == 0:
 92                return True
 93            columns = self.get_query_columns('x')
 94            def execute(query, cur):
 95                for mod in modified:
 96                    mod.update(mod.value)
 97                return True
 98            query = DbUpdateQuery(type(self), 'x', execute)
 99            for mod in modified:
100                query.update(columns[mod.column.name], mod.value)
101            query.where(columns[self.get_primary_key_name()] == self.primary_key)
102            return query.execute(cur)