[sqlalchemy] [REPEAT] ConcurrentModificationError without concurrent modification?

[maxi]

Hi,

I've a problem like this:

http://groups.google.be/group/sqlalchemy/browse_thread/thread/e3ff5bd168f5045a


Now, I'am using sqa 0.3.10, and a very complex mapper estructure.

orm.mapper(PrestacionGrupo, prestacion_grupo)

orm.mapper(Prestacion, prestacion, properties={
'grupo': relation(PrestacionGrupo),
'anexos': relation(Prestacion,
primaryjoin=prestacion.c.parent_id==prestacion.c.pre_id)
}
)

orm.mapper(ConvenioEstado, convenio_estado)

orm.mapper(Convenio, convenio, properties={
'efector': relation(Efector,
primaryjoin=Efector.c.efe_id==convenio.c.efe_id),
'estado': relation(ConvenioEstado,
primaryjoin=ConvenioEstado.c.conv_est_id==convenio.c.conv_est_id,
lazy=False),
'prestaciones': relation(Prestacion,
secondary=convenio_prestacion, lazy=True)
},
extension=ConvenioExtension()
)


I create an instance of Convenio [ convenio = Convenio() ]
I do, convenio.prestaciones.delete(obj_instance) for delete a object
from prestaciones list.
Now, if I delete one object only, this work fine, but if I delete many
(i.e: two obj) that error ocurr when I call to
session.flush([convenio]).


File C:\Python\Python25\Lib\site-packages\sqlalchemy-0.3.10-py2.5.egg
\sqlalchemy\orm\dependency.py, line 372, in process_dependencies
raise exceptions.ConcurrentModificationError(Deleted rowcount %d
does not match number of objects deleted %d % (result.rowcount,
len(secondary_delete)))
ConcurrentModificationError: Deleted rowcount 1 does not match number
of objects deleted 2


Why with deleting one object from list work fine, but this fail if I
delete two or more?.
How I can solve this?

Thanks in advance.













--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Two SqlAlchemy versions installed together

[King Simon-NFHD78]

If you're using easy_install to install them, you should be able to
install them with the '-m' (--multiversion) switch. See:

http://peak.telecommunity.com/DevCenter/EasyInstall#command-line-options

Choosing the version you want to use in your application is then done
like this:

import pkg_resources
pkg_resources.require('SQLAlchemy == 0.3.10')

Or

import pkg_resources
pkg_resources.require('SQLAlchemy = 0.4')

However, I much prefer the solution provided by virtualenv
(http://pypi.python.org/pypi/virtualenv), particularly as the number of
libraries that you depend on grow.

Hope that helps,

Simon

-Original Message-
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of maxi
Sent: 23 January 2008 13:54
To: sqlalchemy
Subject: [sqlalchemy] Two SqlAlchemy versions installed together


Can I get two SqlAlchemy versions installed together?
I need have, sqlalchemy 0.3.10 and the 0.4 last release installed
because I have two version of my application (two diferents brunches).

How can I work whit this?
In my app, how can I indicate which version I want to use?

Thanks,
M.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: [REPEAT] ConcurrentModificationError without concurrent modification?

[Michael Bayer]

On Jan 23, 2008, at 8:01 AM, maxi wrote:


 I create an instance of Convenio [ convenio = Convenio() ]
 I do, convenio.prestaciones.delete(obj_instance) for delete a object
 from prestaciones list.
 Now, if I delete one object only, this work fine, but if I delete many
 (i.e: two obj) that error ocurr when I call to
 session.flush([convenio]).


the error is specifically about a many to many table where it expects  
to delete two rows but only one matches the criteria.  If you are  
using more than one session, or removing rows from a many to many  
table using SQL statements, this error can occur.

Another condition, also mentioned in the original thread, that can  
exactly cause this issue is if you set up two many-to-many relations  
against the same table, instead of using a backref, like:

mapper(A, a, properties={
'bs':relation(B, secondary=a_to_b)
})

mapper(B, b, properties={
'as':relation(A, secondary=a_to_b)
})

The correct way to do the above is:

mapper(A, a, properties={
'bs':relation(B, secondary=a_to_b, backref='bs')
})

mapper(B, b)

hope this helps

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Can't figure out the joins across many tables with select_from()

[Alexandre Conrad]

Hello,

still working intensivly with joined table inheritance. I have troubles 
making a long join across many tables. I need to query players from 
player_table where on the other end, slots_hot.id == 'foo':

player_table = Table('players', meta,
 Column('id', Integer, primary_key=True),
 Column('description', Unicode(20)),
 Column('id_site', None, ForeignKey('sites.id'), nullable=False),
)

site_table = Table('sites', meta,
 Column('id', Integer, primary_key=True),
 Column('name', Unicode(40), nullable=False, unique=True),
 Column('type', Unicode(8)),
)

# inherits site_table
site_client_table = Table('site_clients', meta,
 Column('id', None, ForeignKey('sites.id'), primary_key=True),
 Column('id_client', None, ForeignKey('clients.id')),
)

playlist_table = Table('playlists', meta,
 Column('id', Integer, primary_key=True),
 Column('type', Unicode(8), nullable=False),
 Column('name', Unicode(30), nullable=False),
 Column('description', Unicode(40)),
)

# inherits playlist_table
playlist_site_table = Table('playlists_site', meta,
 Column('id', None, ForeignKey('playlists.id'), primary_key=True),
 Column('id_site', None, ForeignKey('site_clients.id')),
)

hotlink_table = Table('hotlinks', meta,
 Column('id', Integer, primary_key=True),
 Column('id_playlist', None, ForeignKey('playlists_site.id')),
 Column('id_hotslot', None, ForeignKey('slots_hot.id')),
)

slot_table = Table('slots', meta,
 Column('id', Integer, primary_key=True),
 Column('type', Unicode(8), nullable=False),
 Column('position', Integer, nullable=False),
 Column('match_all_options', Boolean, default=False, nullable=False),
 Column('id_slot', None, ForeignKey('slots.id')),
)

# inherits slot_table
hot_slot_table = Table('slots_hot', meta,
 Column('id', None, ForeignKey('slots.id'), primary_key=True),
 Column('name', Unicode(30)),
)

I'm trying to build the query using 
model.Player.query.select_from(player_table.join(site_table.join(site_client_table)))...
 
but then I'm getting this error when trying further 
.join(playlist_site_table):

Can't find any foreign key relationships between 'sites' and 
'_FromGrouping object'

Any idea how I could do this long join ?

Again, I tried using the clearer-to-read syntax:

model.Player.query.join(['site', 'playlists', 'hotlinks', 
'hotslot']).filter(model.SlotHot.c.id=='foo').all()

but of course, as soon as I hit inherited relations, it fails, so I have 
to build using either only filters:

.filter(Foo.c.id==Bar.c.id_foo).filter(Bar.c.id==Baz.c.id_baz).filter(...)

which makes it a 3 liner long query, or using tables directly with 
select_from(), which Im trying to do now, as show above.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Improve code

[VitaminJ]

Hi,

I have an existing piece of functionality but I am wondering if there
are ways to improve the code. A short summary what I have. I modelled
a person class which can have different roles, one being a student
role, another one being an employee role, an alumni role and so on.
Now I am trying to retrieve all persons which have an employee role
but only those that do not have the alumni role.

This is what I came up with:

query = session.query(Person).join('roles')
alumnis =
query.filter(Role.roleID==alumni_table.c.roleID).order_by(Person.lastName).all()
tmp = query.filter(Role.roleID==employee_table.c.roleID).all()
employees = list(set(tmp) - set(alumnis))
employees.sort(key=operator.attrgetter('lastName'))

It is working as expected but I thought there must be better ways to
do it. Instead of letting python do the work I would like to pass this
to sql. I found except_ in the sql expression api but I do not know
where to start with that. So if there is somebody out there who has a
smart idea how to improve the code, let me know...

Thanks and Greetings, Jan

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Using MySQLdb type conversion as engine connection argument

[Adrian]

I am trying to change the default column type mapping in sqlalchemy.
Analogous to the description in the MySQLdb User's Guide (http://mysql-
python.sourceforge.net/MySQLdb.html) I tried the following.

from MySQLdb.constants import FIELD_TYPE
my_conv = { FIELD_TYPE.DECIMAL: float }
ENGINE  = create_engine( 'mysql://%s:[EMAIL PROTECTED]:3306/%s', connect_args =
{'conv': my_conv} )

This works if I use MySQLdb directly but not with sqlalchemy. I
suspect the syntax I used is wrong. It will create an engine and
connection but as soon as a query is issued a TypeError is raised. Is
there an error in my connect_args dictionary or do I have to change
the mapping behaviour somewhere else?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Can't figure out the joins across many tables with select_from()

[Michael Bayer]

On Jan 23, 2008, at 10:25 AM, Alexandre Conrad wrote:


 Hello,

 still working intensivly with joined table inheritance. I have  
 troubles
 making a long join across many tables. I need to query players from
 player_table where on the other end, slots_hot.id == 'foo':

 player_table = Table('players', meta,
 Column('id', Integer, primary_key=True),
 Column('description', Unicode(20)),
 Column('id_site', None, ForeignKey('sites.id'), nullable=False),
 )

 site_table = Table('sites', meta,
 Column('id', Integer, primary_key=True),
 Column('name', Unicode(40), nullable=False, unique=True),
 Column('type', Unicode(8)),
 )

 # inherits site_table
 site_client_table = Table('site_clients', meta,
 Column('id', None, ForeignKey('sites.id'), primary_key=True),
 Column('id_client', None, ForeignKey('clients.id')),
 )

 playlist_table = Table('playlists', meta,
 Column('id', Integer, primary_key=True),
 Column('type', Unicode(8), nullable=False),
 Column('name', Unicode(30), nullable=False),
 Column('description', Unicode(40)),
 )

 # inherits playlist_table
 playlist_site_table = Table('playlists_site', meta,
 Column('id', None, ForeignKey('playlists.id'), primary_key=True),
 Column('id_site', None, ForeignKey('site_clients.id')),
 )

 hotlink_table = Table('hotlinks', meta,
 Column('id', Integer, primary_key=True),
 Column('id_playlist', None, ForeignKey('playlists_site.id')),
 Column('id_hotslot', None, ForeignKey('slots_hot.id')),
 )

 slot_table = Table('slots', meta,
 Column('id', Integer, primary_key=True),
 Column('type', Unicode(8), nullable=False),
 Column('position', Integer, nullable=False),
 Column('match_all_options', Boolean, default=False,  
 nullable=False),
 Column('id_slot', None, ForeignKey('slots.id')),
 )

 # inherits slot_table
 hot_slot_table = Table('slots_hot', meta,
 Column('id', None, ForeignKey('slots.id'), primary_key=True),
 Column('name', Unicode(30)),
 )

 I'm trying to build the query using
 model 
 .Player 
 .query 
 .select_from(player_table.join(site_table.join(site_client_table)))...
 but then I'm getting this error when trying further
 .join(playlist_site_table):

 Can't find any foreign key relationships between 'sites' and
 '_FromGrouping object'

join() takes a second argument which is the ON clause of the join,  
just specify that.


 Again, I tried using the clearer-to-read syntax:

 model.Player.query.join(['site', 'playlists', 'hotlinks',
 'hotslot']).filter(model.SlotHot.c.id=='foo').all()

 but of course, as soon as I hit inherited relations, it fails, so I  
 have
 to build using either only filters:

 .filter 
 (Foo.c.id==Bar.c.id_foo).filter(Bar.c.id==Baz.c.id_baz).filter(...)

are you using select_table with your mappers now ?  If so, you can  
specify criterion in terms of the subclass mappers, just like I  
illustrated in the previous thread.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] defining a StringSet type

[Jonathon Anderson]

I'm trying to use types.TypeDecorator to define a set of strings
stored in the database as a csv list, where the empty set is stored as
null. process_bind_param seems to work, as values set on the bound
field seem to get entered correctly; but process_result_value never
seems to be called when reading that field, or when loading an
instance from the database.

What am I doing wrong? (I'm using 0.4.2p3)

class StringSet (types.TypeDecorator):

A type that receives an iterable of strings, and returns a set
of those strings.

Stored in the database as a csv list of strings.


impl = types.String

def process_bind_param (self, value, engine):
if not value:
return None
else:
return ,.join(set(value))

def process_result_value (self, value, engine):
if not value:
return set()
else:
return set(value.split(,))

def copy (self):
return StringSet(self.impl.length)



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: defining a StringSet type

[Michael Bayer]

On Jan 23, 2008, at 11:09 AM, Jonathon Anderson wrote:


 I'm trying to use types.TypeDecorator to define a set of strings
 stored in the database as a csv list, where the empty set is stored as
 null. process_bind_param seems to work, as values set on the bound
 field seem to get entered correctly; but process_result_value never
 seems to be called when reading that field, or when loading an
 instance from the database.

 What am I doing wrong? (I'm using 0.4.2p3)

would have to see how you're getting results from the database.  if  
you are using textual strings, it wont work.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Using MySQLdb type conversion as engine connection argument

[jason kirtland]

Adrian wrote:
 I am trying to change the default column type mapping in sqlalchemy.
 Analogous to the description in the MySQLdb User's Guide (http://mysql-
 python.sourceforge.net/MySQLdb.html) I tried the following.
 
 from MySQLdb.constants import FIELD_TYPE
 my_conv = { FIELD_TYPE.DECIMAL: float }
 ENGINE= create_engine( 'mysql://%s:[EMAIL PROTECTED]:3306/%s', 
 connect_args =
 {'conv': my_conv} )
 
 This works if I use MySQLdb directly but not with sqlalchemy. I
 suspect the syntax I used is wrong. It will create an engine and
 connection but as soon as a query is issued a TypeError is raised. Is
 there an error in my connect_args dictionary or do I have to change
 the mapping behaviour somewhere else?

Try modifying a full MySQLdb.converters.conversions.copy() with the 
float conversion instead of that sparse mapping.  SA uses bind params 
exclusively for input so you're seeing a conversion failure sooner than 
you might with MySQLdb directly.  Try something like this:

cr = cx.cursor()
cr.execute(SELECT %s, %s, [10.2, 'quux'])
cr.fetchall()



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: [REPEAT] ConcurrentModificationError without concurrent modification?

[maxi]

 the error is specifically about a many to many table where it expects
 to delete two rows but only one matches the criteria.  If you are
 using more than one session, or removing rows from a many to many
 table using SQL statements, this error can occur.

Sorry, I'am a little confuse abut it. (I have only one session)

I've the next table schema:

convenio(conv_id)  --- conv_pre(conv_pre_id, conv_id, pre_id)  ---
prestacion(pre_id)(convenio many to many with prestacion across
conv_pre)

conv_pre has a unique index on (conv_id, pre_id) but conv_pre_id is
the primary key

Then, for simplify, I've this mapper:

mapper(Convenio, convenio, properties={
   'prestaciones': relation(Prestacion,
secondary=convenio_prestacion, lazy=True)
}
)


Now, I want to do:

pre = session.query(Prestacion).get(123)

conv = session.query(Convenio).get(1)

conv.prestaciones.delete(pre)

And this, must delete one record from conv_pre table where conv_id = 1
and pre_id = 123 (pre.pre_id)

I don't understand why this error ocurr.
You can explain me, please?

Thanks so much for your help.





























 Another condition, also mentioned in the original thread, that can
 exactly cause this issue is if you set up two many-to-many relations
 against the same table, instead of using a backref, like:

 mapper(A, a, properties={
 'bs':relation(B, secondary=a_to_b)

 })

 mapper(B, b, properties={
 'as':relation(A, secondary=a_to_b)

 })

 The correct way to do the above is:

 mapper(A, a, properties={
 'bs':relation(B, secondary=a_to_b, backref='bs')

 })

 mapper(B, b)

 hope this helps
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: [REPEAT] ConcurrentModificationError without concurrent modification?

[Michael Bayer]

On Jan 23, 2008, at 2:15 PM, maxi wrote:


 Sorry, I'am a little confuse abut it. (I have only one session)

 I've the next table schema:

 convenio(conv_id)  --- conv_pre(conv_pre_id, conv_id, pre_id)  ---
 prestacion(pre_id)(convenio many to many with prestacion across
 conv_pre)

 conv_pre has a unique index on (conv_id, pre_id) but conv_pre_id is
 the primary key

 Then, for simplify, I've this mapper:

 mapper(Convenio, convenio, properties={
   'prestaciones': relation(Prestacion,
 secondary=convenio_prestacion, lazy=True)
}
 )


 Now, I want to do:

 pre = session.query(Prestacion).get(123)

 conv = session.query(Convenio).get(1)

 conv.prestaciones.delete(pre)

 And this, must delete one record from conv_pre table where conv_id = 1
 and pre_id = 123 (pre.pre_id)

 I don't understand why this error ocurr.
 You can explain me, please?

 Thanks so much for your help.

do you have anything else mapped to the conv_pre table ?  it should  
*only* be mentioned in one place in your mapping setup.




--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Can't figure out the joins across many tables with select_from()

[Michael Bayer]

On Jan 23, 2008, at 11:55 AM, Michael Bayer wrote:



 On Jan 23, 2008, at 10:25 AM, Alexandre Conrad wrote:

 model.Player.query.join(['site', 'playlists', 'hotlinks',
 'hotslot']).filter(model.SlotHot.c.id=='foo').all()


 another thing im considering, along the lines of what I mentioned in 
 http://groups.google.com/group/sqlalchemy/browse_thread/thread/6b5b1cda1b657723#
  , would look like this:

 query.join(['site', 'playlists', ('someprop', table.join(subtable)),
 MySubclass.someprop])

 that is, join to 'someprop' using this selectable as the target.
 which is the same thing as if the mapper has select_table on it in
 this case, but is a way to specify at the query level rather than the
 mapper level.

this feature is added in 4091 if youd like to try.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: [REPEAT] ConcurrentModificationError without concurrent modification?

[maxi]

 do you have anything else mapped to the conv_pre table ?  it should
 *only* be mentioned in one place in your mapping setup.

I only have something like this:

class ConvPre(DBObject):
pass

mapper(ConvPre, conv_pre)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: [REPEAT] ConcurrentModificationError without concurrent modification?

[Michael Bayer]

On Jan 23, 2008, at 2:34 PM, maxi wrote:



 do you have anything else mapped to the conv_pre table ?  it should
 *only* be mentioned in one place in your mapping setup.

 I only have something like this:

 class ConvPre(DBObject):
pass

 mapper(ConvPre, conv_pre)

OK, then you *cannot* use conv_pre as the secondary argument in  
another relation().  You need to use an association:

http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_patterns_association

once that works, you might want to look into the association proxy,  
which is an optional convenience feature:

http://www.sqlalchemy.org/docs/04/plugins.html#plugins_associationproxy



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: defining a StringSet type

[Jonathon Anderson]

What do you mean by textual strings? Do you mean strings backed by a
TEXT type, rather than a fixed-length string?

Why won't that work?

On Jan 23, 10:57 am, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jan 23, 2008, at 11:09 AM, Jonathon Anderson wrote:



  I'm trying to use types.TypeDecorator to define a set of strings
  stored in the database as a csv list, where the empty set is stored as
  null. process_bind_param seems to work, as values set on the bound
  field seem to get entered correctly; but process_result_value never
  seems to be called when reading that field, or when loading an
  instance from the database.

  What am I doing wrong? (I'm using 0.4.2p3)

 would have to see how you're getting results from the database.  if  
 you are using textual strings, it wont work.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: defining a StringSet type

[Michael Bayer]

On Jan 23, 2008, at 3:01 PM, Jonathon Anderson wrote:


 What do you mean by textual strings? Do you mean strings backed by a
 TEXT type, rather than a fixed-length string?

 Why won't that work?

no, i mean:

result = engine.execute(select * from table)

will not work with any TypeEngine or TypeDecorators in place.

as opposed to :

result = engine.execute(table.select())

which *will* work with all the types in place.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Different results with select and object using outerjoin

[MattQc]

Hi,
I have different outputs depending on using a select statement as
oppose to an object mapped query. This is happening when I am using
outer joins.

Here is the trace of the select that gives me something:

In 1: a = select([tblspotbandsol.c.spotbandsolid, tbllane.c.laneid],
tblspotbandsol.c.spotbandsolid==32978,
from_obj=[tblspotbandsol.outerjoin(tbllane,
tbllane.c.laneid==tblspotbandsol.c.laneid)])
In 2: Session.execute(a).fetchall()

2008-01-23 15:00:39,034 INFO sqlalchemy.engine.base.Engine.0x..94
SELECT TBLSPOTBANDSOL.spotbandsolid, TBLLANE.laneid
FROM j1.TBLSPOTBANDSOL LEFT OUTER JOIN j1.TBLLANE ON
TBLLANE.laneid = TBLSPOTBANDSOL.laneid
WHERE TBLSPOTBANDSOL.spotbandsolid = :TBLSPOTBANDSOL_spotbandsolid
2008-01-23 15:00:39,035 INFO sqlalchemy.engine.base.Engine.0x..94
{'TBLSPOTBANDSOL_spotbandsolid': 32978.0}
Out2: [(Decimal(32978), None)]


Object query returns an empty set:

In 3: jLane = tblspotbandsol.outerjoin(tbllane,
tbllane.c.laneid==tblspotbandsol.c.laneid)
In 4: mapper(LaneClass,jLane)
Out4: sqlalchemy.orm.mapper.Mapper object at 0xb36f382c
In 5:
Session.query(LaneClass).filter(LaneClass.spotbandsolid==32978).all()

2008-01-23 15:00:59,379 INFO sqlalchemy.engine.base.Engine.0x..94
SELECT TBLSPOTBANDSOL.spotbandsolid AS
TBLSPOTBANDSOL_spotbandsolid, TBLSPOTBANDSOL.class AS
TBLSPOTBANDSOL_class, TBLSPOTBANDSOL.createdate AS
TBLSPOTBANDSOL_createdate, TBLLANE.createdate AS
TBLLANE_createdate, TBLSPOTBANDSOL.createusername AS
TBLSPOTBANDSOL_createusername, TBLLANE.createusername AS
TBLLANE_createusername,  TBLSPOTBANDSOL.laneid AS
TBLSPOTBANDSOL_laneid, TBLLANE.laneid AS TBLLANE_laneid,
TBLSPOTBANDSOL.ltekd AS TBLSPOTBANDSOL_ltekd, TBLLANE.lanenumber
AS TBLLANE_lanenumber, TBLLANE.label AS TBLLANE_label,
FROM j1.TBLSPOTBANDSOL LEFT OUTER JOIN j1.TBLLANE ON
TBLLANE.laneid = TBLSPOTBANDSOL.laneid
WHERE TBLSPOTBANDSOL.spotbandsolid = :TBLSPOTBANDSOL_spotbandsolid
ORDER BY TBLSPOTBANDSOL.rowid
2008-01-23 15:00:59,380 INFO sqlalchemy.engine.base.Engine.0x..94
{'TBLSPOTBANDSOL_spotbandsolid': 32978.0}
Out5: []


I am pretty new with sqlalchemy, so maybe the answer is obvious for
everyone...

Thank you
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: [REPEAT] ConcurrentModificationError without concurrent modification?

[maxi]

 OK, then you *cannot* use conv_pre as the secondary argument in
 another relation().  You need to use an association:

If I remove ConvPre class and conv_pre mapper too. This problem is
solved ?
I don't need this class and mapper really.

BTW, I will take a look to associationproxy, this look like a great
solution.

Regards.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Unique ID's

[[EMAIL PROTECTED]]

Thanks guys for your help I'm going to give Hermanns methods a go.

Morgan

Hermann Himmelbauer wrote:
 Am Montag, 21. Januar 2008 01:16 schrieb Morgan:
   
 Hi Guys,

 I have field that I want to put a unique identifier in. This unique Id i
 would like to be a composite key or simply a random number. What do you
 guys suggest for this, is there a particular method which works well for
 some of you?
 

 That's a good question, I asked myself some weeks ago, here's how I solved 
 this: 

 In my case, I have database records that have sequential numbers as primary 
 keys. These keys can be calculated by the database and are unique by design 
 (as the primary index is unique).

 This record should hold another field, which should be also unique and in a 
 form of a 8-digit number. However, I'd rather not want this number to be 
 sequential, it should look random. The first way would have been to simple 
 generate a number via random.randint(), look into the database, if 
 it's already in and if not, insert it. However, to guarantee that the number 
 is unique, one should create a unique index on this column. In case the 
 number is already there, the database will raise an error, which has to be 
 catched by the application. Another way would be to lock the table after the 
 select, so that the rare case, that another application instance inserts the 
 same number after my select, is avoided. So, the algorithm could look like 
 this (in pseudo code):

 # Variant 1 with exception handling
 while 1:
   num = random.randint()
   try:
 insert into db_table (col1, col2, col_num, col3, ) % num
   except UniqueNum_IndexViolated:
 continue
   else:
 break

 # Variant 2 with locking
 while 1:
   num = random.randint()
   lock db_table
   result = select * from db_table where col_num = num
   if result: 
 continue
   else:
 insert into db_table (col1, col2, col_num, col3, ) % num
 unlock db_table
   continue

 My problem with variant (1) was that I could not find out how to lock a whole 
 table with SQLAlchemy, moreover, each insert needs a table lock and a select, 
 which is bad performance-wise. The problem with (2) was that I did not know 
 how to catch this specific exception, as I can't simply except any database 
 error but this specific index violation (which may be different on different 
 databases).

 My third idea, which I use now, is to calculate my random number out of my 
 sequential, unique primary index, which is generated by the database during 
 the insert. One helpful guy from #sqlalchemy helped me out with 
 the randomization of the sequential number with this algorithm:

 def mk_arb_seq(id):
  Return an arbitrary number. This number is calculated out of
 the given id. For that, it is multiplied by the large prime numberA.
 Then a modulo operation with prime M where M  A. If A is
 chosen as a non-prime, the sequence is not very arbitrary,
 therefore a prime is recommended. 

 M = 9989
 A = 2760727302517

 return str((A*id) % M).zfill(len(str(M)))

 The last problem with this is that I have no real mathematical proof for that 
 algorithm, that id never maps to one number more than once. However, I 
 simply tested this with a little program and it seems to work.

 If you use the ORM, don't forget to do a session.flush() after adding the 
 object to the session, as this will calculate the primary index. Then you can 
 simply set col_num = mk_arb_seq(primary_index).

 Best Regards,
 Hermann


   


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] odd question

[Monty Taylor]

This may or may not be elixir specific...

If I have an auto-generated mapping table for a many-to-many
relationship, is there a sensible way to add another column to it that's
also has a foreign key relationship to a third table?

Like, if I had this:

Products
  id int
  name varchar

ProductTypes
  id int
  name varchar

Groups
  id int
  name varchar

and then I defined a many to many between products and groups to get

products_groups
  product_id
  group_id

and I wanted to add producttype_id to that ...

?

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: defining a StringSet type

[Jonathon Anderson]

Oh, no. I'm not doing any raw sql.

I have (at a most basic level):

things_table = Table(things, metadata,
Column(id, types.Integer, primary_key=True),
Column(values, StringSet, nullable=True),
)

class Thing (object):
pass

Session.mapper(Thing, things_table)

But when I do Thing.query.all()[0].values, process_result_value is
never called.

~jon

On Jan 23, 2:11 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jan 23, 2008, at 3:01 PM, Jonathon Anderson wrote:



  What do you mean by textual strings? Do you mean strings backed by a
  TEXT type, rather than a fixed-length string?

  Why won't that work?

 no, i mean:

 result = engine.execute(select * from table)

 will not work with any TypeEngine or TypeDecorators in place.

 as opposed to :

 result = engine.execute(table.select())

 which *will* work with all the types in place.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Different results with select and object using outerjoin

[Michael Bayer]

On Jan 23, 2008, at 3:28 PM, MattQc wrote:


 Object query returns an empty set:

 In 3: jLane = tblspotbandsol.outerjoin(tbllane,
 tbllane.c.laneid==tblspotbandsol.c.laneid)
 In 4: mapper(LaneClass,jLane)
 Out4: sqlalchemy.orm.mapper.Mapper object at 0xb36f382c
 In 5:
 Session.query(LaneClass).filter(LaneClass.spotbandsolid==32978).all()

in this case the mapper will attept to compute the natural primary  
key amongst all the columns that are being mapped; by default, it will  
be the primary key columns of tblspotbandsol combined with that of  
tbllane.  If a row does not contain non-null columns for all primary  
key columns, no instance will be returned.  You can see this happening  
if you turn on sqlalchemy.orm.mapper=DEBUG logging.

The three ways to modify this behavior are to specify  
allow_null_pks=True on the mapper so that a primary key with some  
NULL columns is accepted as a valid primary key, to specify the  
primary_key=[some columns] option to the mapper which indicates  
which columns should be counted as part of the primary key and  
overriding the auto-calculation of such, or to remove the primary key  
columns of tbllane from the columns clause of the selectable which  
you are mapping to which has a similar effect to providing  an  
explicit primary key argument.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: defining a StringSet type

[Michael Bayer]

On Jan 23, 2008, at 4:25 PM, Jonathon Anderson wrote:


 Oh, no. I'm not doing any raw sql.

 I have (at a most basic level):

 things_table = Table(things, metadata,
Column(id, types.Integer, primary_key=True),
Column(values, StringSet, nullable=True),
 )

 class Thing (object):
pass

 Session.mapper(Thing, things_table)

 But when I do Thing.query.all()[0].values, process_result_value is
 never called.

unit tests pass on this end, can you provide a full test case  
illustrating the issue ?


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: odd question

[Michael Bayer]

On Jan 23, 2008, at 4:24 PM, Monty Taylor wrote:


 This may or may not be elixir specific...

 If I have an auto-generated mapping table for a many-to-many
 relationship, is there a sensible way to add another column to it  
 that's
 also has a foreign key relationship to a third table?

 Like, if I had this:

 Products
  id int
  name varchar

 ProductTypes
  id int
  name varchar

 Groups
  id int
  name varchar

 and then I defined a many to many between products and groups to get

 products_groups
  product_id
  group_id

 and I wanted to add producttype_id to that ...


If you're asking about creating mappers, a many-to-many table has  
exactly two foreign keys defined, that of a parent table and a child  
table.  Any columns which are not part of those two foreign keys  
disqualifies the table to be considered a many-to-many table by  
SQLAlchemy's ORM; youd have to map a class to the table explicitly.

if youre just talking about adding a column to a Table, i think theres  
an append_column() method which does that(check the docstrings)



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] How to accomplish setup/run-app/teardown with mapped classes and sessions?

[Kumar McMillan]

Hello, I have not been able to figure this out from the docs.

I would like to setup and teardown test data using mapped classes.
The problem is that those same mapped classes need to be used by the
application under test and in case there is an error, the teardown
still needs to run so that subsequent tests can setup more data.  It
seemed like the setup/teardown could be accomplished with a privately
scoped session but what I see is that this private session collides
with that of the application.  Here is a failing test case (also
attached) that illustrates exactly what I need to do (sorry it's a
little long).  The reason it catches the IntegrityError is because
during testing any kind of error can happen and I need to teardown
data regardless.  Should I give up and use insert statements and
engine objects for the setup/teardown?  Or is there a way to make this
test case pass?  I am using sqlalchemy 0.4.2p3

from sqlalchemy import *
from sqlalchemy.orm import scoped_session, sessionmaker, mapper
from sqlalchemy.exceptions import IntegrityError

PrivateSession = scoped_session(
sessionmaker(autoflush=False, transactional=True),
scopefunc=lambda:__name__) # a private scope
AppSession = scoped_session(
sessionmaker(autoflush=False, transactional=True))
dsn = 'sqlite:///:memory:'

def test_sa_scoping():
engine = create_engine(dsn)
metadata = MetaData()

sometable = Table('sometable', metadata,
Column('id', Integer, primary_key=True),
Column('keyname', String(30), unique=True))
class SomeObject(object):
pass

metadata.create_all(bind=engine)
PrivateSession.configure(bind=engine)
AppSession.configure(bind=engine)
mapper(SomeObject, sometable)

fixture_session = PrivateSession()
# create some data to test with :
so = SomeObject()
so.keyname = some unique key name
fixture_session.save(so)
fixture_session.flush()

app_session = AppSession()
so2 = SomeObject()
so2.keyname = some unique key name
app_session.save(so2)
try:
app_session.flush()
except IntegrityError:
# violated unique key
pass
app_session.close()

# after testing application code, I want to tear down
# test even if the app had an error :
assert so in fixture_session
fixture_session.delete(so)
fixture_session.flush()
rs = fixture_session.query(SomeObject).all()
assert rs == [], unexpected: %s % rs

if __name__ == '__main__':
test_sa_scoping()

Traceback (most recent call last):
  File test_sa_scoping.py, line 55, in ?
test_sa_scoping()
  File test_sa_scoping.py, line 50, in test_sa_scoping
fixture_session.flush()
  File 
/Users/kumar/env/sqlalchemy-exp/lib/python2.4/site-packages/SQLAlchemy-0.4.2p3-py2.4.egg/sqlalchemy/orm/session.py,
line 693, in flush
self.uow.flush(self, objects)
  File 
/Users/kumar/env/sqlalchemy-exp/lib/python2.4/site-packages/SQLAlchemy-0.4.2p3-py2.4.egg/sqlalchemy/orm/unitofwork.py,
line 215, in flush
flush_context.execute()
  File 
/Users/kumar/env/sqlalchemy-exp/lib/python2.4/site-packages/SQLAlchemy-0.4.2p3-py2.4.egg/sqlalchemy/orm/unitofwork.py,
line 437, in execute
UOWExecutor().execute(self, tasks)
  File 
/Users/kumar/env/sqlalchemy-exp/lib/python2.4/site-packages/SQLAlchemy-0.4.2p3-py2.4.egg/sqlalchemy/orm/unitofwork.py,
line 930, in execute
self.execute_delete_steps(trans, task)
  File 
/Users/kumar/env/sqlalchemy-exp/lib/python2.4/site-packages/SQLAlchemy-0.4.2p3-py2.4.egg/sqlalchemy/orm/unitofwork.py,
line 951, in execute_delete_steps
self.delete_objects(trans, task)
  File 
/Users/kumar/env/sqlalchemy-exp/lib/python2.4/site-packages/SQLAlchemy-0.4.2p3-py2.4.egg/sqlalchemy/orm/unitofwork.py,
line 936, in delete_objects
task.mapper._delete_obj(task.polymorphic_todelete_objects, trans)
  File 
/Users/kumar/env/sqlalchemy-exp/lib/python2.4/site-packages/SQLAlchemy-0.4.2p3-py2.4.egg/sqlalchemy/orm/mapper.py,
line 1219, in _delete_obj
raise exceptions.ConcurrentModificationError(Deleted rowcount %d
does not match number of objects deleted %d % (c.rowcount,
len(del_objects)))
sqlalchemy.exceptions.ConcurrentModificationError: Deleted rowcount 0
does not match number of objects deleted 1

NOTE:

When I comment out the code that uses AppSession, this test passes.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---


from sqlalchemy import *
from sqlalchemy.orm import scoped_session, sessionmaker, mapper
from sqlalchemy.exceptions import IntegrityError

PrivateSession = scoped_session(
   

[sqlalchemy] Re: How to accomplish setup/run-app/teardown with mapped classes and sessions?

[Michael Bayer]

On Jan 23, 2008, at 5:00 PM, Kumar McMillan wrote:

 Hello, I have not been able to figure this out from the docs.

 I would like to setup and teardown test data using mapped classes.
 The problem is that those same mapped classes need to be used by the
 application under test and in case there is an error, the teardown
 still needs to run so that subsequent tests can setup more data.  It
 seemed like the setup/teardown could be accomplished with a privately
 scoped session but what I see is that this private session collides
 with that of the application.

your teardown code can't have any dependencies on the test code  
itself.  So at the very least start the teardown phase with  
PrivateSession.close() so that you start fresh.

the two ways to create tests that use isolated data are to either  
create and drop tables local to the unit tests themselves, or to run  
the unit tests within an enclosing Transaction (as in, conn =  
engine.connect(); trans = conn.begin(); session.bind=conn) which is  
rolled back at the end of the unit tests.  The SQLA unit tests  
themselves use the former method but I have applied the latter method  
to Pylons tests (and is also what you usually do with Java/Hibernate  
unit tests).



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to accomplish setup/run-app/teardown with mapped classes and sessions?

[Kumar McMillan]

Hi

On Jan 23, 2008 4:07 PM, Michael Bayer [EMAIL PROTECTED] wrote:
 your teardown code can't have any dependencies on the test code
 itself.  So at the very least start the teardown phase with
 PrivateSession.close() so that you start fresh.

I tried adding that to the teardown code but then this assertion fails:
assert so in fixture_session
and if I comment out the assertion, I get the same ConcurrentModificationError

without a stored reference to the object that was saved, I'm not sure
how to delete it [without monkeying with last inserted id].


 the two ways to create tests that use isolated data are to either
 create and drop tables local to the unit tests themselves, or to run
 the unit tests within an enclosing Transaction (as in, conn =
 engine.connect(); trans = conn.begin(); session.bind=conn) which is
 rolled back at the end of the unit tests.  The SQLA unit tests
 themselves use the former method but I have applied the latter method
 to Pylons tests (and is also what you usually do with Java/Hibernate
 unit tests).

ok, I think I see what you're saying.  Removing the
PrivateSession.close(), I tried implementing begin/rollback by
changing the app segment to:

conn = engine.connect()
AppSession.configure(bind=conn)
app_session = AppSession()
trans = conn.begin()
so2 = SomeObject()
so2.keyname = some unique key name
app_session.save(so2)
try:
app_session.flush()
except IntegrityError:
# violated unique key
trans.rollback()
else:
trans.commit()
app_session.close()

...but it still fails with the same error, Deleted rowcount 0 does not
match number of objects deleted 1.  What am I missing?  I don't
understand how the teardown code is dependent on the app code if it is
using a different session and a different connection (now) to save the
same mapped class instances.

Here is the altered test case:



from sqlalchemy import *
from sqlalchemy.orm import scoped_session, sessionmaker, mapper
from sqlalchemy.exceptions import IntegrityError

PrivateSession = scoped_session(
sessionmaker(autoflush=False, transactional=True),
scopefunc=lambda:__name__) # a private scope
AppSession = scoped_session(
sessionmaker(autoflush=False, transactional=True))
dsn = 'sqlite:///:memory:'

def test_sa_scoping():
engine = create_engine(dsn)
metadata = MetaData()

sometable = Table('sometable', metadata,
Column('id', Integer, primary_key=True),
Column('keyname', String(30), unique=True))
class SomeObject(object):
pass

metadata.create_all(bind=engine)
PrivateSession.configure(bind=engine)
mapper(SomeObject, sometable)

fixture_session = PrivateSession()
# create some data to test with :
so = SomeObject()
so.keyname = some unique key name
fixture_session.save(so)
fixture_session.flush()

conn = engine.connect()
AppSession.configure(bind=conn)
app_session = AppSession()
trans = conn.begin()
so2 = SomeObject()
so2.keyname = some unique key name
app_session.save(so2)
try:
app_session.flush()
except IntegrityError:
# violated unique key
trans.rollback()
else:
trans.commit()
app_session.close()

# after testing application code, I want to tear down
# test even if the app had an error :
assert so in fixture_session
fixture_session.delete(so)
fixture_session.flush()
rs = fixture_session.query(SomeObject).all()
assert rs == [], unexpected: %s % rs

if __name__ == '__main__':
test_sa_scoping()


same exception...

sqlalchemy.exceptions.ConcurrentModificationError: Deleted rowcount 0
does not match number of objects deleted 1

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to accomplish setup/run-app/teardown with mapped classes and sessions?

[jason kirtland]

Kumar McMillan wrote:
 Hello, I have not been able to figure this out from the docs.
 
 I would like to setup and teardown test data using mapped classes.
 The problem is that those same mapped classes need to be used by the
 application under test and in case there is an error, the teardown
 still needs to run so that subsequent tests can setup more data.  It
 seemed like the setup/teardown could be accomplished with a privately
 scoped session but what I see is that this private session collides
 with that of the application.  Here is a failing test case (also
 attached) that illustrates exactly what I need to do (sorry it's a
 little long).  The reason it catches the IntegrityError is because
 during testing any kind of error can happen and I need to teardown
 data regardless.  Should I give up and use insert statements and
 engine objects for the setup/teardown?  Or is there a way to make this
 test case pass?  I am using sqlalchemy 0.4.2p3

You're stepping on yourself here with sqlite- you have two transactional 
sessions, but an in-memory SQLite database only allows one connection 
and it and it's underlying transactional state is being shared by both 
sessions.  Specifically, the app_session.close() is rolling back the 
uncommited row flushed by fixture_session.flush().

This setup will work almost as-is when testing against a 
multi-connection database- you just need to commit() your fixture data 
after that flush() so that it will be visible to the app part of the test.

That will get this going for sqlite as well but you'd want to think 
through what a single connection shared between setup/teardown and test 
execution means to your tests.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to accomplish setup/run-app/teardown with mapped classes and sessions?

[Kumar McMillan]

On Jan 23, 2008 4:36 PM, Kumar McMillan [EMAIL PROTECTED] wrote:
 ...but it still fails with the same error, Deleted rowcount 0 does not
 match number of objects deleted 1.  What am I missing?  I don't
 understand how the teardown code is dependent on the app code if it is
 using a different session and a different connection (now) to save the
 same mapped class instances.

ah, I just had to put the setup/teardown routines in their own
respective transactions too.  Now it passes.  Thanks!  Next... to see
if I can clean it up a bit and fit it into my app.

Passing test:


from sqlalchemy import *
from sqlalchemy.orm import scoped_session, sessionmaker, mapper
from sqlalchemy.exceptions import IntegrityError

PrivateSession = scoped_session(
sessionmaker(autoflush=False, transactional=True),
scopefunc=lambda:__name__) # a private scope
AppSession = scoped_session(
sessionmaker(autoflush=False, transactional=True))
dsn = 'sqlite:///:memory:'

def test_sa_scoping():
engine = create_engine(dsn)
metadata = MetaData()

sometable = Table('sometable', metadata,
Column('id', Integer, primary_key=True),
Column('keyname', String(30), unique=True))
class SomeObject(object):
pass

metadata.create_all(bind=engine)
mapper(SomeObject, sometable)

conn = engine.connect()
PrivateSession.configure(bind=conn)
trans = conn.begin()
fixture_session = PrivateSession()
# create some data to test with :
so = SomeObject()
so.keyname = some unique key name
fixture_session.save(so)
fixture_session.flush()
trans.commit()

conn = engine.connect()
AppSession.configure(bind=conn)
app_session = AppSession()
trans = conn.begin()
so2 = SomeObject()
so2.keyname = some unique key name
app_session.save(so2)
try:
app_session.flush()
except IntegrityError:
# violated unique key
trans.rollback()
else:
trans.commit()
app_session.close()

# after testing application code, I want to tear down
# test even if the app had an error :
assert so in fixture_session
trans = conn.begin()
fixture_session.delete(so)
fixture_session.flush()
trans.commit()
rs = fixture_session.query(SomeObject).all()
assert rs == [], unexpected: %s % rs

if __name__ == '__main__':
test_sa_scoping()

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Asynchronous SQLAlchemy

[Mike Lewis]

Hi All,

I'm starting a new project that will probably be using Twisted, but I
want to use SQLAlchemy as well.  I saw a couple of old posts about
Asynchronous SQLAlchemy and two different implementations, but neither
sAsync nor nadbapi seem to be maintained (or at least have new
versions posted), and I don't think they'd be compatible with version
4.x of SQLAlchemy.

Any pointers?

Thanks,
Mike
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: defining a StringSet type

[Jonathon Anderson]

So, in constructing my test case, I figured out what was going on.
(I'm sure this is often the case.)

http://pastebin.com/m612561a6

The problem is that process_result_value is only called when actually
loading values from the database, and since a session maintains an
object cache for object identity persistence, it it doesn't update the
instance value.

Is there some way to get the behavior I want in
test_values_sameinstance without closing/clearing the session, or
duplicating my logic in a python property?

~jon

On Jan 23, 3:31 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jan 23, 2008, at 4:25 PM, Jonathon Anderson wrote:





  Oh, no. I'm not doing any raw sql.

  I have (at a most basic level):

  things_table = Table(things, metadata,
     Column(id, types.Integer, primary_key=True),
     Column(values, StringSet, nullable=True),
  )

  class Thing (object):
     pass

  Session.mapper(Thing, things_table)

  But when I do Thing.query.all()[0].values, process_result_value is
  never called.

 unit tests pass on this end, can you provide a full test case  
 illustrating the issue ?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: defining a StringSet type

[Michael Bayer]

On Jan 23, 2008, at 10:16 PM, Jonathon Anderson wrote:


 So, in constructing my test case, I figured out what was going on.
 (I'm sure this is often the case.)

 http://pastebin.com/m612561a6

 The problem is that process_result_value is only called when actually
 loading values from the database, and since a session maintains an
 object cache for object identity persistence, it it doesn't update the
 instance value.

 Is there some way to get the behavior I want in
 test_values_sameinstance without closing/clearing the session, or
 duplicating my logic in a python property?

sure the python property version is illustrated here:

http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_overriding

depending on what youre doing this may remove the need to have a  
custom column type.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Problem with session and instance

[Alex Turner]

Thanks for the info - what is the current version?

Alex

On Jan 22, 2008 2:36 PM, Michael Bayer [EMAIL PROTECTED] wrote:


 One thing I can see is that you're using an older version of SA, since
 that error message has been changed (either 0.4.0 or an old 0.3
 version).  It means that you cannot issue save() on an instance that
 is already persistent, you need to use update() or save_or_update().
 Also if youre on the 0.4.0 series I strongly recommend getting on the
 latest version.

 On Jan 22, 2008, at 2:18 PM, Alex Turner wrote:

  I am getting an error when I try to save an object for the second
  time.  The things I don't get is that I believe I closed that
  session with a session.close()
 
  Alex
 
  Traceback (innermost last):
Module ZPublisher.Publish, line 119, in publish
Module ZPublisher.mapply, line 88, in mapply
Module ZPublisher.Publish, line 42, in call_object
Module zope.formlib.form, line 769, in __call__
Module Products.Five.formlib.formbase , line 55, in update
Module zope.formlib.form, line 750, in update
Module zope.formlib.form, line 594, in success
Module plone.app.controlpanel.form, line 26, in handle_edit_action
Module mls.agent.browser.client_settings , line 54, in _on_save
Module sqlalchemy.orm.session, line 418, in save
Module sqlalchemy.orm.session, line 577, in _save_impl
  InvalidRequestError: Instance
  'mls.agent.client_settings_impl.ClientSettings object at
  0xe2e3a4c' is a detached instance or is already persistent in a
  different Session
 
 
 
  


 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: defining a StringSet type

[Jonathon Anderson]

I understand that solution. I did say without a python property.

But if that's the only real way to do it, so be it. Maybe I was just
looking for an excuse to learn how to define a custom type. ;)

~jon

On Jan 23, 9:56 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jan 23, 2008, at 10:16 PM, Jonathon Anderson wrote:



  So, in constructing my test case, I figured out what was going on.
  (I'm sure this is often the case.)

 http://pastebin.com/m612561a6

  The problem is that process_result_value is only called when actually
  loading values from the database, and since a session maintains an
  object cache for object identity persistence, it it doesn't update the
  instance value.

  Is there some way to get the behavior I want in
  test_values_sameinstance without closing/clearing the session, or
  duplicating my logic in a python property?

 sure the python property version is illustrated here:

 http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_...

 depending on what youre doing this may remove the need to have a  
 custom column type.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to accomplish setup/run-app/teardown with mapped classes and sessions?

[svilen]

apart of all runtime issues u've hit - session etc - on declaration 
level u'll may do these:
 - destroy all your refs to the mappers/tables etc
 - sqlalchemy.orm.clearmappes()
 - yourmetadata.drop_all()
 - yourengine.dispose()
 - destroy refs to yourmetadata/yourengine
(dbcook.usage.sa_manager.py/destroy())
if your classes are made-on-the-fly inside some function, then every 
time they are _different_ classes. Make sure u do not hold them 
somewhere - or they will keep all the stuff asociated with them 
(mappers and even session). The reason is the replaced __init__ 
method is stored as class.__init (in orm.attributes.register_class) 
and is not cleared in clearmappers.

for easy check, u can run your test 100 times and watch the memory 
used; if it grows then _something_ of all those above is not cleared.

ciao
svilen

On Thursday 24 January 2008 00:00:52 Kumar McMillan wrote:
 Hello, I have not been able to figure this out from the docs.

 I would like to setup and teardown test data using mapped classes.
 The problem is that those same mapped classes need to be used by
 the application under test and in case there is an error, the
 teardown still needs to run so that subsequent tests can setup more
 data.  

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---