sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ph...>]

On Fri, Apr 23, 2004 at 07:48:06PM +0400, Oleg Broytmann wrote:
> On Fri, Apr 23, 2004 at 05:22:00PM +0200, Philippe Normand wrote:
> > And, still the dash doesn't make sense to me
> > because orderBy has to be a string, not a number. Anyway ...

   Let do it with strings:

patients = Patient.select(None, orderBy="-registration_time")

Traceback (most recent call last):
[snip]
  File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py", line 168, in _iterSelect
    cursor.execute(query)
ProgrammingError: ERROR:  Non-integer constant in ORDER BY

SELECT ... ORDER BY 'registration_time' DESC

   See? SQLObject have added excessive apostrophes to the column name :(

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ma...>]

On Fri, Apr 23, 2004 at 05:22:00PM +0200, Philippe Normand wrote:
> > > > patients = Patient.select(None, orderBy=-Patient.q.registrationTime)
> 
> I don't understand the 'None' ... Do you want to select ... nothing ? hmm :)

   I want to select all. None and "all" do the same - select all.

> Try replacing None with 'all'.

   In the real program there is a complex query. It does not matter -
the problem is in orderBy, not in the query.

> And, still the dash doesn't make sense to me
> because orderBy has to be a string, not a number. Anyway ...

   It has not to be a string. At least according to the documentation.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] orderBy table.q.attribute

[Philippe N. <sw...@fr...>]

On Fri, Apr 23, 2004 at 07:16:29PM +0400, Oleg Broytmann wrote:
> On Fri, Apr 23, 2004 at 04:50:23PM +0200, Philippe Normand wrote:
> > On Fri, Apr 23, 2004 at 06:48:43PM +0400, Oleg Broytmann wrote:
> > > Hello.
> > >=20
> > >    How can I orderBy using table.q.attribute magic?
> > >=20
> > > patients =3D Patient.select(None, orderBy=3D-Patient.q.registrationTi=
me)
> > >=20
> >=20
> > Well, what's the dash out there ?
>=20
>    No difference without it:

I don't understand the 'None' ... Do you want to select ... nothing ? hmm :)
Try replacing None with 'all'. And, still the dash doesn't make sense to me
because orderBy has to be a string, not a number. Anyway ...

>=20
>    What if I want to orderBy a complex condition?
>=20
> ...orderBy=3D[-registrationTime, name, surname]

Never tried such a thing.

--=20
Philippe Normand

Re: [SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ma...>]

On Fri, Apr 23, 2004 at 04:50:23PM +0200, Philippe Normand wrote:
> On Fri, Apr 23, 2004 at 06:48:43PM +0400, Oleg Broytmann wrote:
> > Hello.
> > 
> >    How can I orderBy using table.q.attribute magic?
> > 
> > patients = Patient.select(None, orderBy=-Patient.q.registrationTime)
> > 
> 
> Well, what's the dash out there ?

   No difference without it:

patients = Patient.select(None, orderBy=Patient.q.registrationTime)

Traceback:
[snip]
    if self.sourceClass._SO_columnDict.has_key(orderBy):
TypeError: unhashable instance

> If you want to order by registrationTime
> DESC, used reversed param like:
> 
> patients = Patient.select(orderBy=Patient.q.registrationTime,
>                           reversed=True)

   What if I want to orderBy a complex condition?

...orderBy=[-registrationTime, name, surname]

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] orderBy table.q.attribute

[Philippe N. <sw...@fr...>]

On Fri, Apr 23, 2004 at 06:48:43PM +0400, Oleg Broytmann wrote:
> Hello.
>=20
>    How can I orderBy using table.q.attribute magic?
>=20
> patients =3D Patient.select(None, orderBy=3D-Patient.q.registrationTime)
>=20

Well, what's the dash out there ? If you want to order by registrationTime
DESC, used reversed param like:

patients =3D Patient.select(orderBy=3DPatient.q.registrationTime,
                          reversed=3DTrue)
			 =20


--=20
Philippe Normand

[SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ph...>]

Hello.

   How can I orderBy using table.q.attribute magic?

patients = Patient.select(None, orderBy=-Patient.q.registrationTime)

Traceback (most recent call last):
[snip]
  File "/usr/local/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 1068, in select
    connection=connection)
  File "/usr/local/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 1235, in __init__
    orderBy = self._mungeOrderBy(orderBy)
  File "/usr/local/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 1246, in _mungeOrderBy
    if self.sourceClass._SO_columnDict.has_key(orderBy):
TypeError: unhashable instance

   SQLObject 0.5.2 with inheritance patch.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Naming Conventions

[Gavin <bra...@ri...>]

How can you convert the naming of fields to lowercase format with 
underscores ie my_field opposed to myField?
thanks,
Gavin

Re: [SQLObject] MultipleJoin to id

[Oleg B. <ph...@ph...>]

On Thu, Apr 22, 2004 at 11:19:32AM -0500, Ian Bicking wrote:
> class Term(SQLObject):
> 
>     translation = ForeignKey('Term')
> 
>     # This is definitely a separate idea from the above!:
>     translatedTerms = MultipleJoin('Term', joinColumn='translation_id')
> 
> I believe that will work, but I'm not sure.

   This should work, and I actually have thought about this solution...
That is, if this a subterm, just allow translation to be None, and if it
is a mster term, set translation like this?

   subterm = Term.new()
   masterterm = Term.new(translation=subterm)
   print masterterm.translatedTerms # => [subterm]

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Handling lost MySQL connections...

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

Chris Gahan wrote:
> I was looking into fixing the problem with MySQL connections in the pool 
> timing-out if you don't use SQLObject for a while (for example, with a 
> long-running process that doesn't get constant activity). Here's an 
> example of the problem:
> 
>   File "SQLObject\SQLObject.py", line 1228, in count
>     count = conn.countSelect(self)
>   File "SQLObject\DBConnection.py", line 177, in countSelect
>     val = int(self.queryOne(q)[0])
>   File "SQLObject\DBConnection.py", line 156, in queryOne
>     return self._runWithConnection(self._queryOne, s)
>   File "SQLObject\DBConnection.py", line 74, in _runWithConnection
>     val = meth(conn, *args)
>   File "SQLObject\DBConnection.py", line 149, in _queryOne
>     c.execute(s)
>   File "MySQLdb\cursors.py", line 95, in execute
>     return self._execute(query, args)
>   File "MySQLdb\cursors.py", line 114, in _execute
>     self.errorhandler(self, exc, value)
>   File "MySQLdb\connections.py", line 33, in defaulterrorhandler
>     raise errorclass, errorvalue
> OperationalError: (2013, 'Lost connection to MySQL server during query')
> 
> 
> The obvious spot to try to catch the exception and try to reconnect is 
> line 149 of DBConnection, where it does c.execute(s). Reconnecting isn't 
> going to matter though, since you can't return the changed connection to 
> the caller.
> 
> I was thinking that I could handle the exception by subclassing the 
> connection object that MySQLdb.connect() returns, which would have an 
> overloaded connection.execute(). It would have a try/except block around 
> the superclass' .execute() statement, and be able to reconnect if it got 
> OperationalError 2013.
> 
> Do you think adding another level to the class hierarchy is going to add 
> too much overhead for the queries? It's probably not a very common bug, 
> and maybe it should even be something that MySQLdb itself handles.

Another way to do this would be to ping the connection everytime you do 
DBAPI.getConnection.  I believe there's a ping method of some sort for 
MySQL, and perhaps equivalents for other connections (though it's not 
part of the DBAPI).

This wouldn't save you if the connection was lost part way through a 
transaction, or somewhere else where you hang onto a connection for a 
long time.  And it may be less efficient than catching the exception, 
which isn't much overhead unless you actually get an error, and lost 
connection errors shouldn't be too common (relative to queries).  So I 
don't think the overhead of a subclass would be too bad.

   Ian

Re: [SQLObject] MySQL/PostgreSQL inconsistencies

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

Chris Gahan wrote:
> I found some inconsistencies with the way PostgreSQL and MySQL handle 
> integer attributes.
> 
> I.e., you have this class:
> 
> class SO(SQLObject):
>     num = IntCol()
> 
> If you were to make an instance of SO (call it so), and set the num 
> attribute, MySQL would let you set it to a string or an int, while 
> PostgreSQL only lets you set it to an int, and throws a confusing exception 
> if you don't.
> 
> It would be best if IntCol let the user put in ints, strings, or decimal-
> less floats (anything easily coercable to an int), and always spat out an 
> int.

Yes, that would be good... well, maybe.  Certainly the inconsistency is 
less than positive.

To do this would be like BoolCol, which coerces its input.

   Ian

Re: [SQLObject] MultipleJoin to id

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

Oleg Broytmann wrote:
> On Thu, Apr 22, 2004 at 10:59:25AM -0500, Ian Bicking wrote:
> 
>>Hmm... so this doesn't work?
>>
>>class Term(SQLObject):
>>    translation = ForeignKey('Term')
> 
> 
>    It is not what I want.
> 
>    The table must work (and is working now with my _get_ and _set_) the
> following way: there is a term and a list of translations. Master term
> can have or have not translations (aterm.translation is None). If there
> are translations, they are terms from the same table, and for every
> translation there is no a subtranslation:
> 
> for t in aterm.translation:
>    if t.translation is not None:
>       raise TypeError
> 
>    Well, this is not enforced now, and it is not important to my
> question. The question is - how to make translation MultipleJoin? I'd
> like to declare
> 
> class Term(SQLObject):
>    translation = MultipleJoin("Term")
> 
> but there is no a ForeignKey for it. How can I declare id as the
> ForeignKey?

Hmm... okay (untested):

class Term(SQLObject):

     translation = ForeignKey('Term')

     # This is definitely a separate idea from the above!:
     translatedTerms = MultipleJoin('Term', joinColumn='translation_id')

I believe that will work, but I'm not sure.

   Ian

Re: [SQLObject] MultipleJoin to id

[Oleg B. <ph...@ph...>]

On Thu, Apr 22, 2004 at 10:59:25AM -0500, Ian Bicking wrote:
> Hmm... so this doesn't work?
> 
> class Term(SQLObject):
>     translation = ForeignKey('Term')

   It is not what I want.

   The table must work (and is working now with my _get_ and _set_) the
following way: there is a term and a list of translations. Master term
can have or have not translations (aterm.translation is None). If there
are translations, they are terms from the same table, and for every
translation there is no a subtranslation:

for t in aterm.translation:
   if t.translation is not None:
      raise TypeError

   Well, this is not enforced now, and it is not important to my
question. The question is - how to make translation MultipleJoin? I'd
like to declare

class Term(SQLObject):
   translation = MultipleJoin("Term")

but there is no a ForeignKey for it. How can I declare id as the
ForeignKey?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] MultipleJoin to id

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

Oleg Broytmann wrote:
> Hello!
> 
>    I need a MultipleJoin, where ForeignKey is the id of the same table.
> I cannot do it directly, because I cannot redeclare id column, so I
> implemented it the following way:
> 
> class Term(SQLObject):
>     translation = IntCol(default=None) # instead of MultipleJoin
> 
>     def _get_translation(self):
>         _translation = self._SO_get_translation()
>         if _translation is None:
>             return None
>         return self.select(self.q.id == _translation, orderBy="code")
> 
>     def _set_translation(self, codeObject):
>         if codeObject is not None:
>             codeObject = codeObject.id
>         self._SO_set_translation(codeObject)
> 
>     _doc_translation = "ID of the parent code term for this code"
> 
>    But this is ugly, and tiresome to reimplement every time I need such
> a feature. How can I do it in a simpler way?

Hmm... so this doesn't work?

class Term(SQLObject):
     translation = ForeignKey('Term')


Hmm... somewhere the terminology is getting all messed up, so I'm not 
sure how to follow your example.  "translation" without your overrides 
should be the term translated.  But then you want a backreference as 
well -- but you're naming the backreference the same as the forward 
reference.  I think you need another accessor.  There might still be a 
problem after you do that, but it's hard for me to understand this at 
all right now.

   Ian

Re: [SQLObject] Multiple join with the same table

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

Oleg Broytmann wrote:
> Hi!
> 
>    How can I rewrite a complex join like this (simplified):
> 
> SELECT * FROM table1 a, table2 b, table2 c WHERE b.parent = c.id...
> 
>    using SQLBuilder? How can I include a table twice?

Unfortunately, no, there's no way to alias tables with SQLBuilder. 
(Patches accepted!)

Well, I think you could do it by generating the SQL through SQLBuilder 
directly (instead of the magic .q variables), so as to get other tables, 
and then adding clauseTables=['table1 a', 'table2 b'].  But I'm not sure 
about that.

   Ian

Re: [SQLObject] Re: Hi guys. Please, I need help

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

Scott Russell wrote:
> I also flipped the BoolCol to an IntCol because I've never used it and I
> couldn't figure out how to. :) (Is BoolCol a postgres-only column?)

BoolCol should work anywhere, it will just end up as a TINYINT or INT on 
database that don't have BOOLEAN (which is everything but Postgres).  It 
also coerces non-integers into booleans, so it's better than just an IntCol.

   Ian

[SQLObject] Multiple join with the same table

[Oleg B. <ph...@ph...>]

Hi!

   How can I rewrite a complex join like this (simplified):

SELECT * FROM table1 a, table2 b, table2 c WHERE b.parent = c.id...

   using SQLBuilder? How can I include a table twice?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] MultipleJoin to id

[Oleg B. <ph...@ph...>]

Hello!

   I need a MultipleJoin, where ForeignKey is the id of the same table.
I cannot do it directly, because I cannot redeclare id column, so I
implemented it the following way:

class Term(SQLObject):
    translation = IntCol(default=None) # instead of MultipleJoin

    def _get_translation(self):
        _translation = self._SO_get_translation()
        if _translation is None:
            return None
        return self.select(self.q.id == _translation, orderBy="code")

    def _set_translation(self, codeObject):
        if codeObject is not None:
            codeObject = codeObject.id
        self._SO_set_translation(codeObject)

    _doc_translation = "ID of the parent code term for this code"

   But this is ugly, and tiresome to reimplement every time I need such
a feature. How can I do it in a simpler way?

   I am using SQLObject 0.5.2 with inheritance patch.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Re: Hi guys. Please, I need help

[Scott R. <sc...@to...>]

You need to let SQLobject do the work. :)

class Login(SQLObject):
    firstName = StringCol()
    middleInitial = StringCol(length=1, default=None)
    lastName = StringCol()

class db_request(SQLObject):
    new_file    = IntCol(default=1)
    DbRequest = ForeignKey('Login')
    DbSource  = ForeignKey('Login')

... snip ...

r1 = db_request.new(new_file=1, DbRequest=p1, DbSource=p2 )

Notice all I did was remove "Id" from the end of your column names. 
Fields ending in _id are special items to SQLobject.  Besides, you
didn't want to reference the id - you wanted to reference the object. 
Let SO worry about the IDs, unless you're doing something especially
tricky.  

I also flipped the BoolCol to an IntCol because I've never used it and I
couldn't figure out how to. :) (Is BoolCol a postgres-only column?)

  - Scott

[SQLObject] Re: Hi guys. Please, I need help

[fingerclick <fc...@bo...>]

Scott Russell wrote:

> On Wed, 2004-04-21 at 14:17, fingerclick wrote:
> 
>>I´d like to put in one table two references do a same table. Does 
>>sqlObject allows that? Or may i build the sql in another way to
>>make that works? db_login has the names and db_request has 2 fields that 
>>point to db_login( these fields are the persons id ).Thanks in advance.
> 
> 
> Unless I misunderstand the problem, if you take the time to discover how
> to add one reference field, it is the exact same process to add another.
> 
> class request(SQLObject):
>     NewFile = BoolCol(default=1)
>     DbRequestId = ForeignKey('Login')
>     DbSourceId = ForeignKey('Login')
>     ts = DateTimeCol()
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id70&alloc_id638&op=click


Thanks man. But i think i still have a problem. The "DbRequestId" and
the "DbSourceId" fields are linking to table Login. So, wouldn't 
DbRequestId and DbSourceId have to be renamed to Login? So, this is the 
point: i have two fields pointing to the same table. Take a look at my code:

from SQLObject import *

__connection__ = PostgresConnection('user=my_user dbname=my_user')

class Login(SQLObject):
     firstName = StringCol()
     middleInitial = StringCol(length=1, default=None)
     lastName = StringCol()
     addresses = MultipleJoin('db_request')

class db_request(SQLObject):
     new_file    = BoolCol(default=1)
     DbRequestId = ForeignKey('Login')
     DbSourceId  = ForeignKey('Login')

def reset():
     Login.dropTable(ifExists=True)
     Login.createTable()
     db_request.dropTable(ifExists=True)
     db_request.createTable()

reset()

p1 = Login.new(firstName='John', lastName='Doe')
p2 = Login.new(firstName='Cal', lastName='Robson')

r1 = db_request.new( new_file='t', DbRequestId=p1, DbSourceId=p2 )



-> And it reported the following error:

Traceback (most recent call last):
   File "tt.py", line 27, in ?
     r1 = db_request.new( new_file='t', DbRequestId=p1, DbSourceId=p2 )
   File "/usr/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 
907, in new
     inst._SO_finishCreate(id, connection=connection)
   File "/usr/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 
929, in _SO_finishCreate
     id, names, values)
   File "/usr/lib/python2.3/site-packages/SQLObject/DBConnection.py", 
line 163, in queryInsertID
     return self._runWithConnection(self._queryInsertID, table, idName, 
id, names, values)
   File "/usr/lib/python2.3/site-packages/SQLObject/DBConnection.py", 
line 74, in _runWithConnection
     val = meth(conn, *args)
   File "/usr/lib/python2.3/site-packages/SQLObject/DBConnection.py", 
line 629, in _queryInsertID
     q = self._insertSQL(table, names, values)
   File "/usr/lib/python2.3/site-packages/SQLObject/DBConnection.py", 
line 155, in _insertSQL
     return ("INSERT INTO %s (%s) VALUES (%s)" %
   File "/usr/lib/python2.3/site-packages/SQLObject/DBConnection.py", 
line 348, in sqlrepr
     return sqlrepr(v, self.dbName)
   File "/usr/lib/python2.3/site-packages/SQLObject/Converters.py", line 
177, in sqlrepr
     raise ValueError, "Unknown SQL builtin type: %s for %s" % \
ValueError: Unknown SQL builtin type: <class '__main__.Login'> for 
<Login 2 lastName='Robson' middleInitial=None firstName='Cal'>


Thanks in advance for all help, im a little confused about this and i 
really need it. : (

Re: [SQLObject] Hi guys. Please, I need help

[Scott R. <sc...@to...>]

On Wed, 2004-04-21 at 14:17, fingerclick wrote:
> I=B4d like to put in one table two references do a same table. Does =

> sqlObject allows that? Or may i build the sql in another way to
> make that works? db_login has the names and db_request has 2 fields tha=
t =

> point to db_login( these fields are the persons id ).Thanks in advance.=


Unless I misunderstand the problem, if you take the time to discover how
to add one reference field, it is the exact same process to add another.

class request(SQLObject):
    NewFile =3D BoolCol(default=3D1)
    DbRequestId =3D ForeignKey('Login')
    DbSourceId =3D ForeignKey('Login')
    ts =3D DateTimeCol()

[SQLObject] Hi guys. Please, I need help

[fingerclick <fc...@bo...>]

I´d like to put in one table two references do a same table. Does 
sqlObject allows that? Or may i build the sql in another way to
make that works? db_login has the names and db_request has 2 fields that 
point to db_login( these fields are the persons id ).Thanks in advance.

The tables:

CREATE TABLE db_login (
    id             SERIAL   PRIMARY KEY,
    login          TEXT     NOT NULL,
    password       TEXT     NOT NULL,
    ativo          BOOLEAN  DEFAULT true,
    name           TEXT
);

CREATE TABLE db_request (
    id             SERIAL      PRIMARY KEY,
    new_file       BOOLEAN     DEFAULT true,
    db_request_id  INTEGER     NOT NULL REFERENCES db_login (id),
    db_source_id   INTEGER     NOT NULL REFERENCES db_login (id),
    ts             TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Re: [SQLObject] connection URIs

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

jws...@ra... wrote:
> So this example from the docs would now look like what?
> 
> conn = DBConnection.PostgresConnection('yada')
> trans = conn.transaction()
> p = Person.get(1, trans)
> p.firstName = 'Bob'
> trans.commit()
> p.firstName = 'Billy'
> trans.rollback()

That would all be unchanged.  Well, the first line could be done 
differently, like conn = connectionFromURI('postgres://yadayada'), 
though it should work just like it is as well.

   Ian

RE: [SQLObject] connection URIs

[<jws...@ra...>]

So this example from the docs would now look like what?

conn = DBConnection.PostgresConnection('yada')
trans = conn.transaction()
p = Person.get(1, trans)
p.firstName = 'Bob'
trans.commit()
p.firstName = 'Billy'
trans.rollback()

-----Original Message-----
From: Ian Bicking [mailto:ia...@co...]
Sent: Friday, April 16, 2004 3:21 PM
To: Philippe Normand
Cc: sql...@li...
Subject: Re: [SQLObject] connection URIs


Philippe Normand wrote:
> On Fri, Apr 16, 2004 at 01:48:29PM -0400, jws...@ra...
wrote:
> 
>>I'm switching to 0.6 and it's not obvious how to setup the new style
URI's.
>>Do I need to assign the URI to __connection__ , 
>>_connection , or conn ?
> 
> Use __connection__ like in 0.5 .. Table._connection can also be used
> as before. The only thing which changed is the way to assign it:
> 
> __connection__ = dbconnection.connectionForURI(dbURI)

Yes, that's the best way to do it.  You can use a string alone in some 
places as well (e.g., __connection__ = dbURI), but I think I'm going to 
take that out because it can't be made to work everywhere you might 
specify a connection (while connectionForURI() will work).

   Ian

[SQLObject] MySQL/PostgreSQL inconsistencies

[Chris G. <ch...@il...>]

I found some inconsistencies with the way PostgreSQL and MySQL handle 
integer attributes.

I.e., you have this class:

class SO(SQLObject):
    num = IntCol()

If you were to make an instance of SO (call it so), and set the num 
attribute, MySQL would let you set it to a string or an int, while 
PostgreSQL only lets you set it to an int, and throws a confusing exception 
if you don't.

It would be best if IntCol let the user put in ints, strings, or decimal-
less floats (anything easily coercable to an int), and always spat out an 
int.

[SQLObject] Handling lost MySQL connections...

[Chris G. <ch...@il...>]

I was looking into fixing the problem with MySQL connections in the pool 
timing-out if you don't use SQLObject for a while (for example, with a 
long-running process that doesn't get constant activity). Here's an 
example of the problem:

  File "SQLObject\SQLObject.py", line 1228, in count
    count = conn.countSelect(self)
  File "SQLObject\DBConnection.py", line 177, in countSelect
    val = int(self.queryOne(q)[0])
  File "SQLObject\DBConnection.py", line 156, in queryOne
    return self._runWithConnection(self._queryOne, s)
  File "SQLObject\DBConnection.py", line 74, in _runWithConnection
    val = meth(conn, *args)
  File "SQLObject\DBConnection.py", line 149, in _queryOne
    c.execute(s)
  File "MySQLdb\cursors.py", line 95, in execute
    return self._execute(query, args)
  File "MySQLdb\cursors.py", line 114, in _execute
    self.errorhandler(self, exc, value)
  File "MySQLdb\connections.py", line 33, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (2013, 'Lost connection to MySQL server during query')


The obvious spot to try to catch the exception and try to reconnect is 
line 149 of DBConnection, where it does c.execute(s). Reconnecting isn't 
going to matter though, since you can't return the changed connection to 
the caller.

I was thinking that I could handle the exception by subclassing the 
connection object that MySQLdb.connect() returns, which would have an 
overloaded connection.execute(). It would have a try/except block around 
the superclass' .execute() statement, and be able to reconnect if it got 
OperationalError 2013.

Do you think adding another level to the class hierarchy is going to add 
too much overhead for the queries? It's probably not a very common bug, 
and maybe it should even be something that MySQLdb itself handles.