many queries select if in cycle has insert into table

[Дмитрий Косолапов]

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?

[Simon King]

The reason you are getting a new SELECT each time is that, by default,
session.commit() expires all the objects in the session. This is
usually what you want, as you generally want to get the latest state
from the database after each transaction has finished.

If you don't want that behaviour, you can set expire_on_commit to
False when constructing your session:

http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#sqlalchemy.orm.session.Session.commit

Hope that helps,

Simon

[Gunnlaugur Thor Briem]

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:

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.

From the section on 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

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Дмитрий Косолапов]

Thanks!

пятница, 30 августа 2013 г., 17:46:47 UTC+7 пользователь Gunnlaugur Briem написал: