[sqlalchemy] Re: dictionary mapping with foreignkey relations/association objects
Hello Matt, this should help:
you are using many-to-many relationship, (book_author_table - is your
association table)
please read this first:
http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relation_patterns_manytomany
After what you can turn to
http://www.sqlalchemy.org/docs/05/plugins.html#plugins_associationproxy_simplifying
to make the setup even easier.
It should answer your question,
Regards,
Alex
On Sep 21, 10:53 pm, mattmiller [EMAIL PROTECTED] wrote:
greetings,
i don't seem to be able to figure out how to map an input dictionary
to
my objects. specifically, i am trying to map a dictionary with inut
values to my session objects. somewhat stylized, using a non-
declarative approach:
book_table = Table('test_book', meta,
Column(u'id', Integer, primary_key=True),
Column(u'title', Unicode(255)),
mysql_engine='InnoDB')
author_table = Table('test_author', meta,
Column(u'id', Integer, primary_key=True),
Column(u'first_name', Unicode(255)),
Column(u'last_name', Unicode(255)),
# Column(u'book_id', Integer, ForeignKey('test_book.id')),
mysql_engine='InnoDB')
book_author_table = Table('test_book_author', meta,
Column(u'user_id', None, ForeignKey('test_book.id'),
primary_key=True),
Column(u'author_id', None, ForeignKey('test_author.id'),
primary_key=True))
meta.create_all()
class Author(object):pass
class Book(object): pass
mapper(Author, author_table)
mapper(Book, book_table)
in the simple case, i got an input dict with only one author
(although there might be more than one):
in_dict = in2 = {'title':some title 2, 'first_name':'joe',
'last_name':'somebody'}
my first flup is right here as i can't append the author to the author
list in the book oject, since it doesn't exist, duh:
book = Book()
author=Author()
book.authors.append(author) #does not work
how do i kneed to specify my metadata and mappers to get things set up
my way? is this even possible in a non-declarative manner?
although i haven't gotten there, yet, i'm pretty sure the _this_ won't
work even if i had my author obj list available:
for k,v in in_dict.items():
book.k = v
fundamentally, i'm trying to get a web-based input form with lots of
attributes equivalent to the book-author example above into my db as
painless as possible. any suggestions are greatly appreciated, thx
matt
--~--~-~--~~~---~--~~
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: Querying in the many side of a one-to-many relationship
On Sunday 21 September 2008 23:23:00 Gabe wrote:
Hi,
Thanks for the pointer. I must have read over that 100 times and
missed the contains part. I've managed to put this together:
games =
(session.query(models.Game).options(
sqlalchemy.orm.eagerload('scores')).filter(
models.Game.scores.any(models.Score.player ==
player)).all())
Unfortunately, with ~6000 games, where the specified user has
played ~4000 of them, it takes roughly 45 seconds to issue that
query: time curl http://localhost:8080/chart/win_average?
width=400height=400username=solongordonplayername=Solonversion=
2 Solon played 4195 games.
real 0m44.656s
user 0m0.008s
sys 0m0.008s
As you can see I've attempted to eagerload the scores but that
doesn't seem to help much. I tried to eagerload the players as
well but I can't get that syntax to work. I tried specifying lazy
= False in my Columns in the models, but I don't know if that takes
effect after the database has already been created.
Does anyone have advice for speeding this query up?
- try another db, e.g. postgres. sqlite is simple thing, do not
expect miracles.
- maybe, try to express your thing as a join (and not subselect) (i'm
not sql specialist so might be wrong here)
- sometimes the amount of data to be transferred via network becomes
the bottleneck. e.g. in one case of mine eagerloading 5 relations was
2x slower than otherwise.
hth
svilen
On Sep 21, 4:38 pm, [EMAIL PROTECTED] wrote:
see
thesehttp://www.sqlalchemy.org/docs/05/ormtutorial.html#datamappi
ng_joins_...
On Sunday 21 September 2008 20:04:02 Gabe wrote:
Hi Everyone,
I'm somewhat new to database design, so if I've set things up
in an incompatible or silly manner please let me know. I have
the following tables:
class Player(Base):
__tablename__ = players
# Stuff about a player.
class
Game(Base):
A class that holds information about a single
game.
__tablename__ =
games
#
Columns
id = Column(Integer, primary_key =
True)
board =
Column(Binary)
match_length =
Column(Float)
date_added = Column(DateTime, default =
datetime.datetime.now)
class
Score(Base):
A class that holds a player's score for a
game.
__tablename__ =
scores
#
Columns:
id = Column(Integer, primary_key =
True)
game_id = Column(Integer,
ForeignKey(games.id))
player_id = Column(Integer,
ForeignKey(players.id))
score =
Column(Integer)
#
Relations:
game = relation(Game, backref =
backref(scores))
player =
relation(Player)
# Back
References:
# game which points to the game for this object.
I'd like to figure out how to count the wins and losses for a
player. I was thinking something along these lines:
player = session.query(Player).filter(Player.name ==
playername).one() games_containing_player =
session.query(Game).filter(player in Game.scores.player).all()
But that pretty obviously doesn't work. Should I have designed
this set up differently? Right now the only thing I've come up
with, (which seems to think the player is in every game as well
which is wrong) and is really really slow, is:
Get the player.
player =
session.query(models.Player).filter(sqlalchemy.and_(
models.Player.owner == user,
models.Player.name == playername,
models.Player.version == version)).one()
if not player:
return Not a valid player.
# Compute the player's win/loss list.
win_loss = []
games = session.query(models.Game).all()
def had_player(game):
for score in game.scores:
if score.player == player:
return True
return False
filter(had_player, games)
return %s played %d games. % (player.name, len(games))
Thanks for the advice,
Gabe
--~--~-~--~~~---~--~~
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] SA4: Why the automatic rollback on exception during SQL-Expression execution?
Hi! Let's say I have a simple table and want to create a function to ensure that a given row exists in that table. That is, the desired semantics is that after I call this function, the given row will exist: if it doesn't exist before the call, it will be created. I'm using SA 0.4.7 and the SQL Expression Language. My initial attempt follows: def make_sure_row_exists(row_attr_dict): s = MyTable.insert(row_attr_dict) try: s.execute() except IntegrityError: pass # row already exists, so we need do nothing more In sum: try to create the row and if it's already there, great. The trouble is, if the row already exists, SA will issue a ROLLBACK on the connection, wiping out any as-of-yet-uncommitted work that may have built up previously: sqlalchemy.engine.base.Engine.0x..d0 INSERT INTO mytable (x, y) VALUES (?, ?) sqlalchemy.engine.base.Engine.0x..d0 ['1', '1'] # already exists sqlalchemy.engine.base.Engine.0x..d0 ROLLBACK This ROLLBACK makes it hard to use this function as a building block in larger transactions. (Unless I somehow isolate this function in its own transaction, e.g., by using nested transactions.) What that background, I have some questions: 1. Is there a better way to use SA's SQL Expression Language to implement a function with the desired semantics? 2. How can I tell SA that it's fine if the row already exists and not to issue the ROLLBACK? 3. Is there any documentation on the interaction of transactions between ORM sessions and SQL Expression Language connections? (I ask this because I first noticed the ROLLBACK-on-exception issue when work on an ORM session failed to be committed as usual. Since the ORM work was done via a scoped session object and the SQL-EL work was done as shown in the code snippet above on an engine that does not use threadlocal execution strategy, I would have expected the SQL-EL-issued ROLLBACK not to affect the ORM work, they presumably being on different connections, albeit to the same database.) If anybody can shed some light, I would be grateful. Thanks for your help! Cheers, Tom --~--~-~--~~~---~--~~ 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] Fwd: Support for ordered lists of child items
forwarded from pvt email orderinglist ? Begin forwarded message: From: Emmett [EMAIL PROTECTED] Date: September 22, 2008 9:51:31 AM EDT To: Michael Bayer [EMAIL PROTECTED] Subject: Re: Support for ordered lists of child items Hello Michael, 18 months later, would your answer to Aaron still be the same? I have a problem fitting what Aaron described. ie. save+restore of child order after re-ordering in the Python side. Re-ordering child list elements would obviously be ideal, but I could cope with updating an extra integer node attribute instead. I'm completely new to SA and at this stage skimming documentation and looking at the tree examples. Found this thread, so wondering if some newer SA magic can solve this, or if a custom collection class or something else is the best solution. I also looked at the ElementTree examples, but they don't appear to guarantee child order either - correct? Thanks in advance. I looked at your activity in this group. Amazing! On Apr 10 2007, 4:02 am, Michael Bayer [EMAIL PROTECTED] wrote: we dont have the capability to automatically update ordering columns when the elements of a list are moved around. if you move the elements around, you need to execute some step that will update the index columns (or create a custom collection class that does this for you). On Apr 9, 2007, at 12:42 PM, Aaron Digulla wrote: Hello, I'm looking for a feature but couldn't find it in the docs. I have atreelike structure where the user can specify theorderof thechildrenof a node. In DB lingo, I have a parentId and an index column. When I loadchildren, they should be ordered by the index. This seems to be supported. Can SA also update the index column when I movechildrenin the list around? Like: # ... parent has threechildrenA, B C item = parent.children[0] del parent.children[0] parent.children.insert (1, item) # now, parent has threechildrenB, A, C 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: Fwd: Support for ordered lists of child items
Yep, orderinglist handles that case. Michael Bayer wrote: forwarded from pvt email orderinglist ? Begin forwarded message: *From: *Emmett [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *Date: *September 22, 2008 9:51:31 AM EDT *To: *Michael Bayer [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *Subject: **Re: Support for ordered lists of child items* Hello Michael, 18 months later, would your answer to Aaron still be the same? I have a problem fitting what Aaron described. ie. save+restore of child order after re-ordering in the Python side. Re-ordering child list elements would obviously be ideal, but I could cope with updating an extra integer node attribute instead. I'm completely new to SA and at this stage skimming documentation and looking at the tree examples. Found this thread, so wondering if some newer SA magic can solve this, or if a custom collection class or something else is the best solution. I also looked at the ElementTree examples, but they don't appear to guarantee child order either - correct? Thanks in advance. I looked at your activity in this group. Amazing! On Apr 10 2007, 4:02 am, Michael Bayer [EMAIL PROTECTED] wrote: we dont have the capability to automatically update ordering columns when the elements of a list are moved around. if you move the elements around, you need to execute some step that will update the index columns (or create a custom collection class that does this for you). On Apr 9, 2007, at 12:42 PM, Aaron Digulla wrote: Hello, I'm looking for a feature but couldn't find it in the docs. I have atreelike structure where the user can specify theorderof thechildrenof a node. In DB lingo, I have a parentId and an index column. When I loadchildren, they should be ordered by the index. This seems to be supported. Can SA also update the index column when I movechildrenin the list around? Like: # ... parent has threechildrenA, B C item = parent.children[0] del parent.children[0] parent.children.insert (1, item) # now, parent has threechildrenB, A, C 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: SA4: Why the automatic rollback on exception during SQL-Expression execution?
On Sep 22, 2008, at 11:49 AM, Tom Moertel wrote: def make_sure_row_exists(row_attr_dict): s = MyTable.insert(row_attr_dict) try: s.execute() except IntegrityError: pass # row already exists, so we need do nothing more In sum: try to create the row and if it's already there, great. The trouble is, if the row already exists, SA will issue a ROLLBACK on the connection, wiping out any as-of-yet-uncommitted work that may have built up previously: sqlalchemy.engine.base.Engine.0x..d0 INSERT INTO mytable (x, y) VALUES (?, ?) sqlalchemy.engine.base.Engine.0x..d0 ['1', '1'] # already exists sqlalchemy.engine.base.Engine.0x..d0 ROLLBACK the Connection/Engine work by default in an autocommit fashion. To control transaction boundaries: http://www.sqlalchemy.org/docs/04/dbengine.html#dbengine_transactions 3. Is there any documentation on the interaction of transactions between ORM sessions and SQL Expression Language connections? yes: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_sql These patterns have been improved in the 0.5 series as well. (I ask this because I first noticed the ROLLBACK-on-exception issue when work on an ORM session failed to be committed as usual. the ORM Session will always issue a ROLLBACK when a flush() fails. This is due to two things - the database often requires a ROLLBACK in any case (i.e. a postgres transaction typically can't continue after an exception such as the above), and also that flush()'s bookkeeping only executes after the full set of SQL is issued, so therefore does not at the moment support partial progress of its SQL to be issued. In 0.5 this can be worked around using SAVEPOINT via session.begin_nested() (0.4 has more limited support for this usage). Since the ORM work was done via a scoped session object and the SQL-EL work was done as shown in the code snippet above on an engine that does not use threadlocal execution strategy, I would have expected the SQL-EL-issued ROLLBACK not to affect the ORM work, they presumably being on different connections, albeit to the same database.) 0.4 has a behavioral feature which has been fixed in 0.5, which is that the connection pool is set to still use threadlocal execution. Fix this by issuing create_engine(url, pool_threadlocal=False). For any new project, I would strongly urge the usage of 0.5 which is in final release candidate status. Particularly with regards to transactional control, 0.5 is vastly superior to 0.4. Docs are much better too particularly regarding this topic. --~--~-~--~~~---~--~~ 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: ordering_list performance
I'm sure there is potential for improvement on the current orderinglist code- please feel free to send a patch with optimizations you've found to the SA trac. The orderinglist hasn't changed much since 0.3, but with 0.5 there may be entirely new implementations possible. For example, I could imagine one that defers calculation and manipulation of the positioning information until a before_flush hook. That may be perform better, with the trade-off that the position attribute can't be trusted to be in sync with the list order. jean-philippe dutreve wrote: Below is the profiling of code that added 1200 items into an ordering_list relation. I had to bypass the ordering_list stuff for bulk additions in order to have better performance (down to 2 seconds). Hope this post helps to improve this part (using 0.5.0rc1, python 2.5, linux i686, 1.5Go RAM) SA is rocking! jean-philippe Time elapsed: 48.4475638866 s 8875046 function calls (8869157 primitive calls) in 48.443 CPU seconds Ordered by: internal time, call count List reduced from 390 to 10 due to restriction 10 ncalls tottime percall cumtime percall filename:lineno(function) 1292937/12922507.8790.000 12.1340.000 attributes.py: 132(__get__) 12410137.6620.000 39.8360.000 orderinglist.py: 221(_order_entity) 12410135.8700.000 16.9160.000 orderinglist.py: 202(_get_order_value) 4408094.5220.0009.5270.000 attributes.py:394(set) 12364.1980.003 44.0250.036 orderinglist.py: 208(reorder) 1299736/12990483.7520.0004.3730.000 attributes.py: 310(get) 4482253.3370.0005.1570.000 identity.py: 208(modified_event) 4370612.7040.000 14.3310.000 orderinglist.py: 205(_set_order_value) 4408092.2250.000 11.7520.000 attributes.py: 126(__set__) 4482251.7750.0001.8120.000 attributes.py: 958(modified_event) Function was called by... attributes.py:132(__get__) - domain.py:200(addEntry) (1236) 46.741 domain.py:248(__init__) (1236) 47.832 domain.py:272(get)(49452) 0.609 orderinglist.py: 202(_get_order_value)(1241013) 16.916 orderinglist.py:221(_order_entity) - orderinglist.py:208(reorder) (1240326) 44.025 orderinglist.py:232(append) (687)0.013 orderinglist.py:202(_get_order_value) - orderinglist.py: 221(_order_entity)(1241013) 39.836 attributes.py:394(set) - attributes.py:126(__set__) (440809) 11.752 orderinglist.py:208(reorder) - orderinglist.py: 266(__setslice__)(1236) 44.061 attributes.py:310(get) - attributes.py:132(__get__) (1292937) 12.134 attributes.py: 347(get_committed_value)(1)0.000 attributes.py:500(set) (3708)0.367 attributes.py: 837(value_as_iterable)(3090)0.108 identity.py:208(modified_event)- attributes.py:394(set) (440809)9.527 attributes.py: 525(fire_replace_event)(3708)0.236 attributes.py: 579(fire_append_event)(3708)1.960 orderinglist.py:205(_set_order_value) - orderinglist.py: 221(_order_entity)(437061) 39.836 attributes.py:126(__set__) - domain.py: 237(_set_attributes)(1276)0.079 domain.py:255(update) (2472)0.089 orderinglist.py: 205(_set_order_value)(437061) 14.331 attributes.py:958(modified_event) - identity.py: 208(modified_event)(448225)5.157 --~--~-~--~~~---~--~~ 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: ORM mapping existing data tables
Michael,
Thanks for the response and help, I made the change and the class is
populated, this is great!
Again, thanks for your help,
Doug
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of Michael Bayer
Sent: Sunday, September 21, 2008 10:41 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: ORM mapping existing data tables
On Sep 21, 2008, at 9:09 PM, Doug Farrell wrote:
Hi everyone,
I'm new to SqlAlchemy, but I've got some things working and really
am
enjoying it. Right now I'm trying to ORM map some existing MySQL
database tables to a class. I've read the documentation, I guess I'm
just not getting it. Can someone help me out. I've done this:
# initialize the sqlite engine and SqlAlchemy base objects
engine = create_engine('mysql://username:@hostname/database',
echo=True)
meta = MetaData(engine)
myTable = Table('mytable', meta, autoload=True)
l = [c.name for c in pressrouting.columns]
print l
And this works fine, but if I try this:
# initialize the sqlite engine and SqlAlchemy base objects
engine = create_engine('mysql://username:@hostname/database',
echo=True)
meta = MetaData(engine)
Base = declarative_base(metadata=meta)
class MyTable(Base):
__tablename__ = mytable
Pass
I get this error:
sqlalchemy.exc.ArgumentError: Mapper Mapper|MyTable|mytable could
not
assemble any primary key columns for mapped table 'mytable'
What is this error message trying to tell me?
the autoload=True part is missing from your second recipe, so the
table has no columns and therefore no primary key either. Add in
__table_args__ = {'autoload':True}.
t this group at http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
--~--~-~--~~~---~--~~
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: SA4: Why the automatic rollback on exception during SQL-Expression execution?
Michael, thanks for your quick and helpful response. For any new project, I would strongly urge the usage of 0.5... Our project isn't new, but I think it's time to move to SA 0.5. :-) Cheers, Tom --~--~-~--~~~---~--~~ 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: ordering_list performance
What I've done is something like this: account_entries = self.entries[:] for entry in new_entries: insort_right(account_entries, entry) for i, entry in enumerate(account_entries): entry.position = i self.entries = account_entries Don't know if it's the right way to do it but it's much faster. On 22 sep, 18:41, jason kirtland [EMAIL PROTECTED] wrote: I'm sure there is potential for improvement on the current orderinglist code- please feel free to send a patch with optimizations you've found to the SA trac. The orderinglist hasn't changed much since 0.3, but with 0.5 there may be entirely new implementations possible. For example, I could imagine one that defers calculation and manipulation of the positioning information until a before_flush hook. That may be perform better, with the trade-off that the position attribute can't be trusted to be in sync with the list order. jean-philippe dutreve wrote: Below is the profiling of code that added 1200 items into an ordering_list relation. I had to bypass the ordering_list stuff for bulk additions in order to have better performance (down to 2 seconds). Hope this post helps to improve this part (using 0.5.0rc1, python 2.5, linux i686, 1.5Go RAM) SA is rocking! jean-philippe Time elapsed: 48.4475638866 s 8875046 function calls (8869157 primitive calls) in 48.443 CPU seconds Ordered by: internal time, call count List reduced from 390 to 10 due to restriction 10 ncalls tottime percall cumtime percall filename:lineno(function) 1292937/1292250 7.879 0.000 12.134 0.000 attributes.py: 132(__get__) 1241013 7.662 0.000 39.836 0.000 orderinglist.py: 221(_order_entity) 1241013 5.870 0.000 16.916 0.000 orderinglist.py: 202(_get_order_value) 440809 4.522 0.000 9.527 0.000 attributes.py:394(set) 1236 4.198 0.003 44.025 0.036 orderinglist.py: 208(reorder) 1299736/1299048 3.752 0.000 4.373 0.000 attributes.py: 310(get) 448225 3.337 0.000 5.157 0.000 identity.py: 208(modified_event) 437061 2.704 0.000 14.331 0.000 orderinglist.py: 205(_set_order_value) 440809 2.225 0.000 11.752 0.000 attributes.py: 126(__set__) 448225 1.775 0.000 1.812 0.000 attributes.py: 958(modified_event) Function was called by... attributes.py:132(__get__) - domain.py:200(addEntry) (1236) 46.741 domain.py:248(__init__) (1236) 47.832 domain.py:272(get)(49452) 0.609 orderinglist.py: 202(_get_order_value)(1241013) 16.916 orderinglist.py:221(_order_entity) - orderinglist.py:208(reorder) (1240326) 44.025 orderinglist.py:232(append) (687) 0.013 orderinglist.py:202(_get_order_value) - orderinglist.py: 221(_order_entity)(1241013) 39.836 attributes.py:394(set) - attributes.py:126(__set__) (440809) 11.752 orderinglist.py:208(reorder) - orderinglist.py: 266(__setslice__)(1236) 44.061 attributes.py:310(get) - attributes.py:132(__get__) (1292937) 12.134 attributes.py: 347(get_committed_value)(1) 0.000 attributes.py:500(set) (3708) 0.367 attributes.py: 837(value_as_iterable)(3090) 0.108 identity.py:208(modified_event) - attributes.py:394(set) (440809) 9.527 attributes.py: 525(fire_replace_event)(3708) 0.236 attributes.py: 579(fire_append_event)(3708) 1.960 orderinglist.py:205(_set_order_value) - orderinglist.py: 221(_order_entity)(437061) 39.836 attributes.py:126(__set__) - domain.py: 237(_set_attributes)(1276) 0.079 domain.py:255(update) (2472) 0.089 orderinglist.py: 205(_set_order_value)(437061) 14.331 attributes.py:958(modified_event) - identity.py: 208(modified_event)(448225) 5.157 --~--~-~--~~~---~--~~ 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: ordering_list performance
A warning: that depends on a bug in the C version of bisect. When given a list subclass, it mistakenly ignores the subclass method implementations. The below will break, if and when that's fixed to match the pure Python implementation in the standard lib. Calling list.extend(account_entries, new_entries) is probably a safe alternative. * http://bugs.python.org/issue3935 jean-philippe dutreve wrote: What I've done is something like this: account_entries = self.entries[:] for entry in new_entries: insort_right(account_entries, entry) for i, entry in enumerate(account_entries): entry.position = i self.entries = account_entries Don't know if it's the right way to do it but it's much faster. On 22 sep, 18:41, jason kirtland [EMAIL PROTECTED] wrote: I'm sure there is potential for improvement on the current orderinglist code- please feel free to send a patch with optimizations you've found to the SA trac. The orderinglist hasn't changed much since 0.3, but with 0.5 there may be entirely new implementations possible. For example, I could imagine one that defers calculation and manipulation of the positioning information until a before_flush hook. That may be perform better, with the trade-off that the position attribute can't be trusted to be in sync with the list order. jean-philippe dutreve wrote: Below is the profiling of code that added 1200 items into an ordering_list relation. I had to bypass the ordering_list stuff for bulk additions in order to have better performance (down to 2 seconds). Hope this post helps to improve this part (using 0.5.0rc1, python 2.5, linux i686, 1.5Go RAM) SA is rocking! jean-philippe Time elapsed: 48.4475638866 s 8875046 function calls (8869157 primitive calls) in 48.443 CPU seconds Ordered by: internal time, call count List reduced from 390 to 10 due to restriction 10 ncalls tottime percall cumtime percall filename:lineno(function) 1292937/12922507.8790.000 12.1340.000 attributes.py: 132(__get__) 12410137.6620.000 39.8360.000 orderinglist.py: 221(_order_entity) 12410135.8700.000 16.9160.000 orderinglist.py: 202(_get_order_value) 4408094.5220.0009.5270.000 attributes.py:394(set) 12364.1980.003 44.0250.036 orderinglist.py: 208(reorder) 1299736/12990483.7520.0004.3730.000 attributes.py: 310(get) 4482253.3370.0005.1570.000 identity.py: 208(modified_event) 4370612.7040.000 14.3310.000 orderinglist.py: 205(_set_order_value) 4408092.2250.000 11.7520.000 attributes.py: 126(__set__) 4482251.7750.0001.8120.000 attributes.py: 958(modified_event) Function was called by... attributes.py:132(__get__) - domain.py:200(addEntry) (1236) 46.741 domain.py:248(__init__) (1236) 47.832 domain.py:272(get)(49452) 0.609 orderinglist.py: 202(_get_order_value)(1241013) 16.916 orderinglist.py:221(_order_entity) - orderinglist.py:208(reorder) (1240326) 44.025 orderinglist.py:232(append) (687)0.013 orderinglist.py:202(_get_order_value) - orderinglist.py: 221(_order_entity)(1241013) 39.836 attributes.py:394(set) - attributes.py:126(__set__) (440809) 11.752 orderinglist.py:208(reorder) - orderinglist.py: 266(__setslice__)(1236) 44.061 attributes.py:310(get) - attributes.py:132(__get__) (1292937) 12.134 attributes.py: 347(get_committed_value)(1)0.000 attributes.py:500(set) (3708)0.367 attributes.py: 837(value_as_iterable)(3090)0.108 identity.py:208(modified_event)- attributes.py:394(set) (440809)9.527 attributes.py: 525(fire_replace_event)(3708)0.236 attributes.py: 579(fire_append_event)(3708)1.960 orderinglist.py:205(_set_order_value) - orderinglist.py: 221(_order_entity)(437061) 39.836 attributes.py:126(__set__) - domain.py: 237(_set_attributes)(1276)0.079 domain.py:255(update) (2472)0.089 orderinglist.py: 205(_set_order_value)(437061) 14.331 attributes.py:958(modified_event) - identity.py: 208(modified_event)(448225)5.157 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to
[sqlalchemy] Re: ordering_list performance
Ah, looking more closely i see you're replacing self.entries with a list, not insorting into a SA list collection- that's totally ok. It might squeeze a little more speed out to do: updated_entries = list(self.entries) + new_entries base = len(self.entries) for idx, entry in enumerate(new_entries): entry.position = base + idx self.entries = updated_entries orderinglist's extend method could be made to do something much like the above quite efficiently. jason kirtland wrote: A warning: that depends on a bug in the C version of bisect. When given a list subclass, it mistakenly ignores the subclass method implementations. The below will break, if and when that's fixed to match the pure Python implementation in the standard lib. Calling list.extend(account_entries, new_entries) is probably a safe alternative. * http://bugs.python.org/issue3935 jean-philippe dutreve wrote: What I've done is something like this: account_entries = self.entries[:] for entry in new_entries: insort_right(account_entries, entry) for i, entry in enumerate(account_entries): entry.position = i self.entries = account_entries Don't know if it's the right way to do it but it's much faster. On 22 sep, 18:41, jason kirtland [EMAIL PROTECTED] wrote: I'm sure there is potential for improvement on the current orderinglist code- please feel free to send a patch with optimizations you've found to the SA trac. The orderinglist hasn't changed much since 0.3, but with 0.5 there may be entirely new implementations possible. For example, I could imagine one that defers calculation and manipulation of the positioning information until a before_flush hook. That may be perform better, with the trade-off that the position attribute can't be trusted to be in sync with the list order. jean-philippe dutreve wrote: Below is the profiling of code that added 1200 items into an ordering_list relation. I had to bypass the ordering_list stuff for bulk additions in order to have better performance (down to 2 seconds). Hope this post helps to improve this part (using 0.5.0rc1, python 2.5, linux i686, 1.5Go RAM) SA is rocking! jean-philippe Time elapsed: 48.4475638866 s 8875046 function calls (8869157 primitive calls) in 48.443 CPU seconds Ordered by: internal time, call count List reduced from 390 to 10 due to restriction 10 ncalls tottime percall cumtime percall filename:lineno(function) 1292937/12922507.8790.000 12.1340.000 attributes.py: 132(__get__) 12410137.6620.000 39.8360.000 orderinglist.py: 221(_order_entity) 12410135.8700.000 16.9160.000 orderinglist.py: 202(_get_order_value) 4408094.5220.0009.5270.000 attributes.py:394(set) 12364.1980.003 44.0250.036 orderinglist.py: 208(reorder) 1299736/12990483.7520.0004.3730.000 attributes.py: 310(get) 4482253.3370.0005.1570.000 identity.py: 208(modified_event) 4370612.7040.000 14.3310.000 orderinglist.py: 205(_set_order_value) 4408092.2250.000 11.7520.000 attributes.py: 126(__set__) 4482251.7750.0001.8120.000 attributes.py: 958(modified_event) Function was called by... attributes.py:132(__get__) - domain.py:200(addEntry) (1236) 46.741 domain.py:248(__init__) (1236) 47.832 domain.py:272(get)(49452) 0.609 orderinglist.py: 202(_get_order_value)(1241013) 16.916 orderinglist.py:221(_order_entity) - orderinglist.py:208(reorder) (1240326) 44.025 orderinglist.py:232(append) (687)0.013 orderinglist.py:202(_get_order_value) - orderinglist.py: 221(_order_entity)(1241013) 39.836 attributes.py:394(set) - attributes.py:126(__set__) (440809) 11.752 orderinglist.py:208(reorder) - orderinglist.py: 266(__setslice__)(1236) 44.061 attributes.py:310(get) - attributes.py:132(__get__) (1292937) 12.134 attributes.py: 347(get_committed_value)(1)0.000 attributes.py:500(set) (3708)0.367 attributes.py: 837(value_as_iterable)(3090)0.108 identity.py:208(modified_event)- attributes.py:394(set) (440809)9.527 attributes.py: 525(fire_replace_event)(3708)0.236 attributes.py: 579(fire_append_event)(3708)1.960 orderinglist.py:205(_set_order_value) - orderinglist.py: 221(_order_entity)(437061) 39.836 attributes.py:126(__set__) - domain.py:
[sqlalchemy] Relations - How?
I have this problem with setting relations with specific models: Book : isbn - integer translations - many translations prices - many prices Translation : book - FK to book laguage - FK to Language (oneTOone) title - string Language : code - string name - string Currency : code - string name - string Price : currency - FK book -FK brutto - int netto - int so those are my models. I would like now to get books that: isbn1 translation.title in english starts with The ( I don't need other languages for this select) price.netto 100 USD ( I don't need other prices, only in USD for this select) There are 5 Tables to join. Lets say I have 100 objects maching, I dont want to hit the db 100 times. I am begginer in SQLAlchemy so please understand my maybe Stupid question. --~--~-~--~~~---~--~~ 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: Fwd: Support for ordered lists of child items
Thank you Jason. orderinglist looks like what I am after! Is your orderinglist plugin fully functional in 0.4.7p1? Before I attempt it, pls advise if there any technical reason preventing the integration of orderinglist into the basic_tree.py (adjacency list) example? See http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/adjacencytree/basic_tree.py E. jason kirtland wrote: Yep, orderinglist handles that case. Michael Bayer wrote: forwarded from pvt email orderinglist ? Begin forwarded message: *From: *Emmett [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *Date: *September 22, 2008 9:51:31 AM EDT *To: *Michael Bayer [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *Subject: **Re: Support for ordered lists of child items* Hello Michael, 18 months later, would your answer to Aaron still be the same? I have a problem fitting what Aaron described. ie. save+restore of child order after re-ordering in the Python side. Re-ordering child list elements would obviously be ideal, but I could cope with updating an extra integer node attribute instead. I'm completely new to SA and at this stage skimming documentation and looking at the tree examples. Found this thread, so wondering if some newer SA magic can solve this, or if a custom collection class or something else is the best solution. I also looked at the ElementTree examples, but they don't appear to guarantee child order either - correct? Thanks in advance. I looked at your activity in this group. Amazing! On Apr 10 2007, 4:02 am, Michael Bayer [EMAIL PROTECTED] wrote: we dont have the capability to automatically update ordering columns when the elements of a list are moved around. if you move the elements around, you need to execute some step that will update the index columns (or create a custom collection class that does this for you). On Apr 9, 2007, at 12:42 PM, Aaron Digulla wrote: Hello, I'm looking for a feature but couldn't find it in the docs. I have atreelike structure where the user can specify theorderof thechildrenof a node. In DB lingo, I have a parentId and an index column. When I loadchildren, they should be ordered by the index. This seems to be supported. Can SA also update the index column when I movechildrenin the list around? Like: # ... parent has threechildrenA, B C item = parent.children[0] del parent.children[0] parent.children.insert (1, item) # now, parent has threechildrenB, A, C 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] Queries very slow for parsing Wikipedia dump -- any ideas for speeding it up?
Hi: I am working on an open source project to write a search engine / datamining framework of sorts for Wikipedia, and one of the first things I need to do is to parse the Wikipedia dumps into an SQL database. I am using the sqlalchemy to do this but it is very slow (at the current rate, 130 days!!!). I am sure that I am doing something wrong since I am new at this, and am wondering whether any sqlalchemy veterans can offer his/her insights. The code can be found here: http://wikiminer.googlecode.com/svn/trunk/wikipedia_miner.py The critical part of the code is this: for link_label, link_dest_title, dest_frag in self.parse_links(self.text): print 'LINK from:', repr(self.title), 'to', repr(link_dest_title + '#' + dest_frag), 'label', repr(link_label) try: link_dest = session.query(Article).filter_by(title=link_dest_title).one() except sqlalchemy.orm.exc.NoResultFound: link_dest = None print link_dest session.add(Link(self, link_label, link_dest, dest_frag)) Basically what this does is that it parses the links in a page, looks it up in the DB to resolve the reference, and then insert a Link into the DB. The problem is that the articles table is over 7 million rows and there are maybe 50 million links. I have tried using both SQLite and Postgres as the database. Postgres EXPLAIN ANALYZE claims that the above statements should take only around 25 ms! I think I am doing something wrong with sqlalchemy, maybe I am creating too many objects? Any help would be very appreciated. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
