Menu
▾
▴
Re: [SQLObject] ADO DB-API support?
|
From: Magnus <ma...@th...> - 2003-05-29 16:24:28
|
At 22:31 2003-05-28 -0500, Ian Bicking wrote:
>I think __sqltype__ seems a little awkward. You have to agree on the
>types (and type names) that the backend accepts, and that gets into a
>whole discussion that seems rather endless ;)
Most of the time, you only need to tell the DB-API whether it might
need to escape/quote the data or not. In other words, is this a number
or a string? If the class above the DB-API is to handle escape/quote,
it would need to supply "that's" from __sqlrepr__ when it works with i.e.
mxODBC or cx_Oracle, and "'that''s'" when it works with MySQLdb or a
PostgreSQL driver. If it can just tell the DB-API whether it's a string
or a number, it can always supply "that's", or "5" or "314159E-5", and
the driver can handle it just like it does for ints, floats and strings
etc today. This covers the overwhelming majority of used literals.
Actually, it's not really the data type in the database that matters,
but what kind of *literal* we are providing. Maybe the method should
be called __sqlliteral__ or __sql_literal__ rather than __sqltype__
(and the other method __sql_repr__ if we use __sql_literal__)?
The SQL/92 standard supports four kinds of literals:
character string ('that''s')
bit string (B'01010101' or X'f5a1')
exact numeric (4 or +365.2425)
approximate numeric (4E3 or -.15E-15)
In addition to returning any of these, I think __sql_literal__ should
be able to return 'raw' to inticate that the driver should just pass
the value in as it is. It's then up to the application programmer to
provide the right data. If she has to support many tricky backends and
don't have very high performance requirements, she could put logic into
the __sql_repr__ method that will check what backend it is and act based
on that.
Obviously, MS Access is in clear violation with the SQL spec here, as in
a number of other cases :( so *this* would not solve the #1900-01-01#
problem.
I'd like to suggest the following: Each db driver has to supply a
CONSTANT for each kind of literal it supports. At least, it should
support the following constants:
.literal.CHAR Action for such values is typically
"'%s'" % x.replace("'", "''")
.literal.BBIT (or BITS?) Action is typically "B'%s'" % x
.literal.XBIT (or HEX?) Action is typically "X'%s'" % x
.literal.EXACT Action is typically to use as is
.literal.APPROX Action is typically to use as is
.literal.RAW Action is always to us as is.
It could optionally support other literal types such as
.literal.DATE which could provide custom handling of date
strings for some peculiar backend,
.literal.MONEY if this is ever needed...
.literal.BIN to let people pass in binary strings and not need to
convert them to B'0101' or X'ffff'. This could then
pass the binary data as it is if the backend supports
that, or do "X'%s'" % x.encode('hex') as a convenience
for the application programmer. I don't know if there is
a desire to be able to provide a stream instead of a string
for binary data? How do people handle BLOBs today? But
maybe I'm confused here. Backends might not handle bit
fields and BLOBs etc in the same way.
These literals can have any value that evaluates to true. (I guess a lazy
dbi implementer could even use the same value for EXACT, APPROX and RAW.)
If someone comes up with a new form of literal, they bring it up with
the db.sig, and if it's reasonable, it's added to the optional list, so
that we can use a common vocabulary as far as possible.
According to the standard, I think a date should be passed in as a
character literal, and money is (I think) typically passed in as an
exact numeric. But we might imagine some date class that first of
prefers to send .literal.DATE, but if that literal is not supported
by the db-driver, it sends .literal.CHAR. This means that a #date#
aware backend could handle the quoting right.
Something like this...
# mydate.py
def init_module(dbdriver):
global sql_literal
try: sql_literal = dbdriver.literal.DATE
except: sql_literal = dbdriver.literal.CHAR
class MyDate:
def __init__(self, ...):
assert sql_literal
def __sql_literal__(self):
return sql_literal
>>> import sqlite
>>> import mydate
>>> mydate.init_module(sqlite)
>>> d = MyDate(...)
>>> d.__sql_literal__() == sqlite.literal.CHAR
... 1
>>> import my_access_driver
>>> import mydate
>>> mydate.init_module(my_access_driver)
>>> d = MyDate(...)
>>> d.__sql_literal__() == my_access_driver.literal.DATE
... 1
> > And then "dbdriver.quote = quote" or what?
>
>Yes, I forgot to finish it with that.
What if dbdriver is written in c? (I guess you can always have
a thin wrapper.)
>Date is a contrived example, since most drivers handle dates natively.
I know...
>Yes, I definitely agree. If you really want repr, you'd write something
>like:
>
>def quote(val):
> try:
> return old_quote(val)
> except TypeError:
> return repr(val)
>dbdriver.quote = quote
>
>But I definitely repr is bad by default.
Right. Then it's the explicit choice (and responsibility) of the
application programmer.
>The quote function is potentially database specific. After thinking
>about it, this function should have the chance to get at the raw value
>before __sqlrepr__ is called.
I don't see why. If you want class X to behave differently in some
situation, subclass it and put the different behaviour in __sqlrepr__
of the subclass. These functions seem redundant to me. One is more
OO, the other more procedural.
>Hmmm... just looking at the psycopg type stuff. It handles both SQL
>generation and typecasting what comes from the database at the same
>time, more or less. Creation objects that come from the database should
>also be handled, but I don't see any particular need to do those at
>once.
I guess I should look at Federico's code too at some point...
>Actually, maybe psycopg doesn't do this, but rather just calls
>str()/__str__ (where we use __sqlrepr__) -- certainly a special method
>is better, though.
Yes. As long as you only use it for a distinct number of types/classes
that you are in charge of, but if the application programmer is to be
able to add classes, __str__ might be reserved for other duties in his
class.
>Parts of it really need to be in C, I think, so if it's all in C then
>probably everyone could be made happy. Who has an all-Python driver?
>Gadfly I imagine, maybe COM-based drivers...
Adodbapi is all Python. It uses win32all to talk COM with Windows.
You could also use ctypes in Unix for instance.
>Anyway, people talked serious about including Gadfly in the standard
>distribution, so there's interest in supporting database stuff. The
>only weird part about the module is that it would be useless on its own.
If Gadfly was included and used this module, it would be better. I
would rather see sqlite in the standard library though. I don't see
why we can have bsddb and not sqlite. SQLite is much more like a typical
SQL database than Gadfly is.
--
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
|
