Welcome, guest | Sign In | My Account | Store | Cart

Provides a simple way to access your database tables/rows/attributes as Python Classes/Instances/attributes respectively.

Python, 165 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
from Cheetah.Template import Template
import psycopg
conn = psycopg.connect(database="demo")
class Model:
    '''A class providing limited introspective access to (currently) 
    a Postresql Database.
    '''
    global conn 
    def __init__(self, connection=conn):
        self.conn = connection

    def __getattr__(self, name):
        """Returns a subclass of Relation for each table found in
        the model.

        N.B. This returns a class object rather than an instance.  From
        this object you can:-
           - query the database for a cursor,
           - instantiate an instance of the class (i.e. a single row 
             from the database)
           - obtain meta-data from the database table or view
        """
        if name.lower() in self.tables():
            DataSource = type(name, (Relation, dict), {})
            return DataSource
        else:
            raise AttributeError, 'Attribute %s not found' % name


    def getView(self, view_name):
        """The same as __getattr__ except from here, you're allowed to
        specify the view as a string (rather than an object)
        """
        if view_name.lower() in self.tables_and_views():
            DataSource = type(view_name, (Relation, type), {'conn': self.conn})
            return DataSource
        else:
            raise ValueError, 'View %s not found' % view_name


    def tables_and_views(self, schemas=None):
        """Returns a list of tables and views contained in the database on the
        current connection.

        If specified, schemas should be an iterable of schemas from which the
        tables/views should be gathered
        """
        sql = '''select tablename
                 from pg_tables
        	 union
        	 select viewname
        	 from pg_views
        '''
        if schemas:
            sql += "where schemaname in ("
            sql += ", ".join(["%s" for i in schemas]) + ")" 
        cursor = self.conn.cursor()
        cursor.execute(sql, schemas)
        result = cursor.fetchall()
        return [i[0] for i in result]

    def tables(self, schema=None):
        """Returns a list of tables contained in the database to which the
        class is currently attached.

        If specified, schemas should be an iterable of schemas from which the
        tables should be gathered.
        """
        sql = '''select tablename
                 from pg_tables
        '''
        if schema:
            sql += "where schemaname = %s"
        cursor = self.conn.cursor()
        cursor.execute(sql, (schema, ))
        result = cursor.fetchall()
        return [i[0] for i in result]

class Relation:

    conn = psycopg.connect(database="demo")

    def __init__(self, **kwargs):
        tablename = self.__class__.__name__
        sql = self.render_query(view=tablename, pkey=kwargs)
        cursor = self.execute(sql, kwargs)
        result = cursor.fetchone()
        self.data = {}
        for key, value in zip([i[0] for i in cursor.description], result):	
            self.data[key] = value

    def render_query(self, view=None, pkey=None):
        template = Template('''
        select * 
        from $view
        where 
        #for $key, $val in $pkey.iteritems()
        $key = %($key)s
        #end for
        ''', [locals()])
        return template.respond()

    def __getattr__(self, name):
        if name in self.data:
            return self.data[name]
    def __getitem__(self, name):
        if name in self.data:
            return self.data[name]

    @classmethod
    def description(cls):
        """Returns a description of this relations attributes

        This method simply returns the description attribute from the
        DB API cursor object.  This is a sequence of 7 item sequences
        of the form (name, type_code, display_size, internal_size, 
        precision, null_ok) of which only the first two items are
        guaranteed.
        """
        cursor = cls.execute('''select * from %s
                                limit 1''' % cls.__name__)
        return cursor.description


    @classmethod
    def selectAll(cls):
        """A convenience method for selecting an entire relation from
        the database (although we simply forward the call to the more
        general execute)
        """
        return cls.execute("select * from %s" % cls.__name__)
    
    @classmethod
    def execute(cls, template, params=None, commit=True):
        """Executes the specified statement and returns a copy of the cursor
        """
        cursor = cls.conn.cursor()
        cursor.execute(template, params) #execute stmt returns None
        commit and  cls.conn.commit()
        return cursor

    @classmethod
    def insert(cls, mapping):
        """Attempts to insert the specified key value mapping into this table
        """
        keys_fmt = map(lambda x: "%%(%s)s" % x, mapping.keys())
        template = """insert into %(table)s (%(keys)s)
            values (%(values)s);""" % { 'table': cls.__name__, 
                                        'keys': ", ".join(mapping.keys()),
                                        'values': ", ".join(keys_fmt) }
        cls.execute(template, mapping)


if __name__ == '__main__':
    #replace contact with table in your database
    conn = psycopg.connect(database="demo")
    model = Model(conn)
    Contacts = model.contacts
    print Contacts.description   #lists your table attributes
    c = Contacts(id=9)           #c now represents a row in your db
    print c.email                #only if there exists an email attribute
    print c['email']

    cursor = model.contact.selectAll()
    results = cursor.fetchall()
    

I looked into using sqlobject and liked the way you could define your database in terms of Python Classes. What if your database already exists independantly of sqlobject. I know you can use the _fromDatabase attribute, but then you still have to create a class for each Database table.

This module creates the classes dynamically as you request them! You can use either of

Datasource = model.table_name Datasource = model.get_view('table_name')

Datasource is now a class object which is a subtype of Relation, whose name matches your database table, on which you can instantiate instances (i.e. rows) of that table.

row = Datasource(id=123) print row {'name': 'andy', 'age': 24, 'occupation': 'programmer'} print row.name 'andy' print row['name'] 'andy'

The code is very untested. This was done just to practice meta-class programming really. I'd be interested to hear other approaches to this problem