[sqlalchemy] Re: multiple statements in a ddl construct

[az]

On Monday 08 September 2008 18:45:17 jason kirtland wrote:
 alex bodnaru wrote:
  hello friends,
 
  i wanted to do a few sql commands in a ddl construct, but i
  failed with:
 
  pysqlite2:dbapi2 warning: you can execute one statement at a time
 
  i'm not very familiar with python db layer, but i know sqlite may
  be invoked specifically to execute one, or many statements
  divided by ';', so i suspect it's specifically invoked for one
  statement.
 
  while this is a good security measure for sql from untrusted
  sources, like user input, it's quite annoying for a situation
  where free sql should be specifically
  added.
 
  as for my case, i had a batch of inserts based on an external
  file, and i couldn't
  invoke ddl.execute_at in a loop, so i had to switch to inserting
  a batch of unioned
  selects in one insert, which was nice to learn :).

 The use case behind the DDL() construct is a single statement.  You
 can fire multiple statements by using multiple DDL()s.  But for
 inserts, I've found it more useful to write a 'after-create' event
 listener from scratch.  Here's one that I use in pretty much every
 project, in some form or another:

def fixture(table, column_names, *rows):
Insert data into table after creation.
def onload(event, schema_item, connection):
insert = table.insert()
connection.execute(
insert,
[dict(zip(column_names, column_values))
 for column_values in rows])
table.append_ddl_listener('after-create', onload)

 Looks like this in use:

fixture(some_table,
('x', 'y'),
(1, 2),
(3, 4),
(5, 6))

hmm.. interesting. how would u do an initial insert of batch of 
objects (orm-mapped to whatever entangled bunch of tables)? any 
possible optimization? 
for obj in objfactory(somedicts): sess.save(obj); sess.flush() 
isn't very fast thing...
any needed gymnastics with the objects is possible (grouping by type 
or whatever)

--~--~-~--~~~---~--~~
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] Why can't I add several equal items to list?

[mraer]

class List:
pass

class Item:
pass

list = List()
item1 = Item()
item2 = Item()

list.items.append(item1)
list.items.append(item2)
list.items.append(item1)

session.add(list)
session.commit()

after it i have only one item1 in list.

--~--~-~--~~~---~--~~
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 statements in a ddl construct

[alex bodnaru]

hi jason,

On Mon, Sep 8, 2008 at 6:45 PM, jason kirtland [EMAIL PROTECTED] wrote:

 alex bodnaru wrote:
 hello friends,

 i wanted to do a few sql commands in a ddl construct, but i failed with:

 pysqlite2:dbapi2 warning: you can execute one statement at a time

 i'm not very familiar with python db layer, but i know sqlite may be invoked
 specifically to execute one, or many statements divided by ';', so i suspect
 it's specifically invoked for one statement.

 while this is a good security measure for sql from untrusted sources, like 
 user
 input, it's quite annoying for a situation where free sql should be
 specifically
 added.

 as for my case, i had a batch of inserts based on an external file,
 and i couldn't
 invoke ddl.execute_at in a loop, so i had to switch to inserting a
 batch of unioned
 selects in one insert, which was nice to learn :).

 The use case behind the DDL() construct is a single statement.  You can
 fire multiple statements by using multiple DDL()s.  But for inserts,
 I've found it more useful to write a 'after-create' event listener from
 scratch.  Here's one that I use in pretty much every project, in some
 form or another:

   def fixture(table, column_names, *rows):
   Insert data into table after creation.
   def onload(event, schema_item, connection):
   insert = table.insert()
   connection.execute(
   insert,
   [dict(zip(column_names, column_values))
for column_values in rows])
   table.append_ddl_listener('after-create', onload)

 Looks like this in use:

   fixture(some_table,
   ('x', 'y'),
   (1, 2),
   (3, 4),
   (5, 6))


thanks for your idea. it looks cool. i understand this will be
triggered after all DDL end.

but i wanted to do arbitrary sql as DDL provides, and the insert was
just an example for a
series of statements.

just wondered why would this be the place to limit to one statement:
it isn't parsed by SA
in any way.

btw, my case is a generic ddl extension to elixir, made to be run
once. but i made it receive
a list of commands, and register each command in turn, and did the job.

best regards,
alex

 


--~--~-~--~~~---~--~~
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 statements in a ddl construct

[alex bodnaru]

hi az,

thanks for commenting.

loading the whole (elixir) model to the (sqlite) db took about 3 seconds,
and it being done once anyway.
so jetflight performance was not a constraint ;) .

best regards,

alex

On Tue, Sep 9, 2008 at 9:53 AM,  [EMAIL PROTECTED] wrote:

 On Monday 08 September 2008 18:45:17 jason kirtland wrote:
 alex bodnaru wrote:
  hello friends,
 
  i wanted to do a few sql commands in a ddl construct, but i
  failed with:
 
  pysqlite2:dbapi2 warning: you can execute one statement at a time
 
  i'm not very familiar with python db layer, but i know sqlite may
  be invoked specifically to execute one, or many statements
  divided by ';', so i suspect it's specifically invoked for one
  statement.
 
  while this is a good security measure for sql from untrusted
  sources, like user input, it's quite annoying for a situation
  where free sql should be specifically
  added.
 
  as for my case, i had a batch of inserts based on an external
  file, and i couldn't
  invoke ddl.execute_at in a loop, so i had to switch to inserting
  a batch of unioned
  selects in one insert, which was nice to learn :).

 The use case behind the DDL() construct is a single statement.  You
 can fire multiple statements by using multiple DDL()s.  But for
 inserts, I've found it more useful to write a 'after-create' event
 listener from scratch.  Here's one that I use in pretty much every
 project, in some form or another:

def fixture(table, column_names, *rows):
Insert data into table after creation.
def onload(event, schema_item, connection):
insert = table.insert()
connection.execute(
insert,
[dict(zip(column_names, column_values))
 for column_values in rows])
table.append_ddl_listener('after-create', onload)

 Looks like this in use:

fixture(some_table,
('x', 'y'),
(1, 2),
(3, 4),
(5, 6))

 hmm.. interesting. how would u do an initial insert of batch of
 objects (orm-mapped to whatever entangled bunch of tables)? any
 possible optimization?
 for obj in objfactory(somedicts): sess.save(obj); sess.flush()
 isn't very fast thing...
 any needed gymnastics with the objects is possible (grouping by type
 or whatever)

 


--~--~-~--~~~---~--~~
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] Quick way to deep copy an object?

[picoplex]

Is there a quick way to copy an object (including all its
relationships as well as its attributes)?

Regards,

Tim

--~--~-~--~~~---~--~~
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 statements in a ddl construct

[az]

On Tuesday 09 September 2008 18:19:59 alex bodnaru wrote:
 hi az,

 thanks for commenting.

 loading the whole (elixir) model to the (sqlite) db took about 3
 seconds, and it being done once anyway.
 so jetflight performance was not a constraint ;) .
...

  hmm.. interesting. how would u do an initial insert of batch of
  objects (orm-mapped to whatever entangled bunch of tables)? any
  possible optimization?
  for obj in objfactory(somedicts): sess.save(obj); sess.flush()
  isn't very fast thing...
  any needed gymnastics with the objects is possible (grouping by
  type or whatever)

ah, no worries, that was a question to jason.
in my case it is about inserting several hundreds/thousands objects. 

--~--~-~--~~~---~--~~
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: Quick way to deep copy an object?

[az]

u can try:
from copy import copy
dest = copy(src)
it has pickle semantics, YMMV - i have not tried on SA objects.
i use my own, see my post in this thread with some (complex) code:
http://groups.google.com/group/sqlalchemy/tree/browse_frm/thread/2369b78e0f8e21cb/4dd4979ca8501fba

On Tuesday 09 September 2008 18:59:09 picoplex wrote:
 Is there a quick way to copy an object (including all its
 relationships as well as its attributes)?

 Regards,

 Tim


--~--~-~--~~~---~--~~
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 statements in a ddl construct

[jason kirtland]

[EMAIL PROTECTED] wrote:
 On Monday 08 September 2008 18:45:17 jason kirtland wrote:
 alex bodnaru wrote:
 hello friends,

 i wanted to do a few sql commands in a ddl construct, but i
 failed with:

 pysqlite2:dbapi2 warning: you can execute one statement at a time

 i'm not very familiar with python db layer, but i know sqlite may
 be invoked specifically to execute one, or many statements
 divided by ';', so i suspect it's specifically invoked for one
 statement.

 while this is a good security measure for sql from untrusted
 sources, like user input, it's quite annoying for a situation
 where free sql should be specifically
 added.

 as for my case, i had a batch of inserts based on an external
 file, and i couldn't
 invoke ddl.execute_at in a loop, so i had to switch to inserting
 a batch of unioned
 selects in one insert, which was nice to learn :).
 The use case behind the DDL() construct is a single statement.  You
 can fire multiple statements by using multiple DDL()s.  But for
 inserts, I've found it more useful to write a 'after-create' event
 listener from scratch.  Here's one that I use in pretty much every
 project, in some form or another:

def fixture(table, column_names, *rows):
Insert data into table after creation.
def onload(event, schema_item, connection):
insert = table.insert()
connection.execute(
insert,
[dict(zip(column_names, column_values))
 for column_values in rows])
table.append_ddl_listener('after-create', onload)

 Looks like this in use:

fixture(some_table,
('x', 'y'),
(1, 2),
(3, 4),
(5, 6))
 
 hmm.. interesting. how would u do an initial insert of batch of 
 objects (orm-mapped to whatever entangled bunch of tables)? any 
 possible optimization? 
 for obj in objfactory(somedicts): sess.save(obj); sess.flush() 
 isn't very fast thing...
 any needed gymnastics with the objects is possible (grouping by type 
 or whatever)

I don't see any optimizations offhand for ORM-driven loads.  The general 
case is going to have dependencies on the niceties that the ORM 
provides, like assoc proxies, mapper extension actions on insert, etc.


--~--~-~--~~~---~--~~
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 statements in a ddl construct

[jason kirtland]

alex bodnaru wrote:
 hi jason,
 
 On Mon, Sep 8, 2008 at 6:45 PM, jason kirtland [EMAIL PROTECTED] wrote:
 alex bodnaru wrote:
 hello friends,

 i wanted to do a few sql commands in a ddl construct, but i failed with:

 pysqlite2:dbapi2 warning: you can execute one statement at a time

 i'm not very familiar with python db layer, but i know sqlite may be invoked
 specifically to execute one, or many statements divided by ';', so i suspect
 it's specifically invoked for one statement.

 while this is a good security measure for sql from untrusted sources, like 
 user
 input, it's quite annoying for a situation where free sql should be
 specifically
 added.

 as for my case, i had a batch of inserts based on an external file,
 and i couldn't
 invoke ddl.execute_at in a loop, so i had to switch to inserting a
 batch of unioned
 selects in one insert, which was nice to learn :).
 The use case behind the DDL() construct is a single statement.  You can
 fire multiple statements by using multiple DDL()s.  But for inserts,
 I've found it more useful to write a 'after-create' event listener from
 scratch.  Here's one that I use in pretty much every project, in some
 form or another:

   def fixture(table, column_names, *rows):
   Insert data into table after creation.
   def onload(event, schema_item, connection):
   insert = table.insert()
   connection.execute(
   insert,
   [dict(zip(column_names, column_values))
for column_values in rows])
   table.append_ddl_listener('after-create', onload)

 Looks like this in use:

   fixture(some_table,
   ('x', 'y'),
   (1, 2),
   (3, 4),
   (5, 6))

 
 thanks for your idea. it looks cool. i understand this will be
 triggered after all DDL end.
 
 but i wanted to do arbitrary sql as DDL provides, and the insert was
 just an example for a
 series of statements.

The DDL() function is just some sugar for the DDL event shown interface 
above.  If you want to perform more than one statement, the API is in 
place for any customization you'd like.

 just wondered why would this be the place to limit to one statement:
 it isn't parsed by SA
 in any way.

SA doesn't put any limits on what SQL gets pushed through.  It's passed 
through directly to the DB-API execute() method.  I'd guess that most 
DB-API implementations will probably reject multiple statements in a 
single execution.

--~--~-~--~~~---~--~~
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] Adjacency List + Alternate Join Conditions == ???

[GustaV]

Hello,

I'm looking into something a bit tricky : having a relation between a
table and itself (an adjacency list then) with a non-trivial join
condition.
Let's say for example, in the case of a Checkers game board for
example, to query the neighboring tiles of a given tile. To do the
query itself is easy, but then it can't be eagerloaded which is a
shame...

I tried something like (with a stupid condition) :
table_alias = mytable.alias()
mapper(Tile, mytable, properties={
 'neighbors' = relation(Tile, primaryjoin=mytable.c.x 
table_alias.c.x,
   foreign_keys=[mytable.c.x],
   viewonly=True)
})

but that does not do what I could expect : it queries both tables
(normal and aliased) but returns result from the non-alias table
(without eagerloading) or fails (with eagerload) because it uses
incoherent aliased tables.

The problem comes from we have no access to the table being
automatically aliased when doing an adjacency relation here ; and
building a new aliased table is a non-sense.

In one word : HELP !

Gus
--~--~-~--~~~---~--~~
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: Adjacency List + Alternate Join Conditions == ???

[Michael Bayer]

you can manage this by making SQLAlchemy fully aware of table_alias  
by creating a separate mapper.  Since you are viewonly=True you have  
more flexiblity in this regard:

from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
Session = scoped_session(sessionmaker(bind=engine))

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Tile(Base):
 __tablename__ = tiles
 id = Column(Integer, primary_key=True)

Base.metadata.create_all(engine)

tile_table = Tile.__table__
tile_alias = tile_table.alias()
related_tiles = mapper(Tile, tile_alias, non_primary=True)

Tile.neighbors = relation(related_tiles, primaryjoin=tile_table.c.id   
tile_alias.c.id, foreign_keys=[tile_alias.c.id], viewonly=True)

session = sessionmaker(bind=engine)()

t1 = Tile(id=1)
t2 = Tile(id=2)
t3 = Tile(id=3)
t4 = Tile(id=4)

session.add_all([t1, t2, t3, t4])
session.commit()

assert t2.neighbors == [t1]
assert t4.neighbors == [t1, t2, t3]
assert t1.neighbors == []



On Sep 9, 2008, at 4:50 PM, GustaV wrote:


 Hello,

 I'm looking into something a bit tricky : having a relation between a
 table and itself (an adjacency list then) with a non-trivial join
 condition.
 Let's say for example, in the case of a Checkers game board for
 example, to query the neighboring tiles of a given tile. To do the
 query itself is easy, but then it can't be eagerloaded which is a
 shame...

 I tried something like (with a stupid condition) :
 table_alias = mytable.alias()
 mapper(Tile, mytable, properties={
 'neighbors' = relation(Tile, primaryjoin=mytable.c.x 
 table_alias.c.x,
   foreign_keys=[mytable.c.x],
   viewonly=True)
 })

 but that does not do what I could expect : it queries both tables
 (normal and aliased) but returns result from the non-alias table
 (without eagerloading) or fails (with eagerload) because it uses
 incoherent aliased tables.

 The problem comes from we have no access to the table being
 automatically aliased when doing an adjacency relation here ; and
 building a new aliased table is a non-sense.

 In one word : HELP !

 Gus
 


--~--~-~--~~~---~--~~
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: Quick way to deep copy an object?

[Michael Bayer]

copy.copy() is going to have issues since you'd be copying the  
_sa_state of the instance as well.   copying an object that is SQLA  
mapped implies a particular use case in mind, depending on this use  
case is what would determine how you get your copy.

Would you like to get a copy that is simply the same object with the  
same identity, just as present in another session (in which case use  
merge()) ?

Or would you like to get a copy that is a transient (no database  
identity) copy of that object, with all attributes except primary key  
attributes copied over ?  ( in which case you'd use a copy constructor  
which possibly uses mapping metadata to traverse ) ?


On Sep 9, 2008, at 2:16 PM, [EMAIL PROTECTED] wrote:


 u can try:
 from copy import copy
 dest = copy(src)
 it has pickle semantics, YMMV - i have not tried on SA objects.
 i use my own, see my post in this thread with some (complex) code:
 http://groups.google.com/group/sqlalchemy/tree/browse_frm/thread/2369b78e0f8e21cb/4dd4979ca8501fba

 On Tuesday 09 September 2008 18:59:09 picoplex wrote:
 Is there a quick way to copy an object (including all its
 relationships as well as its attributes)?

 Regards,

 Tim


 


--~--~-~--~~~---~--~~
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: Calling stored procedures through Pylons

[Michael Bayer]

On Sep 8, 2008, at 11:11 AM, John Lavoie wrote:


 Hi all,

 I'm using SQLAlchemy in Pylons.  I'm also using Elixir, which was set
 up based on Jonathan's post here: http://cleverdevil.org/computing/68/

 I need to call a stored procedure.  I don't care about return values
 yet, I just want to run something in the database.

 So far I've just been struggling to execute any raw SQL at all, let
 alone my PL/SQL procedure.

 model.Session.execute(select * from dual)

the Session must be bound to an engine first, using Session.bind =  
someengine, in order for execute() to work with a plain textual  
string.   Otherwise you can execute from the engine directly using  
engine.execute(somestring), but then you wouldnt be in the  
transactional context of the 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: multiple statements in a ddl construct

[alex bodnaru]

hi jason,

On Tue, Sep 9, 2008 at 9:59 PM, jason kirtland [EMAIL PROTECTED] wrote:

 alex bodnaru wrote:
 hi jason,

 On Mon, Sep 8, 2008 at 6:45 PM, jason kirtland [EMAIL PROTECTED] wrote:
 alex bodnaru wrote:
 hello friends,

 i wanted to do a few sql commands in a ddl construct, but i failed with:

 pysqlite2:dbapi2 warning: you can execute one statement at a time

 i'm not very familiar with python db layer, but i know sqlite may be 
 invoked
 specifically to execute one, or many statements divided by ';', so i 
 suspect
 it's specifically invoked for one statement.

 while this is a good security measure for sql from untrusted sources, like 
 user
 input, it's quite annoying for a situation where free sql should be
 specifically
 added.

 as for my case, i had a batch of inserts based on an external file,
 and i couldn't
 invoke ddl.execute_at in a loop, so i had to switch to inserting a
 batch of unioned
 selects in one insert, which was nice to learn :).
 The use case behind the DDL() construct is a single statement.  You can
 fire multiple statements by using multiple DDL()s.  But for inserts,
 I've found it more useful to write a 'after-create' event listener from
 scratch.  Here's one that I use in pretty much every project, in some
 form or another:

   def fixture(table, column_names, *rows):
   Insert data into table after creation.
   def onload(event, schema_item, connection):
   insert = table.insert()
   connection.execute(
   insert,
   [dict(zip(column_names, column_values))
for column_values in rows])
   table.append_ddl_listener('after-create', onload)

 Looks like this in use:

   fixture(some_table,
   ('x', 'y'),
   (1, 2),
   (3, 4),
   (5, 6))


 thanks for your idea. it looks cool. i understand this will be
 triggered after all DDL end.

 but i wanted to do arbitrary sql as DDL provides, and the insert was
 just an example for a
 series of statements.

 The DDL() function is just some sugar for the DDL event shown interface
 above.  If you want to perform more than one statement, the API is in
 place for any customization you'd like.

 just wondered why would this be the place to limit to one statement:
 it isn't parsed by SA
 in any way.

 SA doesn't put any limits on what SQL gets pushed through.  It's passed
 through directly to the DB-API execute() method.  I'd guess that most
 DB-API implementations will probably reject multiple statements in a
 single execution.

i really have no experience with pydb. but i know for sure that sqlite can
either work with one statement or with multiple ones, depending on the caller's
option.

alex
 


--~--~-~--~~~---~--~~
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: Adjacency List + Alternate Join Conditions == ???

[GustaV]

That works great!

It revealed a bug though :
When there are columns prefixed by a '_' in the mapper but not in the
DB (quite usual), they are not properly loaded using the 'neighbors'
property.

In your example :

from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
Session = scoped_session(sessionmaker(bind=engine))

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Tile(Base):
 __tablename__ = tiles
 id = Column(Integer, primary_key=True)
 _data = Column('data', Integer)

Base.metadata.create_all(engine)

tile_table = Tile.__table__
tile_alias = tile_table.alias()
related_tiles = mapper(Tile, tile_alias, non_primary=True)

Tile.neighbors = relation(related_tiles, primaryjoin=tile_table.c.id

tile_alias.c.id, foreign_keys=[tile_alias.c.id], viewonly=True)

session = sessionmaker(bind=engine)()

t1 = Tile(id=1, _data=1)
t2 = Tile(id=2, _data=2)
t3 = Tile(id=3, _data=3)
t4 = Tile(id=4, _data=3)

session.add_all([t1, t2, t3, t4])
session.commit()

session.expunge(t1)
session.expunge(t2)
session.expunge(t3)
session.expunge(t4)

t4 = session.query(Tile).get(4)
print t4.neighbors[0]._data


will print None



On Sep 9, 11:12 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 you can manage this by making SQLAlchemy fully aware of table_alias
 by creating a separate mapper.  Since you are viewonly=True you have
 more flexiblity in this regard:

 from sqlalchemy import *
 from sqlalchemy.orm import *
 engine = create_engine('sqlite://', echo=True)
 Session = scoped_session(sessionmaker(bind=engine))

 from sqlalchemy.ext.declarative import declarative_base
 Base = declarative_base()

 class Tile(Base):
  __tablename__ = tiles
  id = Column(Integer, primary_key=True)

 Base.metadata.create_all(engine)

 tile_table = Tile.__table__
 tile_alias = tile_table.alias()
 related_tiles = mapper(Tile, tile_alias, non_primary=True)

 Tile.neighbors = relation(related_tiles, primaryjoin=tile_table.c.id 
 tile_alias.c.id, foreign_keys=[tile_alias.c.id], viewonly=True)

 session = sessionmaker(bind=engine)()

 t1 = Tile(id=1)
 t2 = Tile(id=2)
 t3 = Tile(id=3)
 t4 = Tile(id=4)

 session.add_all([t1, t2, t3, t4])
 session.commit()

 assert t2.neighbors == [t1]
 assert t4.neighbors == [t1, t2, t3]
 assert t1.neighbors == []

 On Sep 9, 2008, at 4:50 PM, GustaV wrote:



  Hello,

  I'm looking into something a bit tricky : having a relation between a
  table and itself (an adjacency list then) with a non-trivial join
  condition.
  Let's say for example, in the case of a Checkers game board for
  example, to query the neighboring tiles of a given tile. To do the
  query itself is easy, but then it can't be eagerloaded which is a
  shame...

  I tried something like (with a stupid condition) :
  table_alias = mytable.alias()
  mapper(Tile, mytable, properties={
  'neighbors' = relation(Tile, primaryjoin=mytable.c.x 
  table_alias.c.x,
foreign_keys=[mytable.c.x],
viewonly=True)
  })

  but that does not do what I could expect : it queries both tables
  (normal and aliased) but returns result from the non-alias table
  (without eagerloading) or fails (with eagerload) because it uses
  incoherent aliased tables.

  The problem comes from we have no access to the table being
  automatically aliased when doing an adjacency relation here ; and
  building a new aliased table is a non-sense.

  In one word : HELP !

  Gus
--~--~-~--~~~---~--~~
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: Adjacency List + Alternate Join Conditions == ???

[Michael Bayer]

On Sep 9, 2008, at 7:27 PM, GustaV wrote:


 t4 = session.query(Tile).get(4)
 print t4.neighbors[0]._data


 will print None

you have to adjust the other mapping appropriately:

related_tiles = mapper(Tile, tile_alias, non_primary=True, properties={
 '_data':tile_alias.c.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
-~--~~~~--~~--~--~---