sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Identity crisis.

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

Alan Kennedy wrote:
> [Alan Kennedy]
>  >> I'm trying to pickle SQLObjects, so that I can store them in
>  >> structured documents. As far as I can see, pickles only need to
>  >> contain the name of the SQLObject class, and the id which uniquely
>  >> identifies the instance.
> 
> [Brian Ray]
>  > I thought SQLObject does not support pickleing because it uses pickel
>  > for persistance:
>  >
>  > http://sourceforge.net/mailarchive/message.php?msg_id=7578480
> 
> Thanks for pointing out that reference Brian, I hadn't seen it before.
> 
> Looking at Ian's message at that url, it looks like he's talking about 
> support for pickling the entire SQLObject, which I would imagine would 
> have complex consequences when it comes to connections, etc, because 
> object identity seems to be related to the connection that the object 
> came from.

I was just thinking about a reference (i.e., no column data).  The 
reference might or might not include the connection; both instances have 
valid use cases.  E.g., the connection may often be configurable, and if 
you change the configuration and load the pickle you may want lot load 
it from the newly configured connection, not the old connection.  OTOH, 
if you are using multiple databases, you may want the connection 
included in the reference.

__setstate__ would require refactoring SQLObject a bit, so that it could 
set up all the special variables that SQLObject creates in __init__. 
That would be doable.  Another option would be to implement __new__, and 
potentially include some magic keyword argument that would cause a 
.get() to be called.  Of course, __init__ gets *re*-called if __new__ 
returns an instance of the class.  Frankly __new__ is a stupid, stupid 
method.

Which makes me think __setstate__ may be the better way to go.  Oh, 
wait, no... that's no good either.  Because sometimes you'll be 
returning an existing instance when unpickling, if that row has already 
been loaded up.  Blah.  And what if you want to load the objects into a 
transaction?  Then you'd want to say, "unpickle to this connection". 
Again, rather annoying, since there's no way to pass information from 
the pickler into the class, except through a global.

At that point, a pickle subclass starts to seem better, where this 
configuration information could be stored in the pickle subclass.

Blah.  Well, there's my indecisive opinion.

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

[SQLObject] Cannot checkout from svn

[Scott S. <sc...@st...>]

Executing:
svn co svn://colorstudy.com/trunk/SQLObject

Gives the following error.
svn: Berkeley DB error while opening 'nodes' table for filesystem 
/var/lib/subversion/repository/db:
Cannot allocate memory

Seems to be a remote error, as I have tried from a Windows, Linux, and 
Mac OS X client, and all give the same error.

What needs to be done?

Scott

Re: [SQLObject] Strange bug with SQLObject 0.6

[Michel T. <mic...@ya...>]

Hi Carlos!

I faced the same problem a day ago. This is a bug with Joins, you may
pass a joinMethodName argument to solve this problem in your version,
or update to a newer version from svn. I don't try the newer version,
but, as Andrew Bennetts suggests me, I will try this weekend :)

I hope I helped you, if I don't, you can mail me in portuguese (you are
from python-brasil, aren't you?)...

seeya

 --- Carlos Ribeiro <car...@gm...> escreveu: 
> Hello all. I'm writing a prototype using sqlobjects and sqlite, and
> I've run into a strange problem. It may be a dumb mistake of mine (as
> I often do :-), but I was not able to spot it. This is the test code:
> 
> -----
> from sqlobject import connectionForURI, SQLObject, StringCol, IntCol,
> \
>                       FloatCol, ForeignKey, MultipleJoin
> 
> db = connectionForURI('sqlite:/work/test.db')
> 
> class dbWorkItem(SQLObject):
>     _connection = db
>     wi_name        = StringCol(length = 60, notNone = True)
>     wi_description = StringCol(length = 200, notNone = True, default
> = '')
>     wi_form_class  = StringCol(length = 40, notNone = True)
>     wi_form_id     = IntCol(default=0)
>     owner          = ForeignKey('dbUser')
>     workflow       = ForeignKey('dbWorkFlow')
> 
> class dbUser(SQLObject):
>     _connection = db
>     user_nickname  = StringCol(length = 15, notNone = True,
> alternateID = True)
>     user_password  = StringCol(length = 15, notNone = True, default =
> '')
>     user_name      = StringCol(length = 40, notNone = True, default =
> '')
>     user_address1  = StringCol(length = 40, notNone = False, default
> = '')
>     user_address2  = StringCol(length = 40, notNone = False, default
> = '')
>     user_city      = StringCol(length = 40, notNone = False, default
> = '')
>     user_comments  = StringCol(length = 400, notNone = False, default
> = '')
>     workitems      = MultipleJoin('dbWorkItem')
> 
> def new_db():
>     dbUser.createTable()
>     dbWorkItem.createTable()
>     dbUser(user_nickname="cribeiro", user_name="Carlos Ribeiro")
>     dbWorkItem(wi_name='task1', wi_description='Task 1',
> wi_form_class='', workflow=None, owner=1)
>     dbWorkItem(wi_name='task2', wi_description='Task 2',
> wi_form_class='', workflow=None, owner=1)
> 
> >>> new_db()
> >>> u = dbUser.get(1)
> >>> u
> <dbUser 1 user_address1='' user_password='' user_address2=''
> user_city='' user_name='Carlos Ribeiro' user_nickname='cribeiro'
> user_comments=''>
> >>> u.workitems
> Traceback (most recent call last):
>   File "<interactive input>", line 1, in ?
> AttributeError: 'dbUser' object has no attribute 'workitems'
> >>> u.user_name
> 'Carlos Ribeiro'
> >>> wi = dbWorkItem.get(1)
> >>> wi
> <dbWorkItem 1 wi_form_class='' wi_form_id=0 wi_name='Entra nota
> fiscal' workflowID=None ownerID=1 wi_description='Entra nota fiscal'>
> >>> wi.owner
> <dbUser 1 user_address1='' user_password='' user_address2=''
> user_city='' user_name='Carlos Ribeiro' user_nickname='cribeiro'
> user_comments=''>
> >>> wi.owner is u
> True
> 
> For some reason, I cant access the dbUser.workitems property. I tried
> the example provided in the documentation (using Person and Address
> entities), and it works. What am I missing here?
> 
> -- 
> Carlos Ribeiro
> Consultoria em Projetos
> blog: http://rascunhosrotos.blogspot.com
> blog: http://pythonnotes.blogspot.com
> mail: car...@gm...
> mail: car...@ya...
> 
> 
> -------------------------------------------------------
> 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
>  

=====
--
Michel Thadeu Sabchuk
Curitiba/PR


	
	
		
_______________________________________________________ 
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/

Re: [SQLObject] Identity crisis.

[Alan K. <sql...@xh...>]

[Alan Kennedy]
 >> I'm trying to pickle SQLObjects, so that I can store them in
 >> structured documents. As far as I can see, pickles only need to
 >> contain the name of the SQLObject class, and the id which uniquely
 >> identifies the instance.

[Brian Ray]
 > I thought SQLObject does not support pickleing because it uses pickel
 > for persistance:
 >
 > http://sourceforge.net/mailarchive/message.php?msg_id=7578480

Thanks for pointing out that reference Brian, I hadn't seen it before.

Looking at Ian's message at that url, it looks like he's talking about 
support for pickling the entire SQLObject, which I would imagine would 
have complex consequences when it comes to connections, etc, because 
object identity seems to be related to the connection that the object 
came from.

However, I'm using the pickle protocol to do something different, which 
is to pickle a *reference* to an SQLObject, so that when the reference 
is unpickled, the original object can be retrieved. So it's much less 
complex than pickling the entire object.

On reading further about the pickle protocol, I see that it does have 
support for pickling references to external objects, i.e. objects that 
are going to be serialised by reference. This is done through the 
persistent_id and persistent_load methods of Pickler and Unpickler objects.

http://docs.python.org/lib/node69.html

I've implemented that (code below), and it works just fine, although the 
mechanism is a little messy. For example, I don't like having to map an 
SQLObject class definition from a string, which could get messy if there 
are multiple modules and imports involved. But that's the fault of the 
pickle protocol, not SQLObject.

Problem for me is that I'm actually trying to do this with David Mertz 
gnosis.xml.pickle, which also uses the pickle protocol, i.e. 
__getstate__, etc. But unfortunately gnosis.xml.pickle doesn't seem to 
support the persistent extensions to the pickle protocol.

Maybe I'll have to hack it (gnosis.xml.pickle). Seems like a lot of hard 
work for such a simple thing :-(

Anyway, here is code that successfully pickles and unpickles 
*references* to SQLObjects, should anyone be interested.

#source ---------------------

import pickle
import sqlobject
import StringIO

__connection__ = sqlobject.connectionForURI('sqlite:/data/database.db')

class SQLObjectPickler(pickle.Pickler):

	def persistent_id(self, obj):
		if isinstance(obj, sqlobject.SQLObject):
			return "%s:%d" % (obj.__class__.__name__, obj.id)
		return None

class SQLObjectUnpickler(pickle.Unpickler):

	def persistent_load(self, obj_str):
		klass_name, obj_id_str = obj_str.split(':')
		klass = eval(klass_name)
		return klass.get(int(obj_id_str))

class MoSQLObject(sqlobject.SQLObject):

	name = sqlobject.StringCol()

if __name__ == "__main__":
	MoSQLObject.createTable(ifNotExists=True)
	obj = MoSQLObject(name='alan')
	buffer = StringIO.StringIO()
	pickler = SQLObjectPickler(buffer, 0)
	print "Object is %s" % str(obj)
	pickler.dump(obj)
	pickled = buffer.getvalue()
	print "Pickled object is %d bytes" % len(pickled)
	unpickler = SQLObjectUnpickler(StringIO.StringIO(pickled))
	unpickled = unpickler.load()
	print "Unpickled object is %s" % str(unpickled)

#source ---------------------

which outputs

#output ---------------------
Object is <MoSQLObject 1 name='alan'>
Pickled object is 16 bytes
Unpickled object is <MoSQLObject 1 name='alan'>
#output ---------------------

Regards,

Alan.

[SQLObject] Strange bug with SQLObject 0.6

[Carlos R. <car...@gm...>]

Hello all. I'm writing a prototype using sqlobjects and sqlite, and
I've run into a strange problem. It may be a dumb mistake of mine (as
I often do :-), but I was not able to spot it. This is the test code:

-----
from sqlobject import connectionForURI, SQLObject, StringCol, IntCol, \
                      FloatCol, ForeignKey, MultipleJoin

db = connectionForURI('sqlite:/work/test.db')

class dbWorkItem(SQLObject):
    _connection = db
    wi_name        = StringCol(length = 60, notNone = True)
    wi_description = StringCol(length = 200, notNone = True, default = '')
    wi_form_class  = StringCol(length = 40, notNone = True)
    wi_form_id     = IntCol(default=0)
    owner          = ForeignKey('dbUser')
    workflow       = ForeignKey('dbWorkFlow')

class dbUser(SQLObject):
    _connection = db
    user_nickname  = StringCol(length = 15, notNone = True, alternateID = True)
    user_password  = StringCol(length = 15, notNone = True, default = '')
    user_name      = StringCol(length = 40, notNone = True, default = '')
    user_address1  = StringCol(length = 40, notNone = False, default = '')
    user_address2  = StringCol(length = 40, notNone = False, default = '')
    user_city      = StringCol(length = 40, notNone = False, default = '')
    user_comments  = StringCol(length = 400, notNone = False, default = '')
    workitems      = MultipleJoin('dbWorkItem')

def new_db():
    dbUser.createTable()
    dbWorkItem.createTable()
    dbUser(user_nickname="cribeiro", user_name="Carlos Ribeiro")
    dbWorkItem(wi_name='task1', wi_description='Task 1',
wi_form_class='', workflow=None, owner=1)
    dbWorkItem(wi_name='task2', wi_description='Task 2',
wi_form_class='', workflow=None, owner=1)

>>> new_db()
>>> u = dbUser.get(1)
>>> u
<dbUser 1 user_address1='' user_password='' user_address2=''
user_city='' user_name='Carlos Ribeiro' user_nickname='cribeiro'
user_comments=''>
>>> u.workitems
Traceback (most recent call last):
  File "<interactive input>", line 1, in ?
AttributeError: 'dbUser' object has no attribute 'workitems'
>>> u.user_name
'Carlos Ribeiro'
>>> wi = dbWorkItem.get(1)
>>> wi
<dbWorkItem 1 wi_form_class='' wi_form_id=0 wi_name='Entra nota
fiscal' workflowID=None ownerID=1 wi_description='Entra nota fiscal'>
>>> wi.owner
<dbUser 1 user_address1='' user_password='' user_address2=''
user_city='' user_name='Carlos Ribeiro' user_nickname='cribeiro'
user_comments=''>
>>> wi.owner is u
True

For some reason, I cant access the dbUser.workitems property. I tried
the example provided in the documentation (using Person and Address
entities), and it works. What am I missing here?

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: car...@gm...
mail: car...@ya...

Re: [SQLObject] Identity crisis.

[Brian R. <br...@se...>]

On Oct 15, 2004, at 3:39 PM, Alan Kennedy wrote:

> I'm trying to pickle SQLObjects, so that I can store them in 
> structured documents. As far as I can see, pickles only need to 
> contain the name of the SQLObject class, and the id which uniquely 
> identifies the instance.


I thought SQLObject does not support pickleing because it uses pickel 
for persistance:

http://sourceforge.net/mailarchive/message.php?msg_id=7578480

This may have changed, but this was the last I heard.

[SQLObject] Identity crisis.

[Alan K. <sql...@xh...>]

Greetings all,

I'm getting to grips with SQLObject at the moment. I like it very much, 
it is an excellent and pythonic bridge between the object and relational 
worlds.

I'm trying to pickle SQLObjects, so that I can store them in structured 
documents. As far as I can see, pickles only need to contain the name of 
the SQLObject class, and the id which uniquely identifies the instance.

 From what I understand, the id attributes of SQLObjects uniquely 
identify the object instance in the SQLObject's class/table. This should 
mean that I could use these ids in references in data stored outside 
SQLObject, and know that I will get the correct instance when I recreate 
the SQLObject, through the use of the ClassName.get(id).

The same should hold true when I store ids in pickles. If I store and 
retrieve SQLObjects through the pickle protocol, i.e. by defining the 
__getstate__ and __setstate__ methods like so

class MoSQLObject(SQLObject):

   def __getstate__(self):
     return self.id

   def __setstate__(self, id):
     return self.__class__.get(id)

Then I should get the same SQLObject, i.e. an object with the same id, 
back from unpickling process.

But I'm finding that that isn't working. The object that I get back from 
the unpickling process seems to be the right SQLObject, but it's missing 
the id attribute.

The following code illustrates the problem.

#source --------

import pickle
import sqlobject

__connection__ = sqlobject.connectionForURI('sqlite:/data/database.db')

class PickleableSQLObject(sqlobject.SQLObject):

     def __getstate__(self):
         return self.id

     def __setstate__(self, id):
         return self.__class__.get(id)

class MoSQLObject(PickleableSQLObject):

     name = sqlobject.StringCol()

if __name__ == "__main__":
     MoSQLObject.createTable(ifNotExists=True)
     obj = MoSQLObject(name='alan')
     print "Object is %s" % str(obj)
     pickled = pickle.dumps(obj)
     print "Pickled object is %d bytes" % len(pickled)
     unpickled = pickle.loads(pickled)
     print "Unpickled object is %s" % str(unpickled)

#source --------

The above code outputs the following

#output --------
Object is <MoSQLObject 1 name='alan'>
Pickled object is 91 bytes
Traceback (most recent call last):
   File "test_sqlobject_pickle.py", line 25, in ?
     print "Unpickled object is %s" % str(unpickled)
   File "C:\Python23\Lib\site-packages\sqlobject\main.py", line 1072, in 
__repr__
     return '<%s %r %s>' \
AttributeError: 'MoSQLObject' object has no attribute 'id'
#output --------

So it seems the id attribute has gone missing. I have verified that it 
is there before the SQLObject is returned from __setstate__, but has 
disappeared by the time it gets returned by pickle.loads().

Which leads me to think one of two things

1. There is some form of clash between the semantics of 'id' attributes 
in the SQLObject and pickle modules.

2. I have failed to grok SQLObject, and am not using it correctly. 
Particularly, I may have missed some essential constraints or rules 
relating to object identity?

I'd be grateful if anyone could shed any light.

TIA,

Alan.

[SQLObject] MS JET support

[Carlos R. <car...@gm...>]

I would like to know if anyone customized SQLObject to use with the MS
JET database engine. I have a customer who has an old application
written using it that will be slowly moved to a open-source database
(MySQL or PostgreSQL, probably). I'm now developing the application
(using SQLite, which has shown to be more than enough for my
development needs and doesn't add any administrative burden). So far,
SQLObjects is helping a lot, but JET support for the transition would
be great.

BTW, I'm willing to write a JET adapter myself, if none is available,
but I may need some help from the developers... and if someone has a
half-baked driver working, then I could use it as a starting point.

Thanks in advance,

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: car...@gm...
mail: car...@ya...

Re: [SQLObject] Related Join

[Andrew B. <and...@pu...>]

On Thu, Oct 14, 2004 at 02:11:46AM -0300, Michel Thadeu wrote:
> Hi guys!
> 
> I recently faced some problems with RelatedJoin, he is droping the
[...]
> 
> I upgraded the version last saturday, I used the site package 0.6, the
> old one was 0.6 too, but from svn... Is this a bug or this is the
> correct way of work now?

It's a bug, recently fixed in SVN.  Could you try upgrading to the latest
SQLObject in SVN and confirm that it fixes the bug?

-Andrew.

[SQLObject] Related Join

[Michel T. <mic...@ya...>]

Hi guys!

I recently faced some problems with RelatedJoin, he is droping the
attribute name I use and using another, the class method added by a s.
I like to make programs in my language, the plural form is not made
this way, and in english it isn't sometimes too.

-----
from sqlobject import *

CONNECTION='a connection string'

class Person(SQLObject):
  _connection=CONNECTION
  name=StringCol()
  roles=RelatedJoin('Role')

class Role(SQLObject):
  _connection=CONNECTION
  description=StringCol()
  people=RelatedJoin('Person')

if __name__=='__main__':
  Person.createTable()
  Role.createTable()
  p=Person(name='person test')
  r=Role(description='role test')
  p.addRole(r)
  p.roles # ok
  r.persons # ok
  r.people # ops
----

I upgraded the version last saturday, I used the site package 0.6, the
old one was 0.6 too, but from svn... Is this a bug or this is the
correct way of work now?

thanks for attention!

=====
--
Michel Thadeu Sabchuk
Curitiba/PR


	
	
		
_______________________________________________________ 
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/

Re: [SQLObject] Error from transfering code from 0.5 to 0.6

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

Eni Mustafaraj wrote:

>  File "C:\PROGRA~2\Python23\Lib\site-packages\sqlobject\main.py", line=20
> 338, in get
>    id =3D cls._idType(id)
> ValueError: invalid literal for int(): 0-9787-IDI
> -------------------
>=20
> I see what the error means, but why it worked before? I was hoping with=
=20
> 0.6 for something =E0 la:
>=20
> Unit.get('0-9787-IDI')
>=20
> but that is probably wishing to much for the moment, isnt'it?
> Should I probably stay with 0.5, or can I somehow change the code in=20
> order to work?

Oh, that was a (somewhat temporary) new feature.  Add:

     _idType =3D str

to your class definition.  Before SQLObject was lax about this, but it=20
caused some problems when you mixed integers and strings.

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

[SQLObject] Error from transfering code from 0.5 to 0.6

[Eni M. <en...@Ma...>]

Hello,

I had some code working on version 0.5 and updated to 0.6
to get advantage of "get()", etc. But alas, the old code isn't working=20
anymore.
Could someone please tell me what should I do to fix it?

The class is:

class Unit(SQLObject):
    _connection=3Dconn
    _style =3D Style()
    _idName =3D "FABRICATION_NO"
    _fromDatabase=3DTrue

and some test code works fine with the class:

all =3D Unit.select('all')
el =3D all[0]
fields =3D [(c.kw['name'], getattr(el, c.kw['name'])) for c in el._column=
s]

# >>> fields
#('NAME', None), ('SITE_NAME_1', ''), ('SITE_NAME_2', None),
#('FABRICATION_NO', '0-9787-IDI'), ('DATASOURCE', 'CSd'),
...

Actually, I know that the ID (in this case the field 'FABRICATION_NO')=20
is not
an Integer as required, but since it worked, I let it be (in fact, I=20
cannot have an
incremental as primary key, since this is a legacy database that cannot=20
be touched).

Now, with 0.6, I get the following error:

-----------------
Traceback (most recent call last):
  File=20
"C:\PROGRA~2\Python23\lib\site-packages\Pythonwin\pywin\framework\scriptu=
tils.py",=20
line 310, in RunScript
    exec codeObject in __main__.__dict__
  File "C:\webware_working\lib\new_code\test_sqlObject.py", line 67, in ?
    el =3D all[0]
  File "C:\PROGRA~2\Python23\Lib\site-packages\sqlobject\main.py", line=20
1238, in __getitem__
    return list(self.clone(start=3Dstart, end=3Dstart+1))[0]
  File=20
"C:\PROGRA~2\Python23\Lib\site-packages\sqlobject\dbconnection.py", line=20
456, in next
    obj =3D self.select.sourceClass.get(result[0],=20
selectResults=3Dresult[1:], connection=3Dself.dbconn)
  File "C:\PROGRA~2\Python23\Lib\site-packages\sqlobject\main.py", line=20
338, in get
    id =3D cls._idType(id)
ValueError: invalid literal for int(): 0-9787-IDI
-------------------

I see what the error means, but why it worked before? I was hoping with=20
0.6 for something =E0 la:

Unit.get('0-9787-IDI')

but that is probably wishing to much for the moment, isnt'it?
Should I probably stay with 0.5, or can I somehow change the code in=20
order to work?

thanks and regards,
Eni

Re: [SQLObject] SQLObject Performance

[Stuart B. <stu...@ca...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Brad Bollenbach wrote:

| My interest is in PostgreSQL, so I'll comment on that. psycopg claims to
| support the DB-API v2, and thus should have prepared statements.

psycopg 1.xx does not - it just pretends to as when it was written
PostgreSQL didn't support them. The experimental psycopg 2 does support
them I believe.

- --
Stuart Bishop <stu...@ca...>   http://www.canonical.com/
Canonical Ltd.                              http://www.ubuntulinux.com/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBaoFEAfqZj7rGN0oRAhRaAJ9hpdvCg8EWMs3efRQIk/T35U84OgCfW2Vd
9tHLQq5Yt21C9qOmaqF1Odo=
=KOfm
-----END PGP SIGNATURE-----

Re: [SQLObject] Unexpected Behavior

[Andrew B. <and...@pu...>]

On Sun, Oct 10, 2004 at 11:25:40PM -0400, Todd Boland wrote:
> In the following class definition, I would expect 
> organization.phone_numbers to contain the phone numbers associated with 
> the organization. However, they are defined in 
> organizaiton.phoneNumbers instead.  I explicitly stated I wanted them 
> in organization.phone_numbers. Why bother assigning RelatedJoins in the 
> examples if the member the coder assigns is irrelevant?

Have you tried the latest SQLObject from SVN?  This looks to me like a bug
that was fixed recently.

-Andrew.

[SQLObject] Unexpected Behavior

[Todd B. <it...@it...>]

In the following class definition, I would expect 
organization.phone_numbers to contain the phone numbers associated with 
the organization. However, they are defined in 
organizaiton.phoneNumbers instead.  I explicitly stated I wanted them 
in organization.phone_numbers. Why bother assigning RelatedJoins in the 
examples if the member the coder assigns is irrelevant?

P.S. Thanks for a terrific piece of code! I absolutely can't live 
without SQLObject now that I've taken a short amount of time to learn 
it.

--- 8< ---

class Organization(SQLObject):
         _table = "organizations"
         url = StringCol(length=128)
         name = StringCol(length=64)
         shipping_address = ForeignKey("Address")
         billing_address = ForeignKey("Address")
         addresses = RelatedJoin('Address',
                 joinColumn='organization_id',
                 otherColumn='address_book_id',
                 intermediateTable='organizations_pivot_address_book')
         phone_numbers = RelatedJoin('PhoneNumber',
                 joinColumn='organization_id',
                 otherColumn='phone_book_id',
                 intermediateTable='organizations_pivot_phone_book')

--- 8< ---

 >>> organization = Organization.get(1)
 >>> organization.phone_numbers
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
AttributeError: 'Organization' object has no attribute 'phone_numbers'
 >>> organization.phoneNumbers
[<PhoneNumber 1 phone_number='(508) 555-2885' name='Phone'>, 
<PhoneNumber 2 phone_number='(978) 555-0058' name='Facsimile'>]

-- 
Todd Boland
Charged Software
(508) 887-2885
Facsimile: (978) 264-0058

-- 
Statement of Confidentiality:

The information contained in this electronic message and any 
attachments to this message are intended for the exclusive use of the 
addressee(s) and may contain confidential or privileged information.

If you are not the intended recipient, please notify Todd Boland, at 
(508) 887-2885 immediately, and destroy all copies of this message and 
any attachments.

Re: [SQLObject] DateTimeCol and mx.DateTime

[rharper <rh...@sh...>]

* rharper <rh...@sh...> [2004-10-09 14:32]:
> Here is a testcase that seems to show DateTimeCol() broken for
> mx.DateTime objects.  This worked for me in 0.5.2.  Could be the mysqldb
> underneath SQLObject.  Anyone seen this?

Did a little more digging and mysqldb 1.1.5 includes a new
DateTime_or_None method that uses python 2.2.3 datetime.

/usr/lib/python2.3/site-packages/MySQLdb/times.py:

...
try:
    from pytimes import *

except ImportError:
    try:
        from mxdatetimes import *

    except ImportError:
        # no DateTime? We'll muddle through somehow.
        from stringtimes import *

It uses pytimes which is written to use datetime.datetime.  By moving
pytimes.* out of the way, it will fallback on mx.DateTime.  There
doesn't seem to be a way to configure mysqldb to use one over the other.

In any case, the test passes with pytimes.py out of the way:
(shake) tests # python test.py 
Testing mysql
............................................................
----------------------------------------------------------------------
Ran 60 tests in 8.231s

OK

Ryan Harper

[SQLObject] DateTimeCol and mx.DateTime

[rharper <rh...@sh...>]

Here is a testcase that seems to show DateTimeCol() broken for
mx.DateTime objects.  This worked for me in 0.5.2.  Could be the mysqldb
underneath SQLObject.  Anyone seen this?

Index: test.py
===================================================================
--- test.py     (revision 273)
+++ test.py     (working copy)
@@ -275,6 +275,19 @@
         self.assertEqual(TestSO8.select().count(), 0)
         self.assertEqual(TestSO9.select().count(), 0)
 
+class TestSO10(SQLObject):
+      date = DateTimeCol()
+
+class TestCase90(SQLObjectTest):
+
+   classes = [TestSO10]
+
+   def testDateTimeCol(self):
+      d = DateTime.DateTimeFrom('31 Jul 2004 01:30:43 GMT')
+      tc10a = TestSO10(date=d)
+      self.assertEqual(tc10a.date,d)
+
+
 ########################################
 ## Fancy sort
 ########################################

(shake) tests # python test.py
Testing mysql
..................................................F.........
======================================================================
FAIL: testDateTimeCol (__main__.TestCase90)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "test.py", line 288, in testDateTimeCol
    self.assertEqual(tc10a.date,d)
  File "/usr/lib/python2.3/unittest.py", line 302, in failUnlessEqual
    raise self.failureException, \
AssertionError: datetime.datetime(2004, 7, 31, 1, 30, 43) != <DateTime object for '2004-07-31 01:30:43.00' at 40342bf0>

----------------------------------------------------------------------
Ran 60 tests in 7.802s

FAILED (failures=1)

(shake) SQLObject # COLUMNS=120 dpkg --list python*mysql* | grep ii
ii  python2.3-mysqldb   1.1.5-2  A Python interface to MySQL

(shake) SQLObject # COLUMNS=120 dpkg --list mysql* | grep ii
ii  mysql-client  4.0.21-3 mysql database client binaries
ii  mysql-common  4.0.21-3 mysql database common files (e.g. /etc/mysql/my.cnf)
ii  mysql-server  4.0.21-3 mysql database server binaries

Ryan Harper

Re: [SQLObject] SQLObject Performance

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

On Friday, October 8, 2004, at 06:40  PM, Ian Bicking wrote:

> Colin Stewart wrote:

[snip]

>> Using the db driver's quoting would also mean that the SQL can be 
>> cached as prepared statements by the DB.  For some (e.g. Oracle) that 
>> can lead to a pretty significant speed up in itself.
>
> I don't know enough about prepared statements to know if it would 
> help.  If it's possible to prepare, up front, three or four statements 
> for every class, then it could help.  If you have to prepare a 
> statement, then use it several times, then prepare another statement, 
> it's unlike to help, since SQLObject doesn't know enough to predict 
> what statements are going to be used in sequence often enough to help.
>
> Also, the postgres and mysql drivers (and probably sqlite) don't have 
> any prepared statements, and do all the quoting on the client side.  
> I'm most interested in those backends, so there's not a huge amount to 
> be gained.  They probably do the quoting in C, though, with some 
> performance gain there.

My interest is in PostgreSQL, so I'll comment on that. psycopg claims 
to support the DB-API v2, and thus should have prepared statements.

So here's a unit test that demonstrates, perhaps, a decent way of 
hooking up bare SQL to a method (untested, I stayed up till 4am last 
night playing Mao, and I'm off to Heathrow in 10 minutes, so please 
forgive me if this code is a bit dirty. :) This may need to be molded 
around slightly to solve your problem but I hope it illustrates an 
interesting new idea and gets some thinking going on about this problem:

class SQLMethodTest(SQLObjectTest):
     def testSQLMethod(self):
         class USAddressesResult(object):
             class USCitizen(object):
                 def __init__(self, id, name, address):
                     self.id = id
                     self.name = name
                     self.address = address

             def __init__(self, results):
                 self.results = results

             def __iter__(self):
                 for r in self.results:
                     yield USCitizen(r[0], r[1], r[2])

         class Person(SQLObject):
             usaddresses = SQLMethod(
                 sql = """\
                 SELECT p.id, p.name, a.address
                 FROM person p, address a
                 WHERE p.id = a.personid
                   AND a.country_code = 'US'""",
                 result = USAddressesResult)
             usaddresses = classmethod(usaddresses)

         uscitizens = list(Person.usaddresses())
         self.assertEquals(len(uscitizens), 2)
         self.assertEquals(uscitizens[0].name, "Ford Prefect")
         self.assertEquals(uscitizens[0].address, "Somewhere near 
Betelgeuse")

The benefits gained from this feature would be:

1. The speed of working with raw SQL.

2. Being able to get a resultset that actually does what you need 
(since the current concept that "an element in a result set maps to one 
row in a table" often doesn't make sense, as each row returned from a 
SELECT query is often *not* a row in a table.)

3. You could, of course, use placeholders in the SQL statement and then 
the SQLMethod method would accept that many params.

Thoughts, comments, feedback welcome.

Gotta run,

--
Brad Bollenbach

Re: [SQLObject] SQLObject Performance

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

Colin Stewart wrote:
> Is it safe to use the DBAPI.getConnection() call to get the underlying 
> connection and use this mixed in with ordinary SQLObject calls?  It 
> would be nice to be able to mix raw SQL and SQLObject as needed over the 
> same connection.  I'm not sure how SQLObject's caching works, but 
> there's presumably a mechanism for invalidating results from individual 
> tables?

If you look back in the archives a couple weeks, I outlined some ways to 
expire the cache manually.  Generally if you are doing inserts it should 
be fine, as there's nothing related to inserts that gets cached.  Joins 
and selects are not cached (except for .get), though they will returns 
the cached versions of objects.  With an insert, this isn't an issue. 
Doing deletes or updates could cause cache consistency problems, so 
you'd have to expire the cache.

>>/> 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./
>>
> 
> Using the db driver's quoting would also mean that the SQL can be cached 
> as prepared statements by the DB.  For some (e.g. Oracle) that can lead 
> to a pretty significant speed up in itself.

I don't know enough about prepared statements to know if it would help. 
  If it's possible to prepare, up front, three or four statements for 
every class, then it could help.  If you have to prepare a statement, 
then use it several times, then prepare another statement, it's unlike 
to help, since SQLObject doesn't know enough to predict what statements 
are going to be used in sequence often enough to help.

Also, the postgres and mysql drivers (and probably sqlite) don't have 
any prepared statements, and do all the quoting on the client side.  I'm 
most interested in those backends, so there's not a huge amount to be 
gained.  They probably do the quoting in C, though, with some 
performance gain there.

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

[SQLObject] Re: SQLObject + postgresql + twisted

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

Miika Keskinen wrote:
n> I'm sorry I did not post to mailing list but I rather ask your opinion
> about two things that I've found that'll be pretty usefull.

Well, no reason not to share the ideas with the mailing list, so I'll 
copy it.

> Have you though about having oppotrunity to use
> twisted.enterprise.adbapi as database backend? I'm big twisted fan and
> using sqlobject with adbapi would help on some things.

That could certainly be another backend.  In the end, SQLObject expects 
synchronous database operations, so it would probably have to turn that 
into a synchronous operation as well.  Which would just be confusing, 
since the underlying drivers are synchronous, and adbapi turns them into 
asynchronous calls using threads, and then they'd have to be made 
synchronous again, and then the whole thing might become asynchronous 
when you use SQLObject if your code is asynchronous.  So... I'm not sure 
of the benefit.

It's possible that SQLObject could be made a bit more asynchronous 
without extreme effort, at least for some of the methods.  Simply by 
fiddling with main.makeProperties you might be able to do this -- adding 
an asynchronous method for each _get_ and _set_.  With continuations (in 
Stackless) you might be able to turn it all into something asynchronous 
via a special DBConnection object; maybe greenlets can do this?  But 
that's all very experimental.  And this all has to interact safely with 
C extension modules, since all the database drivers are written in C.

> And secondly could postgresql INHERITS be used with sqlobject? I think
> it would not require big changes and if you have some column defined
> as 'id serial' and then you inherit that table the childs will inherit
> that 'serial'-property and each child will have then  unique id. Also
> selecting from parent can return elements found from child-tables.

SQLObject really expects to get back a single kind of object from a 
select, not one of several possible tables.  I don't think it would be 
that easy.  Generally I'm suspicious of inheritance in relational 
databases.  Maybe other people have a thought on this.  There was also a 
patch out there to add subclassing that didn't rely on database support.

> Of course this is postgresql specific (or not specific as inheritance is
> part of sql99 iirc) as mysql doesn't implement it - dunno if other
> system do.
> 
> But if you find any sense of these I'll be more than glad to drop in
> some piece of code to test these in practice?

If you'd like, I can give you subversion access and you can try some of 
these ideas out in a branch.

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

Re: [SQLObject] MulipleJoin select problem when upgrading

[Andrew B. <and...@pu...>]

On Tue, Oct 05, 2004 at 01:45:21PM -0500, Brian Ray wrote:
> Hello:
> 
> I am currently upgrading from SQLObject .5 to .6.  Everything has been 
> working fine until I ran into the problem below.

This also looks like the bug fixed in r273.  Try SQLObject from SVN.

-Andrew

Re: [SQLObject] Name of MultipleJoin attribute being ignored.

[Andrew B. <and...@pu...>]

On Fri, Sep 24, 2004 at 09:31:02AM -0700, David M. Cook wrote:
> On Fri, Sep 24, 2004 at 11:00:24AM -0500, Ian Bicking wrote:
> 
> > I haven't had a chance to look at MultipleJoin closely, to really figure 
> > out what it's problem is, but I did (rather blindly) apply a patch sent 
> > by Cyril.  It's not in 0.6, but it's in the repository, maybe it will 
> > help you.
> 
> Just tried out the SVN sqlobject and see the same thing.

Try latest SVN (r273).  Brad has just committed a fix that fixes this issue
for me.

-Andrew.

Re: [SQLObject] SQLObject Performance

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

Hi Ian,


> > 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 safe to use the DBAPI.getConnection() call to get the underlying
connection and use this mixed in with ordinary SQLObject calls?  It
would be nice to be able to mix raw SQL and SQLObject as needed over the
same connection.  I'm not sure how SQLObject's caching works, but
there's presumably a mechanism for invalidating results from individual
tables?


> > 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.


Using the db driver's quoting would also mean that the SQL can be cached
as prepared statements by the DB.  For some (e.g. Oracle) that can lead
to a pretty significant speed up in itself.

Thanks for your reply,

Colin.

[SQLObject] _init method

[Daniel E B. <da...@li...>]

Hello all I have been playing with SQLObject again because I just 
redesigned a rather large database. I was wondering if there was a way 
to tell whether something was fetched or inserted into the database with 
the _init method. Also are there hook methods for doing anything special 
with SELECT, INSERT, UPDATE and DELET? I have some special fields that 
are tacked onto every table that I use: createdOn, createdBy, 
modifiedOn, modifiedBy, partner_id, and tag. I would like to be able to 
add things to each type of query that utilizes these fields. Also, I 
cannot seem to be able to pass in a transaction when instantiating an 
SQLObject class. Some help there would be nice too. I was trying 
something like:

conn = Foo._connection
trans = conn.transaction()
Foo(bar=0, biz=1, baz=sqlbuilder.func.NOW(), connection=trans)
trans.rollback()

when overriding _init and printing out some stuff the connection param 
is None.

Thanks for the Help,
Dan

[SQLObject] MulipleJoin select problem when upgrading

[Brian R. <br...@se...>]

Hello:

I am currently upgrading from SQLObject .5 to .6.  Everything has been 
working fine until I ran into the problem below.

# Works if .5 but not in .6:
...
class foo(SQLObject):
    name = StringCol()
    foobar = MultipleJoin('foobar')
...
class foobar(SQLObject):
    title = StringCol()
    foo = ForeignKey('foo')
...
newbob = foo( name = 'bob')
newtitle = foobar(title = 'Programmer', foo = newbob)

# later:
................................
bob = STDtables.foo.select(' name = 'bob')[0]
...
if bob.foobar[0].title == 'Programmer':
	print '000101010'
else:
	print 'hello'
...

AttributeError: 'foo' object has no attribute 'foobar'

Was I doing something wrong in the first place? How do I get this to 
work with .6?

Thanks in advance, Brian