sqlobject-discuss — All things SQLObject-related

[SQLObject] Re: Problems with transactions

[Cyril E. <cy...@de...>]

I answer my own message,

I didn't understand that I must pass a transction object in lieu of the 
connection object. That works this way. But I think there is a problem 
yet with the pool of sql connections when doing multithreading. Not sure 
that the same SQL connection is used twice by _runWithConnection during 
a transaction implying multiple SQL statements. Any ideas?

Cyril Elkaim

Cyril Elkaim wrote:
> Hello, again.
> 
> Put it simply Transactions do not work. In fact you must have 
> autoCommit=True to make them work. Here is a transcript of what happens 
> when you set autoCommit=false:
> 
>  1/Pool    :  ACQUIRE pool=[]
>  1/QueryIns:  INSERT INTO Repository.Category (id, description, 
> language, number) VALUES ('FR_FR.CAT1', 'Consultation', 'fr_FR', 'A')
>  1/Pool    :  RELEASE (implicit, autocommit=0) pool=[]
>  1/ROLLBACK:  auto
>  1/Pool    :  ACQUIRE pool=[]
>  1/QueryOne:  SELECT description, language, number FROM 
> Repository.Category WHERE id = 'FR_FR.CAT1'
>  1/Pool    :  RELEASE (implicit, autocommit=0) pool=[]
>  1/ROLLBACK:  auto
> 
> 
> The QueryOne request fails always because the INSERT request have been 
> roll back. The reason is that SQLObject creates, and closes a connection 
> for each SQL statement. Why this behaviour? Should it not be possible to 
> open the database connection when creating the SQLObject connection and 
> releasing it _only_ when its parent is destroyed by the client code?
> 
> 
> Again, I'm ready to go into the code but I need to know the method to 
> send patches.
> 
> 
> Cyril Elkaim
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by BEA Weblogic Workshop
> FREE Java Enterprise J2EE developer tools!
> Get your free copy of BEA WebLogic Workshop 8.1 today.
> http://ads.osdn.com/?ad_id=5047&alloc_id=10808&op=click

[SQLObject] object has no attribute

[MJR <py...@ce...>]

Any idea why the one-to-many example from SQLObject documentation works with
addresses=MultipleJoin('Address'), but doesn't with
addr=MultipleJoin('Address').

I am using a version from SVN.

tx,
mike


+++++++++++++++++++++++++++++++++++++++++++++++++++

Traceback (most recent call last):
  File "C:\wroot\mps\tracker\test.py", line 26, in ?
    print p.addr
AttributeError: 'Person' object has no attribute 'addr'


+++++++++++++++++++++++++++++++++++++++++++++++++++
from sqlobject import *
__connection__ = 'mysql://test@localhost/test'

class Person(SQLObject):
    firstName = StringCol()
    middleInitial = StringCol(length=1, default=None)
    lastName = StringCol()
    addr = MultipleJoin('Address')

class Address(SQLObject):
    street = StringCol()
    city = StringCol()
    state = StringCol(length=2)
    zip = StringCol(length=9)
    person = ForeignKey('Person')

def reset():
    Person.dropTable(ifExists=True)
    Person.createTable()
    Address.dropTable(ifExists=True)
    Address.createTable()

reset()

p = Person(firstName='John', lastName='Doe')
print p.addr

a1 = Address(street='123', city='Smallsville', state='IL', zip='50484',
person=p)
print [a.street for a in p.addr]

+++++++++++++++++++++++++++++++++++++++++++++++++++

[SQLObject] Problems with transactions

[Cyril E. <cy...@de...>]

Hello, again.

Put it simply Transactions do not work. In fact you must have 
autoCommit=True to make them work. Here is a transcript of what happens 
when you set autoCommit=false:

  1/Pool    :  ACQUIRE pool=[]
  1/QueryIns:  INSERT INTO Repository.Category (id, description, 
language, number) VALUES ('FR_FR.CAT1', 'Consultation', 'fr_FR', 'A')
  1/Pool    :  RELEASE (implicit, autocommit=0) pool=[]
  1/ROLLBACK:  auto
  1/Pool    :  ACQUIRE pool=[]
  1/QueryOne:  SELECT description, language, number FROM 
Repository.Category WHERE id = 'FR_FR.CAT1'
  1/Pool    :  RELEASE (implicit, autocommit=0) pool=[]
  1/ROLLBACK:  auto


The QueryOne request fails always because the INSERT request have been 
roll back. The reason is that SQLObject creates, and closes a connection 
for each SQL statement. Why this behaviour? Should it not be possible to 
open the database connection when creating the SQLObject connection and 
releasing it _only_ when its parent is destroyed by the client code?


Again, I'm ready to go into the code but I need to know the method to 
send patches.


Cyril Elkaim

RE: [SQLObject] How to insert a row from dictionary

[Ahmed M. A. <ahm...@wa...>]

Try:
	myTable.new(**myDict)

-----Message d'origine-----
De : sql...@li...
[mailto:sql...@li...]De la part de MJR
Envoyé : lundi 30 août 2004 10:50
À : sql...@li...
Objet : [SQLObject] How to insert a row from dictionary


I am wondering whether ther is a simple way to insert a row from dictionary
where keys are columns.

Something like:

myDict={'col1': val1, 'col2':val2}

myTable.new(myDict)

   instead of

myTable.new(col1=val1, col2=val2)


tx,
mike




-------------------------------------------------------
This SF.Net email is sponsored by BEA Weblogic Workshop
FREE Java Enterprise J2EE developer tools!
Get your free copy of BEA WebLogic Workshop 8.1 today.
http://ads.osdn.com/?ad_id=5047&alloc_id=10808&op=click
_______________________________________________
sqlobject-discuss mailing list
sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

[SQLObject] How to insert a row from dictionary

[MJR <py...@ce...>]

I am wondering whether ther is a simple way to insert a row from dictionary
where keys are columns.

Something like:

myDict={'col1': val1, 'col2':val2}

myTable.new(myDict)

   instead of

myTable.new(col1=val1, col2=val2)


tx,
mike

[SQLObject] Problems with validators and connectionFromURI

[Cyril E. <cy...@de...>]

Hello,

We have found the following two problems:

If someone modify a property and then read it back the 'toPython' 
validator's method is _not_ called. In our case we are using a 
'UnicaodeValidator' to store Unicode strings in UTF8 into a Postgres 
database, code follows:


class UnicodeValidator(sqlobject.include.validators.Validator):

     def toPython(self, value, state=None):
         if value is None:
             return None

         return unicode(value,"utf8")

     def fromPython(self, value, state=None):
         if value is None:
             return None

         return value.encode("utf8")


Reading an object from the database from the first time correctly calls 
the validator 'toPython' method , but not after a write. Is somebody 
else having this problem? It arises even when disabling the cache. The 
'fromPython' method _seems_ to be called every time.

My second concern is with the parsing of the 'query' part in 
connectionFromURI; the values are assigned to properties as String and 
are not evaluated, for Boolean values (like enableing and disabling the 
cache) it ask for problems as setting cache=False always make a True 
value. A simple solution is to use eval before assigning the property 
inside the code of the method, but it may raise security problems. Again 
is somebody else having this problem?

If I do a patch what is the procedure to post it and make it reviewed?

All in all the library is quite impressive and I must thank the author 
for his work.

Cyril Elkaim

[SQLObject] connection document on wiki

[Charles B. <li...@st...>]

Hi,

I've been revisiting the concept of connections and caching in SQLObject to
get a few of the nuances straight in my head.  In the process I tried to jot
these concepts down in writing and posted them on the SQLObject Wiki site
here:
http://wiki.sqlobject.org/connections.html

This process lead me to two big questions on the topic:

1.  Is there a way to manually close a connection?
2.  Is there a way to manually expire the cache of a connection?

In a prior mailing list thread I think I read that objects are not coupled
with connections, so maybe question 2 is worded incorrectly.  (this is
specifically asked with the method of passing a connection to a object in
mind).

On a more general note I'd be very interested to hear details on how others
deal with connections in their SQLObject applications, especially
multi-process applications.

Thanks in advance,
-Charles.

Re: [SQLObject] One to 'a lot' relationship

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

(oops, did not send to the list, apologies for the direct email Ian)

Ian Bicking wrote:
> 
> SVN is significantly better, though I actually thought 0.5.2 also did 
> this properly.  Anyway, it's supposed to fetch all the columns on any 
> select.
> 
> There's no database changes necessary to upgrade, but the main module 
> has changed names from SQLObject to sqlobject, and MyClass(id) is now 
> MyClass.get(id), and MyClass.new(**kw) is now MyClass(**kw).
> 
To add to the list of changes:

The connection logic is deprecated, e.g. you now need to use :

   from sqlobject.postgres import builder;
   PostgresConnection = builder();
   conn = PostgresConnection(...)

or

   connectionForURI("postgres://...")

I'm using the former as I can't seem to get the latter to work atm.

The names of the MultipleJoin properties now have an 's' appended to
them (I'm not sure if they are fully pluralised, i.e. is address now
addresses?). Also the capitalisation seems to have changed so that for a
joined table ABCUnit the property is now aBCUnits instead of the old
abcUnit - this seems a little counter intuitive, is it possible to
revert back or select the old naming scheme in some way.

In addition the rev206 SVN release still issues individual selects for
every row in the joined table, e.g.

It will issue :

SELECT id FROM address WHERE person_id = 299

and then for every returned id it will issue:

SELECT * FROM address WHERE id = 488
...
(3500 times)

where * is the name of every column in the address table.

The culprit seems to be

class FileConnection(DBConnection):

     ...

     def _SO_selectJoin(self, soClass, column, value):
         results = []
         # @@: seems lame I need to do this...
         value = int(value)
         for id in self._allIDs(soClass._table):
             d = self._fetchDict(soClass._table, id)
             if d[column] == value:
                 results.append((id,))
         return results


which overrides the default _SO_selectJoin in DBConnection.

Am I doing something wrong?

Robert

Re: [SQLObject] One to 'a lot' relationship

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

Robert Leftwich wrote:
> What is the best (read fastest) way to handle a 1-many relationship in 
> SQLObject where the many is thousands of rows? The current 0.5.2 release 
> issues an individual select for every row on the many side, which is a 
> significant bottleneck. Is the SVN version better in this regard or do I 
> need to code up something specific for this situation? If SVN is the way 
> to go, is there a specific upgrade strategy I need to follow or can I 
> just uninstall 0.5.2 and install the SVN version w/o requiring db changes?

SVN is significantly better, though I actually thought 0.5.2 also did 
this properly.  Anyway, it's supposed to fetch all the columns on any 
select.

There's no database changes necessary to upgrade, but the main module 
has changed names from SQLObject to sqlobject, and MyClass(id) is now 
MyClass.get(id), and MyClass.new(**kw) is now MyClass(**kw).

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

[SQLObject] One to 'a lot' relationship

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

What is the best (read fastest) way to handle a 1-many relationship in 
SQLObject where the many is thousands of rows? The current 0.5.2 release 
issues an individual select for every row on the many side, which is a 
significant bottleneck. Is the SVN version better in this regard or do I 
need to code up something specific for this situation? If SVN is the way 
to go, is there a specific upgrade strategy I need to follow or can I 
just uninstall 0.5.2 and install the SVN version w/o requiring db changes?


Robert

[so many questions, so few answers:-(]

Re: [SQLObject] How do ForeignKeys work?

[paul <pa...@su...>]

Thanks Ian,

Ian Bicking wrote:
> The bug is here: CA isn't defined (just like the exception says).  Maybe 
> you mean self.CA.Type?  That might not work either.
I thought it worked like class vars and instance vars...(you see I don't know 
much about the inner workings of SQLObject ;)

> In SQLObject in SVN you could do something like:
> 
> def _create(self, id, **kw):
>     if kw['CA'].Type not in ['ca_' + kw['Type'], 'ca_root']:
>         raise ValueError, ...
>     super(Cert, self)._create(id, **kw)
> 
> def _set_Type(self, value):
>     _value = ...
>     if value not in _valid: ...
>     if not self._SO_creating and self.CA.Type not in ...:
>     self._SO_set_Type(value)
Will check this.

thanks again,
  Paul

Re: [SQLObject] How do ForeignKeys work?

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

paul wrote:
> Hi,
> 
> I have to classes like so:
> 
> CaCert(CertItem):
>  SignedCerts = MultipleJoin('Cert')
>  ...
> 
> Cert(CertItem):
>  CA = ForeignKey('CaCert')
>  Type = StringCol()
>  ...
> 
>  def _set_Type(self, value):
>   _valid = ['email', 'client', 'object', 'server']
>   if value not in _valid:
>     raise ValueError('Type must be one of %s' % _valid)
>   elif CA.Type not in  ['ca_'+value, 'ca_root']:
>     raise ValueError('CA must support cert type %s.' % type)
>   self._SO_set_Type(value)

The bug is here: CA isn't defined (just like the exception says).  Maybe 
you mean self.CA.Type?  That might not work either.

In SQLObject in SVN you could do something like:

def _create(self, id, **kw):
     if kw['CA'].Type not in ['ca_' + kw['Type'], 'ca_root']:
         raise ValueError, ...
     super(Cert, self)._create(id, **kw)

def _set_Type(self, value):
     _value = ...
     if value not in _valid: ...
     if not self._SO_creating and self.CA.Type not in ...:
     self._SO_set_Type(value)

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

Re: [SQLObject] Creating a sqlite connection on Windows

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

M@ McCray wrote:
> I'm using SQLObject from SVN (rev 206) on Windows. I'm trying to connect to a
> sqlite database on a drive other than C: -- it's failing. It's failing because
> the leading slash '/' from the connection URI is being used in the filename for
> the database. So you wind up with something like:
> 
> /E:\My Test\test.db
> 
> Which doesn't work, obviously. I've tried various combinations of connection
> URIs but they all send the leading slash to sqlite.
> 
> It seems like it could be as simple as stripping the leading slash from the URI
> in SQLiteConnection.connectionFromURI(), or maybe in the constructor. But that
> might not be great for non-Windows OSes?
> 
> Now to my question: Is this just a bug I need to enter, or is there some other
> way of handling this in SQLObject that I'm just not aware of?

You should enter it as a bug.  If I don't fix it in code, I'd at least 
fix it in documentation.

For now, you should be able to use SQLiteConnection('E:/My 
Text/test.db') to define the connection.

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

[SQLObject] Creating a sqlite connection on Windows

[M. <dar...@gm...>]

I'm using SQLObject from SVN (rev 206) on Windows. I'm trying to connect to a
sqlite database on a drive other than C: -- it's failing. It's failing because
the leading slash '/' from the connection URI is being used in the filename for
the database. So you wind up with something like:

/E:\My Test\test.db

Which doesn't work, obviously. I've tried various combinations of connection
URIs but they all send the leading slash to sqlite.

It seems like it could be as simple as stripping the leading slash from the URI
in SQLiteConnection.connectionFromURI(), or maybe in the constructor. But that
might not be great for non-Windows OSes?

Now to my question: Is this just a bug I need to enter, or is there some other
way of handling this in SQLObject that I'm just not aware of?

THX,
 M@

[SQLObject] How do ForeignKeys work?

[paul <pa...@su...>]

Hi,

I have to classes like so:

CaCert(CertItem):
  SignedCerts = MultipleJoin('Cert')
  ...

Cert(CertItem):
  CA = ForeignKey('CaCert')
  Type = StringCol()
  ...

  def _set_Type(self, value):
   _valid = ['email', 'client', 'object', 'server']
   if value not in _valid:
     raise ValueError('Type must be one of %s' % _valid)
   elif CA.Type not in  ['ca_'+value, 'ca_root']:
     raise ValueError('CA must support cert type %s.' % type)
   self._SO_set_Type(value)
  ...

But instance creation fails:
server_params = {...
sampleRoot = CaCert.new(**server_params)
client_params = {'Type':'email', 'CN':'foo@bar', 'OU':'B17 Corporation',
                      'DaysValid':365, 'email':'foo@bar', 'CA':sampleRoot}
Cert.createTable(ifNotExists=True)
Cert.new(**client_params)

Traceback (most recent call last):
   File "certs.py", line 212, in ?
     testcert = Cert.new(**client_params)
   File "certs.py", line 134, in new
     obj = super(CertItem, cls).new(**kw)
   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 904, in new
     setattr(inst, name, value)
   File "certs.py", line 148, in _set_Type
     elif CA.Type not in  ['ca_'+value, 'ca_root']:
NameError: global name 'CA' is not defined

I can access the CaCert instance in new(cls, **kw) through kw['CA'] so I could 
do those checks in new but what if one do not need to override new? Is there 
another way to access attributes of CA? Generally, is it possible to do checks 
in the setter methods which depend on other Columns values?

thanks
  Paul

[SQLObject] Fedora Core 2 RPM of SQLObject

[Jonathan G. <jga...@jo...>]

I put together an RPM for Fedora Core 2 and it is available at:

http://www.jonathangardner.net/yum/jonathangardner/Fedora/2/i386/python-SQL=
Object-0.5.2-1.i386.rpm

If there's someone else maintaining an RPM for SQLObject, I'd be more than=
=20
willing to use theirs.

Thank you!

=2D-=20
Jonathan Gardner
jga...@jo...

Re: [SQLObject] Newbie question

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

paul wrote:
> whenever I try to reload a module where I have a class derived from 
> SQLObject defined I get:

You can't reload SQLObject modules.  Reloading in Python is funky, and 
it means that every class in that module is duplicated.  Since SQLObject 
resolves foreign key references by name (e.g., ForeignKey('ClassName')), 
it would cause problems when there existed two classes with the same 
name.  Worse yet when one of those classes is obsolete.

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

[SQLObject] Newbie question

[paul <pa...@su...>]

Hi all,

whenever I try to reload a module where I have a class derived from SQLObject 
defined I get:

class ConfigItem(SQLObject):
   File "C:\PROGRA~1\Python-2.3\lib\site-packages\SQLObject\SQLObject.py", line 
152, in __new__
     assert not classRegistry.get(registry, {}).has_key(className), "A database 
object by the name %s has already been created" % repr(className)
AssertionError: A database object by the name 'ConfigItem' has already been created

the module does not create an instance, it just defines the object like this:

class ConfigItem(SQLObject):
   _connection = _conn
   var = StringCol(unique=True)
   value = StringCol()

why is __new__ called when I reload the module and how can I avoid the error?

thanks
  Paul

[SQLObject] orderBy reversed

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

Hi,

I have a little problem w/ orderBy keyword of MultipleJoin().  I have the
following class:

class Album(SQLObject):
     # [ ... ]
    albumObjects =3D
MultipleJoin('AlbumObject',orderBy=3D-AlbumObject.q.modificationDate)

And when i try to use it, i get:

File "/usr/lib/python2.3/site-packages/sqlobject/joins.py", line 87, in
sorter
   orderBy =3D orderBy[1:]\nexceptions.AttributeError: SQLPrefix instance
has no attribute '__getitem__'

And in fact there's no __getitem__ method in SQLExpression (from which
SQLPrefix inheritates).
If I replace -AlbumObject.q.modificationDate by '-modificationDate', it
works like a charm. Do you think it's a bug in the sqlbuilder module or
in joins.py ?

Philippe

Re: [SQLObject] Can table be joined with itself?

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

>> Hello,
>> Is it possible to make a query like this in SQLObject?
>> SELECT B.id FROM B, A as A1, A as A2
>> WHERE A1.b_id =3D B.id AND A2.b_id =3D B.id AND A1.x =3D 123 AND A2.x =
=3D 234;
>
> Not sure what your intent is, but just in case its a simple "lookup" st=
yle
> reference back to the same table, here's a real life example:

No, it was not that case. Probably it can be done only with low-level
select(query_string), as Andrew Bennetts wrote.

BTW, I noticed that when I access MultipleJoin column, it results
in many DB queries. First only IDs are fetched, and then all rows/objects
one by one. Is there a reason, why all data is not fetched in one
query, like in eg. selectBy()?

I tried to change it to:

   def performJoin(self, inst):
       return self.otherClass.select("%s =3D %s" % (self.joinColumn, inst=
.id))

and it works for me, althogh probably won't work in every case (?).

Marcin

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

[SQLObject] Re: SQL Server and SQLObject... unit testing

[Joseph K. <jko...@ma...>]

Ian Bicking wrote:
> Joseph Kocherhans wrote:
> 
> There's not that much going on.  You just have to add a 
> mssqlConnection() function to the top, which should be easy, and then 
> run test.py -dmssql

Thanks Ian. I'll give that a try. I was worried I'd have to install and 
test all of the other dbs as well, which I really don't want to do. I'm 
willing to do the testing for sql server though.


>> Too many other things going on. Also, (since you're back Ian ;) is the 
>> addition of something like this someething you'd consider since mxODBC 
>> isn't free? 
> 
> Sure, it can go in.  The only issue is that I can't test it.  I can't 
> test Sybase either, nor MaxDB (MaxDB simply because it would be too hard 
> to install).

Cool. I've had a few people ask me for this. I'll try to get some tests 
going and get some patches to you in the next week or two.

Thanks,
Joseph

Re: [SQLObject] Can table be joined with itself?

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

On Fri, Aug 20, 2004 at 02:14:46PM +0200, Marcin Wojdyr wrote:
> Hello,
> 
> Is it possible to make a query like this in SQLObject?
> SELECT B.id FROM B, A as A1, A as A2
> WHERE A1.b_id = B.id AND A2.b_id = B.id AND A1.x = 123 AND A2.x = 234;

This might work:

B.select('A1.b_id = B.id AND A2.b_id = B.id AND A1.x = 123 AND A2.x = 234',
         clauseTables=['A as A1', 'A as A2'])
     
-Andrew.

[SQLObject] Can table be joined with itself?

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

Hello,

Is it possible to make a query like this in SQLObject?
SELECT B.id FROM B, A as A1, A as A2
WHERE A1.b_id =3D B.id AND A2.b_id =3D B.id AND A1.x =3D 123 AND A2.x =3D=
 234;

Marcin

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

Re: [SQLObject] SQL Server and SQLObject... unit testing

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

Joseph Kocherhans wrote:
> Anyone want to try to help me get some unit tests written for an mssql 
> connection that uses mxODBC? I have the code written and it seems to 
> work well, but I don't really understand how SQLObject's unit tests 
> work, and I'm not going to take the time to figure them out anytime 
> soon. 

There's not that much going on.  You just have to add a 
mssqlConnection() function to the top, which should be easy, and then 
run test.py -dmssql

> Too many other things going on. Also, (since you're back Ian ;) is 
> the addition of something like this someething you'd consider since 
> mxODBC isn't free? 

Sure, it can go in.  The only issue is that I can't test it.  I can't 
test Sybase either, nor MaxDB (MaxDB simply because it would be too hard 
to install).

> If not, have you considered methods for adding in 
> support for different databases as external modules? I couldn't figure 
> out how to get things working without modifying the following:
> 
> sqlobject/converters.py
> sqlobject/__init__.py
> sqlobject/col.py
> setup.py

Yes, those all have to be changed now.  It would be nice to separate 
this out more cleanly at some point, but it's not a big priority to me 
either.

> Ideas and comments are welcome. I also have an mxodbcda adapter for 
> zope3 in the works for use with sqlos. One step at a time though.

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

Re: [SQLObject] Hi, I'm back.

[Jeremy F. <je...@go...>]

On Wed, 2004-08-18 at 19:20 -0500, Ian Bicking wrote:
> Last night I closed most of the bugs on SourceForge.  If anyone knows of 
> other bugs, they should have put them in the bug tracker! 

I've been having problems with the caches growing without bound in 0.6.
I've been meaning to come up with a test-case to demonstrate it.

	J