[DB-SIG] Re: db module wrapper

Randall Smith randall at tnr.cc
Sun Aug 22 20:05:33 CEST 2004


Peter L. Buschman wrote:
> 
> 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
I've got all 5 paramstyles working with escaping.  However, it is an 
ugly solution.  Yours is much nicer.  Look in the QueryFormatter class 
of the attachment I send you.
> 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
Did this.  It is nice.
> 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.
I asked about this on this list.  Nobody seemed interested.  It would be 
nice to have a list of which modules raise which exceptions for which 
events.
> 
> Kindest Regards,
> 
> Peter Buschman

Peter.  Looks like we have the same objective except for the 
'intelligence about SQL dialects'.  I have no intention of interpreting 
SQL.  I think it would make allot of sense for us to join forces.  Case 
in point:  I spent many hours yesterday on paramstyle conversion.

Do you have a project website or CVS?  Project name?  I've got CVS on 
Sourceforge.  If we decide to join code we could use yours or my CVS. 
If not, we should at least share code and ideas.

I'll send you an attachment in another email with my work.  This 
conversation should be on DB-SIG, so I'm not attaching it here.
> 
> 
> 
> 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
>>
> 
> ------------------------------------------------------------------------
> 
> 
> 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