Table and Row wrappers

[Aaron Brady]

Hi, python hobbyist, new to MySQLdb thinking aloud:

Can we have something like:

uri= cnxn.table( 'uri' ) # has auto_increment 'id'
path= ''
slashdotrow= uri.NewRow( href= "http://slashdot.org/", local= path+ "slashdot.org.htm" )
urlretrieve( slashdotrow.href, slashdotrow.local ) #poss'ly in subclass, gets bulky
import htmllib as H
import formatter as F
parser= H.HTMLParser( F.NullFormatter() )
parser.feed( open( slashdotrow.local ).read() )
slashdotanchs= parser.anchorlist

cnxn.table( 'successes' ).NewRow( id= slashdotrow.id, success= 1 )

anchs= cnxn.table( 'anchs' )
for anch in slashdotanchs:
newuri= uri.NewRow( href= anch )
anchs.NewRow( id= slashdotrow.id, linkid= newuri.id )

slashdotlinks= anchs.where( id= slashdotrow.id )
print len( slashdotlinks ), 'links off Slashdot.'

[details]

Details very open for debate. Possibly include a caching model, commit model, transaction model, very open.

Any interest?

Full implementation below. Working in Python 2.5 on Windows XP.

(Personal note: Knowing myself, I'll probably pursue on my own, even in case there's not. But sounds cool, no?)

'''
Hi, python hobbyist, new to MySQLdb thinking aloud:

Can we have something like:
'''
import MySQLdb as SQL
import new
from urllib import urlretrieve

class Row:
def init( self, cnxn, tname, keyn, keyv ):
self.cnxn, self.tname= cnxn, tname
self.keyn, self.keyv= keyn, keyv
def getattr( self, name ):
cursor= self.cnxn.cursor()
query= 'SELECT %s FROM %s WHERE %s = %%s'
query%= name, self.tname, self.keyn
suc= cursor.execute( query, self.keyv )
assert suc== 1
vals= cursor.fetchall()[0]
assert len( vals )== 1
return vals[0]

class Table:
def init( self, cnxn, tname ):
self.cnxn, self.tname= cnxn, tname
self.asskey()
def asskey( self ):
cursor= self.cnxn.cursor()
cursor.execute( 'SHOW COLUMNS FROM %s'% self.tname )
cols= cursor.fetchall()
for col in cols:
if col[3]== 'PRI':
self.keyn= col[0]
return
print cols
print self.tname
assert False, 'A primary key is required for tables wrapped in Table.'
def NewRow( self, kwargs ):
cursor= self.cnxn.cursor()
query= 'INSERT INTO %s( %s ) VALUES( %%s )'
query%= self.tname, ', '.join( kwargs.keys() )
query%= ', '.join( [ '%s' ] * len( kwargs ) )
cursor.execute( query, kwargs.values() )
return Row( self.cnxn, self.tname, self.keyn, cursor.lastrowid )
def where( self, *args, kwargs ):
assert not args, 'Args reserved for inequalities (or whereinequality)'
assert len( kwargs )== 1, 'Only one keyword equality implemented, one required.'
cursor= self.cnxn.cursor()
query= 'SELECT %s FROM %s WHERE %s = %%s'
query%= self.keyn, self.tname, list( kwargs.keys() )[0]
print query, list( kwargs.values() )[0]
suc= cursor.execute( query, list( kwargs.values() )[0] )
rows= []
for row in cursor.fetchall():
rows.append( Row( self.cnxn, self.tname, self.keyn, row[0] ) )
return rows

def table( self, tname ):
return Table( self, tname )

def initwrapper( cnxn ):
cursor= cnxn.cursor()
initqueries=
'DROP DATABASE IF EXISTS joe2',
'CREATE DATABASE joe2',
'USE joe2',
'CREATE TABLE uri( id INT KEY auto_increment, href VARCHAR(200), local VARCHAR(200) )',
'CREATE TABLE successes( akey INT KEY auto_increment, id INT, success BOOLEAN )',
'CREATE TABLE anchs( akey INT KEY auto_increment, id INT, linkid INT )'
for query in initqueries:
cursor.execute( query )
cnxn.table= new.instancemethod( table, cnxn )

cnxn= SQL.connect( host= "localhost", user= "root", passwd= "1234" )
initwrapper( cnxn )
'''idea below, implemented above.'''

uri= cnxn.table( 'uri' ) # has auto_increment 'id'
path= ''
slashdotrow= uri.NewRow( href= "http://slashdot.org/", local= path+ "slashdot.org.htm" )
urlretrieve( slashdotrow.href, slashdotrow.local ) #poss'ly method in subclass, gets bulky
import htmllib as H
import formatter as F
parser= H.HTMLParser( F.NullFormatter() )
parser.feed( open( slashdotrow.local ).read() )
slashdotanchs= parser.anchorlist

cnxn.table( 'successes' ).NewRow( id= slashdotrow.id, success= 1 )

anchs= cnxn.table( 'anchs' )
for anch in slashdotanchs:
newuri= uri.NewRow( href= anch )
anchs.NewRow( id= slashdotrow.id, linkid= newuri.id )

slashdotlinks= anchs.where( id= slashdotrow.id )
print len( slashdotlinks ), 'links off Slashdot.'

'''
[details]

Details open for debate. Possibly include a caching model, commit model, transaction model, very open.

Any interest?

Full implementation below.

(Personal note: Knowing myself, I'll probably pursue on my own, even in case there's not. But sounds cool, no?)
'''