Re: [SQLObject] ADO DB-API support?

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

Some parts of my reasoning below is more relevant to generic DB-API
than to SQLObject, I hope this is ok. (Tell me if I get boring.)

At 15:34 2003-05-27 -0500, Ian Bicking wrote:
>*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.

I doubt it :). The sqlite driver and the related PostgreSQL driver both
look for an attribute called '_quote', which I suppose they use for their
internal wrappers for SQL types that don't quite match SQL. (MONEY etc)
That's where I got the idea, and someone on the db-sig list suggested
that adding type information was needed as well. Perhaps I can at least
convince Gerard H=E4ring to rename _quote to _dbapi_string in his drivers?

Hopefully *someone* will like the idea. Maybe with a few more champions...=
 :)

A PEP might be a good idea. Another idea I had was to write some short
generic DB-API tutorial that clarifies all the required features of
the current DB-API spec. Don't have time for either right now though.
Both bookkeeping and paying customers need priority handling... :)

>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.

My though was to allow .select("name=3D?", ("Guido's",)) as an alternative
to .select("name=3D'Guido''s'")

In cases like this you relieve a certain burden from the application
coder. That might be good, since you can (I suppose) write SQLObject
code without knowing a lot of SQL. I think it's easier to learn that
all literals are replaced with a '?' and that the actual values are
passed in a tuple, than to learn all about quoting and escaping.

It would also solve the MS Access date problem...

>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.

sql.replace(r"\'", "''") before the re would handle that otherwise.
As a coder I think it's best to follow SQL given a choice, so I'd
suggest using '' rather than \'.

>I don't know how some databases treat things like " -- is "user?" a
>valid column name in Postgres?

Anyone using a questionmark in a table or column name in SQL should
be dragged to the square and shot in dawn, but I think the re can be
fixed to handle this as well.

In the worst case I can just write a small C (or Pyrex?) routine to
do this without re. After all, there isn't a lot of SQLism in this,
is there. A ? should be replaced with ...something... unless it's
in a quote. A quote started with " should end with " unless the
preceeding character was \, and a quote started with ' should end with
' unless the preceeding character was \. 'That''s' can be seen as two
quoted areas from a ? replacement perspective.

Is there a flaw in that strategy?

>I don't know... doing ad hoc SQL parsing
>seems like trouble to me.  %s avoids that, since it doesn't do parsing.

On the other hand, it brings out other problems. I won't repeat what I
wrote on the db-sig.

>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.

I would hate that. There *is* a standard here, which is widely used
and I think it's reasonable to implement it both from performance,
maintenance and stability point of view. Why not use that?

Why not deprecate the %-operator in Python while we're at it. It
seems like a similar approach to me.

>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.

I think it's better to try to follow an established standard rather
than to invent a new one. My detailed knowledge is with SQL/92.

Both Dynamic SQL and the SQL CLI mandates ? for parameter passing.
The SQL standard does not enforce that an implementation provides
Dynamic or CLI. It's ok to only provide embedded SQL or the module
language, both of which use :name, but both of these standards allow
parameters to be passed in anywhere, as in:

"SELECT :col FROM :table WHERE :filter"

I.e. the :name syntax is according to the SQL standard not to be
quoted or escaped by the backend, but the ? parameters are, and
that's the way the Python DB-API standard requires. So the only
way to be complient with both SQL/92 and the Python DB-API is to
allow use of paramstyle qmark!

Obviously, we can use "SELECT %s FROM %s WHERE %s" and pass in
stuff with pythons %-operator to make our SQL even more dynamic,
but this is plain and simple Python string handling, and I think
it should be entirely explicit to the coder.


--
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=20