[SQLObject] Re: Errors inserting into table with no autoincrement ID (MySQL)

[Kumar M. <kum...@gm...>]

I'm hoping someone can prove that I am completely mad by showing me a
better way, but I've only gotten this working with a patch to
sqlobject.main.sqlmeta.addColumnsFromDatabase and
sqlobject.mysql.mysqlconnection.MySQLConnection._queryInsertID

Coincidentally, there was a thread posted a day after mine with the
exact same problem, which is where I got the first patch :
http://pythonpaste.org/archives/thread/20051109.150226.06df84a3.en.html#200=
51109.150226.06df84a3

The db schema here is a little whacked (not my doing) and on top of
that, I am trying to synchronize keys to another database so I need to
explicitly insert ID numbers.  The two issues seem to only happen when
using fromDatabase and are specifically:
1.  cannot insert a new row if primary key is not an autoincrement ID
(MySQL only of course)
2. cannot explicitly set id=3DN when inserting a row

I will paste and attach the test case to run, but here are the
exceptions raised before applying the two code patches :

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
ERROR: test_cat_cust_id (__main__.TestCategories)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "./test_so_noauto.py", line 88, in test_cat_cust_id
    Categories(id=3D1, categoriesImage=3D'test.gif')
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/SQLObject-0.7rc1-py2.4.egg/sqlobject/main.py",
line 1179, in __init__
    self._create(id, **kw)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/SQLObject-0.7rc1-py2.4.egg/sqlobject/main.py",
line 1206, in _create
    self._SO_finishCreate(id)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/SQLObject-0.7rc1-py2.4.egg/sqlobject/main.py",
line 1230, in _SO_finishCreate
    id, names, values)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/SQLObject-0.7rc1-py2.4.egg/sqlobject/dbconnection.py",
line 744, in queryInsertID
    return self._dbConnection._queryInsertID(
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/SQLObject-0.7rc1-py2.4.egg/sqlobject/mysql/mysqlconnection.py",
line 78, in _queryInsertID
    self._executeRetry(conn, c, q)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/SQLObject-0.7rc1-py2.4.egg/sqlobject/mysql/mysqlconnection.py",
line 60, in _executeRetry
    return cursor.execute(query)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/MySQLdb/cursors.py",
line 137, in execute
    self.errorhandler(self, exc, value)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/MySQLdb/connections.py",
line 33, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1110, "Column 'categories_id' specified twice")

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
ERROR: test_cat_descr (__main__.TestCategories)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "./test_so_noauto.py", line 92, in test_cat_descr
    cat_descr =3D CategoriesDescription(categories=3Dcat, categoriesName=3D=
'test2')
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/SQLObject-0.7rc1-py2.4.egg/sqlobject/main.py",
line 1179, in __init__
    self._create(id, **kw)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/SQLObject-0.7rc1-py2.4.egg/sqlobject/main.py",
line 1206, in _create
    self._SO_finishCreate(id)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/SQLObject-0.7rc1-py2.4.egg/sqlobject/main.py",
line 1233, in _SO_finishCreate
    self._init(id)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/sit=
e-packages/SQLObject-0.7rc1-py2.4.egg/sqlobject/main.py",
line 942, in _init
    raise SQLObjectNotFound, "The object %s by the ID %s does not
exist" % (self.__class__.__name__, self.id)
SQLObjectNotFound: The object CategoriesDescription by the ID 0 does not ex=
ist

----------------------------------------------------------------------
Applying the following patches makes the tests pass (but I have no
idea if it breaks anything else!)
----------------------------------------------------------------------

#!/usr/bin/env python

from sqlobject import *
import sqlobject
import unittest

conn =3D connectionForURI('mysql://uu:pp@localhost/test_so_noauto',
debug=3DTrue, debugOutput=3DTrue)
sqlhub.processConnection =3D conn.transaction()

# MONKEYPATCH 1, from
http://pythonpaste.org/archives/thread/20051109.150226.06df84a3.en.html#200=
51109.150226.06df84a3
# necessary for inserting custom IDs ...
def addColumnsFromDatabase(sqlmeta, connection=3DNone):
    soClass =3D sqlmeta.soClass
    conn =3D connection or soClass._connection
    for columnDef in conn.columnsFromSchema(sqlmeta.table, soClass):
        # Next two lines added
        dbName =3D sqlmeta.style.pythonAttrToDBColumn(columnDef.name)
        if dbName =3D=3D sqlmeta.idName:
            continue
        if columnDef.name not in sqlmeta.columnDefinitions:
            sqlmeta.addColumn(columnDef)
sqlobject.main.sqlmeta.addColumnsFromDatabase =3D
classmethod(addColumnsFromDatabase)

# MONKEYPATCH 2, so that IDs can be found when they are not
autoincrements, but foreign keys
def _queryInsertID(self, conn, soInstance, id, names, values):
    table =3D soInstance.sqlmeta.table
    idName =3D soInstance.sqlmeta.idName
    c =3D conn.cursor()
    # ADDED: try to get id value from attribute, as in a foreign key obj
    if id is None:
        id =3D getattr(soInstance,
                    soInstance.sqlmeta.style.dbColumnToPythonAttr(idName), =
None)
        if id is not None and self.debug:
            print "MONKEYPATCH got ID from attr: %s" % id
    # ADDED: also checking that idName not already in names ...
    if id is not None and idName not in names:
        names =3D [idName] + names
        values =3D [id] + values
    q =3D self._insertSQL(table, names, values)
    if self.debug:
        self.printDebug(conn, q, 'QueryIns')
    self._executeRetry(conn, c, q)
    if id is None:
        try:
            id =3D c.lastrowid
        except AttributeError:
            id =3D c.insert_id()
    if self.debugOutput:
        self.printDebug(conn, id, 'QueryIns', 'result')
    return id
sqlobject.mysql.mysqlconnection.MySQLConnection._queryInsertID =3D _queryIn=
sertID

"""
CREATE TABLE `categories` (
  `categories_id` int(11) NOT NULL auto_increment,
  `categories_image` varchar(64) default NULL,
  PRIMARY KEY  (`categories_id`)
) ENGINE=3DInnoDB;

CREATE TABLE `categories_description` (
  `categories_id` int(11) NOT NULL,
  `categories_name` varchar(32) NOT NULL,
  PRIMARY KEY  (`categories_id`)
) ENGINE=3DInnoDB;
"""

class Categories(SQLObject):
    class sqlmeta:
        idName =3D 'categories_id'
        fromDatabase =3D True
        style =3D DefaultStyle(longID=3DTrue)

class CategoriesDescription(SQLObject):
    categories =3D ForeignKey('Categories')
    class sqlmeta:
        idName =3D 'categories_id'
        fromDatabase =3D True
        style =3D DefaultStyle(longID=3DTrue)

class TestCategories(unittest.TestCase):
    def teardown(self):
        conn.rollback()

    def test_cat_cust_id(self):
        Categories(id=3D1, categoriesImage=3D'test.gif')

    def test_cat_descr(self):
        cat =3D Categories(categoriesImage=3D'test2.gif')
        cat_descr =3D CategoriesDescription(categories=3Dcat,
categoriesName=3D'test2')

if __name__ =3D=3D "__main__":
    unittest.main()