Re: [SQLObject] ADO DB-API support?

[Ian B. <ia...@co...>]

On Tue, 2003-05-27 at 02:43, Magnus Lyck=E5 wrote:
> At 15:09 2003-05-26 -0500, Ian Bicking wrote:
> >I don't think the DB-API is not complete enough to be considered "righ=
t"
> >for this.  AFAIK, there is no standard way to indicate how non-standar=
d
> >types should be quoted, and I cannot see what the *actual* SQL sent is=
.
>=20
> Right. I've brought up both issues on the db-sig mailing list recently.
>=20
> 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.
>=20
> 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.
>=20
> 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.

*If* those changes were made, then I'd feel much differently about using
parameters.  If you want to write up a PEP, to maybe take it past the
discussion that's gone on for so long, then that'd be great, and I'd be
happy to help proofread it or whatever... but it's really the authors of
the database adapters who need convincing.  Which may be easy, I don't
know.

> >By giving up control to the underlying adapter, I fear that other
> >compatibility issues will be harder to fix later.
>=20
> 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.

Right now I'm not constructing SQL until it's just about to be sent off,
so it's not really a problem.

> >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 off=
er
> >a much more complete interface (with things like SQLBuilder.func.NOW()=
).
>=20
> As I mentioned above, I think that could be fixed.
>=20
> >Parameter passing will solve this date quoting problem, but at some
> >later date I expect a problem that parameter passing *won't* solve, an=
d
> >in fact will be made much more difficult to solve because of parameter
> >passing.
>=20
> 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.

I would still disagree, at least for SQLObject.  Or rather, I would say
that separate parameters is a decision that in most places is delayed
quite a ways (until your connection is just about to send the SQL), so
it's not a big deal either way.

> >Perhaps -- I don't even know, because I'm not using parameters.
>=20
> 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).
>=20
> >AFAIK, prepared statements and whatnot
> >only offer advantages in a certain context, a context which will not
> >exist in SQLObject.
>=20
> 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.

Well, prepared statements can reduce SQL parse time, but since there
isn't any interface to prepare a statement well ahead of using that
statement, it's not so important to us.

> >Can you change the ?'s reasonably efficiently, while protecting agains=
t
> >literals that contain ?'s ?
>=20
> 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. Dictiona=
ry
> 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 wha=
t
> I should be doing now.)
>=20
> quote_separation =3D re.compile(r"(.*?)('.*?')", re.DOTALL)
> def parseQmark(sql):
>      '''parseQmark(sql_with_?) -> sql_with_%s
>=20
>      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 =3D sql.replace('%', '%%')
>      # Extra "''" needed to make my re happy if sql doesn't end with '
>      notQuoted_quoted =3D quote_separation.findall(sql+"''")
>      replaced =3D [nq.replace('?', '%s')+q for (nq, q) in notQuoted_quo=
ted]
>      return "".join(replaced)[:-2] # remove extra ''

Well, for instance, MySQL allows strings like 'that\'s it', which would
break this.  But it also allows 'that''s it', so maybe people will just
have to use the one.

I don't know how some databases treat things like " -- is "user?" a
valid column name in Postgres?  I don't know... doing ad hoc SQL parsing
seems like trouble to me.  %s avoids that, since it doesn't do parsing.=20
The other idea, like

cursor.execute("INSERT INTO foo (bar, baz) VALUES (", foo, ", ", baz")")

Would also work without doing parsing, even if it is a bit annoying.=20
Personally I don't mind %s at all -- so long as you aren't hardcoding
string literals into your queries, having to quote % isn't that big a
deal.