Re: [sqlalchemy] How to return object with maximum column via a mapper?

[Hermann Himmelbauer]

Am Donnerstag 18 Februar 2010 02:07:15 schrieb Michael Bayer:
 On Feb 17, 2010, at 12:27 PM, Hermann Himmelbauer wrote:
  Hi,
  I have the following many to one relation:
 
  - A bank account table (acc)
  - An Interest rate table, which relates to the account table. Colums are
  an ID, a rate (decimal), a date and a flag outlining if the interest rate
  is credit or debit ('H' / 'S')
  - One account may have multiple interest rates (one to many)
 
  What I now want is to retrieve the most recent (the current valid)
  interest rate for a specific account. For now, I did this like this:
 
  mapper_acc = mapper(Acc, table_acc, properties = {
 # Current debit irate
 'current_debit_irate': relation(
 IRate, order_by = table_irate.c.date.desc(),
 uselist = False, primaryjoin = and_(
 table_acc.c.accid == table_irate.c.accid,
 table_irate.c.type == S),
 cascade=all)
  })
 
 
  This works but is very inefficient, as this mapper seems to read in all
  interest rate objects despite I use uselist=False, which is slow. So I
  wonder if it's possible to optimize this in some way so that SQLAlchemy
  constructs some specific SQL, something like:
 
  select * from irates where irateid = (select irateid, max(date) from
  irates where accid = 123 and type = 'S' group by date)

 Here, you'd build the query representing the max() for your related item,
 then create a non-primary mapper which maps IRate to it.  Build your
 relation then using that nonprimary mapper as the target.  I think i just
 showed this to someone on this list about a week ago.


 iratealias = irate.alias()
 i.e. irate_select = select(irate).where(irate.c.id=select([iratealias.c.id,
 max(date)]).where(...)).alias()

 irate_mapper = mapper(IRate, irate_select, non_primary=True)
 mapper(Acc, acc, properties={current_irate, relation(irate_mapper)})

Wow, that was complicated. I managed to simplify (and probably speed up) the 
query by using order_by with limit. To sum up, I tried the following:

1) Your idea with a slightly modified mapper:
irate_select = 
select([table_irate]).alias().order_by(table_irate.c.date.desc()).limit(1).alias()
irate_mapper = mapper(IRate, irate_select, non_primary = True)
mapper(Acc, acc, properties={current_debit_irate, relation(irate_mapper)})

--- Did not work as my database (MaxDB) unfortunately does not support order 
by in subqueries

2) Hint from stepz via #freenode:
# Current debit irate
'current_debit_irate_new2': relation(
IRate, uselist=False, 
primaryjoin=table_irate.c.irateid == select(
[table_irate.c.irateid], table_irate.c.accid == table_acc.c.accid
).correlate(table_acc).order_by(
table_irate.c.date.desc()).limit(1)),

 Does also not work for the same reason.

3) Hint in another list reply: Use the original with lazy='dynamic'

# Dynamic loading
'current_debit_irate_dynamic': relation(
IRate, order_by = table_irate.c.date.desc(),
uselist = False, lazy = 'dynamic', primaryjoin = and_(
table_acc.c.accid == table_irate.c.accid,
table_irate.c.type == S),
cascade=all),

And then in my class, I have something like this:
@property
def current_debit_irate(self):
return self.current_debit_irate_query[0]

 This adds a limit 1 at the end of the query and acutally works! Great!

-- Conclusion ---

To my mind, an excellent thing would be to add something like limit similar 
to order_by to the relation() specifier, as this would then save all of the 
above.

Best Regards,
Hermann

-- 
herm...@qwer.tk
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

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

[sqlalchemy] How to return object with maximum column via a mapper?

[Hermann Himmelbauer]

Hi,
I have the following many to one relation:

- A bank account table (acc)
- An Interest rate table, which relates to the account table. Colums are an 
ID, a rate (decimal), a date and a flag outlining if the interest rate is 
credit or debit ('H' / 'S')
- One account may have multiple interest rates (one to many)

What I now want is to retrieve the most recent (the current valid) interest 
rate for a specific account. For now, I did this like this:

mapper_acc = mapper(Acc, table_acc, properties = {
# Current debit irate
'current_debit_irate': relation(
IRate, order_by = table_irate.c.date.desc(),
uselist = False, primaryjoin = and_(
table_acc.c.accid == table_irate.c.accid,
table_irate.c.type == S),
cascade=all)
})


This works but is very inefficient, as this mapper seems to read in all 
interest rate objects despite I use uselist=False, which is slow. So I 
wonder if it's possible to optimize this in some way so that SQLAlchemy 
constructs some specific SQL, something like:

select * from irates where irateid = (select irateid, max(date) from irates 
where accid = 123 and type = 'S' group by date)

Any clues?

Best Regards,
Hermann

-- 
herm...@qwer.tk
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

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

Re: [sqlalchemy] SQLAlchemy 5.4.p1 - RuntimeError - why?

[Hermann Himmelbauer]

Am Dienstag 24 November 2009 22:23:49 schrieb Michael Bayer:
 I see 0.5.4p1 !  upgrading is always the first response.   this was a
 bug that was fixed.

Thanks a lot, this seems to have solved it!

Best Regards,
Hermann

-- 
herm...@qwer.tk
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--

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

[sqlalchemy] SQLAlchemy 5.4.p1 - RuntimeError - why?

[Hermann Himmelbauer]

Hi,
I'm experiencing some interesting error here with SQLAlchemy:
When I execute one of my functions, which do a simple session.query, the 
following traceback occurs, but only sometimes - no clue why and when:

--- snip ---
  File /home/bank/zbsp/buildout/src/bsp/bsp/torder.py, line 183, in 
search_torder_tmpl
customer = get_customer(bspconf, session, customerid=customerid)
  File /home/bank/zbsp/buildout/src/bsp/bsp/customer.py, line 71, in 
get_custo
mer
customer = session.query(Customer).filter_by( 
File 
/home/bank/zbsp/buildout/eggs/SQLAlchemy-0.5.4p1-py2.4.egg/sqlalchemy/orm/query.py,
 
line 1226, in first
ret = list(self[0:1]) 
File 
/home/bank/zbsp/buildout/eggs/SQLAlchemy-0.5.4p1-py2.4.egg/sqlalchemy/orm/query.py,
 
line 1147, in __getitem__
return list(res) 
File 
/home/bank/zbsp/buildout/eggs/SQLAlchemy-0.5.4p1-py2.4.egg/sqlalchemy/orm/query.py,
 
line 1286, in __iter__
self.session._autoflush() 
File 
/home/bank/zbsp/buildout/eggs/SQLAlchemy-0.5.4p1-py2.4.egg/sqlalchemy/orm/session.py,
 
line 899, in _autoflush
self.flush() 
File 
/home/bank/zbsp/buildout/eggs/SQLAlchemy-0.5.4p1-py2.4.egg/sqlalchemy/orm/session.py,
 
line 1354, in flush
self._flush(objects) 
File 
/home/bank/zbsp/buildout/eggs/SQLAlchemy-0.5.4p1-py2.4.egg/sqlalchemy/orm/session.py,
 
line 1359, in _flush
if (not self.identity_map.check_modified() and 
File 
/home/bank/zbsp/buildout/eggs/SQLAlchemy-0.5.4p1-py2.4.egg/sqlalchemy/orm/identity.py,
 
line 56, in check_modified
for state in self._mutable_attrs:
RuntimeError: dictionary changed size during iteration
- snip --

Any hints about what to do?

Best Regards,
Hermann


-- 
herm...@qwer.tk
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--

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

[sqlalchemy] How to check for references before deleting?

[Hermann Himmelbauer]

Hi,
I use in my database tables, that reference other tables, e.g. table A - 
table B. In my deletion routine, I want to prohibit people to delete rows 
from A, which have references to B.

How would I do that?

My first approach was to rely on the databases referential integrity and catch 
related dabases errors, however, SQLAlchemy works around these references, as 
due to the cascade = all rule in the mapper, the rows of table B are 
simply deleted before.

On the other hand, I can't simply omit cascade=all, as I need this for 
object creation. passive_deletes, delete_orphan and the like are also no 
solution.

What I currently do is to manually check for related objects and raise an 
error if I find one, but I estimate that there will be some SQLAlchemy trick 
that does that for me?

Best Regards,
Hermann

-- 
herm...@qwer.tk
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] session.begin_nested() (=Savepoints) on SQLite

[Hermann Himmelbauer]

Hi,
I'm currently trying to use savepoints on SQLite, however, there are some 
problems with it which I don't understand. The following traceback occurs 
quite often:

- snip --
  File /home/dusty/prog/bsp/buildout/src/bsp/bsp/tests/__init__.py, line 
156, in checkbooking
session.rollback()
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/session.py,
 
line 649, in rollback
self.transaction.rollback()
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/session.py,
 
line 404, in rollback
transaction._rollback_impl()
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/session.py,
 
line 415, in _rollback_impl
t[1].rollback()
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py,
 
line 1028, in rollback
self._do_rollback()
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py,
 
line 1068, in _do_rollback
self.connection._rollback_to_savepoint_impl(self._savepoint, 
self._parent)
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py,
 
line 763, in _rollback_to_savepoint_impl
self.engine.dialect.do_rollback_to_savepoint(self, name)
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/default.py,
 
line 113, in do_rollback_to_savepoint
connection.execute(expression.RollbackToSavepointClause(name))
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py,
 
line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py,
 
line 874, in _execute_clauseelement
return self.__execute_context(context)
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py,
 
line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement, 
context.parameters[0], context=context)
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py,
 
line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, 
context)
  
File 
/home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py,
 
line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e, 
connection_invalidated=is_disconnect)
OperationalError: (OperationalError) no such savepoint: sa_savepoint_1 
u'ROLLBACK TO SAVEPOINT sa_savepoint_1' []
- snip --

The very same code works well with SAPDB, so I'm unsure if the reason for that 
lies in my code.

Any hints?

Best Regards,
Hermann

-- 
herm...@qwer.tk
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Session does not write updated object attribute (no SQL update) (SA 0.4.6)

[Hermann Himmelbauer]

Hi,
I created a custom type that represents a string as list of int(elements), 
e.g.:

 - [2, 2, 2, 2]

The class I created looks as follows:

class IntList(types.TypeDecorator):
List of integers that is represented by string in the RDB
impl=String

def process_bind_param(self, value, engine):
if value == [] or value is None:
return None
else:
return ''.join((str(x) for x in value))
def process_result_value(self, value, engine):
if value is None:
return []
else:
return [int(x) for x in value]

This data type is then used in one of my tables, which is represented by an 
object.

The problem I have is that changing items in the list seems not to trigger SA, 
so that a session.flush() does not perform an update, e.g.

myobj.intlist[0] = 8

When I, however, do the following, it works:

l = list(myobj.intlist)
l[0] = 8
myobj.intlist = l

On the other hand, the following does not:

myobj.intlist[0] = 8
l = list(myobj.intlist)
myobj.intlist = l

Can please someone enlighten me why this happens, and what I can do about it?

Best Regards,
Hermann


-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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 create constraints for mappers (for SA 0.4.7)?

[Hermann Himmelbauer]

Am Montag 25 August 2008 22:37:47 schrieb Michael Bayer:
 On Aug 25, 2008, at 12:22 PM, Hermann Himmelbauer wrote:
  Hi,
  I'm wondering if the following is possible with mappers:
 
  I have two tables A and B, whereas B is referenced by A. What I need
  is a
  special mapper for A that returns all referenced rows in B except
  those, that
  have a specific value in one column of the row, e.g.:
 
  Table A:
  id, sometext, reftoB
  1, xyz, 1
  2, abc, 2
 
  Table B:
  id, sometext
  1, foo
  2, NOTTHISONE - Should not be returned by the mapper
 
  Is there a simple way to achieve this?

 I'm not 100% clear on what you're asking since it seems like you are
 looking for a list of A based on something in B, but you said
 returns all referenced rows in B.   If you just wanted all Bs
 without sometext=NOTTHISONE thats just query(B).filter(B.sometext!
 ='NOTTHISONE').

Yes, that's exactly what I need, yes. The only thing is, that I'd like to have 
this as a mapper property.

The background for all this is the following:

- In my existing database, I have relations to other tables, which don't 
necessarily need to exist, so it's simply a ForeignKey without not null.
- Unfortunately, the design is based on an old, existing database, which did 
not support NULL / referential integrity, therefore we inserted 0 for NULL 
(= no reference). This logic is implemented in a very old C-Client code, 
which I don't want to overwork, but to which we need to maintain 
compatibility.
- With SQLAlchemy, ForeignKey() creates referential integrity, which is 
basically a good thing, however, these 0 references, which have no 
counterpart in another table, violate the integrity constraints.

So there are basically two options:

- Somehow tell the database, that 0 is a NULL value, too. (That does not 
seem to be possible).
- Somehow disable referential integrity (but I don't really know how, 
moreover, I'd probably have to rewrite the mappers, whereas I also don't 
really know how).
- Insert a dummy row, which id 0 into all referenced tables. The old C-Code 
would never get these, as it does not try to reference 0-references. The 
only thing to do here would be to rewrite my mappers, so that they don't get 
these 0-references back.

Thanks for help!

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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 create constraints for mappers (for SA 0.4.7)?

[Hermann Himmelbauer]

Am Dienstag 26 August 2008 01:41:20 schrieb David Gardner:
 I think what you might want to do is something like this:

 a_query=select([a_table,b_table],
 b_table.c.sometext!='NOTHISONE').alias('a_query')
 mapper(AObject, q_query)

Interesting, thanks - yes, that might a solution, although, if I understand it 
right, this query draws the two tables together into one, right? (and q_query 
== a_query?).

Best Regards,
Hermann


-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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 create constraints for mappers (for SA 0.4.7)?

[Hermann Himmelbauer]

Hi,
I'm wondering if the following is possible with mappers:

I have two tables A and B, whereas B is referenced by A. What I need is a 
special mapper for A that returns all referenced rows in B except those, that 
have a specific value in one column of the row, e.g.:

Table A:
id, sometext, reftoB
1, xyz, 1
2, abc, 2

Table B:
id, sometext
1, foo
2, NOTTHISONE - Should not be returned by the mapper

Is there a simple way to achieve this? 

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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] Display SQL of create table

[Hermann Himmelbauer]

Hi,
Is there a simple way to display the create statements of SA Tables? If I do a 
my_table.create(), some SQL is created and executed, is there a way to find 
retrieve this as a string without creating the table?

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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: Multiple encodings in my database

[Hermann Himmelbauer]

Am Freitag, 27. Juni 2008 01:20 schrieb Michael Bayer:
 first of all, the stack trace suggests you have not set the encoding
 parameter on create_engine() as it's still using UTF-8.

 If you mean that a single database column may have different encodings
 in different rows, you want to do your own encoding/decoding with
 encoding errors set to something liberal like ignore.  You also
 need to use your own custom type, as below:

 from sqlalchemy import types
 class MyEncodedType(types.TypeDecorator):
   impl = String

   def process_bind_param(self, value, dialect):
   assert isinstance(value, unicode)
   return value.encode('latin-1')

   def process_result_value(self, value, dialect):
   return value.decode('latin-1', 'ignore')

 then use MyEncodedType() as the type for all your columns which
 contain random encoding.   No convert_unicode setting should be used
 on your engine as this type replaces that usage.

Perfect, that works, thanks!

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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] Multiple encodings in my database

[Hermann Himmelbauer]

Hi,
I'm trying to access a database via SA, which contains varchars with 
different, arbitrary encodings. Most of them are ascii or ISO-8859-2 encoded, 
however, many are windows-1252 encoded and there are also some other weird 
ones.

In my engine setup, I set the encoding to latin1 and set convert_unicode to 
True, as I my application requires the database values in unicode format.

If SA now tries to retrieve such a key, the following traceback occurs:

--
  File /home/dusty/prog/python_modules/sqlalchemy/engine/base.py, line 1605, 
in _get_col
return processor(row[index])
  File /home/dusty/prog/python_modules/sqlalchemy/databases/maxdb.py, line 
112, in process
return value.decode(dialect.encoding)
  
File /local/home/dusty/python/Python-2.4.4/lib/python2.4/encodings/utf_8.py, 
line 16, in decode
return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode bytes in position 3-6: invalid 
data
-

What can I do? It's not so important that all characters are correctly 
displayed, but it's vital that such improper encodings do not crash my 
application. Perhaps, there's some universal encoding that is able to deal 
with such problems?

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

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

[Hermann Himmelbauer]

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


-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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] Error while setting default dates on a table with default=func.now()

[Hermann Himmelbauer]

Hi,
I have a table that looks appr. like this (I deleted some columns):

table_acc = Table(
'konto', metadata,
Column('kontoid', Integer, primary_key=True, key='accid'),
Column('erdat', DateTime, default=func.now(), key='datopen'))

What I'd like to accomplish is that when during an insert the value for the 
column erdat is None, the current time should be inserted. But when I try 
to insert data, the following error is raised:

File /home/dusty/prog/python_modules/sqlalchemy/engine/base.py, line 935, in 
_handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e, 
connection_invalidated=is_disconnect)
OperationalError: (OperationalError) no such function: now u'INSERT INTO 
konto (kontonr, ktobez, kapital, disposal, ktogeb, gebuehren, rahmen, erdat, 
datlabs, standlabs, ktostatus, datsal, waehrungid) VALUES 
(?, ?, ?, ?, ?, ?, ?, now(), now(), ?, ?, ?, ?)' 
['5', 'Bankkonto', '01000.50', '11000.50', 
'0.00', '0.00', '0.00', '0.00', 'A', 
None, 1]

What can I do about this? It seems, that now() is not recognized, but how is 
this possible?

I am using SA 0.4.1, SA0.4.2p3 + Python-2.4.4 and sqllite.

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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: Error while setting default dates on a table with default=func.now()

[Hermann Himmelbauer]

Am Freitag, 18. Januar 2008 22:43 schrieb Michael Bayer:
 On Jan 18, 2008, at 10:59 AM, Hermann Himmelbauer wrote:
  Hi,
  I have a table that looks appr. like this (I deleted some columns):
 
  table_acc = Table(
 'konto', metadata,
 Column('kontoid', Integer, primary_key=True, key='accid'),
 
  What can I do about this? It seems, that now() is not recognized,
  but how is
  this possible?
 
  I am using SA 0.4.1, SA0.4.2p3 + Python-2.4.4 and sqllite.

 with sqlite use func.current_timestamp() instead of now().

Yes, this did the trick - thank's a lot!
One more question: I user sqlite only for testing my database application, for 
the real thing I'll use MaxDB and/or postgresql; Will 
func.current_timestamp() work for these databases, or should I set up some 
proxy function that returns either now() or current_timestamp() regarding to 
the database?

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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: SQLAlchemy 0.4.0 Released

[Hermann Himmelbauer]

Am Mittwoch, 17. Oktober 2007 21:40 schrieb Michael Bayer:
 Hey list -

 I'm very happy to announce that we've put out 0.4.0 final.   Thanks
 to all the contributors as well as all the beta testers who have
 helped us move through six beta releases, just to make sure we've got
 everything right (or as much right as we can).  For those still
 working with 0.3, its time to upgrade ! :)  Lots of folks have
 already done it and it's not so hard.  I think this is the most well
 documented and easy to use SQLAlchemy yet, and its definitely the
 fastest by a wide margin...many kinds of operations are 50% faster
 and the large majority of applications should be at least 20-30%
 faster.   We now have hotspot profiling tests as part of our
 testsuite so that performance-reducing changes immediately raise red
 flags (and of course, performance-increasing changes unleash a shower
 of balloons).

Congratulations! That's a huge step forward!

Best wishes,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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: Aliasing table and column names

[Hermann Himmelbauer]

Am Montag, 15. Oktober 2007 20:14 schrieb Michael Bayer:
 HI Hermann -

 table/column naming looks like this:

 mytable = Table('sometablename', metadata,
   Column('colname', Integer, key='someothername')
 )

 access the column:

 mytable.c.someothername

 so above, we access the real database table sometablename.colname
 using an expression such as mytable.c.someothername, so both names
 are modified in the application against what they are in the database.

Perfect, thanks!

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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] Aliasing table and column names

[Hermann Himmelbauer]

Hi,
I'm currently implementing an application, which accesses a database that's 
implemented in german. Table and column names are all in german.

As some english-only speaking programmers are involved in the development, I'm 
considering renaming them to english, as otherwise, these german identifiers 
will be all over the Python code. However, I must not directly rename them in 
the database itself, as other older applications are depending on the german 
identifiers.

But, perhaps there's a way to alias the table and column names via SA in some 
way? 

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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] Bug in SA-0.4b6

[Hermann Himmelbauer]

Hi,
I have problem here with SA-0.4b6:

I created an own table that inherits from sqlalchemy.Table like this:

class MyTable(sqlalchemy.Table):
   pass

When I now create tables using this class (e.g. mytable = MyTable(...)), the 
tables are not created via metadata.create_all().

The log files denotes it, it has the following lines:

INFO sqlalchemy.engine.base.Engine 0x..10 PRAGMA table_info(mytable)
INFO sqlalchemy.engine.base.Engine.0x..10 ()

But the following, important lines, are missing (they are there when mytable's 
class is sqlalchemy.Table):

INFO sqlalchemy.engine.base.Engine.0x..10
CREATE TABLE mytable (
   myid .
   
)

INFO sqlalchemy.engine.base.Engine.0x..10 None
INFO sqlalchemy.engine.base.Engine.0x..10 COMMIT

With SA-3.10 I don't have this problem.

So, I assume, this is a bug?

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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: Feature suggestion: Description attribute in Tables/Columns

[Hermann Himmelbauer]

Am Mittwoch, 19. September 2007 23:44 schrieb Paul Johnston:
 Hi,

 For my documentation, it is very handy to gather the table definitions
 directly from my SQLAlchemy-based python code - that's relatively easy, I

 This would be useful for me too, and it would be good to have hooks to
 store arbitrary information. I'd be using this as hints for a form
 builder, just like the Django auto-admin does.

I currently do it like this:

class DocTable(Table):
 Extends Table by adding a description attribute 
def __init__(self, name, metadata, **kwargs):
 Extract some parameters and call parent 
description =  kwargs.pop('description', None)
self.description = description
super(DocTable, self).__init__(name, metadata, **kwargs)

class DocColumn(Column):
 Extends Column by adding a description attribute 
def __init__(self, name, type_, *args, **kwargs):
 Extract some parameters and call parent 
description =  kwargs.pop('description', None)
self.description = description
super(DocColumn, self).__init__(name, type_, *args, **kwargs)

And then I use DocTable / DocColumn instead of Table/Column.

Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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: Automatically loading data into objects

[Hermann Himmelbauer]

Am Freitag, 14. September 2007 23:52 schrieb jason kirtland:
 Hermann Himmelbauer wrote:
  Hi,
  In one of my database tables I have a varchar that is mapped to an object
  with a string attribute. This specific varchar should however be
  represented by a certain Python object, therefore it would be very handy,
  if there would be a way to automatically load/represent this data. Is
  there support from SQLAlchemy?
 
  I thought about writing a property for my attribute but I'm unsure if
  this is compatible with SQLAlchemy, as SA sets up its own properties for
  attributes when the object is mapped, so probably mine would be
  overwritten?

 You could either create a custom type for the column to do translation
 to/from object form or use a property as a proxy to the varchar
 attribute.  They're both pretty easy to implement:

 http://www.sqlalchemy.org/docs/04/types.html#types_custom
 http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_overri
ding

Perfect, thanks!

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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] Feature suggestion: Description attribute in Tables/Columns

[Hermann Himmelbauer]

Hi,
I am creating my database via SQLAlchemy, therefore I use several Column() 
objects in a Table() clause.

For my documentation, it is very handy to gather the table definitions 
directly from my SQLAlchemy-based python code - that's relatively easy, I 
simply import my table-definitions file and look for module attributes that 
end with _table (by convention, I always name my tables like that).

Then I can read the table/columns, format them nicely and put them in my 
documentation.

What's missing, however, are description fields: It would be perfect, if I 
could specify a description attribute for the table and the column directly, 
e.g. like that:

myperson_table = Table('person', metadata, 
  Column('id', Integer, primary_key=True, description = 'Identification 
Number'),
  Column('name', String(20), description = 'Name of a person'),
  description = 'Table for my Persons')

I could, also create my own Table/Column classes that hold this information, 
but maybe this could be implemented in SA as a standard feature?

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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] Profiling support

[Hermann Himmelbauer]

Hi,
I'd like to know if there is some profiling support in SQLAlchemy. It would be 
nice if there would be some data accompanying SQL statements in the logfiles 
that can be used to optimize the RDB-part of the application (e.g. lazy/eager 
loading, specifying queries, creating indexes etc.).

How do you handle this?

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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] Automatically loading data into objects

[Hermann Himmelbauer]

Hi,
In one of my database tables I have a varchar that is mapped to an object with 
a string attribute. This specific varchar should however be represented by a 
certain Python object, therefore it would be very handy, if there would be a 
way to automatically load/represent this data. Is there support from 
SQLAlchemy?

I thought about writing a property for my attribute but I'm unsure if this is 
compatible with SQLAlchemy, as SA sets up its own properties for attributes 
when the object is mapped, so probably mine would be overwritten?

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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 safely increasing a numeric value via SQLAlchemy

[Hermann Himmelbauer]

Am Dienstag, 11. September 2007 10:54 schrieb svilen:
 in 0.4 there is atomic update, e.g. update set a=expression

 syntax is something like
 table.update( values=dict-of-name-expression ).execute(
 **bindings-if-any)
 expressions is whatever sa sql expression

Ah, that's interesting. Is it possible to specify an if construct there, too?

I need to select the value, subtract something from it, check that the 
resulting value is  0 and if yes, write it.

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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 dynamically create where-clauses

[Hermann Himmelbauer]

Hi,
My web-application, that is in front of SQLAlchemy has various input-fields, 
which should act as a where-clause (with like()), a query looks e.g. like 
this:

session.query(MyObj).filter(table1.c.input1.like(inputfield_1+'%'),
   table1.c.input2.like(inputfield_2+'%'),
   ...)

The problem is that if such an input field is empty, it should not be included 
in the query. If I rule out empty fields manually, I have 2^n if-clauses (I 
think), so I need to dynamically create the where-clauses in some way.

What would be the best way to accomplish this?

Best Regards,
Hermann


-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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] Automatic converter from latin1 to Unicode

[Hermann Himmelbauer]

Hi,
I am writing a web application which retrieves data through SQLAlchemy. 
Character-based data in this database is encoded as latin1.

My Web-framework (Zope3) expects strings in the unicode format and throws  
errors like this:

UnicodeDecodeError: 'ascii' codec can't decode byte 0xfc in position 1: 
ordinal not in range(128)

I could of course convert data manually from latin1-unicode, however, this 
will be quite some work, moreover my database will be converted to unicode 
some day.

A nice solution would be if SQLAlchemy could automatically do this conversion 
(in both directions) - this would solve all my problems. Is this possible?

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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] MySQL MaxDB interface for SQLAlchemy

[Hermann Himmelbauer]

Hi,
It seems SQLAlchemy provides no database connector for MySQL's SAPDB/MaxDB 
database and that there is no ongoing work in this direction.

I'm currently working on a project, which relies on MaxDB, therefore I need 
such a connector. As I am not involved in the SQLAlchemy development, writing 
such an interface would be quite some work for me, however, if someone 
already did that for another database, it's probably a 1 or 2-day work.

So, I'm wondering if someone would perhaps write such an interface on a paid 
base? The resulting code would of course be open source and should then be 
part of the SQLAlchemy distribution.

Perhaps someone is interested in doing this?

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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] MaxDB database interface

[Hermann Himmelbauer]

Hi,
Does someone know if there's a database interface for MySQL's MaxDB or if 
someone is developing it? 

If not - is it much effort to develop that?

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

--~--~-~--~~~---~--~~
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: Accessing attributes in many to many relations

[Hermann Himmelbauer]

Am Dienstag, 9. Januar 2007 17:01 schrieb Michael Bayer:
 we do have an extension called associationproxy that is used for
 something similar with association objects...it may give you some
 ideas on how to proxy attributes across.   i dont think you should
 approach the idea of adding individual properties as quite
 complicated...for example, you could write a function that adds all
 the Address properties to the Person class en-masse:

   for key in class_mapper(Address).props:
   setattr(Person, key, property(...some getter function...))

 if you come up with something generally useful in that idiom, you can
 post to the SA wiki or we can even make another extension if its
 really of general use.

At first, thanks for your quick answer.

My basic idea was to maintain the abstraction between the data model and the 
actual object. So the ideal place to specify such things would be in the 
mapper. To my mind, some magic with the properties could do it, e.g. like 
this:

mapper(Person, person_table, properties = dict(
  address=relation(Address, secondary=r_pers_addrid_table,
 proxy_relation_attributes=True))

In case proxy_relation_attributes is set to True, all columns (except the 
foreign keys which are obsolete) are made accessible through the relation - 
very simple syntax.

Correct me if I'm wrong, but many to many relations are normally done this 
way:

table1  ---  association_table  --- table2

table1 has a one to many relation to association_table and table2 has also a 
one to many relation to association_table. From the perpective of table1, it 
should therefore in any case be  possible to locate association/relation  
attributes along with the attributes of table2 (and the same in the other 
direction). The only problem could be name clashes, which could probably be 
handled in some way (e.g. raise an exception or prefix them).

This may also obsolete the associationproxy - but maybe I don't fully 
understand its concepts.

This solution would make it possible to alter the data model, e.g. make a one 
to many relation from a many to many relation, e.g.:

table1 -  table2

without having to touch the object representing the function - the only thing 
I'd have to change is the mapper.

I have no clue how I would implement such a relation property as I'm very new 
to SA, perhaps it's impossible for some reason. However, I think putting 
attributes in association tables is a common pattern in data models, so this 
could be of use for many SA users.

What do you think of this idea?

Best Regards,
Hermann

-- 
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7

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