Re: [SQLObject] ADO DB-API support?

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

On Mon, 2003-05-26 at 05:15, Magnus Lyck=E5 wrote:
> But Date is really just an example... The thing is that if I use
> something like "INSERT INTO X (a,b,c) VALUES (?,?,?)" or
> "SELECT * FROM X WHERE A < ?", and pass in the literals in a params
> tuple, the problem goes away, regadless of what kind of data this
> might be. (Of course, I still have to provide data in the right
> format, but I don't have to solve quoting or escaping.)
>=20
> After all, the point with the second parameter to cursor.execute()
> is there to promote this kind of coding. As far as I understand, neithe=
r
> MySQL, PostGreSQL or SQLite supports Dynamic SQL or the SQL CLI properl=
y,
> so there %s is used, and the parameters are passed into the SQL string =
in
> the interface, but for interfaces to databases with proper Dynamic SQL
> support, both the SQL string with question marks and the parameter tupl=
e
> are just passed to a lower layer, and handled by the database vendor's
> driver. I think this is the right way to do it.
>=20
> There are certinaly lots of other problems in various SQL dialects that
> we can't solve with this fix. Contatenation operators and join syntax e=
tc
> makes it difficult to always get SQL to work cross platform, but for
> handling of quoting and escaping of literals used in SQL queries, there
> *is* a right way to do it, so trying to solve it again in the Python la=
yer
> seems...well, non-optimal.

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

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

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.

> As far as I understand, it's only the code for INSERT and UPDATE that
> I *need* to fix. For INSERT it seems that the easiest way is to fix it
> in _queryInsertID and not even call _insertSQL. Just call execute with
> the values as a separate parameter, and pass in ",".join(['?']*len(valu=
es))
> in the second parenthesis.
>=20
> For updates I need to override _SO_update so that I just insert
> "SET A =3D ?, B =3D ?" and pass the parameters to self.query. This in
> turn means that I need to add an optional param parameter to query
> and _query, but it seems like a reasonable thing to do.
>=20
> Then I still don't have perfect support for selects, but I can do
> without that for now I think.

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

> >Well, for select in particular, maybe params would be fine.  However,
> >SQLObject has maintained database independence fairly well, and the
> >annoying paramstyle issue would make code that uses params less
> >portable.
>=20
> So far, you ony seem to support backends that don't support parameter
> passing though.

Perhaps -- I don't even know, because I'm not using parameters.  That
ignorance makes me happy ;)  I don't see a big advantage to using
parameters besides quoting -- AFAIK, prepared statements and whatnot
only offer advantages in a certain context, a context which will not
exist in SQLObject. =20

> >   Though I'm certainly okay with:
> >
> >   Person.select('birthdate < %s', params=3D(Date(1999, 4, 12),))
> >
> >Where any non-param %'s have to be quoted (like %%), then the database
> >connection changes %s to ? or whatever the paramstyle is.  Should be
> >easy enough to implement.
>=20
> As you might have seen in the db-sig mailing list, I recently wrote
> code that goes the other way: ? to %s. This has the advantage that
> you can write your SQL queries just the way a proper SQL book describes
> them, i.e. you can write "SELECT * FROM X WHERE A =3D ? AND B LIKE 'X%'=
"
> without having to think about doubling % or using any other non-SQL
> construct in the SQL code. It's also fairly simple to convert qmark
> style SQL to named, numbered or even Sybase @-style quoting if that
> is needed.

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

> >I don't use params, and don't plan to, for other SQL construction.
> >Mostly it's because I like having complete control over the SQL, which=
 I
> >do when I do the string substitution myself.
>=20
> I think you are doing more work than you have to that way. As
> I said, for MySQL, PostgreSQL and SQLite it's no big difference,
> since they do that in a simple way in the interface code anyway,
> but for most commercial it might well be. At least for interfaces
> like ADO and ODBC where you use one interface but can have any of
> many backends behind that interface.
>=20
> This also means that you place a burden on the application developer
> to solve this right for filtering in the select statement.
>=20
> I do realize that changing this now is quite some work though...

It's not so much the work of changing this -- it wouldn't be all that
difficult, as I've tried to keep SQL generation isolated -- but I'm not
convinced this is the right thing to do.

  Ian