[DB-SIG] Re: db module wrapper
Peter L. Buschman
plblists at iotk.com
Sun Aug 22 14:36:54 CEST 2004
Hi Randall:
I've been following your discussion avidly. I've been working gradually on
the same thing, a DBAPI 2.0 compliant wrapper to abstract the
differences between drivers as part of a larger database abstraction layer
I've been working on. The final project will have lots more intelligence
about SQL dialects and the capabilities / supported features of individual
RDBMS, but the initial step is just to get this small piece working.
I was wondering how far you'd come with paramstyle conversion? I've
attached some of my conversion code that allows me to convert from
any paramstyle to any other paramstyle, but it does not yet support escaped
placeholders. Once I have this working, I plan to incorporate
Kevin Jacob's exception wrapping trick to create a thin layer on top of any
DBAPI 2.0 module I choose to use. The final piece necessary is
to abstract the exception arguments of different drivers so they are
returned consistently.
Kindest Regards,
Peter Buschman
At 05:19 AM 8/22/2004, Randall Smith wrote:
>Jonathan M. Franz wrote:
>>Well, I'm late to this conversation - so I apologize if my points have
>>already been made.
>>What Randal is doing is very similar to what we're working towards for
>>PDO 2.0 - PDO _is_ actively maintained and promoted, btw. Heck, back in
>Developers are certainly spread thin and when I saw your post, my first
>thought was maybe I should drop what I'm doing and use PDO. It is similar
>in that they both try to hide differences in underlying modules. There is
>a difference in the scopes of the projects. PDO is ADO for Python. The
>scope of my project is to maintain a DB API V2 interface while hiding
>module differences. Although we do have some duplicated effort, there are
>several differences. I just want my DB API2 code to work with all
>modules. You want an ADO interface.
>>the 1.0.2 days we even were thrown on some CDs that linux enterprise
>>magazine in europe distributed. Frankly, we've always been surprised by
>>the cold reception we've gotten on the db-sig mailing list whenever we
>>raise a concern or mention our module. Perhaps we've been too
>>argumentative about things we feel the dbapi is lacking?
>>Some of the arguments earlier this year when Guido spoke up were things
>>we took very seriously - but the conversation about what todo for dbapi
>>3.0 died for no apparent (to me) reason.
>>Anyway, some thoughts on ways we do things now in pdo, and how they
>>intersect with Randall's ideas:
>>(cross pollination of ideas is always a good thing)
>>- connection strings
>>We use a single string to hold connection data, with the core parameters
>>standardized (dbapi-module,username, password, database), and the rest
>>given in a module specific way, but all formated into a single string thusly:
>>aConnection =
>>pdo.connect("module=someModule;user=foobar;passwd=mypass;db=myDB;host=localhost")
>>
>I don't plan to modify connection parameters. The thought being that when
>you switch databases, you have to change this anyway.
>>This should be _very_ familar to ADO users.
>>- parameter formating
>>Currently we just use the underlying dbapi module's quoting method - but
>>for 2.0 (which we hope to beta soon!) we're moving towards a :named
>>style, using a modified form of the recipe for style unification at
>>ASPN. http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/278612
>>We'd considered a positional ('?') style, but found it too limiting.
>>The first time you do a insert or update with 20 ?s in it, you get the
>>feeling that it is rather awkward.
>>Of course, you could allow the user to switch styles on the fly - it'd
>>just be an extra set of conversion functions.
>When discussing this, people had different opinions. Therefore, I'm
>enabling all 5 paramstyles.
>>- exceptions
>>We already have implemented a unified exception system - PDO contains
>>it's own exceptions, and the textual description contains the underlying
>>exception and the underlying exception's text - allowing for users to
>>capture one set of exceptions and yet giving them all the detail they
>>need to find out why the dbapi module. It's easy to implement, just take
>>a look and steal away.
>The popular opinion was to use Kevin Jacob's idea:
>http://mail.python.org/pipermail/db-sig/2003-April/003345.html
>
>I say popular. Ian and Kevin suggested it.
>>==============
>>Things to ponder:
>>One thing that made my life rather painful when working on PDO 2.0 was
>>that the underlying type systems in dbapi modules are so different. The
>>implementation of editable resultsets (no more update queries!) required
>>some interesting juggling to bring the underlying types into the current
>>namespace in a usable way.
>Yea. That does sound difficult. Editable resultsets are nice.
>>Do you plan on editable resultsets in your module?
>Definitely not. To much work. I'm mainly programming to API 2. Config
>files are used to adapt modules that do not completely comply. In theory,
>a module that is API2 compliant needs no config.
>>More soon as I catch up further with the thread.
>>~Jon Franz
>>NeuroKode Labs, LLC
>>
>>_______________________________________________
>>DB-SIG maillist - DB-SIG at python.org
>>http://mail.python.org/mailman/listinfo/db-sig
>
>I'm all about not duplicating effort. I'm not a glutton for needless
>work. Whether or not my project is made public and maintained depends on
>public interest. Are our projects similar enough that I should drop
>mine? I don't know. I'm not looking for an ADO interface, so I plan on
>using what I have created. Others may think there is too much
>duplication. In that case, I'll use it, but not publish/maintain it.
>Though, as judged from the list, there seems to be interest. So my
>current plans are to put it in Sourceforge CVS.
>
>Randall
>_______________________________________________
>DB-SIG maillist - DB-SIG at python.org
>http://mail.python.org/mailman/listinfo/db-sig
>
-------------- next part --------------
import string
import re
PLACEHOLDER_TOKENS = {
'qmark' : '?',
'format' : '%s',
}
PLACEHOLDER_EXPS = {
'qmark' : re.compile('(\?)'),
'numeric' : re.compile('(:\d+)'),
'named' : re.compile('(:\w+)'),
'format' : re.compile('(%s)'),
'pyformat' : re.compile('(%\(\w+\)s)'),
}
PARAMNAME_EXPS = {
'named' : re.compile(':(\w+)'),
'pyformat' : re.compile('%\((\w+)\)s'),
}
PLACEHOLDER_SUBS = {
'qmark' : lambda param_number : '?',
'numeric' : lambda param_number : ':%d' % (param_number),
'named' : lambda param_number : ':param%d' % (param_number),
'format' : lambda param_number : '%s',
'pyformat' : lambda param_number : '%%(param%d)s' % (param_number),
}
PARAMSTYLES = {
'all' : [ 'qmark', 'numeric', 'named', 'format', 'pyformat' ],
'sequence' : [ 'qmark', 'numeric', 'format' ],
'dict' : [ 'named', 'pyformat' ],
'token' : [ 'qmark', 'format' ],
}
##
##
##
def token_to_token( from_paramstyle, to_paramstyle, query, params ):
from_token = PLACEHOLDER_TOKENS[from_paramstyle]
to_token = PLACEHOLDER_TOKENS[to_paramstyle]
new_params = params
new_query = string.replace(query, from_token, to_token)
return new_query, new_params
##
##
##
def sequence_to_sequence( from_paramstyle, to_paramstyle, query, params ):
placeholder_exp = PLACEHOLDER_EXPS[from_paramstyle]
placeholder_sub = PLACEHOLDER_SUBS[to_paramstyle]
new_query = query
new_params = params
param_num = 0
for placeholder in placeholder_exp.findall(query):
param_num += 1
new_placeholder = placeholder_sub(param_num)
new_query = string.replace(new_query, placeholder, new_placeholder, 1)
return new_query, new_params
##
##
##
def sequence_to_dict( from_paramstyle, to_paramstyle, query, params ):
placeholder_exp = PLACEHOLDER_EXPS[from_paramstyle]
placeholder_sub = PLACEHOLDER_SUBS[to_paramstyle]
new_query = query
new_params = {}
param_num = 0
for placeholder in placeholder_exp.findall(query):
param_num += 1
param_name = 'param%d' % (param_num)
new_placeholder = placeholder_sub(param_num)
new_query = string.replace(new_query, placeholder, new_placeholder, 1)
new_params[param_name] = params[param_num-1]
return new_query, new_params
##
##
##
def dict_to_sequence( from_paramstyle, to_paramstyle, query, params ):
placeholder_exp = PLACEHOLDER_EXPS[from_paramstyle]
placeholder_sub = PLACEHOLDER_SUBS[to_paramstyle]
paramname_exp = PARAMNAME_EXPS[from_paramstyle]
new_query = query
new_params = []
param_num = 0
for placeholder in placeholder_exp.findall(query):
param_num += 1
param_name = paramname_exp.findall(placeholder)[0]
new_placeholder = placeholder_sub(param_num)
new_query = string.replace(new_query, placeholder, new_placeholder, 1)
new_params.append(params[param_name])
return new_query, new_params
#def paramstyle_convert( from_paramstyle, to_paramstyle, query, params ):
# placeholder_exp = placeholder_exps[from_paramstyle]
# placeholder_sub = placeholder_subs[to_paramstyle]
# new_query = query
# if from_paramstyle in seq_paramstyles:
# from_is_sequence = 1
# from_is_dict = 0
# else:
# from_is_sequence = 0
# from_is_dict = 1
# if to_paramstyle in seq_paramstyles:
# to_is_sequence = 1
# to_is_dict = 0
# new_params = []
# else:
# to_is_sequence = 0
# to_is_dict = 1
# new_params = {}
# param_number = 0
# for placeholder in placeholder_exp.findall(query):
# param_number += 1
# new_placeholder = placeholder_sub(param_number)
# if from_is_dict:
# paramname_exp = paramname_exps[to_paramstyle]
# paramname = paramname_exp.findall(placeholder)
if __name__ == '__main__':
sequence_params = ['a', 'b', 'c', 'd']
dict_params = {
'foo' : 'a',
'bar' : 'b',
'baz' : 'c',
'quux' : 'd',
}
tests = {
'qmark' : [ 'SELECT * FROM ? WHERE ? > ? OR ? IS NOT NULL', sequence_params ],
'numeric' : [ 'SELECT * FROM :1 WHERE :2 > :3 OR :4 IS NOT NULL', sequence_params ],
'named' : [ 'SELECT * FROM :foo WHERE :bar > :baz OR :quux IS NOT NULL', dict_params ],
'format' : [ 'SELECT * FROM %s WHERE %s > %s OR %s IS NOT NULL', sequence_params ],
'pyformat' : [ 'SELECT * FROM %(foo)s WHERE %(bar)s > %(baz)s OR %(quux)s IS NOT NULL', dict_params ],
}
print ''
print '[ PLACEHOLDER MATCH TESTS ]'
print ''
for paramstyle in tests.keys():
query = tests[paramstyle][0]
regexp = PLACEHOLDER_EXPS[paramstyle]
print regexp.findall(query)
indent = 4
width = 16
print ''
print '[ TOKEN TO TOKEN TESTS ]'
print ''
for from_paramstyle in PARAMSTYLES['token']:
from_query = tests[from_paramstyle][0]
from_params = tests[from_paramstyle][1]
print "%s[ %s ] '%s' '%s'" % ( ' ' * indent, from_paramstyle, from_query, from_params )
print ''
for to_paramstyle in PARAMSTYLES['token']:
to_query, to_params = sequence_to_sequence(from_paramstyle, to_paramstyle, from_query, from_params)
print '%s%s%s: %s' % ( ' ' * indent * 2, to_paramstyle, '.' * (width + indent - len(to_paramstyle)), to_query )
print '%s%s: %s' % ( ' ' * indent * 2, ' ' * (width + indent), to_params )
print ''
print ''
print '[ SEQUENCE TO SEQUENCE TESTS ]'
print ''
for from_paramstyle in PARAMSTYLES['sequence']:
from_query = tests[from_paramstyle][0]
from_params = tests[from_paramstyle][1]
print "%s[ %s ] '%s' '%s'" % ( ' ' * indent, from_paramstyle, from_query, from_params )
print ''
for to_paramstyle in PARAMSTYLES['sequence']:
to_query, to_params = sequence_to_sequence(from_paramstyle, to_paramstyle, from_query, from_params)
print '%s%s%s: %s' % ( ' ' * indent * 2, to_paramstyle, '.' * (width + indent - len(to_paramstyle)), to_query )
print '%s%s: %s' % ( ' ' * indent * 2, ' ' * (width + indent), to_params )
print ''
print ''
print '[ SEQUENCE TO DICT TESTS ]'
print ''
for from_paramstyle in PARAMSTYLES['sequence']:
from_query = tests[from_paramstyle][0]
from_params = tests[from_paramstyle][1]
print "%s[ %s ] '%s' '%s'" % ( ' ' * indent, from_paramstyle, from_query, from_params )
print ''
for to_paramstyle in PARAMSTYLES['dict']:
to_query, to_params = sequence_to_dict(from_paramstyle, to_paramstyle, from_query, from_params)
print '%s%s%s: %s' % ( ' ' * indent * 2, to_paramstyle, '.' * (width + indent - len(to_paramstyle)), to_query )
print '%s%s: %s' % ( ' ' * indent * 2, ' ' * (width + indent), to_params )
print ''
print ''
print '[ DICT TO SEQUENCE TESTS ]'
print ''
for from_paramstyle in PARAMSTYLES['dict']:
from_query = tests[from_paramstyle][0]
from_params = tests[from_paramstyle][1]
print "%s[ %s ] '%s' '%s'" % ( ' ' * indent, from_paramstyle, from_query, from_params )
print ''
for to_paramstyle in PARAMSTYLES['sequence']:
to_query, to_params = dict_to_sequence(from_paramstyle, to_paramstyle, from_query, from_params)
print '%s%s%s: %s' % ( ' ' * indent * 2, to_paramstyle, '.' * (width + indent - len(to_paramstyle)), to_query )
print '%s%s: %s' % ( ' ' * indent * 2, ' ' * (width + indent), to_params )
print ''
# print ''
# print '[ PARAMSTYLE TRANSLATIONS ]'
# for paramstyle, query, params in tests:
# print ''
# from_paramstyle = paramstyle
# print '%s[ %s ]' % (' ' * indent, from_paramstyle.upper())
# print ''
# label = 'query'
# print '%s%s%s: %s' % (' ' * indent, label, '.' * (width + indent - len(label)), query)
# label = 'paramstyle'
# print '%s%s%s: %s' % (' ' * indent, label, '.' * (width + indent - len(label)), paramstyle)
# print ''
# for to_paramstyle in paramstyles:
# paramstyle_convert(from_paramstyle, to_paramstyle, query, params)
# translate_paramstyle = paramstyle_translations[from_paramstyle][to_paramstyle]
# translated_query, translated_params = translate_paramstyle(query, *params)
# label = '%s_query' % (to_paramstyle)
# print '%s%s%s: %s' % (' ' * indent * 2, label, '.' * (width - len(label)), translated_query)
# label = '%s_params' % (to_paramstyle)
# print '%s%s%s: %s' % (' ' * indent * 2, label, '.' * (width - len(label)), translated_params)
# print ''
More information about the DB-SIG
mailing list