#66 SQLObject.selectBy() generates wrong SQL with None values

closed-fixed
nobody
General (125)
5
2005-03-29
2005-03-26
Anonymous
No

OS: WinXP
Python: 2.3.5
SQLObject: 0.6.1 (release)

Example with SQL debug info:

>>> from sqlobject import *
>>> conn=connectionForURI('mysql://root:root@localhost/test')
>>> conn.debug=True
>>> class T(SQLObject):
... _connection=conn
... x=IntCol()
...
>>> T.createTable()
1/Pool : ACQUIRE pool=[]
1/Query : CREATE TABLE t (
id INT PRIMARY KEY AUTO_INCREMENT,
x INT
)
1/Pool : RELEASE (implicit, autocommit=True) pool=[]
>>> r1=T(x=1)
1/Pool : ACQUIRE pool=[]
1/QueryIns: INSERT INTO t (x) VALUES (1)
1/Pool : RELEASE (implicit, autocommit=True) pool=[]
1/Pool : ACQUIRE pool=[]
1/QueryOne: SELECT x FROM t WHERE id = 1
1/Pool : RELEASE (implicit, autocommit=True) pool=[]
>>> r2=T(x=None)
1/Pool : ACQUIRE pool=[]
1/QueryIns: INSERT INTO t (x) VALUES (NULL)
1/Pool : RELEASE (implicit, autocommit=True) pool=[]
1/Pool : ACQUIRE pool=[]
1/QueryOne: SELECT x FROM t WHERE id = 2
1/Pool : RELEASE (implicit, autocommit=True) pool=[]
>>> for r in T.select():
... print r.x
...
1/Pool : ACQUIRE pool=[]
1/Select : SELECT t.id, t.x FROM t WHERE 1 = 1
1
None
1/Pool : RELEASE (implicit, autocommit=True) pool=[]

Two records are created. Records are wrote to the database table (copied from PHPMyAdmin):

1 1
2 NULL

One can select records with x=1 using T.selectBy():

>>> for r in T.selectBy(x=1):
... print r.x
...
1/Pool : ACQUIRE pool=[]
1/Select : SELECT t.id, t.x FROM t WHERE x = 1
1
1/Pool : RELEASE (implicit, autocommit=True) pool=[]

But this fails if you select the records with x=None:

>>> for r in T.selectBy(x=None):
... print r.x
...
1/Pool : ACQUIRE pool=[]
1/Select : SELECT t.id, t.x FROM t WHERE x = NULL
1/Pool : RELEASE (implicit, autocommit=True) pool=[]

No records found, since x=NULL is not identical to x IS NULL, at least on MySQL. I've tracked down the problem into SQLObject. I found, that the selectBy method uses a wrong shortcut to quickly generate the WHERE clause:

In dbconnection.py at line 451:

def _SO_columnClause(self, soClass, kw):
return ' AND '.join(['%s = %s' %
(soClass._SO_columnDict[key].dbName,
self.sqlrepr(value))
for key, value
in kw.items()])

As a workaround, I've overridden selectBy() in my Table subclass of SQLObject and used an alternative implementation:

def selectBy(cls,connection=None,**kw):
return cls.select(AND(*tuple([(getattr(cls.q,k)==v) for k,v in kw.items()])))
selectBy=classmethod(selectBy)

This workaround constructs a full select from the keyword arguments of selectBy(). This is somewhat slower, but works correctly.

Discussion

  • Viktor Ferenczi

    Viktor Ferenczi - 2005-03-26

    Logged In: YES
    user_id=142612

    Accidentally, I've created this entry without logging in.
    Please change the submitter to complex if you allowed to do
    so. Thanks - complex

     
  • Oleg Broytman

    Oleg Broytman - 2005-03-29
    • status: open --> closed-fixed
     
  • Oleg Broytman

    Oleg Broytman - 2005-03-29

    Logged In: YES
    user_id=4799

    _SO_columnClause() was fixed in SQL long ago.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks