#55 Updating a BLOBCol fails

closed-accepted
MySQL (29)
5
2005-05-02
2005-02-16
Anonymous
No

I'm using win2k, python 2.4, mysql 4.1.9, and the
latest SQLObject from SVN (rev 612).

Beginning with this sql file:
-------------------------
create database subway;
use subway;

CREATE TABLE `todos` (
`id` int(4) unsigned NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`description` text,
`done` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=InnoDB;

-- insert some test data
INSERT INTO `todos` SET title='Trash',
description='Take out the trash on Thursday night.',
done=0;

I run this python program:
------------------
import sqlobject

__connection__ = "mysql://root:root@localhost/subway"

class Todo(sqlobject.SQLObject):
_table = 'todos'
_fromDatabase = True

if __name__ == "__main__":
row = Todo.get(1)
print "row=%s" % row

row.description = 'Take out the trash on Friday night.'
#row.title = 'Hello'
#row.done = 1

row = Todo.get(1)
print "row=%s" % row

I get this error output:
-----------------
D:\Program
Files\Python24\lib\site-packages\sqlobject\main.py:320:
DeprecationWarning: '_table' is deprecated; please set
the 'table' attribute in sqlmeta instead
"attribute in sqlmeta instead", level=2)
row=<Todo 1 title='Trash' description='Take out the
trash on Thursday night.' done=1>
Traceback (most recent call last):
File "D:\Program
Files\eclipse\workspace\subway-local\test-sql.py", line
15, in ?
row.description = 'Take out the trash on Friday night.'
File "<string>", line 1, in <lambda>
File "D:\Program
Files\python24\Lib\site-packages\sqlobject\main.py",
line 889, in _SO_setValue
[(self._SO_columnDict[name].dbName,
File "D:\Program
Files\python24\Lib\site-packages\sqlobject\dbconnection.py",
line 398, in _SO_update
self.query("UPDATE %s SET %s WHERE %s = %s" %
File "D:\Program
Files\python24\Lib\site-packages\sqlobject\dbconnection.py",
line 490, in sqlrepr
return sqlrepr(v, self.dbName)
File "D:\Program
Files\python24\Lib\site-packages\sqlobject\converters.py",
line 185, in sqlrepr
raise ValueError, "Unknown SQL builtin type: %s for
%s" % \ ValueError: Unknown SQL builtin type: <type
'array.array'> for array('c', 'Take out the trash on
Friday night.')

I've traced this error deep into the code and found
that BLOBCol is the problem. The two commented lines
in the python program:
row.title = 'Hello'
row.done = 1
both work fine. Note: title is a StringCol object, and
done is a basic Col object.

Inserting these print statements at line 880 in main.py
in method _SO_setValue:
----------------------
print " self._columns=%s" % self._columns
print " dbValue=%s" % dbValue

I get this output:
-----------------
self._columns=[<sqlobject.col.StringCol object at
0x00A0B510>, <sqlobject.col.BLOBCol object at
0x00A0B9F0>, <sqlobject.col.Col object at 0x00A0BD50>]
dbValue=array('c', 'Take out the trash on Friday night.')

So for a BLOBCol object, fromPython(value,
self._SO_validatorState) returns this wierd two element
array that can not be handled by the sqlrepr method in
converters.py.

If I just make dbValue into a simple string, for
example dbValue='Take out the trash on Friday night.',
then everything works fine and the update succeeds.

-Justin (shackletj at yahoo nospam dot com)

Discussion

  • Nobody/Anonymous

    Logged In: NO

    This all relates back to MySQL-python. See the bug tracker at:
    http://sourceforge.net/tracker/index.php?func=detail&aid=975831&group_id=22307&atid=374932

    After some more investigation, in __init__.py of MySQLdb
    (ver 1.2.0) there is the method:
    def Binary(x):
    from array import array
    return array('c', x)
    As far as I can see, this method operates on all data
    returned from mysql BLOB columns, and returns an array. Any
    versions of MySQL-python with this method should cause an
    error to be thrown in sqlobject, because sqlobject's
    converters.py does not handle objects of type array. This
    method is present in MySQL-python version 1.2.0, 1.1.10,
    1.1.9, but back in MySQL-python 1.0.1 there is a different
    method that just returns a simple string:
    def Binary(x): return str(x)

    Here is one solution that I came up with. Modify the
    StringLikeConverter method in converters.py to handle the
    array type:
    ----------
    from array import array
    def StringLikeConverter(value, db):
    if type(value) == type(array('c','')):
    try:
    value = value.tounicode()
    except ValueError:
    value = value.tostring()

    if db in ('mysql', 'postgres'):
    for orig, repl in sqlStringReplace:
    value = value.replace(orig, repl)
    elif db in ('sqlite', 'firebird', 'sybase', 'maxdb'):
    value = value.replace("'", "''")
    else:
    assert 0, "Database %s unknown" % db
    return "'%s'" % value

    registerConverter(type(""), StringLikeConverter)
    registerConverter(type(u""), StringLikeConverter)
    registerConverter(type(array('c','')), StringLikeConverter)

    I'm sure there is a better way to do this, but this is what
    I thought might work.

    -Justin

     
  • Oleg Broytman

    Oleg Broytman - 2005-05-02
    • assigned_to: nobody --> phd
    • status: open --> closed-accepted
     
  • Oleg Broytman

    Oleg Broytman - 2005-05-02

    Logged In: YES
    user_id=4799

    The patch applied at revision 756. Thank you.

     

Log in to post a comment.