[DB-SIG] db-api suggestions / zike data classes

Andy Dustman adustman@comstar.net
Mon, 17 Apr 2000 19:01:50 -0400 (EDT)


On Fri, 14 Apr 2000, Michal Wallace (sabren) wrote:

> 
> Hello Everybody :)
> 
>    I'm attempting to build an abstraction layer on top of the DB-API,
> that gives an object-oriented interface to individual records. Here's
> an example:
> 
> 
> ##################################
> 
> import someDBAPIcompliantModule
> dbc = someDBAPIcompiantModule.connect("blah blah")
> 
> import zdc.Record
> rec = zdc.Record(dbc, "myTable")
> 
> rec.new()
> rec["name"] = "Michal"
> rec["age"] = 23
> rec.save()
> 
> 
> rec.select(name="Fred")
> print "Fred is " + rec["age"] + "years old."
> # or:
> print "Fred is " + rec[1] + "years old."
> 
> ##################################

Also take a look at:

http://starship.python.net/crew/adustman

for SQLDict (it doesn't have it's own page). It has an ObjectBuilder
class, which uses table and column specifications to determine what
attributes the object has, and when these objects are registered with a
SQLDict object, it generates the necessary SQL to SELECT, UPDATE, and
DELETE the objects directly. As an example (not guaranteed to work):

from SQLDict import MySQLDict, ObjectBuilder

class Person(ObjectBuilder):

    table = "myTable"
    columns = ["name", "age"]
    update_columns = columns
    indices = [ ('name', ['name']), ('age', ['age']) ]

    def _set_age(self, age):
        if age<0: raise ValueError, "nobody's that young"
        self.__dict__['age']=age

real_db = MySQLdb.connect(...)
db = MySQLDict(real_db)
Person().register(db) # register with the database

# all three of the following are equivalent
p = Person("Michal",23)
p = Person(name="Michal", age=23)
p = Person()
p.name = "Michal"
p.age = 23
p.age = -1 # raises ValueError
print repr(p) # prints Person("Michal",23)
print str(p) # prints Person(name="Michal",age=23)

db.insert(p) # INSERT into the database

p23 = db.Person.age[23].fetchall() # get everyone who's 23
geezers = db.Person.select(WHERE="WHERE age>=70").fetchall()
db.Person.name['Michal'] = p2 # UPDATE
del db.Person.name['Michal']

---

The standard SQLDict class will work at least with mxODBC; it is a very
simple matter to subclass it to adapt it for other databases, so long as
they are DB-API.
 
>   First, if I'm just looking at some random connection object, and I
> use it to create a cursor object... How can I get the fieldtypes out
> of the description? The fieldtypes are arbitrary values that have to
> be compared against something back in the module's namespace... But
> since this is a random connection object, *I don't know what module
> to look in!!!*

MySQLdb (and others) export some psuedo-types that can be used for
comparison. You can take the type field from the description and make
comparisons like t == STRING, or t == NUMBER. This is defined in the DB
API interface.
 
>   Suppose I have an autonumber / auto_increment / whatever field. I
> run an INSERT statement. How do I get the generated number? As far
> as I can tell, I have to use a module-specific function call. Wouldn't
> this be a fairly useful process to standardize?

Useful, yes. Two ways with MySQLdb: One is to use pure SQL and SELECT
LAST_INSERT_ID(). The other is to do cursor.insert_id(). Neither is
portable, of course. In Solid, I have seen this done as a stored
procedure call, so it is a real implementation problem.

>   I personally don't have any particular need for this at the moment,
> but I'm sure it would be nice to have down the line: dbc.tables() to
> give a list of tables?  Possibly even dbc.queries() or dbc.sprocs()
> for providers that had queries and stored procedures.. I'm guessing
> most modules have something like this already, but it's no fun without
> a standard. :)

In most databases, you seem to have to suck this out of the system tables,
which have a standard layout. 

-- 
andy dustman       |     programmer/analyst     |      comstar.net, inc.
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d
"Therefore, sweet knights, if you may doubt your strength or courage, 
come no further, for death awaits you all, with nasty, big, pointy teeth!"