The reason for the extra selects is that calling commit() marks objects in the session as expired, so they need to be refreshed. From the ORM tutorial<http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html> :
SQLAlchemy by default refreshes data from a previous transaction the first > time it’s accessed within a new transaction, so that the most recent state > is available. The level of reloading is configurable as is described in *Using > the Session* <http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html>. >From the section on committing<http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#committing> : Another behavior of commit() is that by default it expires the state of all > instances present after the commit is complete. This is so that when the > instances are next accessed, either through attribute access or by them > being present in a Query result set, they receive the most recent state. > To disable this behavior, configure sessionmaker with > expire_on_commit=False. The other option, is to just commit after the loop, not inside it. That is OK if the loop is not too long-running and there aren't tricky locking ramifications — and indeed it may be preferable if you want to make sure the ManagerConfigs you create are transactionally consistent with the BarLogs they are based on; for that you might also consider loading the BarLogs .with_lockmode('read') Gulli On Fri, Aug 30, 2013 at 8:10 AM, Дмитрий Косолапов <kosolapo...@gmail.com>wrote: > my program code: > > > engine = create_engine(connect_str, echo=True)Session = > sessionmaker(bind=engine)for bar in default_session.query(BarLog)[:3]: > conf = ManagerConfig(indicator_config='', timeframe=bar.timeframe, > paper_no=1) > default_session.add(conf) > default_session.commit() > > > > log: > > 2013-08-29 22:52:58,640 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) > 2013-08-29 22:52:58,642 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id > AS bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min, > bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume > AS bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS > bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS > bar_log_paper_no > FROM bar_log > LIMIT %(param_1)s > 2013-08-29 22:52:58,642 INFO sqlalchemy.engine.base.Engine {'param_1': 3} > 2013-08-29 22:52:58,646 INFO sqlalchemy.engine.base.Engine INSERT INTO > manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s, > %(timeframe)s, %(indicator_config)s) RETURNING manager_config.id > 2013-08-29 22:52:58,646 INFO sqlalchemy.engine.base.Engine > {'indicator_config': ‘', ’paper_no': 1, ‘timeframe’: 1} > 2013-08-29 22:52:58,647 INFO sqlalchemy.engine.base.Engine COMMIT > 2013-08-29 22:52:58,662 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) > 2013-08-29 22:52:58,664 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id > AS bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min, > bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume > AS bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS > bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS > bar_log_paper_no > FROM bar_log > WHERE bar_log.id = %(param_1)s > 2013-08-29 22:52:58,664 INFO sqlalchemy.engine.base.Engine {'param_1': 2} > 2013-08-29 22:52:58,667 INFO sqlalchemy.engine.base.Engine INSERT INTO > manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s, > %(timeframe)s, %(indicator_config)s) RETURNING manager_config.id > 2013-08-29 22:52:58,668 INFO sqlalchemy.engine.base.Engine > {'indicator_config': ‘', ’paper_no': 1, ‘timeframe’: 1} > 2013-08-29 22:52:58,670 INFO sqlalchemy.engine.base.Engine COMMIT > 2013-08-29 22:52:58,679 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) > 2013-08-29 22:52:58,681 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id > AS bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min, > bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume > AS bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS > bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS > bar_log_paper_no > FROM bar_log > WHERE bar_log.id = %(param_1)s > 2013-08-29 22:52:58,681 INFO sqlalchemy.engine.base.Engine {'param_1': 3} > 2013-08-29 22:52:58,685 INFO sqlalchemy.engine.base.Engine INSERT INTO > manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s, > %(timeframe)s, %(indicator_config)s) RETURNING manager_config.id > 2013-08-29 22:52:58,685 INFO sqlalchemy.engine.base.Engine > {'indicator_config': ‘', ’paper_no': 1, ‘timeframe’: 1} > 2013-08-29 22:52:58,688 INFO sqlalchemy.engine.base.Engine COMMIT > > > how to use one select query and many insert queries? > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.