Re: [SQLObject] ADO DB-API support?

[Magnus <ma...@th...>]

At 15:09 2003-05-26 -0500, Ian Bicking wrote:
>I don't think the DB-API is not complete enough to be considered "right"
>for this.  AFAIK, there is no standard way to indicate how non-standard
>types should be quoted, and I cannot see what the *actual* SQL sent is.

Right. I've brought up both issues on the db-sig mailing list recently.

I think the db driver should look for a standardized attribute in
each parameter it gets, and if it finds it, it should call it as
a method, and then recieve a string which is the representation
of data that we want to send, and a description of what type it
should become in the backend. I think this combo is needed.

If it gets '123' and DECIMAL it knows that it should replace the
? with 123, but if it gets '123' and VARCHAR it should replace ?
with '123'. I haven't tried to implement that though.

I'd also like to see some kind of debug mode where the final SQL
could be viewed before sending to the backend. It would also be
useful if this string would pop up in tracebacks.

>By giving up control to the underlying adapter, I fear that other
>compatibility issues will be harder to fix later.

Maybe... But if we keep the parameters separate as long as
possible, we have a lower enthropy in our code. It's always
fairly simple to pass in parameters, but it's not so simple
to get them out of the string.

>Additionally, the underlying vendor's driver *cannot* be the most
>authoritative participant.  That driver is written in C, with C types --
>the Python type system is much richer.  At the Python level we can offer
>a much more complete interface (with things like SQLBuilder.func.NOW()).

As I mentioned above, I think that could be fixed.

>Parameter passing will solve this date quoting problem, but at some
>later date I expect a problem that parameter passing *won't* solve, and
>in fact will be made much more difficult to solve because of parameter
>passing.

Perhaps...but I'm not so certain, and as I said it's easier to for
*from* sepatare parameters than to go *to* separate parameters.

>Yes, that implementation would work.  You should be able to do that all
>in a subclass, without changing any current classes, though I imagine
>you'll have to override most of DBAPIConnection.

Well, all concrete drivers subclass DBAPIConnection... This will just
override a few more methods... :)

>Perhaps -- I don't even know, because I'm not using parameters.

No backend uses %s. That's a DB-API invention, so no driver with
paramstyle format or pyformat has backend support for parameter
passing (and vice versa I think).

>AFAIK, prepared statements and whatnot
>only offer advantages in a certain context, a context which will not
>exist in SQLObject.

True. That makes SQLObject a bit different. It still seems that most
people prefer parameter passing, but your tradeoff is not quite that
of an ordinary application coder.

>Can you change the ?'s reasonably efficiently, while protecting against
>literals that contain ?'s ?

It's just a few lines of code, and my thought was to cache these SQL
strings in a dictionary, so we don't need to rerun the translation if
it had been done before. Maybe that's overkill; it would cause some
new problems. On my 700MHz Duron, I transform 4 simple SQL statements
1000 times in about 320 millisecs. I.e. 80 microsecs per call. Dictionary
lookup in a caching version is about 45 millisecs for the 1000 lookups
each of the four strings. About 11 microsecs per call. I guess that's
nothing compared to the overhead in calling the sql server. I should
try though... (After my bookkeeping is with my accountant... That's what
I should be doing now.)

quote_separation = re.compile(r"(.*?)('.*?')", re.DOTALL)
def parseQmark(sql):
     '''parseQmark(sql_with_?) -> sql_with_%s

     Parse SQL written with qmark parameter style (i.e. Direct SQL
     or SQL CLI style) so that the python %-operator can be used to
     pass in parameters. This means that % needs to be changed to %%,
     and ? needs to be changed to %s if it's not inside quotes.
     '''
     sql = sql.replace('%', '%%')
     # Extra "''" needed to make my re happy if sql doesn't end with '
     notQuoted_quoted = quote_separation.findall(sql+"''")
     replaced = [nq.replace('?', '%s')+q for (nq, q) in notQuoted_quoted]
     return "".join(replaced)[:-2] # remove extra ''

in cursor.execute(), we'd have something like
     if sql in statement_cache:
         sql = statement_cache[sql]
     else:
         tmp = parseQmark(sql)
         statement_cache[sql]= tmp
         sql = tmp

This would enable drivers such as the ones for PostgreSQL, MySQL and
SQLite to use paramstyle 'qmark'. Taking this one step further, we could
then pass in :1, :2, :3 or :a, :b, :c etc instead of the values to get
support for the drivers that use numbered or named parameter styles, or
even @a, @b... to support Sybase.

It's no big deal. Disregarding the issue of SQLObject for a moment, I think
it would be great if we managed to run all DB-API drivers with a single
parmstyle. Maybe there should be a "compatible mode" in DB-API that
enforced paramstyle qmark at the possible expense of some performance.

"module.set_paramstyle('qmark')" ?


--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program