[sqlalchemy] Help with DeferredReflection and setting up an engine at runtime?
I am having a bit of trouble getting DeferredReflection working the way I
want; not sure if I am overlooking something obvious or if I just don't
really understand how it's supposed to work.
I'm trying to define my models before creating my engine (this does not
work):
Base = declarative_base(cls=*DeferredReflection*)
class CityStats(Base):
__tablename__ = 'city_stats'
__table_args__ = {'schema': 'prod', *'autoload': True*}
if __name__ == '__main__':
engine = create_engine('...')
Base.metadata.bind = engine
Base.prepare(engine)
When I run this I get an error creating the CityStats class:
sqlalchemy.exc.UnboundExecutionError:
No engine is bound to this Table's MetaData. Pass an engine to the Table
via autoload_with=someengine, or associate the MetaData with an engine
via metadata.bind=someengine
Of course it does work if I create my engine and set Base.metadata.bind
BEFORE I define the CityStats model (this works):
engine = create_engine('...')
Base.metadata.bind = engine
class CityStats(Base):
__tablename__ = 'city_stats'
__table_args__ = {'schema': 'prod', *'autoload': True*}
Base.prepare(engine)
I'm trying to avoid some kind of model_init() function that everyone who
imports my models.py file will have to remember to call before importing my
models. Is this possible?
--
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.
[sqlalchemy] Cannot use pgbouncer's statement mode
Hello there. I'm using SQLAlchemy 0.7.10 with postgres+pgbouncer, and
I'm trying to move from a transaction-mode pool towards a
statement-mode pool.
The reason for this move, is that I've got quite a high volume of
reads that cannot be quickly serviced in transaction mode, since many
connections are left idling in a transaction for perhaps up to 1
second. A combination of network roundtrips and GIL-related delays
conspire to get me into this position.
So, I've been refactoring my application so that I can use two kinds
of sessions, read-write normal transactional ones, and read-only ones
that use autocommit and a different pgbouncer configured for statement
pooling.
Problem I run into, is that even in autocommit mode, SQLAlchemy itself
issues multi-statement transactions, like this:
s = get_slave_session(autocommit=True)
c = s.connection()
Traceback (most recent call last):
File stdin, line 1, in module
File /opt/jampp/analytics/matlogbrowser/model/base.py, line 80, in
connection
return sqlalchemy.orm.Session.connection(self, *p, **kw)
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py, line
784, in connection
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py, line
788, in _connection_for_bind
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py, line
313, in _connection_for_bind
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line
2489, in contextual_connect
File build/bdist.linux-x86_64/egg/sqlalchemy/pool.py, line 236, in connect
File build/bdist.linux-x86_64/egg/sqlalchemy/pool.py, line 401, in __init__
File build/bdist.linux-x86_64/egg/sqlalchemy/pool.py, line 746, in _do_get
File build/bdist.linux-x86_64/egg/sqlalchemy/pool.py, line 189, in
_create_connection
File build/bdist.linux-x86_64/egg/sqlalchemy/pool.py, line 287, in __init__
File build/bdist.linux-x86_64/egg/sqlalchemy/event.py, line 380,
in exec_once
File build/bdist.linux-x86_64/egg/sqlalchemy/event.py, line 389, in __call__
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/strategies.py,
line 167, in first_connect
File build/bdist.linux-x86_64/egg/sqlalchemy/dialects/postgresql/base.py,
line 1000, in initialize
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py,
line 171, in initialize
File build/bdist.linux-x86_64/egg/sqlalchemy/dialects/postgresql/base.py,
line 1184, in _get_server_version_info
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line
1449, in execute
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line
1628, in _execute_text
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line
1698, in _execute_context
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line
1691, in _execute_context
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py,
line 331, in do_execute
sqlalchemy.exc.DatabaseError: (DatabaseError) Long transactions not allowed
ERROR: Long transactions not allowed
'select version()' {}
Here, get_slave_session returns a subclass of Session that will
override connection() to return a connection that has
execution_options(autocommit=True) applied (because for pgbouncer,
even the implicit transaction for selects is a transaction). The
session itself is also built with a autocommit=True.
Now, it seems the session is using a connection that is indepepndent
of what connection() returns. What do I have to override to get the
desired behavior? (that is: one query per transaction no matter which
type of query).
--
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.
Re: [sqlalchemy] Help with DeferredReflection and setting up an engine at runtime?
On Feb 17, 2014, at 6:23 PM, Rob Crowell robccrow...@gmail.com wrote:
I am having a bit of trouble getting DeferredReflection working the way I
want; not sure if I am overlooking something obvious or if I just don't
really understand how it's supposed to work.
I'm trying to define my models before creating my engine (this does not work):
Base = declarative_base(cls=DeferredReflection)
class CityStats(Base):
__tablename__ = 'city_stats'
__table_args__ = {'schema': 'prod', 'autoload': True}
if __name__ == '__main__':
engine = create_engine('...')
Base.metadata.bind = engine
Base.prepare(engine)
When I run this I get an error creating the CityStats class:
sqlalchemy.exc.UnboundExecutionError: No engine is bound to this Table's
MetaData. Pass an engine to the Table via autoload_with=someengine, or
associate the MetaData with an engine via metadata.bind=someengine”
its all about the stack trace, lets look:
Traceback (most recent call last):
File test.py, line 8, in module
class CityStats(Base):
File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/ext/declarative/api.py,
line 53, in __init__
_as_declarative(cls, classname, cls.__dict__)
File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/ext/declarative/base.py,
line 251, in _as_declarative
**table_kw)
File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/schema.py, line 350,
in __new__
table._init(name, metadata, *args, **kw)
File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/schema.py, line 423,
in _init
self._autoload(metadata, autoload_with, include_columns)
File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/schema.py, line 439,
in _autoload
msg=No engine is bound to this Table's MetaData.
File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/base.py, line 459, in
_bind_or_error
what we see here is that this script doesn’t get to create_engine() at all,
it’s trying to hit the database as soon as you say “CityStats(Base)”. Why is
that? Because you have “autoload=True” in your table args, which means,
“reflect this table *right now*”. That is, you are defeating the purpose of
using DeferredReflection.
The solution is just take out that autoload=True. Any class which descends
from the Base here is automatically part of the “classes to reflect” since you
have DeferredReflection at the base.
Also, if you’re on 0.9 take a look at the new “automap” extension, I’ve been
using it and it’s pretty keen. There’s one fix for it in not-yet-released
0.9.3 but it’s only needed if you’re dealing with inheritance structures.
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Cannot use pgbouncer's statement mode
On Feb 17, 2014, at 7:31 PM, Michael Bayer mike...@zzzcomputing.com wrote: O from sqlalchemy import event, create_engine eng = create_engine(postgresql+psycopg2://scott:tiger@localhost/test, echo=True) @event.listens_for(eng, first_connect, insert=True) @event.listens_for(eng, connect, insert=True) def setup_autocommit(dbapi_conn, rec): dbapi_conn.autocommit = True conn = eng.connect() print conn.execute(select 1).fetchall() oh and also if you are on 0.8 (not 0.7), you can just send AUTOCOMMIT to create_engine: eng = create_engine(postgresql+psycopg2://scott:tiger@localhost/test, echo=True, isolation_level='AUTOCOMMIT' ) much easier signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Help with DeferredReflection and setting up an engine at runtime?
Interesting, thanks Michael. I didn't realize autoload was implied when
using DeferredReflection but that makes sense.
Thanks!
On Monday, February 17, 2014 7:17:34 PM UTC-5, Michael Bayer wrote:
On Feb 17, 2014, at 6:23 PM, Rob Crowell robcc...@gmail.com javascript:
wrote:
I am having a bit of trouble getting DeferredReflection working the way I
want; not sure if I am overlooking something obvious or if I just don't
really understand how it's supposed to work.
I'm trying to define my models before creating my engine (this does not
work):
Base = declarative_base(cls=*DeferredReflection*)
class CityStats(Base):
__tablename__ = 'city_stats'
__table_args__ = {'schema': 'prod', *'autoload': True*}
if __name__ == '__main__':
engine = create_engine('...')
Base.metadata.bind = engine
Base.prepare(engine)
When I run this I get an error creating the CityStats class:
sqlalchemy.exc.UnboundExecutionError:
No engine is bound to this Table's MetaData. Pass an engine to the Table
via autoload_with=someengine, or associate the MetaData with an engine
via metadata.bind=someengine”
its all about the stack trace, lets look:
Traceback (most recent call last):
File test.py, line 8, in module
class CityStats(Base):
File
/Users/classic/dev/sqlalchemy/lib/sqlalchemy/ext/declarative/api.py, line
53, in __init__
_as_declarative(cls, classname, cls.__dict__)
File
/Users/classic/dev/sqlalchemy/lib/sqlalchemy/ext/declarative/base.py,
line 251, in _as_declarative
**table_kw)
File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/schema.py, line
350, in __new__
table._init(name, metadata, *args, **kw)
File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/schema.py, line
423, in _init
self._autoload(metadata, autoload_with, include_columns)
File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/schema.py, line
439, in _autoload
msg=No engine is bound to this Table's MetaData.
File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/base.py, line
459, in _bind_or_error
what we see here is that this script doesn’t get to create_engine() at
all, it’s trying to hit the database as soon as you say “CityStats(Base)”.
Why is that? Because you have “autoload=True” in your table args, which
means, “reflect this table *right now*”. That is, you are defeating the
purpose of using DeferredReflection.
The solution is just take out that autoload=True. Any class which
descends from the Base here is automatically part of the “classes to
reflect” since you have DeferredReflection at the base.
Also, if you’re on 0.9 take a look at the new “automap” extension, I’ve
been using it and it’s pretty keen. There’s one fix for it in
not-yet-released 0.9.3 but it’s only needed if you’re dealing with
inheritance structures.
--
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.
Re: [sqlalchemy] Cannot use pgbouncer's statement mode
On Mon, Feb 17, 2014 at 9:31 PM, Michael Bayer mike...@zzzcomputing.com wrote: the Python DBAPI as you know returns a connection that's in a transaction. So if you are using an environment that has zero tolerance for even two statements in one transaction, you should turn on autocommit at the psycopg2 level, using the connection.autocommit flag. you can achieve this with an on-connect event, with some tricks currently needed to absolutely force it as the first thing that happens (I need to either document this or make a nicer API, but this works for now), like this: from sqlalchemy import event, create_engine eng = create_engine(postgresql+psycopg2://scott:tiger@localhost/test, echo=True) @event.listens_for(eng, first_connect, insert=True) @event.listens_for(eng, connect, insert=True) def setup_autocommit(dbapi_conn, rec): dbapi_conn.autocommit = True conn = eng.connect() print conn.execute(select 1).fetchall() This is exactly what I need, thanks. Furthermore, with psycopg2 level autocommit, I don't think I even need a second statement-based pool, it will decrease idle-in-transaction time on its own. In fact, I thought this is what execution_options(autocommit=True) did, though looking at the code, now I realize it wasn't. Many thanks :) PS: Yeah, I know I have to update to 8.x ;-) -- 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.
Re: [sqlalchemy] Cannot use pgbouncer's statement mode
On Feb 17, 2014, at 9:27 PM, Claudio Freire klaussfre...@gmail.com wrote: This is exactly what I need, thanks. Furthermore, with psycopg2 level autocommit, I don't think I even need a second statement-based pool, it will decrease idle-in-transaction time on its own. In fact, I thought this is what execution_options(autocommit=True) did, though looking at the code, now I realize it wasn’t. right, we currently don’t link the SQLAlchemy-level “autocommit” things with a DBAPI-level autocommit. Not all DBAPIs have an autocommit option as it isn’t in the spec and I’m generally just concerned about how wide of a behavioral difference there’d be between different backends if we sometimes used their autocommit, sometimes, ours, etc. signature.asc Description: Message signed with OpenPGP using GPGMail
