sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Enums, lookup tables, and python classes...

[Edmund L. <el...@in...>]

Bud P.Bruegger wrote:

> I've used the above pattern a lot and very successfully so.  But I
> believe also that it is a pattern that is limited strictly to the
> relational domain--so I've been pondering how to deal with this in a
> more object-oriented way.  And here is what I've come up with so far:
> 
> I believe the "lookup tables" or whatever you call them should behave
> as Python Classes to the Application programmer.  They may of course
> still be stored in the dbms (class attributes instead of instance
> attributes...).  If this sounds interesting, I'll keep you posted on
> my progress in this..

But this is how they are behaving right now, and it seems awkward and is 
inefficient.

Contents of the lookup table should be accessible as objects for 
updates, etc. But when it is being used to constrain the values of a 
column in another table, then that column should really be a first class 
attribute.

What I think is more natural is to have two kinds of foreign key 
constraints along the lines of:

class LookUpTable(SQLObject):
     _columns = [
         StringCol("stuff", default=None),
         StringCol("name", unique=1)]

class ConstrainedTable(SQLObject):
     _columns = [
         StringCol("moreStuff", default=None),
         StringCol("constrainedStuff",
             foreignKeyAttribute="LookUpTable.name")]

class NormalTable(SQLObject):
     _columns = [
         StringCol("otherStuff", default=None),
         StringCol("objectStuff", foreignKey="LookUpTable.name")

In use:

# Add entries to LookUpTable, treating it as an object
LookUpTable.new(name="Cat")
LookUpTable.new(name="Dog")

# Add entries to NormalTable, as per current SQLObject behavior
obj = NormalTable.new(objectStuff=<instance of LookUpTable>)
print obj.objectStuff
<instance of LookUpTable>

# Add entries to ConstrainedTable, where we have specified that
# the lookup table should not be "objectified" since it is really
# just a constraint table.
obj = ConstrainedTable.new(constrainedStuff="Cat")
print obj.constraintedStuff
"Cat"

...Edmund.

Re: [SQLObject] PickleCol?

[Bud P. B. <bu...@si...>]

On Fri, 30 May 2003 10:56:59 +0200
"Bud P. Bruegger" <bu...@si...> wrote:

> So what do you think of a PickleCol?

As a followup, this is how I implemented PickleCol in my own code
(where it becomes a attribute that sits above columns):

class PickleAttr(Attr):
    "provides a shortcut constructor for most common use case of Attr"

    def __init__(self, name, dbName=None, classMap=None):
        dbN = dbName or name
        dbCol = DbCol(dbN, 'blob')
	toSql = lambda x: [pickle.dumps(x, bin)]
        fromSql = lambda x: pickle.loads(x[0])
        Attr.__init__(self, name, dbCol, \
                     fromSql=fromSql, \
	             toSql = toSql, \
	             classMap=classMap)

So quite simple and straight forward....
BTW, the PsycopgDbDriver overrides the generic DbDrivers method
nonStandardTypes such that it can translate 'blob' to 'bytea'...

[SQLObject] PickleCol?

[Bud P. B. <bu...@si...>]

At the risk that this gets overly ZOPE-DB-ish for you guys...

In my current project, there is a sub-aggregate of objects that needs
to be stored in the dbms, BUT, that I don't plan to use in queries and
that I'm happy with retrieving all at once together with the object
that contains it.  

So to simplify the dbms schema and to drastically cut down on remote
dbms access round trips, I thought of storing it as a pickle...

So what do you think of a PickleCol?

--b


/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

[SQLObject] Enums, lookup tables, and python classes...

[Bud P. B. <bu...@si...>]

On Fri, 30 May 2003 00:33:00 -0400
Edmund Lian <el...@in...> wrote:

> 7. I don't use enums at all. For table driven applications, I use 
> primary keys coupled with foreign key constraints. This allows you to 
> change the allowed values of a column by varying the contents of another 
> table. This is one argument against using synthetic primary keys. But 
> using synthetic primary keys is still OK so long as unique columns can 
> be specified, and the foreign key constraint can take the name of a column.

I've used the above pattern a lot and very successfully so.  But I
believe also that it is a pattern that is limited strictly to the
relational domain--so I've been pondering how to deal with this in a
more object-oriented way.  And here is what I've come up with so far:

I believe the "lookup tables" or whatever you call them should behave
as Python Classes to the Application programmer.  They may of course
still be stored in the dbms (class attributes instead of instance
attributes...).  If this sounds interesting, I'll keep you posted on
my progress in this..

[SQLObject] Impressions so far

[Edmund L. <el...@in...>]

Ian,

I've been banging away at SQLObject all day, trying to jam a small 
portion of my data model into it, or else rewrite it so it would fit. 
Here are my thoughts so far, each worth $0.02, and in no particular order.

I have appended + and minus signs to indicate strength of feeling. More 
of either = stronger positive or negative reation. +- = no reaction.

1. Being able to autogenerate schema is nice, and this is how I've been 
using testing SQLObject (SO). ++

2. Support for multiple DBs, particularly PostgreSQL, is good. +++

3. There needs to be an object.delete() method that does not require you 
to supply the object ID. When not supplied, it deletes the current 
object. There is a bug in this right now. When you do 
object.destroySelf(), the corresponding row is deleted from the DB, but 
the instance data is not invalidated. +-

4. Being able to supply a callable object for the default value of a 
column is good. +++

5. Being able to override table name is important because when you have 
lots of tables floating around, you often want to control their names so 
that you group them together is a specific way when the table names are 
sorted. ++++

6. Need to have some way of specifying unique constraints for single and 
  groups of columns for auto schema generation. --- See (7) below.

7. I don't use enums at all. For table driven applications, I use 
primary keys coupled with foreign key constraints. This allows you to 
change the allowed values of a column by varying the contents of another 
table. This is one argument against using synthetic primary keys. But 
using synthetic primary keys is still OK so long as unique columns can 
be specified, and the foreign key constraint can take the name of a column.

8. There is no way to specify (at least not that I can see), outer 
joins. These are important. ---

9. Method of specifying joins seems a tad clumsy. Object Relational 
Membrane's method is cleaner.

10. There needs to be a way to specify a not null constraint.

11. As an extension of (7), it is clumsy to have to pass in an object 
(A) when creating another object (B) that has a foreign key constraint 
refering to a column in (A). Here's an example that assumes I can add 
unique (6), not null constraints (10), and name a column for a foreign 
key (7) to a column:

class Gender(SQLObject):
     _columns = [
         StringCol("name", unique=1, nullable=0)]

class Person(SQLObject):
     _columns = [
         StringCol("name"),
         StringCol("gender", nullable=0, foreignKey="Gender.name")]

With this schema, one should be able to do:

Gender.new(name="Male") # Done once
Gender.new(name="Female") # Done once
man = Person(name="Geoff", gender="Male")  # somewhere else in the code

If one has to pass in an instance of Gender, then it becomes very clumsy 
and slow, because you have to instantiate the object you want first. i.e.:

male = Gender.select(Gender.q.name=="Male")
man = Person(name="Geoff", gender=male)

A big yuk. Makes table driven applications awful.  ----

12. Similarly, if a class/table is just being used for integrity checks, 
returning an object rather than the referenced column within the row is 
clumsy. Assuming the schema in (11), one would like:

target = Person.select(Person.q.name=="Geoff")
print target.gender
"Male"

When an object is returned, one would have to know the name of the 
column and then dereference it:

target = Person.select(Person.q.name=="Geoff")
print target.gender.name
"Male"

13. Being able to add and drop columns at runtime is great. This removes 
the need to regenerate the tables in a live database. +++

14. Modeling (which seems rather hard to get into) seems to have very 
expressive ways to get complex joins. I haven't tried it at all, but 
have browsed the docs. Might be worth stealing some ideas from them. But 
gee, theyReallyLikeLongMethodNames! Or maybe I'm sensitive to them due 
to RSI...

...Edmund.

[SQLObject] Re: Bug, was: one to one relationship via foreign key--bug or dumbness?

[Edmund L. <el...@in...>]

Ian Bicking wrote:

> Okay, my fix was just all wrong, I mixed up the argument order.  CVS
> fixed this time, maybe.

Sorry, not really. My tests:

 >>> from test import *
 >>> cc = ComponentCategory(1)
 >>> comp = Component.new(name="Test Comp5")
 >>> comp
<Component 5 name='Test Comp5' categoryId=None sequenceNum=None>
 >>> comp.category
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "<string>", line 1, in <lambda>
TypeError: _SO_foreignKey() takes exactly 3 arguments (2 given)


 >>> comp = Component.new(name="Test Comp6", category=cc)
 >>> comp
<Component 6 name='Test Comp6' categoryId='1' sequenceNum=None>
 >>> comp.category
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "<string>", line 1, in <lambda>
TypeError: _SO_foreignKey() takes exactly 3 arguments (2 given)


...Edmund.

[SQLObject] Re: Bug, was: one to one relationship via foreign key--bug or dumbness?

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

On Thu, 2003-05-29 at 22:18, Edmund Lian wrote:
> Ian Bicking wrote:
> 
> > It expects an id or a ComponentCategory object -- since you didn't pass
> > an object, it tried to treat it like an integer id.
> > 
> > Anyway, try it again from CVS and see if it works for you now.
> 
> Better, but still no joy... with the same data model, I get:

Okay, my fix was just all wrong, I mixed up the argument order.  CVS
fixed this time, maybe.

I really have to look at my unit tests again, because they really should
be catching these things for me.  I think I'm missing tests for all the
easy things...

[SQLObject] Re: Bug, was: one to one relationship via foreign key--bug or dumbness?

[Edmund L. <el...@in...>]

Ian Bicking wrote:

> It expects an id or a ComponentCategory object -- since you didn't pass
> an object, it tried to treat it like an integer id.
> 
> Anyway, try it again from CVS and see if it works for you now.

Better, but still no joy... with the same data model, I get:

Create category:

 >>> from test import *
 >>> compCat = ComponentCategory.new(name="Test Cat")
 >>> compCat
<ComponentCategory 1 name='Test Cat' description=None sequenceNum=None>


Create component with default category of None:

 >>> comp = Component.new(name="Test Comp")
 >>> comp
<Component 1 name='Test Comp' categoryId=None sequenceNum=None>
 >>> comp.category
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "<string>", line 1, in <lambda>
AttributeError: 'Component' object has no attribute 'ComponentCategory'


Create component with instance of ComponentCategory as argument for 
category:

 >>> comp = Component.new(name="Test Comp2", category=compCat)
 >>> comp
<Component 2 name='Test Comp2' categoryId='1' sequenceNum=None>
 >>> comp.category
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "<string>", line 1, in <lambda>
AttributeError: 'Component' object has no attribute 'ComponentCategory'


...Edmund.

[SQLObject] Re: Bug, was: one to one relationship via foreign key--bug or dumbness?

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

On Thu, 2003-05-29 at 21:07, Edmund Lian wrote:
> Ian Bicking wrote:
> 
> > It should be an attribute category, which is categoryId with the "Id"
> > removed.
> 
> Hmmm... I think there's a bug then. Here's what I'm seeing...
> 
> My SQLObject module is:
> 
> from SQLObject import *
> 
> __connection__ = PostgresConnection(host="localhost", db="sqlobj",
>                   user="sqlobj", passwd="sqlobj")
> 
> class ComponentCategory(SQLObject):
>      _table = "st_component_category"
>      _columns = [
>          StringCol("name", length=100),
>          StringCol("description", default=None),
>          IntCol("sequenceNum", default=None)]
> 
> class Component(SQLObject):
>      _table = "st_component"
>      _columns = [
>          StringCol("name"),
>          StringCol("categoryId", foreignKey="ComponentCategory",
>              default=None),
>          IntCol("sequenceNum", default=None)]
> 
> ComponentCategory.createTable(ifNotExists=1)
> Component.createTable(ifNotExists=1)
> 
> 
> My test run and output is:
> 
> Python 2.2.2 (#1, Mar 21 2003, 23:01:54)
> [GCC 3.2.3 20030316 (Debian prerelease)] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
>  >>> from test import *
>  >>> cc = ComponentCategory.new(name="Test Cat")
>  >>> cc
> <ComponentCategory 1 name='Test Cat' description=None sequenceNum=None>
>  >>> comp = Component.new(name='Test Comp')
>  >>> comp
> <Component 1 name='Test Comp' categoryId=None sequenceNum=None>
>  >>> comp.__dict__
> {'_SO_val_categoryId': None, '_SO_writeLock': <thread.lock object at 
> 0x821c728>, '_SO_val_sequenceNum': None, '_SO_val_name': 'Test Comp', 
> 'id': 1}
>  >>> comp.category
> Traceback (most recent call last):
>    File "<stdin>", line 1, in ?
>    File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
> 915, in __repr__
>      return '<%s %i %s>' \
> AttributeError: 'ComponentCategory' object has no attribute 'id'

That's a weird error.  It looks like it's instantiating a
ComponentCategory object, but hitting an excepting when the object is
printed.  

Hmm... looks like I wasn't handling None there properly -- instead of
returning None it was returning a broken ComponentCategory object.  Try
CVS again.

Note that the actual ComponentCategory instance will never be in the
__dict__ of the category object -- only the id is stored.

> Passing in a string gives:
> 
>  >>> comp = Component.new(name='Test Comp2', category='Test Cat')
> Traceback (most recent call last):
>    File "<stdin>", line 1, in ?
>    File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
> 747, in new
>      kw[column.name] = getID(kw[column.foreignName])
>    File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
> 1059, in getID
>      return int(obj)
> ValueError: invalid literal for int(): Test Cat

It expects an id or a ComponentCategory object -- since you didn't pass
an object, it tried to treat it like an integer id.

Anyway, try it again from CVS and see if it works for you now.

  Ian

[SQLObject] Bug, was: one to one relationship via foreign key--bug or dumbness?

[Edmund L. <el...@in...>]

Ian Bicking wrote:

> It should be an attribute category, which is categoryId with the "Id"
> removed.

Hmmm... I think there's a bug then. Here's what I'm seeing...

My SQLObject module is:

from SQLObject import *

__connection__ = PostgresConnection(host="localhost", db="sqlobj",
                  user="sqlobj", passwd="sqlobj")

class ComponentCategory(SQLObject):
     _table = "st_component_category"
     _columns = [
         StringCol("name", length=100),
         StringCol("description", default=None),
         IntCol("sequenceNum", default=None)]

class Component(SQLObject):
     _table = "st_component"
     _columns = [
         StringCol("name"),
         StringCol("categoryId", foreignKey="ComponentCategory",
             default=None),
         IntCol("sequenceNum", default=None)]

ComponentCategory.createTable(ifNotExists=1)
Component.createTable(ifNotExists=1)


My test run and output is:

Python 2.2.2 (#1, Mar 21 2003, 23:01:54)
[GCC 3.2.3 20030316 (Debian prerelease)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
 >>> from test import *
 >>> cc = ComponentCategory.new(name="Test Cat")
 >>> cc
<ComponentCategory 1 name='Test Cat' description=None sequenceNum=None>
 >>> comp = Component.new(name='Test Comp')
 >>> comp
<Component 1 name='Test Comp' categoryId=None sequenceNum=None>
 >>> comp.__dict__
{'_SO_val_categoryId': None, '_SO_writeLock': <thread.lock object at 
0x821c728>, '_SO_val_sequenceNum': None, '_SO_val_name': 'Test Comp', 
'id': 1}
 >>> comp.category
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
915, in __repr__
     return '<%s %i %s>' \
AttributeError: 'ComponentCategory' object has no attribute 'id'


Note that there is no category attribute. Unless I'm misreading the 
docs, I would expect that instantiating a new Component but not 
specifying a category or category ID should result in an instance with 
an attribute "category" with value None.

Also, I would actually expect to instantiate Component and specify a 
category by name (or more inconveniently, pass in an instance of it I 
guess) rather than ID.

Passing in a string gives:

 >>> comp = Component.new(name='Test Comp2', category='Test Cat')
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
747, in new
     kw[column.name] = getID(kw[column.foreignName])
   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
1059, in getID
     return int(obj)
ValueError: invalid literal for int(): Test Cat


Pass in an instance of ComponentCategory gives:

 >>> cc
<ComponentCategory 1 name='Test Cat' description=None sequenceNum=None>
 >>> comp = Component.new(name='Test comp 3', category=cc)
 >>> comp
<Component 3 name='Test comp 3' categoryId='1' sequenceNum=None>
 >>> comp.__dict__
{'_SO_val_categoryId': '1', '_SO_writeLock': <thread.lock object at 
0x82db400>, '_SO_val_sequenceNum': None, '_SO_val_name': 'Test comp 3', 
'id': 3}
 >>> comp.category
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
918, in __repr__
     ' '.join(['%s=%s' % (name, repr(value)) for name, value in 
self._reprItems()]))
TypeError: an integer is required


Here is an example of what users probably don't expect to do--deal with 
object IDs:

 >>> comp = Component.new(name='Test comp 3', categoryId=1)
 >>> comp
<Component 2 name='Test comp 3' categoryId='1' sequenceNum=None>


Notice that the attribute "category" is still missing:

 >>> comp.__dict__
{'_SO_val_categoryId': '1', '_SO_writeLock': <thread.lock object at 
0x8210ee8>, '_SO_val_sequenceNum': None, '_SO_val_name': 'Test comp 3', 
'id': 2}


...Edmund.

Re: [SQLObject] one to one relationship via foreign key--bug or dumbness?

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

On Thu, 2003-05-29 at 19:35, Edmund Lian wrote:
> class Component(SQLObject):
>      _columns = [
>          StringCol("name"),
>          StringCol("categoryId", foreignKey="ComponentCategory",
> 	    default=None),
>          IntCol("sequenceNum", default=None)]
> 
> To behave as follows:
> Instantiating:
> c = Component.new(name="test")
> 
> And c should have an attribute "componentCategory" that returns either
> None, or an instance of class ComponentCategory.
> 
> In fact, c does not have an attribute called "componentCategory". Am I
> not understanding something?

It should be an attribute category, which is categoryId with the "Id"
removed.

  Ian

[SQLObject] one to one relationship via foreign key--bug or dumbness?

[Edmund L. <el...@in...>]

I'm fiddling with SQLObject from CVS now, and I don't seem to be
understanding the documentation about foreignKey. The manual says:

"""
foreignKey:

     You can give a class name (as a string) for foreignKey, which means
that the column references another class (which is another row in
another table). The column name should end with ID, and the database
column is expected to end with _id. This will create another method in
your class.

     For instance, if the personID column points to the Person class,
then in addition to a personID attribute your class will have a person
attribute which will return the corresponding Person instance.
"""


Base on this, I would expect:

class ComponentCategory(SQLObject):
     _columns = [
         StringCol("name", length=100),
         StringCol("description", default=None),
         IntCol("sequenceNum", default=None)]

class Component(SQLObject):
     _columns = [
         StringCol("name"),
         StringCol("categoryId", foreignKey="ComponentCategory",
	    default=None),
         IntCol("sequenceNum", default=None)]

To behave as follows:
Instantiating:
c = Component.new(name="test")

And c should have an attribute "componentCategory" that returns either
None, or an instance of class ComponentCategory.

In fact, c does not have an attribute called "componentCategory". Am I
not understanding something?

...Edmund.

[SQLObject] LORRAINE Is hot kfdku

[Evan R. <1jw...@br...>]

<p>Told ya I would do it!!
<a href=3D"http://atmosphere@80.235.78.213"></p>
<p><img src=3D"http://ana...@ww.../byot/tn4790/sierra.jpg=
?apprenticeship"> </a></p>
<br>
<br>
<br>I got more.. if you are daring :)
<br>
<br>
<br>
<a href=3D"http://bed@80.235.78.213/r.php">beam me off scotty</a>=
</font></td>


bhatzzt ntqusevnlfdjs d mg

Re: [SQLObject] ADO DB-API support?

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

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 

Re: [SQLObject] ADO DB-API support?

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

On Wed, 2003-05-28 at 15:20, Magnus Lyck=E5 wrote:
> At 12:39 2003-05-28 -0500, Ian Bicking wrote:
> >I was thinking of changing the magic method I use to __sqlrepr__, whic=
h
> >seems like a good name to me.  Just having a single convention would b=
e
> >a start.
>=20
> Ok with me. It would be good if there was at least a name
> given as an option in the DB-API spec. This function obviously
> exist in a number of implementations, with different names.
>=20
> >Right now I think quoting would probably best be done like:
> >
> >* Integers, floats, strings are all automatically quoted.  Maybe
> >mxDateTime and datetime objects too.  No hooks for these -- the
> >underlying C driver may want to handle these data structures on its ow=
n
> >anyway.
>=20
> Right.
>=20
> >* Anything with a __sqlrepr__ method has that called, with no
> >arguments.  The result is expected to be fully quoted.
>=20
> This is what I thought first, but there were some oppsition to
> the idea on the db-sig mailing list. Let's return to the date
> and Access. Let's say that you have your own date class, and you
> deliver the string "'1984-06-04'". This won't work on Access.
> If __sqlrepr__() had returned '1984-06-04' and another method
> __sqltype__ had returned 'DATE', then the driver could have
> known that it would do "'%s'" % __sqlrepr__() on sane platforms
> and "#%s#" % __sqlrepr__() on that warped MS platform.

Yes, I started thinking about this when I was partway through writing
down my thoughts.  Generally the place I am using this kind of
functionality is with various explicit literals, and SQLBuilder in
particular (e.g. SQLBuilder.func.NOW()).  In that case it wouldn't cause
too much of a problem that the constructed SQL was not
backend-specific.  I know I've this hook elsewhere, but I'm at a loss to
remember when.

If it's not considered the end-all of SQL construction and backend
abstraction, then I think it's still a useful hook.

> If the interface doesn't build the full SQL statement in the
> interface, but actually sends the parameters separately to
> the backend, you might end up with things like
>=20
> INSERT INTO T1 (C1) VALUES ('''that''''s''')
>=20
> That would be a bit sad... :(
>=20
> But still, it's a start. It's certainly reasonable that the
> result from __sqlrepr__ is passed in as is if there is no
> __sqltype__ attribute in the object. I think that __sqltype__
> is also a good idea though.

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 ;)

But maybe it should be done in the default quote function, then it can
be overridden for weird databases.

> >* If both those fail, then there's a function which has one last chanc=
e
> >to return a SQL representation on the object.  This would be for quoti=
ng
> >types that you couldn't add a __sqlrepr__ method to -- for instance, i=
f
> >mxDateTime objects weren't automatically handled, you might handle the=
m
> >here.  Usage something like:
> >
> >import dbdriver
> >old_quote =3D dbdriver.quote
> >def quote(val):
> >     if type(val) is DateTimeType:
> >         return val.strftime("'%c'")
> >     else:
> >         return old_quote(val)
>=20
> And then "dbdriver.quote =3D quote" or what?=20

Yes, I forgot to finish it with that.

> Do you register
> this? Why not just supply something like date.Format('%Y-%m-%d')
> instead of your plain date?

Date is a contrived example, since most drivers handle dates natively.=20
Maybe an arbitrary precision number would be a better example.

> But finally: "In case of doubt, refuse the temptation to guess."
> At least some drivers fall back on repr() in an else-statement.
> I only want a "raise TypeError" in the default case.

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 =3D quote

But I definitely repr is bad by default.

> >Maybe there's a better way to phrase this hook, but this might be
> >sufficient.  The last quoting technique would probably be the only way
> >to add your own quoting that was database-specific (as would be
> >necessary with Access and mxDateTime objects).  So maybe __sqlrepr__
> >should actually just be part of the standard quote function.
>=20
> But drivers that can talk to Access, such as mxODBC and adodbapi have
> no problem with this, since they just pass the unquoted date string to
> the backend and let the ODBC driver handle that. Remember? That's where
> we started. Generally, it's always possible to wrap object in a small
> class that just implements __init__(self, value) and __sqlrepr__(self),
> so I don't quite see the need for this quote function.

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.

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.

Actually, maybe psycopg doesn't do this, but rather just calls
str()/__str__ (where we use __sqlrepr__) -- certainly a special method
is better, though.

> >Maybe create a DBAPI module again (didn't DBAPI 1 have a common module=
?)
> >-- put this function in there, the quote function, some common
> >exceptions for everyone to use.  It'd be DBAPI 3.0, or 2.1... anyway,
> >that's a lot of the biggest problems people seem to have.
>=20
> Agreed. I think some drivers might be implemented entirely in
> C, and others want to be completely Python to be as portable as
> possible, so it might not be trivial to write a module that all
> will agree with...
>=20
> Well, if it's written as a .pyd/.so and becomes a Python standard
> module it should work... :)

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?=20
Gadfly I imagine, maybe COM-based drivers...=20

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.

> With standardized and uniform SQL access and the new datetime class
> I only think we need a fixed point / money data type, and Python
> will be ready to become the COBOL of the 21st century! :)
> (It does sound awful, doesn't it. I guess that's why I like it.)

w00t!

Re: [SQLObject] two postgres questions

[Edmund L. <el...@in...>]

Ian Bicking wrote:

> I think views don't jive well with SQLObject -- they create an opaque
> structure.

Why not have views result in read-only objects? Viz:

class roObject(SQLObject):
     _view = 'my_view'
     ...
     ...


> But yes, normalization can lead to a whole bunch of tables. 
> I'm interested extending the power of joins so that some of these can be
> handled more easily, without having to have too many classes involved.

And this is where every ORM falls down right now. When you're starting 
from scratch, every ORM is more or less OK. But if you have to use it 
with a pre-existing schema, oop...


> But you don't have to use complicated queries anyway.  There's nothing
> intrinsically Wrong about doing the logic in Python.  List comprehension
> even makes set operations look nicer, and caching can provide
> performance improvements.

But this means writing an algorithm instead of describing what you want 
(declaratively, with SQL).

I haven't done any benchmarking, but given all the indexing that can go 
on in an RDBMS, it seems to me that the iterative Python version of:

select *
from table_one
where col_one in (
    select col_two
    from table_two
    where blah)

Is going to be slower since there isn't going to be the benefit of 
indexing. Caching won't make anything faster since the RDBMS is caching too.

OTOH, the overhead of the RDBMS parsing, planning, etc. might be quite 
high. Hmmm... anybody care to speculate on this? I must benchmark this 
one day...


> It could probably be argued that those complicated queries are a form of
> premature optimization.  Like most optimization, complex queries usually
> are based on the requirements of pieces of code that would otherwise be
> considered factored.  Those queries may be an important form of
> optimization, but I think that should still be accessible even if it
> won't be as natural as using normal object access.

I don't know if I agree with you about this... I'll have to think about 
it more. The point about using SQL is that you aren't thinking about 
optimization or algorithms at all. You're thinking of the data and 
relationships embodied therein--or at least you should be.

In a perfect world, you figure out what data you're looking for, then 
describe what you want. It is the RDBMS that does the optimization to 
give you what you describe. It is only when it isn't performing well 
that one would even think of optimizing your SQL, adding more indices, etc.

...Edmund.

Re: [SQLObject] two postgres questions

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

At 19:45 2003-05-28 -0400, Edmund Lian wrote:
>To be honest, I'd rather see synthetic primary keys banished. But, while 
>PostgreSQL allows changes in primary keys to cascade automatically to 
>foreign keys in other tables, Oracle and a few other databases do not. So, 
>the need to be portable might well necessitate synthetic keys.

Does *any* database but PostgreSQL support cascaded key updates
like that?

Anyway, this is *not* the big problem with "natural" primary keys.

The main problem is that the business rules change, and making big
changes in large, actively used databases is very disruptive and
expensive. We want to isolate each needed change as much as possible.

It's far from unique that the set of columns that was the ideal key
yesterday isn't that any longer. Yesterday it was a solid business
rule that only one row in this table could be created per person
each day, so personId + registryDate seemed to be the ideal combo.
The business experts are so sure that they would bet their arm on it.
Today things changed, and in a few cases, it might actually happen
that there have to be two rows for one person on certain dates.

With synthetic keys, this means that we drop a unique index. With
natural keys, our primary key breaks, and with that maybe a whole
tree of keys for detail and sub-detail tables that use these
fields as parts of their primary keys. Yuk! Been there, done that...
I solved it ugly, and cheated with the date in those rare cases,
using the next day, if the current was used, but I'd rather do
things cleanly.

Using the primary key of one table as part of the primary key for
another table just because there is a parent-child relationship
between those tables mean that we have a much tighter coupling
between the tables than we have with synthetic keys. While this
evil, tight coupling can sometimes be avoided in the *primary*
keys of dependent tables, we can't avoid having the entire
primary key (which contains business information for another table)
as foreign key fields in the dependent table.

Not only the data model is hampered by this. The classes in the
application logic, whether it's implemented in Python, Java or
C++ will carry along attributes that really belong to another
class! This also means that the cost of implementing a business
rule change is multiplied.

By using a uniform primary key type, we will also always have a
uniform foreign key type, and it will be much easier to change
the table structure. After all we sometimes realize the the X
objects aren't really atttibutes of the Y object, but rather of
the Z objects. Natural keys create some kind of software structure
cement, and makes changes in business rules very hard. Often,
these structures get cemented long before the product is even
launched. It's one thing that it's difficult to migrate loads of
important business data, but it *should* at least be swift to
change the system if it isn't running yet.

For a different problem that brought me to the same conclusion, see
http://www.thinkware.se/cgi-bin/thinki.cgi/CaseStudyUnbrokenSeries



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

Re: [SQLObject] two postgres questions

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

On Wed, 2003-05-28 at 18:53, Edmund Lian wrote:
> Apologies for the delay in replying...

No problem, we all have other lives too...

> > What I suspect Edmund's problem is, is that his tables don't map well to
> > classes.  Or maybe more accurately, his *usage* doesn't map well to
> > instances and attribute access.  I'm imagining that his applications are
> > phrased with rows being relations, not so much structured data.  Or at
> > least some of the data.
> 
> Not to mention that in a fully normalized database, there can be a ton 
> of joins to get what you want (or else views are used, but it's the same 
> thing).

I think views don't jive well with SQLObject -- they create an opaque
structure.  But yes, normalization can lead to a whole bunch of tables. 
I'm interested extending the power of joins so that some of these can be
handled more easily, without having to have too many classes involved.

> My usage is certainly very traditional in that I use fully normalized 
> data models. Getting what I want does require the use of subselects, 
> multiway joins and outer joins, transactions and rollbacks, etc. I still 
> can't see how to do these, or else change the data model to avoid these 
> so that I can use SQLObject. But, I'll try and report back what I find.

Any ORM involves moving logic into Python (or whatever programming
language).  That's a compromise you'll have to deal with.

But you don't have to use complicated queries anyway.  There's nothing
intrinsically Wrong about doing the logic in Python.  List comprehension
even makes set operations look nicer, and caching can provide
performance improvements.

It could probably be argued that those complicated queries are a form of
premature optimization.  Like most optimization, complex queries usually
are based on the requirements of pieces of code that would otherwise be
considered factored.  Those queries may be an important form of
optimization, but I think that should still be accessible even if it
won't be as natural as using normal object access.

  Ian

Re: [SQLObject] two postgres questions

[Edmund L. <el...@in...>]

Ian,

Apologies for the delay in replying...

You wrote:

> If you really need a sophisticated data definition, you can always
> create the table yourself, and allow the database to handle much of the
> logic (like constraints).  That seems acceptable to me.

Yes, this is a good solution.


> What I suspect Edmund's problem is, is that his tables don't map well to
> classes.  Or maybe more accurately, his *usage* doesn't map well to
> instances and attribute access.  I'm imagining that his applications are
> phrased with rows being relations, not so much structured data.  Or at
> least some of the data.

Not to mention that in a fully normalized database, there can be a ton 
of joins to get what you want (or else views are used, but it's the same 
thing).

My usage is certainly very traditional in that I use fully normalized 
data models. Getting what I want does require the use of subselects, 
multiway joins and outer joins, transactions and rollbacks, etc. I still 
can't see how to do these, or else change the data model to avoid these 
so that I can use SQLObject. But, I'll try and report back what I find.

...Edmund.

Re: [SQLObject] two postgres questions

[Edmund L. <el...@in...>]

Bud P.Bruegger wrote:

Apologies for taking so long to reply. As I mentioned in another 
message, I've got a lot of things happening at home and work right now.

> I agree that "legacy" isn't a good term.  What I meant is really a
> pre-existing data schema that has to be accesses (in contrast to one
> that is newly created following the needs of the middleware).

I was just being sensitive... :-)  Ian's right--legacy is loaded with 
unnecessarily negative connotations in the tech industry.


> Do you see a strong need to live without such an OID?  Is it possible
> to retrofit pre-existing schemas with such an OID field?

I'm going to try (see the message I just emailed to Luke). To be honest, 
I'd rather see synthetic primary keys banished. But, while PostgreSQL 
allows changes in primary keys to cascade automatically to foreign keys 
in other tables, Oracle and a few other databases do not. So, the need 
to be portable might well necessitate synthetic keys.

I suppose that as long as one could still add integrity constraints to 
the real but non-functional primary keys, then the use of synthetic keys 
won't be so bad.


> Is this what you need or is there more to it?

Not sure yet. Since I'm in danger of being too theoretical, I'm going to 
try using SQLObject for a project, and will report where it comes up short.


> I would believe that a concrete example would be the best way of
> understanding each other.  I admit to have difficulties to fully
> understand the situation so far.  I tried to make it more concrete
> above what I have understood about constraints.  I still have only a
> vague understanding of the multiple joins...

I'll write more as I find the problems... I'll use SQLObject from the 
viewpoint of traditional data modeler--full constraints, relational 
integrity, etc. and see where I run aground...

...Edmund.

Re: [SQLObject] two postgres questions

[Edmund L. <el...@in...>]

Luke Opperman wrote:

> 3. Recognizing that constraints are *part* of the data model, this is just a
> clarification of (2), a desire to model everything via relational theory but
> to access this model via objects.
> 
> I don't see anything inherent in (3) that requires you to "trade pure object
> orientation", the problem is that we're not aware of an object mapper that
> supports as much as we'd like.

Yes, you're right of course.


> Since I'm interested in making SQLObject (or a byproduct of) able to support
> what I consider to be all of relational theory in an object-accessible way,
> I'm curious what you consider to be the too-complex parts today. So far I've
> heard multi-way joins and constraints. Let me see if I can elaborate these,
> then you do the same?

I'll do this soon... sorry (to you, Bud, and Ian) for taking such a long 
time to reply. Lot's of things happening with family and work right now.

One thing that I want to do is sit down and try using SQLObject in its 
current form for a small portion of a real project that does use a lot 
of relational integrity, and then report where I get stuck. This would 
be a good way to stretch it.

I've just spent a couple of days going over MiddleKit, Object Relational 
Membrane, Modeling, and SQLObject. I keep coming back to SQLObject for a 
variety of reasons, mostly to do with the documentation, user community, 
and interface aesthetics. When I have time, I'll post my impressions 
(worth $0.02) of each.

...Edmund.

Re: [SQLObject] ADO DB-API support?

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

At 12:39 2003-05-28 -0500, Ian Bicking wrote:
>I was thinking of changing the magic method I use to __sqlrepr__, which
>seems like a good name to me.  Just having a single convention would be
>a start.

Ok with me. It would be good if there was at least a name
given as an option in the DB-API spec. This function obviously
exist in a number of implementations, with different names.

>Right now I think quoting would probably best be done like:
>
>* Integers, floats, strings are all automatically quoted.  Maybe
>mxDateTime and datetime objects too.  No hooks for these -- the
>underlying C driver may want to handle these data structures on its own
>anyway.

Right.

>* Anything with a __sqlrepr__ method has that called, with no
>arguments.  The result is expected to be fully quoted.

This is what I thought first, but there were some oppsition to
the idea on the db-sig mailing list. Let's return to the date
and Access. Let's say that you have your own date class, and you
deliver the string "'1984-06-04'". This won't work on Access.
If __sqlrepr__() had returned '1984-06-04' and another method
__sqltype__ had returned 'DATE', then the driver could have
known that it would do "'%s'" % __sqlrepr__() on sane platforms
and "#%s#" % __sqlrepr__() on that warped MS platform.

If the interface doesn't build the full SQL statement in the
interface, but actually sends the parameters separately to
the backend, you might end up with things like

INSERT INTO T1 (C1) VALUES ('''that''''s''')

That would be a bit sad... :(

But still, it's a start. It's certainly reasonable that the
result from __sqlrepr__ is passed in as is if there is no
__sqltype__ attribute in the object. I think that __sqltype__
is also a good idea though.

>* If both those fail, then there's a function which has one last chance
>to return a SQL representation on the object.  This would be for quoting
>types that you couldn't add a __sqlrepr__ method to -- for instance, if
>mxDateTime objects weren't automatically handled, you might handle them
>here.  Usage something like:
>
>import dbdriver
>old_quote = dbdriver.quote
>def quote(val):
>     if type(val) is DateTimeType:
>         return val.strftime("'%c'")
>     else:
>         return old_quote(val)

And then "dbdriver.quote = quote" or what? Do you register
this? Why not just supply something like date.Format('%Y-%m-%d')
instead of your plain date?

But finally: "In case of doubt, refuse the temptation to guess."
At least some drivers fall back on repr() in an else-statement.
I only want a "raise TypeError" in the default case.

>Maybe there's a better way to phrase this hook, but this might be
>sufficient.  The last quoting technique would probably be the only way
>to add your own quoting that was database-specific (as would be
>necessary with Access and mxDateTime objects).  So maybe __sqlrepr__
>should actually just be part of the standard quote function.

But drivers that can talk to Access, such as mxODBC and adodbapi have
no problem with this, since they just pass the unquoted date string to
the backend and let the ODBC driver handle that. Remember? That's where
we started. Generally, it's always possible to wrap object in a small
class that just implements __init__(self, value) and __sqlrepr__(self),
so I don't quite see the need for this quote function.

> > Both bookkeeping and paying customers need priority handling... :)
>
>Yeah, I hear that.  I have to cut myself off from this stuff every so
>often.

And I forget it... :( Back to the books...

>Parsing string literals with regular expressions never really works, at
>least when they use backslash quoting.  Python should have a standard
>function to do this, written in C (maybe that's one of the things
>kjbuckets does).  Or Pyrex.  Anyway, lacking that, writing such a thing
>could be useful.
>
>If the function works well enough, maybe it would be an incentive for
>packages like MySQLdb to use ? placeholders as well.  Ultimately that's
>the solution that would work best for DBAPI as a whole.

I think that would be best, and I hope it would be possible to get
this done.

>Maybe create a DBAPI module again (didn't DBAPI 1 have a common module?)
>-- put this function in there, the quote function, some common
>exceptions for everyone to use.  It'd be DBAPI 3.0, or 2.1... anyway,
>that's a lot of the biggest problems people seem to have.

Agreed. I think some drivers might be implemented entirely in
C, and others want to be completely Python to be as portable as
possible, so it might not be trivial to write a module that all
will agree with...

Well, if it's written as a .pyd/.so and becomes a Python standard
module it should work... :)

With standardized and uniform SQL access and the new datetime class
I only think we need a fixed point / money data type, and Python
will be ready to become the COBOL of the 21st century! :)
(It does sound awful, doesn't it. I guess that's why I like it.)


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

[SQLObject] mbox

[Marcos D. <md...@gr...>]

	Hi all. I'm interested in using this module, so I want to learn it. I
already read the docs, so I'm looking for the second source of knowledge one can
get hands on: the mailinglist's full mbox so far. I looked for it in the sf
page, but couldn't find it. any links? or can anybody that has it, please put it
online?

	TIA

-- 
If you think my facts are wrong then tell me. I don't mind learning right
from wrong.

Re: [SQLObject] ADO DB-API support?

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

On Tue, 2003-05-27 at 18:21, Magnus Lyck=E5 wrote:
> I doubt it :). The sqlite driver and the related PostgreSQL driver both
> look for an attribute called '_quote', which I suppose they use for the=
ir
> 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 drive=
rs?

I was thinking of changing the magic method I use to __sqlrepr__, which
seems like a good name to me.  Just having a single convention would be
a start.

Right now I think quoting would probably best be done like:

* Integers, floats, strings are all automatically quoted.  Maybe
mxDateTime and datetime objects too.  No hooks for these -- the
underlying C driver may want to handle these data structures on its own
anyway.
* Anything with a __sqlrepr__ method has that called, with no
arguments.  The result is expected to be fully quoted.
* If both those fail, then there's a function which has one last chance
to return a SQL representation on the object.  This would be for quoting
types that you couldn't add a __sqlrepr__ method to -- for instance, if
mxDateTime objects weren't automatically handled, you might handle them
here.  Usage something like:

import dbdriver
old_quote =3D dbdriver.quote
def quote(val):
    if type(val) is DateTimeType:
        return val.strftime("'%c'")
    else:
        return old_quote(val)

Maybe there's a better way to phrase this hook, but this might be
sufficient.  The last quoting technique would probably be the only way
to add your own quoting that was database-specific (as would be
necessary with Access and mxDateTime objects).  So maybe __sqlrepr__
should actually just be part of the standard quote function.

> Hopefully *someone* will like the idea. Maybe with a few more champions=
... :)
>=20
> 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... :)

Yeah, I hear that.  I have to cut myself off from this stuff every so
often.

> >I would still disagree, at least for SQLObject.  Or rather, I would sa=
y
> >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.
>=20
> My though was to allow .select("name=3D?", ("Guido's",)) as an alternat=
ive
> to .select("name=3D'Guido''s'")

Yes, if you didn't use literals (at least string literals) in the code
it should be okay. =20

> >Well, for instance, MySQL allows strings like 'that\'s it', which woul=
d
> >break this.  But it also allows 'that''s it', so maybe people will jus=
t
> >have to use the one.
>=20
> 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 \'.

The replace doesn't work for '\\', though. =20

> 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.
>=20
> Is there a flaw in that strategy?

Parsing string literals with regular expressions never really works, at
least when they use backslash quoting.  Python should have a standard
function to do this, written in C (maybe that's one of the things
kjbuckets does).  Or Pyrex.  Anyway, lacking that, writing such a thing
could be useful.

If the function works well enough, maybe it would be an incentive for
packages like MySQLdb to use ? placeholders as well.  Ultimately that's
the solution that would work best for DBAPI as a whole.

Maybe create a DBAPI module again (didn't DBAPI 1 have a common module?)
-- put this function in there, the quote function, some common
exceptions for everyone to use.  It'd be DBAPI 3.0, or 2.1... anyway,
that's a lot of the biggest problems people seem to have.

  Ian

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