sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] bug or feature?

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

On Nov 26, 2003, at 8:09 AM, Emanuele Olivetti wrote:
> Hi,
> first of all I'd like to say that SQLObject is a beautiful module
> and I'm using it with with very interesting results (with SQLite
> on a Linux box).
>
> I tried to run this, to try many-to-many relations:
> ---
> from SQLObject import *
>
> __connection__=SQLiteConnection('bug.sqlite', debug=1)
>
> class A(SQLObject):
>     b=RelatedJoin('B')
>
> class B(SQLObject):
>     a=RelatedJoin('A')
>
> A.createTable(ifNotExists=True)
> B.createTable(ifNotExists=True)
>
> a=A.new()
> b=B.new()
> ---
>
> I got an:
> "_sqlite.DatabaseError: near ")": syntax error"
> due to this SQL command:
> "INSERT INTO a () VALUES ()"
>
> Is it a pysqlite problem or SQLObject problem?

A SQLObject problem I suppose, though it's because you have trivial 
classes.  The only column in your A and B tables is the ID (which 
SQLite generates automatically on insert).  So there's no values to 
insert, and there's no way to express an insert that doesn't insert 
anything (strictly speaking, it inserts an ID, but because it is 
implicit it doesn't show up in the SQL, and so the SQL is invalid).

Add some columns to your classes and you should be okay.  They won't be 
useful until you do that anyway.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] Arbitrary SQL statement

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

On Nov 25, 2003, at 7:59 PM, Mike Moum wrote:
> Is it possible to execute an arbitrary SQL statement from within SQL 
> Object?
> I know that select statements are possible, but how about any legal SQL
> statement?

Well, you can always do __connection__.query(sql).  But you do have to 
be concerned about cache consistency.  If you are just doing complex 
select statements it's not a problem -- if you are updating or deleting 
rows you may invalidate existing SQLObject instances (but those 
instances won't know about it, so you have a consistency problem).

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

[SQLObject] bug or feature?

[Emanuele O. <oli...@it...>]

Hi,
first of all I'd like to say that SQLObject is a beautiful module
and I'm using it with with very interesting results (with SQLite
on a Linux box).

I tried to run this, to try many-to-many relations:
---
from SQLObject import *

__connection__=SQLiteConnection('bug.sqlite', debug=1)

class A(SQLObject):
    b=RelatedJoin('B')

class B(SQLObject):
    a=RelatedJoin('A')

A.createTable(ifNotExists=True)
B.createTable(ifNotExists=True)

a=A.new()
b=B.new()
---

I got an:
"_sqlite.DatabaseError: near ")": syntax error"
due to this SQL command:
"INSERT INTO a () VALUES ()"

Is it a pysqlite problem or SQLObject problem?

In the following you can find the complete log (debug=1).

Cheers,

						Emanuele

--- log transcript ---
/tmp 143>python bug-many-to-many.py
 1/QueryOne:  SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name = 'a'
 1/COMMIT  :  auto
 1/Query   :  CREATE TABLE a (
    id INTEGER PRIMARY KEY
)
 1/COMMIT  :  auto
 1/QueryOne:  SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name = 'a_b'
 1/COMMIT  :  auto
 1/Query   :  CREATE TABLE a_b (
a_id INT NOT NULL,
b_id INT NOT NULL
)
 1/COMMIT  :  auto
 1/QueryOne:  SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name = 'b'
 1/COMMIT  :  auto
 1/Query   :  CREATE TABLE b (
    id INTEGER PRIMARY KEY
)
 1/COMMIT  :  auto
 1/QueryIns:  INSERT INTO a () VALUES ()
Traceback (most recent call last):
  File "bug-many-to-many.py", line 14, in ?
    a=A.new()
  File "/opt/python2.3/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 883, in new
    inst._SO_finishCreate(id)
  File "/opt/python2.3/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 905, in _SO_finishCreate
    id, names, values)
  File "/opt/python2.3/lib/python2.3/site-packages/SQLObject/DBConnection.py", line 160, in queryInsertID
    return self._runWithConnection(self._queryInsertID, table, idName, id, names, values)
  File "/opt/python2.3/lib/python2.3/site-packages/SQLObject/DBConnection.py", line 72, in _runWithConnection
    val = meth(conn, *args)
  File "/opt/python2.3/lib/python2.3/site-packages/SQLObject/DBConnection.py", line 717, in _queryInsertID
    c.execute(q)
  File "/opt/python2.3/lib/python2.3/site-packages/sqlite/main.py", line 244, in execute
    self.rs = self.con.db.execute(SQL)
_sqlite.DatabaseError: near ")": syntax error
-----------------------------------------------------------------

[SQLObject] Arbitrary SQL statement

[Mike M. <mm...@wo...>]

Hello,

Is it possible to execute an arbitrary SQL statement from within SQL Object?  
I know that select statements are possible, but how about any legal SQL 
statement?

TIA,
Mike
-- 
the Moum's (Mike, Dede, Suzanne, Jeff, Kristen)
Tipp City, Ohio, USA
Visit the Baha'i World web site at www.bahai.org
Visit the U.S. Baha'i web site at www.us.bahai.org

[SQLObject] Re: trapping sets and gets in SQLObject

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

On Nov 20, 2003, at 1:02 PM, Aaron Swartz wrote:
> I'm working on a toolkit for building database-backed websites using 
> SQLObject. One of the things I want to do is support static rendering, 
> so rendered pages could be stored on disk as HTML files and served up 
> normally until they were changed. I want to do this by trapping all 
> sets and gets in SQLObject:
>
> When a page gets rendered it notes down every get the page makes and 
> stores that info away. Then, when another page makes a set it sees if 
> that set affects any rendered pages and, if so, re-renders them.
>
> Anyway, I see you can trap the setters and getters for attributes 
> pretty easily but I was wondering if there was a way to tap into all 
> database sets and gets. (If nothing else, I guess I can just create an 
> SQLObject subclass with a nosy getattr and setattr.)

If you want to trap sets you can override, I think, _SO_setValue() and 
set() -- all database sets go through one or the other -- maybe 
sometimes both if you override a _set_ method, but I'm not sure if I 
changed that or not.

For gets it's a little more complicated, but maybe that's not 
necessary.  An instance can be initialized with column values several 
ways.  I think _SO_selectInit should catch most of them, and maybe 
there's a _SO_getValue for when you have _cacheValues = False.

You can also fiddle with the makeProperties function, which catches all 
the getters and setters.  __getattr__ and __setattr__ won't work for 
you, but __getattribute__ and __setattribute__ will, if you want to be 
particularly general about setters.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] BUG: ForeignKey in another database

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

On Nov 19, 2003, at 10:27 AM, J-P Lee wrote:
> OK, I've narrowed this down.  It works fine when accessing the records 
> directly using Notes(1).user.name.  However, when iterating over a 
> query result, it fails.
>
> for rec in Notes.select():
>    print rec.user.name
>
> I'm looking at the differences between the versions.  With the latest 
> release, conn is being passed along whereas in the previous release 
> it's retrieved from the instance.  I assume all this connection 
> passing was added to resolve transaction problems.  Is there a way to 
> make iterSelect work using 2 databases?

Mmm... I guess it was an unintended feature of 0.4 that you could do 
cross-database joins.  I'm guessing you could do:

for rec in Notes.select():
     user = User(rec.userID)
     print user.name

or:

class Notes(SQLObject):
     ....
     def _get_user(self):
         return User(self.userID)

Probably the best way to handle this would be to separate transactions 
from databases and connections.  That is, you'd have one transactional 
context, which would potentially connect to multiple databases, and 
multiple specific database connections.  Then you'd attach classes to a 
database, and this attachment would be fairly strong (stronger than 
currently, where now you can suddenly "move" an instance to another 
database by passing in a connection= parameter).  Anyway, that's what 
should happen to SQLObject in the future, and it might produce cleaner 
code, because transactions are kind of wonky right now.  But for now a 
workaround will have to do.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] BUG: ForeignKey in another database

[J-P L. <sql...@si...>]

OK, I've narrowed this down.  It works fine when accessing the records 
directly using Notes(1).user.name.  However, when iterating over a query 
result, it fails.

for rec in Notes.select():
    print rec.user.name

I'm looking at the differences between the versions.  With the latest 
release, conn is being passed along whereas in the previous release it's 
retrieved from the instance.  I assume all this connection passing was 
added to resolve transaction problems.  Is there a way to make 
iterSelect work using 2 databases?


Ian Bicking wrote:

> Hmm... I'm not really sure.  It sounds like you haven't created the 
> table like you should have.  Though I also feel like "user" is a 
> reserved word, so it shouldn't even work anyway.  To test -- what 
> happens when you do __connection__.queryAll('SELECT * FROM user')?

Re: [SQLObject] Return Of The Undead __len__ Method

[Frank B. <fb...@fo...>]

Hallo,
Ian Bicking hat gesagt: // Ian Bicking wrote:

> Looks like anon CVS is still out of date.  The sad thing is it's  
> "fixed" from SF's point of view (they were able to get the delay down  
> to 5 hours instead of a day)... of course, this is more than five  
> hours, to be sure, but they've been having other problems as well.  I  
> wish them luck.
> 
> Really I wish they'd just allow people to rsync CVS HEAD or something  
> -- that's all people really want to get anonymously anyway.  It's  
> certainly the only part that needs to be timely.

Do you know, if it is possible to un-anonymously checkout (SQLObject)
from SF? I tried my SF username with the pserver- and the ext-method,
but this seems to require write access to the repository, which I
don't have or want. 

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Return Of The Undead __len__ Method

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

On Nov 18, 2003, at 10:48 AM, Frank Barknecht wrote:
> Hallo,
>
> is it me or does current CVS still have a __len__ method on
> SelectResults?
>
> I also checked here:  
> http://cvs.sourceforge.net/viewcvs.py/sqlobject/SQLObject/SQLObject/ 
> SQLObject.py?rev=1.63&view=auto
>
> It still seems to live...

Looks like anon CVS is still out of date.  The sad thing is it's  
"fixed" from SF's point of view (they were able to get the delay down  
to 5 hours instead of a day)... of course, this is more than five  
hours, to be sure, but they've been having other problems as well.  I  
wish them luck.

Really I wish they'd just allow people to rsync CVS HEAD or something  
-- that's all people really want to get anonymously anyway.  It's  
certainly the only part that needs to be timely.

The version in my repository, which should be the one in 0.5.1, is  
1.64, and it has __len__ removed.  The almost-live CVS should also be  
up-to-date.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] BUG: ForeignKey in another database

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

On Nov 17, 2003, at 5:59 PM, J-P Lee wrote:
> Hi All,
>
> I just switched from v0.4 to v0.51 and my app broke. It looks like=20
> there's a bug in the current release. =A0 I'm doing this:
>
> Common.py
> __connection__ =3D PostgresConnection('dbname=3Dcommon ...')
> class User(SQLObject):
> =A0=A0=A0 name =3D StringCol()
> =A0=A0=A0 ...
>
> App.py
> __connection__ =3D PostgresConnection('dbname=3Dapp ...')
> from Common import User
>
> class Notes(SQLObject):
> =A0=A0=A0 ...
> =A0=A0=A0 user =3D ForeignKey('User')
> =A0=A0=A0 ...
>
> This use to work and I use to be able to do Notes(1).user.name.=A0 Now =
I=20
> get the following error:
>
> Traceback (most recent call last):
> =A0 File "../../external/lib/python/SQLObject/SQLObject.py", line 815,=20=

> in _SO_foreignKey
> =A0 File "../../external/lib/python/SQLObject/SQLObject.py", line 407,=20=

> in __new__
> =A0 File "../../external/lib/python/SQLObject/SQLObject.py", line 667,=20=

> in _init
> =A0 File "../../external/lib/python/SQLObject/DBConnection.py", line=20=

> 306, in _SO_selectOne
> =A0 File "../../external/lib/python/SQLObject/DBConnection.py", line=20=

> 149, in queryOne
> =A0 File "../../external/lib/python/SQLObject/DBConnection.py", line =
72,=20
> in _runWithConnection
> =A0 File "../../external/lib/python/SQLObject/DBConnection.py", line=20=

> 142, in _queryOne
> psycopg.ProgrammingError: ERROR:=A0 Relation "user" does not exist
>
> SELECT name, ... FROM user WHERE id =3D xx
>
> Is there another way to do this?

Hmm... I'm not really sure.  It sounds like you haven't created the=20
table like you should have.  Though I also feel like "user" is a=20
reserved word, so it shouldn't even work anyway.  To test -- what=20
happens when you do __connection__.queryAll('SELECT * FROM user')?

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

[SQLObject] Return Of The Undead __len__ Method

[Frank B. <fb...@fo...>]

Hallo,

is it me or does current CVS still have a __len__ method on
SelectResults?

I also checked here: http://cvs.sourceforge.net/viewcvs.py/sqlobject/SQLObject/SQLObject/SQLObject.py?rev=1.63&view=auto

It still seems to live...

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Some Postgres bits

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

On Nov 18, 2003, at 5:06 AM, Frank Barknecht wrote:
> Hallo,
>
> I may need case insensitive search in PostgreSQL. "ILIKE" is the case
> insensitive "LIKE" in Postgres. Is it somehow possible to inherit and
> extend SQLBuilder to present an ILIKE() method (and maybe matching
> starts/endswiths)?

If you look at SQLBuilder.LIKE, it should be pretty clear how to write 
an ILIKE function.

> Slightly related but also quite off-topic: Importing a CSV-file into a
> Postgres-table managed by SQLObject gives "pkey index exists" errors
> on the id-column (because the csv contains ids that are already
> present and actually should to be updated). The only solution I found
> for this is to empty the table with "delete from X" before doing the
> "copy from STDIN" import. Does anyone know a nicer way to do this?

I think MySQL has something like REPLACE (DELETE or INSERT depending on 
existence of a key).  There's other DB-specific ways to do the same 
thing, maybe something like that exists for Postgres.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

[SQLObject] Some Postgres bits

[Frank B. <fb...@fo...>]

Hallo,

I may need case insensitive search in PostgreSQL. "ILIKE" is the case
insensitive "LIKE" in Postgres. Is it somehow possible to inherit and
extend SQLBuilder to present an ILIKE() method (and maybe matching
starts/endswiths)?

Slightly related but also quite off-topic: Importing a CSV-file into a
Postgres-table managed by SQLObject gives "pkey index exists" errors
on the id-column (because the csv contains ids that are already
present and actually should to be updated). The only solution I found
for this is to empty the table with "delete from X" before doing the
"copy from STDIN" import. Does anyone know a nicer way to do this?

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

[SQLObject] BUG: ForeignKey in another database

[J-P L. <sql...@si...>]

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body>
Hi All,<br>
<br>
I just switched from v0.4 to v0.51 and my app broke. It looks like
there's a bug in the current release. &nbsp; I'm doing this:<br>
<br>
<u>Common.py<br>
</u>__connection__ = PostgresConnection('dbname=common ...')<br>
class User(SQLObject):<br>
&nbsp;&nbsp;&nbsp; name = StringCol()<br>
&nbsp;&nbsp;&nbsp; ...<br>
<br>
<u>App.py</u><br>
__connection__ = PostgresConnection('dbname=app ...')<br>
from Common import User<br>
<br>
class Notes(SQLObject):<br>
&nbsp;&nbsp;&nbsp; ...<br>
&nbsp;&nbsp;&nbsp; user = ForeignKey('User')<br>
&nbsp;&nbsp;&nbsp; ...<br>
<br>
This use to work and I use to be able to do Notes(1).user.name.&nbsp; Now I
get the following error:<br>
<br>
Traceback (most recent call last):<br>
&nbsp; File "../../external/lib/python/SQLObject/SQLObject.py", line 815, in
_SO_foreignKey<br>
&nbsp; File "../../external/lib/python/SQLObject/SQLObject.py", line 407, in
__new__<br>
&nbsp; File "../../external/lib/python/SQLObject/SQLObject.py", line 667, in
_init<br>
&nbsp; File "../../external/lib/python/SQLObject/DBConnection.py", line 306,
in _SO_selectOne<br>
&nbsp; File "../../external/lib/python/SQLObject/DBConnection.py", line 149,
in queryOne<br>
&nbsp; File "../../external/lib/python/SQLObject/DBConnection.py", line 72,
in _runWithConnection<br>
&nbsp; File "../../external/lib/python/SQLObject/DBConnection.py", line 142,
in _queryOne<br>
psycopg.ProgrammingError: ERROR:&nbsp; Relation "user" does not exist<br>
<br>
SELECT name, ... FROM user WHERE id = xx<br>
<br>
Is there another way to do this?<br>
<br>
Thanks,<br>
<br>
J-P<br>
</body>
</html>

Re: [SQLObject] Re: SQLObject ways of doing things

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

On Nov 17, 2003, at 3:30 AM, po...@nl... wrote:
> Thanks for your reply. Couple of comments:
> +++++++++++++++
>> * Update control. The existing system passes Mysql timestamp (which
>> are automatically updated on every database update) columns around to
>> avoid update leapfrogging (updated by another user) -- the generated
>> sql is always of type:
>>
>> update x set a=b where id=y and lastupdated=20030101010101
>>
>> passing the cursor response back as basis for a confirmation or
>> warning to the user.
>>
>> Is there an easy way to do this in SQLObject, or could it be put in
>> (perhaps via _updatecontrolfield and _updatecontrolvalue variables)?
>
> No, this doesn't really exist.  Lots of people want it though
> (optimistic locking).  I'm certainly open to implementations, but I'm
> not working on it myself.
> +++++++++++++++
>
> Couldn't this be as simple as something like the following two lines 
> before sending any update sql to the database:
>
> if hasattr(self,'_updateconttrolfield') :
>     updatesqlstring = "%s AND %s = '%s'"%(updatesqlstring,
>                              self._updatecontrolfield,
>                              getattr(self,'_updatecontrolvalue',None)
>
> Up to the user to set appropriate values. This could also be used to 
> prevent unintended updates (by setting intentionally "wrong" values).

Well, it gets a bit more complicated because DBConnection handles 
making all the SQL, and SQLObject instances only communicate abstractly 
with the connection to send queries.  But it could be mostly like this. 
  It should probably take the form of a special Col attribute on the 
SQLObject class.

Then there's other details -- like you want to throw an exception if 
the update fails, so you have to test for that.  But that would 
probably be mostly it.  And of course you have to get the timestamp to 
start with, and consider how and when the timestamp can be updated.

> ++++++++++++++++
>> * A generic afterUpdate function that can be overridden -- called each
>> time something is changed in the database (used to tidy up non-column
>> variables, issue a self.expire() to force re-lookup of column values,
>> getting the new timestamp for any further updates, etc.).
>
> We actually don't do an expire after an update, on the assumption we
> already know what our new column values are.  If you have dependent
> non-column variables, overriding _set_* as necessary may be sufficient
> to keep them in sync.  Otherwise you'd have to put in a hook in
> _SO_setValue and set.
> +++++++++++++++
>
> We don't know the new database column values if there are 
> automatically calculated columns like the timestamp column in the 
> previous point (or, surely, if an sql function has been used?). I 
> would have thought some sort of refresh (or option to do that) was 
> pretty important after an update.

In the case of timestamp columns, I think those are best to preallocate 
(e.g., by calling time.time()), instead of simply assigning them to 
NOW() and then re-querying the database.  SQLObject otherwise doesn't 
expect any columns to be calculated.  It's better to do that in the 
Python code.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

[SQLObject] Re: SQLObject ways of doing things

[<po...@nl...>]

Thanks for your reply. Couple of comments:

+++++++++++++++
> * Update control. The existing system passes Mysql timestamp (which =

> are automatically updated on every database update) columns around to =

> avoid update leapfrogging (updated by another user) -- the generated =

> sql is always of type:
>
> update x set a=3Db where id=3Dy and lastupdated=3D20030101010101
>
> passing the cursor response back as basis for a confirmation or =

> warning to the user.
>
> Is there an easy way to do this in SQLObject, or could it be put in =

> (perhaps via _updatecontrolfield and _updatecontrolvalue variables)?

No, this doesn't really exist.  Lots of people want it though =

(optimistic locking).  I'm certainly open to implementations, but I'm =

not working on it myself.
+++++++++++++++

Couldn't this be as simple as something like the following two lines before=
 sending any update sql to the database:

if hasattr(self,'_updateconttrolfield') :
    updatesqlstring =3D "%s AND %s =3D '%s'"%(updatesqlstring,
                             self._updatecontrolfield,
                             getattr(self,'_updatecontrolvalue',None)

Up to the user to set appropriate values. This could also be used to preven=
t unintended updates (by setting intentionally "wrong" values).



++++++++++++++++
> * A generic afterUpdate function that can be overridden -- called each =

> time something is changed in the database (used to tidy up non-column =

> variables, issue a self.expire() to force re-lookup of column values, =

> getting the new timestamp for any further updates, etc.).

We actually don't do an expire after an update, on the assumption we =

already know what our new column values are.  If you have dependent =

non-column variables, overriding _set_* as necessary may be sufficient =

to keep them in sync.  Otherwise you'd have to put in a hook in =

_SO_setValue and set.
+++++++++++++++

We don't know the new database column values if there are automatically cal=
culated columns like the timestamp column in the previous point (or, surely=
, if an sql function has been used?). I would have thought some sort of ref=
resh (or option to do that) was pretty important after an update. =


Rgds

Paul Mothersdill

Re: [SQLObject] SQLObject ways of doing things.....

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

On Nov 16, 2003, at 12:39 PM, po...@nl... wrote:
> I'm looking at using SQLObject as a replacement for a similar 
> structure (now a little untidy) in an existing database system. I've 
> run into the following points and would appreciate comments -- there 
> may be ways of doing this with SQLObject that I've overlooked, or 
> these may be things that could be useful additional features. The 
> basic structure is a system-wide sub-class of SQLObject, which is in 
> turn subclassed for each database table.
>
> * Hooks for generic column-level override of _set_ and _get_ -- 
> something like a "beforeWrite" function that can be overridden:
>
> def beforeWrite(self, columnName, value)
>     code to produce newValue
>     self._set_columnName(newValue)
>
> similar for afterRead.

There's no documented way to do this.  _SO_setValue and set both do 
updates directly -- if you override both these methods to call 
beforeWrite, you'd have it.

_SO_selectInit is called after a read, generally.  It will be called 
after an expire() call, but I think it can also be called other times 
-- when you do a select() it will fetch column values since it doesn't 
know if the object exists or not.  I can't remember if _SO_selectInit 
gets called in that case.

> Reason: the existing system has quite a lot of logic for this type of 
> stuff in the central class based on the column name and/or type. To 
> write out individual overrides for each column in each derived class 
> would involve a lot of decentralised code.
>
> * Update control. The existing system passes Mysql timestamp (which 
> are automatically updated on every database update) columns around to 
> avoid update leapfrogging (updated by another user) -- the generated 
> sql is always of type:
>
> update x set a=b where id=y and lastupdated=20030101010101
>
> passing the cursor response back as basis for a confirmation or 
> warning to the user.
>
> Is there an easy way to do this in SQLObject, or could it be put in 
> (perhaps via _updatecontrolfield and _updatecontrolvalue variables)?

No, this doesn't really exist.  Lots of people want it though 
(optimistic locking).  I'm certainly open to implementations, but I'm 
not working on it myself.

> * A generic afterUpdate function that can be overridden -- called each 
> time something is changed in the database (used to tidy up non-column 
> variables, issue a self.expire() to force re-lookup of column values, 
> getting the new timestamp for any further updates, etc.).

We actually don't do an expire after an update, on the assumption we 
already know what our new column values are.  If you have dependent 
non-column variables, overriding _set_* as necessary may be sufficient 
to keep them in sync.  Otherwise you'd have to put in a hook in 
_SO_setValue and set.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

[SQLObject] SQLObject ways of doing things.....

[<po...@nl...>]

I'm looking at using SQLObject as a replacement for a similar structure (no=
w a little untidy) in an existing database system. I've run into the follow=
ing points and would appreciate comments -- there may be ways of doing this=
 with SQLObject that I've overlooked, or these may be things that could be =
useful additional features. The basic structure is a system-wide sub-class =
of SQLObject, which is in turn subclassed for each database table.

* Hooks for generic column-level override of _set_ and _get_ -- something l=
ike a "beforeWrite" function that can be overridden:

def beforeWrite(self, columnName, value)
    code to produce newValue
    self._set_columnName(newValue)

similar for afterRead.

Reason: the existing system has quite a lot of logic for this type of stuff=
 in the central class based on the column name and/or type. To write out in=
dividual overrides for each column in each derived class would involve a lo=
t of decentralised code.

* Update control. The existing system passes Mysql timestamp (which are aut=
omatically updated on every database update) columns around to avoid update=
 leapfrogging (updated by another user) -- the generated sql is always of t=
ype:  =


update x set a=3Db where id=3Dy and lastupdated=3D20030101010101

passing the cursor response back as basis for a confirmation or warning to =
the user.

Is there an easy way to do this in SQLObject, or could it be put in (perhap=
s via _updatecontrolfield and _updatecontrolvalue variables)?

* A generic afterUpdate function that can be overridden -- called each time=
 something is changed in the database (used to tidy up non-column variables=
, issue a self.expire() to force re-lookup of column values, getting the ne=
w timestamp for any further updates, etc.).

Re: [SQLObject] IntCol not working - desparately need help

[Mike M. <mm...@wo...>]

On Friday 14 November 2003 11:31 am, Ian Bicking wrote:

Thank you both for prompt and accurate replies.  Approximately 1-3/4 seconds 
after waking up this morning, it occured to me that "index" is indeed a 
reserved word in SQL, which goes to confirm one more time that continuing to 
beat one's head against the wall late at night is futile and painful.  
Unfortunately, that doesn't stop me from doing it, though.

Also, thanks for not rubbing my nose in it.

Mike
> On Nov 14, 2003, at 2:10 AM, Randall Randall wrote:
> > The word "index" is reserved in MySQL.
> >
> > You should use
> > myIndex = IntCol()
> > if you have to have a separate index.
>
> Good catch.  You can also do
>      index = IntCol(colName="person_index")
>
> --
> Ian Bicking | ia...@co... | http://blog.ianbicking.org

-- 
the Moum's (Mike, Dede, Suzanne, Jeff, Kristen)
Tipp City, Ohio, USA
Visit the Baha'i World web site at www.bahai.org
Visit the U.S. Baha'i web site at www.us.bahai.org

[SQLObject] How to show a bitmap from SQL?

[S.Holmgren <ste...@ms...>]

Hi, I'm new to SQL. picture loaded in a BLOB record...
Trying to do something like this:

         mydb = MySQLdb.Connect(db='test')
         cursor = mydb.cursor()
         stmt = "select Picture, Nickname from Friends where Id=1"
         cursor.execute(stmt)
         resultSet = cursor.fetchall()
         for p, n in resultSet:
             pic =  wxImage(p[0],wxBITMAP_TYPE_BMP)
             self.pic1.SetBitmap(pic)
             ......
             ......
             ......
         mydb.close()

Error: Can't load image from file:ÿ......

/Holmis

Re: [SQLObject] IntCol not working - desparately need help

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

On Nov 14, 2003, at 2:10 AM, Randall Randall wrote:
> On Friday, November 14, 2003, at 02:04 AM, Mike Moum wrote:
>
>> Hi,
>>
>> I have the following test code:
>>
>> from SQLObject import *
>> import MySQLdb
>>
>> conn = MySQLdb.connect()
>> conn.cursor().execute('create database mike')
>> conn.close()
>>
>> compcode = 'mike'
>> conn = MySQLConnection(user = '', db = 'mike')
>>
>> class Person(SQLObject):
>>     _connection = conn
>>     firstName = StringCol(length = 20, default = None)
>>     lastName = StringCol(length = 20, default = None)
>>     index = IntCol()
>
> The word "index" is reserved in MySQL.
>
> You should use
> myIndex = IntCol()
> if you have to have a separate index.

Good catch.  You can also do
     index = IntCol(colName="person_index")

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] IntCol not working - desparately need help

[Randall R. <ra...@ra...>]

On Friday, November 14, 2003, at 02:04 AM, Mike Moum wrote:

> Hi,
>
> I have the following test code:
>
> from SQLObject import *
> import MySQLdb
>
> conn = MySQLdb.connect()
> conn.cursor().execute('create database mike')
> conn.close()
>
> compcode = 'mike'
> conn = MySQLConnection(user = '', db = 'mike')
>
> class Person(SQLObject):
>     _connection = conn
>     firstName = StringCol(length = 20, default = None)
>     lastName = StringCol(length = 20, default = None)
>     index = IntCol()

The word "index" is reserved in MySQL.

You should use
myIndex = IntCol()
if you have to have a separate index.

However, probably you could use the SQLObject 'id'
column for this, which is created automatically
for you:

class Person(SQLObject):
   _connection = conn
   firstName = StringCol(length=20, default=None)
   lastName = StringCol(length=20, defaultNone)

 >>> p = Person.new()
 >>> print p.id
1

--
Randall Randall
ra...@ra...

[SQLObject] IntCol not working - desparately need help

[Mike M. <mm...@wo...>]

Hi,

I have the following test code:

from SQLObject import *
import MySQLdb

conn = MySQLdb.connect()
conn.cursor().execute('create database mike')
conn.close()

compcode = 'mike'
conn = MySQLConnection(user = '', db = 'mike')

class Person(SQLObject):
    _connection = conn
    firstName = StringCol(length = 20, default = None)
    lastName = StringCol(length = 20, default = None)
    index = IntCol()
    
    
Person.createTable(ifNotExists = True)

p = Person.new(firstName = 'Mike', lastName = 'Moum')
p = Person.new(firstName = 'Dean', lastName = 'Paque')


< end code >

When I try to run it, I get:

[root@localhost test]# python sqlobjtest.py
Traceback (most recent call last):
  File "sqlobjtest.py", line 18, in ?
    Person.createTable(ifNotExists = True)
  File "/usr/local/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 
965, in createTable
    cls._connection.createTable(cls)
  File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py", 
line 271, in createTable
    self.query('CREATE TABLE %s (\n%s\n)' % \
  File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py", 
line 123, in query
    return self._runWithConnection(self._query, s)
  File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py", 
line 72, in _runWithConnection
    val = meth(conn, *args)
  File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py", 
line 120, in _query
    conn.cursor().execute(s)
  File "/usr/local/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, 
in execute
    return self._execute(query, args)
  File "/usr/local/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, 
in _execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python2.3/site-packages/MySQLdb/connections.py", line 
33, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL 
syntax.  Check the manual that corresponds to your MySQL server version for 
the right syntax to use near 'INT\n)' at line 5")
[root@localhost test]#


When I comment out the IntCol field in the Person class, all is well.
Please help!  Am working on a program.

Thanks,
Mike
-- 
the Moum's (Mike, Dede, Suzanne, Jeff, Kristen)
Tipp City, Ohio, USA
Visit the Baha'i World web site at www.bahai.org
Visit the U.S. Baha'i web site at www.us.bahai.org

Re: [SQLObject] Reversed ordering, again

[Randall R. <ra...@ra...>]

On Wednesday, November 12, 2003, at 12:07 PM, Ian Bicking wrote:

> On Nov 11, 2003, at 12:11 AM, Randall Randall wrote:
>> It may be that these are considered bugs, or simply weren't
>> yet implemented as documented; I don't know.  The lack of
>> response the other day suggests the latter. :)
>
> I think it was one of those 
> I-meant-to-look-at-it-more-closely-when-I-had-some-time-then-I-forgot 
> situations.  There was just a missing "return" in reversed().

Well, thanks for the fix! :)

> It should be fixed now in CVS.

...or in 0.5.1, from your announcement.

--
Randall Randall
ra...@ra...

[SQLObject] Re: SQL Object's scalability

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

On Nov 12, 2003, at 2:30 PM, Scott Chapman wrote:
>> Hmm... Does anyone else have thoughts on this?  Basically it's an
>> issue that the query "SELECT %s FROM %s WHERE oid = %s" % (idName,
>> table, c.lastoid()) may not be using an index on the oid= portion,
>> which would be problematic.
>
> It's also an issue where not all tables have OID's these days.
>
> I posted a followup question on the PostgreSQL mailing list about the
> need for the front end (SQLObject in this case) to know "too much"
> about the database's structure and asked if there's an easy way to fix
> this.  So far no good answers appear.
>
>> At one time SQLObject used nextval to pre-allocate the ID, then did
>> the insert (which is what Firebird does now, and what Oracle and
>> Sybase would do).  The disadvantage of this is that you needed to
>> know the name of the sequence, which would sometimes vary (and maybe
>> there were truncation problems).  The disadvantage with the current
>> scheme (besides the index) is that you need to define your ID field
>> as "DEFAULT nextval('somesequence')" (or do that implicitly through
>> SERIAL).
>
> What do you mean "truncation problems"?

That names get truncated to 32 characters, which can more easily hit 
with SERIAL's names (table_id_name_seq, e.g., 
long_table_name_long_table_name_id_seq).  Things still mostly work as 
long as the first 32 characters are unique (which they generally are), 
but it can get awkward.  So you might instead want to use a sequence 
name like long_table_name_seq, but then you can't as easily predict the 
sequence name.

>> The current scheme is convenient, but I don't know if it's really
>> worth it.  Should we go back to preallocating an ID through a
>> nextval(sequence)?
>
> I see this as the only way to do it effectively as things are now.  
> This
> is not a very elegant solution but that's the way it goes.
>
> The convenience could be made the same for an end-user of the 
> SQLObject,
> perhaps.  You could also make it so the user had more than one option
> here. (I am very fond of user-togglable in cases like this).

At least there should be something like an _idSequence option, so you 
can identify the sequence you want to use.  That's probably good enough 
-- easy to explain, and the only real requirement it places on the user 
is that there must be some sequence (unless you always give explicit 
IDs on row creation).

>> Really, to facilitate non-integer IDs, it would be nice if we could
>> define a generic ID generator (which may or may not access the
>> database).  But I'm not really clear about all the use cases for
>> that, so it's probably not a good idea to generalize that yet.
>
> A generic ID generator in the front end (not the database itself)?  
> That
> sounds like a Bad Thing, in that you'd better be SURE that no other
> application inserts anything into the database because it won't have
> access to your generator to generate a unique ID.

Yeah, I'm not really sure how it might work, since I'm not sure about 
the use cases.  If you just want to be explicit about the key (e.g., 
use username as the primary key) then that's not so big a deal, you 
just pass it in as part of new().  In other instances you might query 
something else -- like another sequence-like-object in the database, or 
a shared sequence, or an ID generator from another system.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org