sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] SQLObject Performance

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

Colin Stewart wrote:
> Hi,
> 
> I've just started using SQLObject in a program that loads web server log 
> information into a MySQL database.  The program consists of a tight loop 
> that reads a web server entry, parses the data, performs one SQL lookup, 
> and then inserts the data into a table.
> 
> When I use the MySQL libraries directly the program handles ~290 rec/s.  
> When I use SQLObject to perform the lookup and SQL insert the rate drops 
> to 60 rec/s.

For that use case, it's hard to say.  Really the value of an ORM 
decreases when you are dealing with large datasets, especially when 
dealing with that data as a collection, like you would with web server 
logs.  You're really going to want to deal with that data inside MySQL, 
not in Python -- e.g., to get a hit count, you'll want to run the 
appropriate SQL command, not load the rows and count them in Python. 
You can do a count in SQLObject, but there's lots of aggregate functions 
that you can't do, so you'll hit a wall.

> Is it unrealistic to use SQLObject for DB interaction when handling 
> batch loads of data?  I've done a quick profile of the code (top few 
> calls below) and nothing jumps out as being particularly easy to optimise...
> 
>    ncalls  tottime  percall  cumtime  percall filename:lineno(function)
> 15308/14042    2.500    0.000    4.430    0.000 converters.py:179(sqlrepr)

It's interesting that sqlrepr is at the top.  I'll have to think about 
how I'm using it.  It's also been suggested that SQLObject rely on the 
database driver's quoting instead of doing its own.  This may lend more 
weight to that opinion.

>      1210    2.310    0.002    3.830    0.003 main.py:755(set)
>      3630    2.140    0.001    3.700    0.001 cursors.py:166(__do_query)
>     14042    1.370    0.000    5.800    0.000 dbconnection.py:438(sqlrepr)
>     23244    1.240    0.000    1.240    0.000 main.py:1176(instanceName)
>      3419    0.830    0.000   15.820    0.005 dbconnection.py:114(_runWithConnection)
>     14253    0.810    0.000    0.810    0.000 converters.py:81(lookupConverter)
>      1210    0.800    0.001    1.510    0.001 main.py:812(_SO_selectInit)

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

[SQLObject] SQLObject Performance

[Colin S. <co...@ow...>]

Hi,

I've just started using SQLObject in a program that loads web server log
information into a MySQL database.  The program consists of a tight loop
that reads a web server entry, parses the data, performs one SQL lookup,
and then inserts the data into a table.

When I use the MySQL libraries directly the program handles ~290 rec/s. 
When I use SQLObject to perform the lookup and SQL insert the rate drops
to 60 rec/s.

Is it unrealistic to use SQLObject for DB interaction when handling
batch loads of data?  I've done a quick profile of the code (top few
calls below) and nothing jumps out as being particularly easy to
optimise...


   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
15308/14042    2.500    0.000    4.430    0.000 converters.py:179(sqlrepr)
     1210    2.310    0.002    3.830    0.003 main.py:755(set)
     3630    2.140    0.001    3.700    0.001 cursors.py:166(__do_query)
    14042    1.370    0.000    5.800    0.000 dbconnection.py:438(sqlrepr)
    23244    1.240    0.000    1.240    0.000 main.py:1176(instanceName)
     3419    0.830    0.000   15.820    0.005 dbconnection.py:114(_runWithConnection)
    14253    0.810    0.000    0.810    0.000 converters.py:81(lookupConverter)
     1210    0.800    0.001    1.510    0.001 main.py:812(_SO_selectInit)


Colin.

Re: [SQLObject] Serializing SQLObjects (Across Joins) as XML

[Philippe N. <ph...@re...>]

Hi,

[...snip...]
>> Q2: Reality check: Is there another xml serialization framework, or even
>> something with SQLObject, that makes what I'm trying to do a waste of time=
?
>> I've been keeping my eyes open for such an animal for several months, but =
I
>> haven't seen anything that I think would marry happily with SQLObject.
>
>You should give a look at XMLObject http://xmlobject.base-art.net/ ,
>which has an API similar to SQLObject, but serializes to and from XML
>instead of an RDBMS.  What you want to do is somewhere in the middle,
>but they might have ideas (and code) over there as well.
>

First, sorry for late reply ;)
I think it could be fun to make (one day) a kind of mixin between
SQLObject and XMLObject. Currently XMLObject is not very mature though.
But i believe it could be fun to have that kind of bridge between RDBMS
and XML (all in wonder Pythonic way).

Philippe

[SQLObject] Re: self join

[Culley H. <har...@gm...>]

I figured it out.  I wasn't grokking the naming conventions....  the
multiple join column has to be named just so and the joinColumn
reference is to the actual column name rather than the sqlobject
munged column name.  Makes for some ugly method names when dealing
with legacy tables but that is ok!

All is good-- Ian thanks for all your hard work on this!  Amazing tool...

culley


On Fri, 1 Oct 2004 18:20:52 -0700, Culley Harrelson <har...@gm...> wrote:
> Hi,
> 
> I am trying to model a self join and having trouble.  Say we have a
> table called node with two columns:  id, parent_id.  So we have a
> class:
> 
> class Node(SQLObject):
>     parent = ForeignKey('node')
>     children = MultipleJoin('node', joinColumn='parentID')
> 
> The childrens reference is invalid.  How do I do this?
> 
> culley
>

[SQLObject] self join

[Culley H. <har...@gm...>]

Hi,

I am trying to model a self join and having trouble.  Say we have a
table called node with two columns:  id, parent_id.  So we have a
class:

class Node(SQLObject):
    parent = ForeignKey('node')
    children = MultipleJoin('node', joinColumn='parentID')

The childrens reference is invalid.  How do I do this?

culley

Re: [SQLObject] Preventing Duplicate Class Definitions

[Brad B. <br...@bb...>]

On Friday, October 1, 2004, at 08:29  PM, Ian Bicking wrote:

> Brad Bollenbach wrote:
>> Hi all,
>> So I've written a little unit test to merge a small patch from Steve 
>> Alexander to prevent defining the same class twice.
>
> Doesn't this already happen?  I think it raises an AssertionError.  
> Oh, wait, maybe it doesn't; I must have forgotten that part when I 
> moved the code into classregistry.py.  Well, you are right, that 
> should raise an error.

I can confirm that before I integrated Steve's code, the test failed 
because no exception was getting raised.

Patch committed.

--
Brad Bollenbach

Re: [SQLObject] Preventing Duplicate Class Definitions

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

Luke Opperman wrote:
> I'll see if I can get a unit test together to illustrate a problem I've 
> been
> having, but here's the scenario (may be a python import issue that we can't
> get around either, I recall problems with imports with different import 
> lines,
> but here the lines at least look the same...)
> 
> File structure:
> 
> ./  <-- webware py/psps mostly live here, importing Objects.X
> ./Objects  <-- primarily SQLObjects
> ./Objects/Core  <-- a few central things here, where the problem is.
> 
> the absolute path to ./ is in PYTHONPATH.
> 
> So both a psp/py in ./ and a script in ./Objects/Core use an import line 
> like:
> 
> from Objects.User import User
> 
> but this leads to a duplicate class error from SQLObject.
> 
> The full import pattern looks like:
> 
> ./Page.py -> from Objects.User import User
> ./Objects.User is imported
> ./Page.py -> from Objects.Core.CheckUser import CheckUser
> ./Objects/Core/CheckUser.py -> from Objects.User import User
> 
> Note, this is on an existing app using 0.5, I'll try to get some time to 
> track
> it down further next week.

I think this is a problem that has occurred with Webware a number of 
times, mostly as a side effect when the path gets complicated and 
redundant.  I think Python will import a file as two separate modules, 
if it finds the module in different ways.  E.g., if /path and 
/path/inner are both in sys.path, and in one place you import 
inner.module and the other you just import module, you'll get two modules.

I'm not clear exactly when this happens, it's subtle.  I would recommend 
printing out both module objects, and seeing if their IDs match.

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

Re: [SQLObject] Preventing Duplicate Class Definitions

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

Brad Bollenbach wrote:
> Hi all,
> 
> So I've written a little unit test to merge a small patch from Steve 
> Alexander to prevent defining the same class twice. 

Doesn't this already happen?  I think it raises an AssertionError.  Oh, 
wait, maybe it doesn't; I must have forgotten that part when I moved the 
code into classregistry.py.  Well, you are right, that should raise an 
error.

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

Re: [SQLObject] Preventing Duplicate Class Definitions

[Luke O. <lu...@me...>]

I'll see if I can get a unit test together to illustrate a problem I've been
having, but here's the scenario (may be a python import issue that we can't
get around either, I recall problems with imports with different import lines,
but here the lines at least look the same...)

File structure:

./  <-- webware py/psps mostly live here, importing Objects.X
./Objects  <-- primarily SQLObjects
./Objects/Core  <-- a few central things here, where the problem is.

the absolute path to ./ is in PYTHONPATH.

So both a psp/py in ./ and a script in ./Objects/Core use an import line like:

from Objects.User import User

but this leads to a duplicate class error from SQLObject.

The full import pattern looks like:

./Page.py -> from Objects.User import User
./Objects.User is imported
./Page.py -> from Objects.Core.CheckUser import CheckUser
./Objects/Core/CheckUser.py -> from Objects.User import User

Note, this is on an existing app using 0.5, I'll try to get some time to track
it down further next week.

- Luke

Quoting Brad Bollenbach <br...@bb...>:

> Hi all,
>
> So I've written a little unit test to merge a small patch from Steve
> Alexander to prevent defining the same class twice. The functionality
> this adds is perhaps best described by the unit test:
>
> class ClassRegistryTest(SQLObjectTest):
>      def testErrorOnDuplicateClassDefinition(self):
>          """Raise an error if a class is defined more than once."""
>          class Duplicate(SQLObject):
>              pass
>
>          try:
>              class Duplicate(SQLObject):
>                  pass
>          except ValueError, err:
>              self.assertEqual(str(err), "class Duplicate is already in
> the registry")
>          else:
>              self.fail("should have raised an error on duplicate class
> definition")
>
> It's been quite a while since I've been involved in SQLObject
> development, but there are more patches on the way (I just have to
> write unit tests before I check them in), so I'd like to get some
> feedback from the list to ensure the time I'm spending on this and
> upcoming patches is time well spent.
>
> Comments appreciated; silence is consent. ;)
>
> Cheers,
>
> --
> Brad Bollenbach
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: IT Product Guide on ITManagersJournal
> Use IT products in your business? Tell us what you think of them. Give us
> Your Opinions, Get Free ThinkGeek Gift Certificates! Click to find out more
> http://productguide.itmanagersjournal.com/guidepromo.tmpl
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>



--
The Pursuit of Counterfactual Histories

[SQLObject] Preventing Duplicate Class Definitions

[Brad B. <br...@bb...>]

Hi all,

So I've written a little unit test to merge a small patch from Steve 
Alexander to prevent defining the same class twice. The functionality 
this adds is perhaps best described by the unit test:

class ClassRegistryTest(SQLObjectTest):
     def testErrorOnDuplicateClassDefinition(self):
         """Raise an error if a class is defined more than once."""
         class Duplicate(SQLObject):
             pass

         try:
             class Duplicate(SQLObject):
                 pass
         except ValueError, err:
             self.assertEqual(str(err), "class Duplicate is already in 
the registry")
         else:
             self.fail("should have raised an error on duplicate class 
definition")

It's been quite a while since I've been involved in SQLObject 
development, but there are more patches on the way (I just have to 
write unit tests before I check them in), so I'd like to get some 
feedback from the list to ensure the time I'm spending on this and 
upcoming patches is time well spent.

Comments appreciated; silence is consent. ;)

Cheers,

--
Brad Bollenbach

Re: [SQLObject] Serializing SQLObjects (Across Joins) as XML

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

Sean M. Hester wrote:
> First, kudos to all SQLObject contributors. I've been using it for just over
> 24 hours, and I already can't imagine database persistence without it.
> 
> Second, I am new to the list, but I did search the archives before posting.
> I only offer this disclaimer because I was surprised by how few messages I
> saw containing "XML".
> 
> That being said, I'm looking for a pythonic solution to serialize my
> SQLObject subclasses as xml. At the single SQLObject level this is
> trivial--in fact the included __repr__ definition is just a few
> modifications away from returning a valid xml representation of the object.
> 
> What's not trivial--for me--is representing the related objects' data. I
> would like to represent any *-to-many joins for the object as child elements
> of the original object. For instance, I'd like to have the "xml" property of
> a Person from the personaddress.py example look like the following:
> 
>     <person id="1" firstName="John" lastName="Doe">
>         <address id="1" street="123" city="Smallsville" state="IL"
> zip="50484"/>
>     </person>

Note that "id" is a special attribute in XML, and should be unique in 
the document.  Maybe instead you should use a dbid attribute.

> If I wanted to custom build each object's xml representation by hand that
> also would be trivial, but I'm trying to abstract this out so all the work
> is done within a generic super-class (which itself would be a sub-class of
> SQLObject).
> 
> So...
> 
> Q1: If I'm creating a new intermediate class for the purpose of adding the
> XML serialization features, what is the best abstract way to navigate the
> one-to-many join data? I see that each SQLObject has a join list and
> dictionary, but I'm getting lost on interrogating the Join objects to get to
> the SQLObjects on the other side of the relationship.

You'll want to look at the objects in self._SO_joinDict, which has 
instances of joins.SOJoin.  Subclasses of SOJoin, actually. 
self._SO_joinDict[key].performJoin(self) should give you the instances 
you want.  You could get infinite loops if you have RelatedJoins; in 
that case, you might want to keep track of the objects that have been 
serialized, and if the object has already been serialized just reference 
it by id instead of fully serializing it.

> Q2: Reality check: Is there another xml serialization framework, or even
> something with SQLObject, that makes what I'm trying to do a waste of time?
> I've been keeping my eyes open for such an animal for several months, but I
> haven't seen anything that I think would marry happily with SQLObject.

You should give a look at XMLObject http://xmlobject.base-art.net/ , 
which has an API similar to SQLObject, but serializes to and from XML 
instead of an RDBMS.  What you want to do is somewhere in the middle, 
but they might have ideas (and code) over there as well.

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

Re: [SQLObject] Problem With UPDATE

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

CLIFFORD ILKAY wrote:
> I have eliminated Webware out of the loop for testing purposes. 
> Regardless of whether I use two different SQLObject instances to change 
> the data or one instance of SQLObject with psql, I get the same 
> behaviour. Below, I have two Python shells running which I have called 
> Instance 1 and Instance 2. You will notice that the second instance does 
> NOT see changes made by the first even though SQLObject is used on both 
> cases.

Yes, it's really an issue of two processes -- one process doesn't see 
what the other process is doing.

> How would you use sync() or expire() in the example below? I could not 
> find any documentation on it.

You'd put in .sync before using the item.  But that will lead to an 
excess of queries.

I think you can do:
   GrpType._connection.cache.expireAll()

This technique isn't well tested.  But it will essentially forget all 
the cached values.  Hmm... or maybe you need to do:

cache = GrpType._connection.cache
for id in cache.allIDs(GrpType):
     cache.expire(id, GrpType)


You can also use sync, like this (which will be slow):

> Instance 1
> ----------
>  >>> from myClasses import *
>  >>> from psycopg import * # needed to trap for exceptions
>  >>> grpTypes = GrpType.select(orderBy=GrpType.q.name)
>  >>> for theGroupType in grpTypes:
           theGroupType.sync()
> ...     print theGroupType.id, theGroupType.name
> ...
> 1 Test 1
> 2 Test 2
> 3 Test 3
> 4 Test 4
> 5 Test 5
> 6 Test 6
> 7 Test 7

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

[SQLObject] Serializing SQLObjects (Across Joins) as XML

[Sean M. H. <str...@ma...>]

First, kudos to all SQLObject contributors. I've been using it for just over
24 hours, and I already can't imagine database persistence without it.

Second, I am new to the list, but I did search the archives before posting.
I only offer this disclaimer because I was surprised by how few messages I
saw containing "XML".

That being said, I'm looking for a pythonic solution to serialize my
SQLObject subclasses as xml. At the single SQLObject level this is
trivial--in fact the included __repr__ definition is just a few
modifications away from returning a valid xml representation of the object.

What's not trivial--for me--is representing the related objects' data. I
would like to represent any *-to-many joins for the object as child elements
of the original object. For instance, I'd like to have the "xml" property of
a Person from the personaddress.py example look like the following:

    <person id="1" firstName="John" lastName="Doe">
        <address id="1" street="123" city="Smallsville" state="IL"
zip="50484"/>
    </person>

If I wanted to custom build each object's xml representation by hand that
also would be trivial, but I'm trying to abstract this out so all the work
is done within a generic super-class (which itself would be a sub-class of
SQLObject).

So...

Q1: If I'm creating a new intermediate class for the purpose of adding the
XML serialization features, what is the best abstract way to navigate the
one-to-many join data? I see that each SQLObject has a join list and
dictionary, but I'm getting lost on interrogating the Join objects to get to
the SQLObjects on the other side of the relationship.

Q2: Reality check: Is there another xml serialization framework, or even
something with SQLObject, that makes what I'm trying to do a waste of time?
I've been keeping my eyes open for such an animal for several months, but I
haven't seen anything that I think would marry happily with SQLObject.

Any help or suggestions would be appreciated.

-- 

Sean M. Hester 
"Good code is art."

Re: [SQLObject] Problem With UPDATE

[CLIFFORD I. <cli...@di...>]

At 10:45 AM 30/09/2004 -0500, Ian Bicking wrote:
>SQLObject caches objects fairly aggressively.  When you update something 
>in the database without using SQLObject, the cache will still remain with 
>the old (now incorrect) values.  When you restart the AppServer, you are 
>purging the cache, and hence the problem went away.
>You can refresh values with .sync() or .expire() (expire refreshes the 
>values lazily).  Right now there's no "expire everything" method, though 
>there really should be.
>
>Does that explain your problem?

Hi Ian,

I have eliminated Webware out of the loop for testing purposes. Regardless 
of whether I use two different SQLObject instances to change the data or 
one instance of SQLObject with psql, I get the same behaviour. Below, I 
have two Python shells running which I have called Instance 1 and Instance 
2. You will notice that the second instance does NOT see changes made by 
the first even though SQLObject is used on both cases.

How would you use sync() or expire() in the example below? I could not find 
any documentation on it.

Instance 1
----------
 >>> from myClasses import *
 >>> from psycopg import * # needed to trap for exceptions
 >>> grpTypes = GrpType.select(orderBy=GrpType.q.name)
 >>> for theGroupType in grpTypes:
...     print theGroupType.id, theGroupType.name
...
1 Test 1
2 Test 2
3 Test 3
4 Test 4
5 Test 5
6 Test 6
7 Test 7

Instance 2
----------
Execute same code as above, same results.

Instance 1
----------
 >>> x = GrpType.get(7)
 >>> x
<GrpType 7 name='Test 7' modUserId='cilkay' modDate=<DateTime object for 
'2004-09-30 16:46:39.88' at 407c83d8>>
 >>> x.name = 'I have changed'
 >>> x
<GrpType 7 name='I have changed' modUserId='cilkay' modDate=<DateTime 
object for '2004-09-30 16:46:39.88' at 407c83d8>>
 >>> grpTypes = GrpType.select(orderBy=GrpType.q.name)
 >>> for theGroupType in grpTypes:
...     print theGroupType.id, theGroupType.name
...
7 I have changed
1 Test 1
2 Test 2
3 Test 3
4 Test 4
5 Test 5
6 Test 6

Instance 2
----------
 >>> grpTypes = GrpType.select(orderBy=GrpType.q.name)
 >>> for theGroupType in grpTypes:
...     print theGroupType.id, theGroupType.name
...
1 Test 1
2 Test 2
3 Test 3
4 Test 4
5 Test 5
6 Test 6
7 Test 7

Regards,

Clifford Ilkay
Dinamis Corporation
3266 Yonge Street, Suite 1419
Toronto, Ontario
Canada M4N 3P6

Tel: 416-410-3326 

Re: [SQLObject] FloatCol, distinct+count and others

[Robert L. <ro...@le...>]

(Oops, replied directly to Ian - hey, it's 4:45am what else do you expect!)

Ian Bicking wrote:
> Marcin Wojdyr wrote:
> 
>> Hi,
>> I will write about a few things, that make me not 100% happy
>> with SQLObject, in one mail.
>>
>> First about MultipleJoin: as Robert wrote in
>> http://sourceforge.net/mailarchive/forum.php?thread_id=5435228&forum_id=30269 
>>
>> person.addresses will generate n+1 queries to DB.
> 
> 
> I don't think that is what happens...?  It's certainly not intended to 
> cause that many selects.

Unfortunately, it does, at least it does when using Postgres, a fresh install of
SQLObject 0.6, Python 2.3.3, W2000 :-(

The first select is :

SELECT id FROM address WHERE person_id = ###

which returns 'n' id's

then for every id in the returned list there is :

SELECT column(s)... FROM address WHERE id = ####

i.e. n+1 queries to DB.

Robert

Re: [SQLObject] Problem With UPDATE

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

CLIFFORD ILKAY wrote:
> Hello,
> 
> Since this question did not elicit any response on the Webware list and 
> since it could well be a SQLObject problem, I shall ask here as well.
> 
> I am running WebWare 0.8.1, SQLObject 0.6, mod_webkit2, and PostgresSQL 
> 7.4.1 on Mandrake 10. I have a form that uses a JavaScript library to 
> make an xmlrpc call to WebWare. When the user enters a new item and hits 
> the Submit button, an xmlprc call is made which does an INSERT into the 
> appropriate PostgreSQL table via SQLObject and another xmlrpc call is 
> made which does a SELECT * on that same table so that the list of items 
> can be updated. The the new item is inserted and it appears on the list 
> of items displayed in a table on the form, no problem. Keep this in mind.
> 
> I have a simple JavaScript routine that sorts the columns in the table 
> when the user clicks on a link representing the columns. I noticed that 
> the first item was not in the correct sort order. Upon closer 
> examination, I noticed it had a leading space. I went to a shell where I 
> had psql running and did a "UPDATE my_table set name = 'No Leading Space 
> Name' where id = 99". I refreshed the browser but saw the first row in 
> the table was still the item with the leading space. I double checked 
> from psql to make sure I had not made a mistake and sure enough, there 
> was no leading space and things sorted properly. I inserted another row 
> using the form described above. Again, the new item was added to the db 
> and to the table on the form but the problematic data remained 
> *unchanged*. In order for the new item to be added to the table, it had 
> to have come from the result of the xmlrpc request. If I restart the 
> AppServer and refresh the browser, the data in the table on the form 
> reflects what is in the db.

SQLObject caches objects fairly aggressively.  When you update something 
in the database without using SQLObject, the cache will still remain 
with the old (now incorrect) values.  When you restart the AppServer, 
you are purging the cache, and hence the problem went away.

You can refresh values with .sync() or .expire() (expire refreshes the 
values lazily).  Right now there's no "expire everything" method, though 
there really should be.

Does that explain your problem?

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

Re: [SQLObject] FloatCol, distinct+count and others

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

Marcin Wojdyr wrote:
> Hi,
> I will write about a few things, that make me not 100% happy
> with SQLObject, in one mail.
> 
> First about MultipleJoin: as Robert wrote in
> http://sourceforge.net/mailarchive/forum.php?thread_id=5435228&forum_id=30269
> person.addresses will generate n+1 queries to DB.

I don't think that is what happens...?  It's certainly not intended to 
cause that many selects.

> About FloatCol, what do you think should be a type of FloatCol?
> I think double precision in every database (but I'm not sure if other
> people agree), as I wrote in
> http://sourceforge.net/mailarchive/forum.php?thread_id=5604389&forum_id=30269

Sure, I guess that's fine.

> About count() in distinct'ed select: now it silently returns not-distincted
> count. 

Right now it's supposed to fail with an assertion error.  If it's not, 
I'll have to look into it.  Or...

> As Jeremy wrote, SQLite doesn't support count(distinct ...),
> but I think the patch from
> http://sourceforge.net/mailarchive/forum.php?thread_id=5545866&forum_id=30269
> would work fine - it works as expected in dbms that support count(distinct),
> and probably raises exception in SQLite, what is better than returning
> wrong value.

I can just apply that patch.  It's probably fine even without 
cross-database support.

> And when I found SQLObject, I first downloaded "Mostly Live CVS Tarball".
> It took me a while to realize, that it is outdated. Now it seems to be
> older than 0.5.2 tarball.

Oops, I have to get rid of that link, thanks.

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

[SQLObject] Problem With UPDATE

[CLIFFORD I. <cli...@di...>]

Hello,

Since this question did not elicit any response on the Webware list and 
since it could well be a SQLObject problem, I shall ask here as well.

I am running WebWare 0.8.1, SQLObject 0.6, mod_webkit2, and PostgresSQL 
7.4.1 on Mandrake 10. I have a form that uses a JavaScript library to make 
an xmlrpc call to WebWare. When the user enters a new item and hits the 
Submit button, an xmlprc call is made which does an INSERT into the 
appropriate PostgreSQL table via SQLObject and another xmlrpc call is made 
which does a SELECT * on that same table so that the list of items can be 
updated. The the new item is inserted and it appears on the list of items 
displayed in a table on the form, no problem. Keep this in mind.

I have a simple JavaScript routine that sorts the columns in the table when 
the user clicks on a link representing the columns. I noticed that the 
first item was not in the correct sort order. Upon closer examination, I 
noticed it had a leading space. I went to a shell where I had psql running 
and did a "UPDATE my_table set name = 'No Leading Space Name' where id = 
99". I refreshed the browser but saw the first row in the table was still 
the item with the leading space. I double checked from psql to make sure I 
had not made a mistake and sure enough, there was no leading space and 
things sorted properly. I inserted another row using the form described 
above. Again, the new item was added to the db and to the table on the form 
but the problematic data remained *unchanged*. In order for the new item to 
be added to the table, it had to have come from the result of the xmlrpc 
request. If I restart the AppServer and refresh the browser, the data in 
the table on the form reflects what is in the db.

To eliminate the JavaScript libraries, I started a Python shell and hit the 
xmlrpc server from there. The output of psql and the Python shell are below.

devdb=# select * from grp_type;
  id  | description | mod_user_id |        mod_date
-----+-------------+-------------+------------------------
  145 | Test 1      | cilkay      | 2004-09-23 01:53:17-04
  146 | Test 2      | cilkay      | 2004-09-23 01:53:17-04
  147 | Test 3      | cilkay      | 2004-09-23 01:53:17-04
  148 | Test 4      | cilkay      | 2004-09-23 01:53:17-04
  150 | Test 6      | cilkay      | 2004-09-27 04:10:53-04
  151 | Test 7      | cilkay      | 2004-09-27 04:10:53-04
  149 |  Test 5     | cilkay      | 2004-09-27 00:13:21-04
(7 rows)

 >>> import xmlrpclib
 >>> server = xmlrpclib.Server('http://localhost/wk/ww/grpTypeMethods')
 >>> groups = server.getAll()
 >>> print groups
{'146': 'Test 2', '147': 'Test 3', '151': 'Test 7', '145': 'Test 1', '150': 
'Test 6', '148': 'Test 4', '149': ' Test 5'}
 >>>

devdb=# update grp_type set description = 'Test 5' where id = 149;
UPDATE 1
devdb=# select * from grp_type;
  id  | description | mod_user_id |        mod_date
-----+-------------+-------------+------------------------
  145 | Test 1      | cilkay      | 2004-09-23 01:53:17-04
  146 | Test 2      | cilkay      | 2004-09-23 01:53:17-04
  147 | Test 3      | cilkay      | 2004-09-23 01:53:17-04
  148 | Test 4      | cilkay      | 2004-09-23 01:53:17-04
  150 | Test 6      | cilkay      | 2004-09-27 04:10:53-04
  151 | Test 7      | cilkay      | 2004-09-27 04:10:53-04
  149 | Test 5      | cilkay      | 2004-09-27 00:13:21-04
(7 rows)

 >>> groups = server.getAll()
 >>> print groups
{'146': 'Test 2', '147': 'Test 3', '151': 'Test 7', '145': 'Test 1', '150': 
'Test 6', '148': 'Test 4', '149': ' Test 5'}
 >>>

Note the leading space in ' Test 5' in the Python results. Note that the db 
has changed. Now I restart the AppServer and execute server.getAll() again.

 >>> groups = server.getAll()
 >>> print groups
{'146': 'Test 2', '147': 'Test 3', '151': 'Test 7', '145': 'Test 1', '150': 
'Test 6', '148': 'Test 4', '149': 'Test 5'}
 >>>

Python now reflects the state of the database. If I do an INSERT or a 
DELETE on grp_type from psql and execute server.getAll(), the changes will 
be reflected in "groups". It is only on UPDATE that it does not work as it 
should. Why is it behaving like this? More importantly, how can I make this 
work as it should? The xmlrpc code is below.

from WebKit.XMLRPCServlet import XMLRPCServlet
from devdb import *
from psycopg import * # needed to trap for exceptions

class grpTypeMethods(XMLRPCServlet):

     def exposedMethods(self):
         return ['listMethods','insert','getOne','getAll']

     def listMethods(self):
         return ['listMethods','insert','getOne','getAll']

     def insert(self,theDescription):
         try:
             newGrpType = GrpType(
                 description=theDescription,
                 modUserId='cilkay'
                 )
             return newGrpType.id
         except IntegrityError:
             return 0

     def remove(self,theID):
         theGrpType = GrpType.get(theID)
         theGrpType.destroySelf()

     def getOne(self,theID):
         groupType = GrpType.get(theID)
         return [groupType.id,groupType.description]

     def getAll(self):
         result = {}
         grpTypes = None
         grpTypes = GrpType.select(orderBy=GrpType.q.description)
         for theGroupType in grpTypes:
             result[str(theGroupType.id)] = theGroupType.description
         return result


Regards,

Clifford Ilkay
Dinamis Corporation
3266 Yonge Street, Suite 1419
Toronto, Ontario
Canada M4N 3P6

Tel: 416-410-3326  

[SQLObject] FloatCol, distinct+count and others

[Marcin W. <wo...@un...>]

Hi,
I will write about a few things, that make me not 100% happy
with SQLObject, in one mail.

First about MultipleJoin: as Robert wrote in
http://sourceforge.net/mailarchive/forum.php?thread_id=3D5435228&forum_id=
=3D30269
person.addresses will generate n+1 queries to DB.

About FloatCol, what do you think should be a type of FloatCol?
I think double precision in every database (but I'm not sure if other
people agree), as I wrote in
http://sourceforge.net/mailarchive/forum.php?thread_id=3D5604389&forum_id=
=3D30269

About count() in distinct'ed select: now it silently returns not-distinct=
ed
count. As Jeremy wrote, SQLite doesn't support count(distinct ...),
but I think the patch from
http://sourceforge.net/mailarchive/forum.php?thread_id=3D5545866&forum_id=
=3D30269
would work fine - it works as expected in dbms that support count(distinc=
t),
and probably raises exception in SQLite, what is better than returning
wrong value.

And when I found SQLObject, I first downloaded "Mostly Live CVS Tarball".
It took me a while to realize, that it is outdated. Now it seems to be
older than 0.5.2 tarball.

Anyway, I'm quite happy with SQLObject :)

Marcin

--=20
Marcin Wojdyr  |  http://www.unipress.waw.pl/~wojdyr

Re: [SQLObject] problem with querying a table

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

Markus Jais wrote:
> <sqlobject.main.SelectResults object at 0x400b26ec>
> Traceback (most recent call last):
>   File "./query.py", line 11, in ?
>     print res1.count()
>   File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line
> 1239, in count
>     count = self.accumulate('COUNT(*)')
>   File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line
> 1235, in accumulate
>     return conn.accumulateSelect(self,expression)
> AttributeError: 'NoneType' object has no attribute 'accumulateSelect'

It looks like "conn" is None, which means you probably haven't set up a 
connection for your table.

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

[SQLObject] problem with querying a table

[Markus J. <mar...@ya...>]

hi

I have an Author table with firstName and Lastname columns.

when I want to query the table like this:

-----
res1 = Author.select(Author.q.firstName == "Alex")
print res1
#print Author.select().count()
print res1.count()
--------

I get this error

./query.py

<sqlobject.main.SelectResults object at 0x400b26ec>
Traceback (most recent call last):
  File "./query.py", line 11, in ?
    print res1.count()
  File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line
1239, in count
    count = self.accumulate('COUNT(*)')
  File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line
1235, in accumulate
    return conn.accumulateSelect(self,expression)
AttributeError: 'NoneType' object has no attribute 'accumulateSelect'


but this is like the examples in the test directory.
maybe I just oversaw something.

what am I doing wrong ?

I would just like to write something like this:

if(res1.count() > 0):
	for author in res1:
		print author


regards

Markus

Re: [SQLObject] AND syntax in sqlobject 0.6

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

Akki Nitsch wrote:
> Hi everybody!
> 
> Did anything changed with the AND syntax in sqlobject 0.6
> 
> Here's my code:
> 
> old_entries = ServerEntrie.select(AND (ServerEntrie.q.entrieDate <
> time.time() - 3600, ServerEntrie.q.entrieStatus != 4))
> 
> And here's the exection:
> 
> NameError: global name 'AND' is not defined
> 
> What is my mistake? Is there some different to sqlobject 0.5

Maybe the symbols exported by "from sqlobject import *" have changed, 
though I hadn't intended for that to happen.  When I test it out, AND 
does seem to be exported.  You can also use &, which doesn't need to be 
imported (though be careful about the order of precedence).

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

Re: [SQLObject] AND syntax in sqlobject 0.6

[Akki N. <ak...@ak...>]

That's it !

Sometimes i'm so stupid - thank you very much!

Regards

Akki

#############################################################
The freedom of meaning one thing and saying
something different is not permitted.
				E.W. Dijkstra

On Mon, 27 Sep 2004, Oleg Broytmann wrote:

> On Mon, Sep 27, 2004 at 01:41:43PM +0200, Akki Nitsch wrote:
> > NameError: global name 'AND' is not defined
>
>    Have you imported the name "AND"?
>
> Oleg.
> --
>      Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
>            Programmers don't die, they just GOSUB without RETURN.
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: YOU BE THE JUDGE. Be one of 170
> Project Admins to receive an Apple iPod Mini FREE for your judgement on
> who ports your project to Linux PPC the best. Sponsored by IBM.
> Deadline: Sept. 24. Go here: http://sf.net/ppc_contest.php
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

Re: [SQLObject] AND syntax in sqlobject 0.6

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

On Mon, Sep 27, 2004 at 01:41:43PM +0200, Akki Nitsch wrote:
> NameError: global name 'AND' is not defined

   Have you imported the name "AND"?

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

[SQLObject] AND syntax in sqlobject 0.6

[Akki N. <ak...@ak...>]

Hi everybody!

Did anything changed with the AND syntax in sqlobject 0.6

Here's my code:

old_entries = ServerEntrie.select(AND (ServerEntrie.q.entrieDate <
time.time() - 3600, ServerEntrie.q.entrieStatus != 4))

And here's the exection:

NameError: global name 'AND' is not defined

What is my mistake? Is there some different to sqlobject 0.5

Thank you!

Akki

#############################################################
The freedom of meaning one thing and saying
something different is not permitted.
				E.W. Dijkstra