[sqlalchemy] Enclose each test in a transaction
Hi! I am trying to make a base class for our tests, that after each test case all the changes made by tests and the tested code are rolled back. I saw the pattern here http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#joining-a-session-into-an-external-transaction, but i have some problems. class BaseTest(unittest.TestCase): http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#joining-a-session-into-an-external-transaction def __call__(self, result=None): Wrapper around default __call__ method to perform common test set up. This means that user-defined Test Cases aren't required to include a call to super().setUp(). testMethod = getattr(self, self._testMethodName) skipped = (getattr(self.__class__, __unittest_skip__, False) or getattr(testMethod, __unittest_skip__, False)) if not skipped: try: self._pre_setup() except (KeyboardInterrupt, SystemExit): raise except Exception: result.addError(self, sys.exc_info()) return super(BaseTest, self).__call__(result) if not skipped: try: self._post_teardown() except (KeyboardInterrupt, SystemExit): raise except Exception: result.addError(self, sys.exc_info()) return def _pre_setup(self): # connect to the database self._connection = apilib.engine.connect() # begin a non-ORM transaction self._transaction = self._connection.begin() # make apilib.Session to be inside our transaction apilib.Session.configure(bind=self._connection) self.session = sessionmaker(bind=self._connection)() def _post_teardown(self): # roll back all changes made by the tests self._transaction.rollback() self.session.close() apilib.Session.configure(bind=apilib.engine) # return connection to the Engine self._connection.close() 1. The tests code uses apilib.Session, while the tests use self.session. 2. Looks like if in code some does an additional session.rollback(), all the enclosing transaction is rolled back, and all objects created in setUp() (which use self.session) are lost. 3. Did i do correctly that i made the tested code use the session with test connection in transaction? Otherwise i guess sessions in the tested code and test itself would be in different transactions, and the tested code would not see changes made in setUp() 4. Is this the best pattern for our needs? -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] problem with with_entities(), over() and label()
On 6.6.2013 19:33, Michael Bayer wrote: On Jun 6, 2013, at 12:56 PM, Ladislav Lenart lenart...@volny.cz wrote: Hello. I have already solved the issue by using subquery: SELECT t.id AS t_id, t.rownum AS t_rownum FROM ( SELECT FROM foo.id AS id, row_number() OVER (ORDER BY foo.id) AS rownum ) AS t WHERE rownum % 50 = 1 I have just tried your suggestion about using HAVING instead of WHERE, but that fails with the same error. Thus a label cannot be used inside a query. However, I am still curious whether the original WindowedRangeQuery recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery works or also has this error. the recipe as you noted uses from_self(), which means, wrap myself in a subquery, so that's where the necessary subquery is applied. That explains it :-) Thank you, Ladislav Lenart -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] [ANN] Modified WindowedRangeQuery recipe
Hello. I modified the recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery to better suit my needs. Perhaps someone else will find this useful: CODE BEGIN from sqlalchemy.orm import subqueryload from sqlalchemy.sql.expression import distinct def windowed_query(filter_query, data_query, column, window_size): Perform (a correct) yield_per() operation. See WindowedQuery.yield_per() for more. EXAMPLE: gen = windowed_query( filter_query=session.query(Foo).filter(Foo.name.like(u'%foo%')), data_query=session.query(Foo).options(Foo.bars), column=Foo.id, window_size=50, ) for each_foo in gen: print each_foo.name for each_bar in each_foo.bars: print each_bar return WindowedQuery(filter_query, data_query, column).yield_per(window_size) class WindowedQuery(object): Perform (a correct) yield_per() operation. def __init__(self, filter_query, data_query, column): # A query with NO options(...) and NO order_by(...). MUST contain all # necessary join() and filter() to limit the result set as desired. self._filter_query = filter_query # A simple query with options() to fetch the actual data. self._data_query = data_query # id column of the (main) result ORM class. self._column = column def yield_per(self, window_size): Process results in chunks. Steps: * Obtain ids of ALL result rows via self._filter_query. * Divide ids to chunks of equal size and perform ONE query for EACH chunk to fetch the data via self._data_query. A chunk is determined by the test q.filter(self._column.in_(chunk)). This is the only way that works in presence of the read-committed isolation level. q = self._data_query for each_window in self._windows(window_size): for each_result in q.filter(each_window): yield each_result def _windows(self, window_size): chunk = [] chunk_size = 0 for each in self._q_column(): if chunk_size window_size: chunk.append(each) chunk_size += 1 if chunk_size == window_size: yield self._window_for_chunk(chunk) chunk = [] chunk_size = 0 if chunk_size 0: yield self._window_for_chunk(chunk) def _q_column(self): # distinct() ensures that each id is returned at most once despite # a possible multiplying effect of a join(). return self._filter_query.with_entities(distinct(self._column)) def _window_for_chunk(self, chunk): return self._column.in_(chunk) ## CODE END ## MOTIVATION: I have learned recently that Query.yield_per() does not work nicely in combination with subqueryload(). The above recipe fixes that. Unfortunately its usage is not as elegant and simple as q.yield_per(...). If you have any idea how to accomplish the same with ONE query only (in SA 0.7.9): def windowed_query(query, column, window_size): query --magic- filter_query query --magic- data_query ... I would very much like to hear about it. PERFORMANCE: My first tests suggest that it might be one order of magnitude better than the Query.yield_per() we use now. Note also that yield_per() with subqueryload() was still about twice as fast as the same query without yield_per(). But this will be highly dependent on the query I guess. WARNING: We do not use this in the production yet. Use at your own risk. Happy SA hacking, Ladislav Lenart -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Simple [?] question about column prefixes
Hi all! I'm refactoring a database schema but I need it to mantain reverse compatibility with older versions of our software - using views. But, to avoid confusion to other developers, new tables have two underscores as a prefix, like: class Base(object): @declared_attr def __tablename__(cls): return __%s % to_python_case(cls.__name__) Now I want to add column prefixes too. I know I can append to this Base object: __mapper_args__ = dict( column_prefix='_' ) But, when I inherit this new base on classes that I need to use __mapper_args__, column names probably won't have prefixes. Any ideas, perhaps an event listener to prepend the underscore into *all* column names? Cheers, Richard. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [BUG][PATCH] Function names not quoted when necessary
logged this as http://www.sqlalchemy.org/trac/attachment/ticket/2749
On Jun 6, 2013, at 10:27 PM, Ryan Kelly rpkell...@gmail.com wrote:
Function names in SQL can contain pretty much anything, e.g.:
=# create function A Bug?(integer) returns integer as $$ select $1; $$
language sql;
CREATE FUNCTION
But when attempting to use the function from SQLAlchemy:
from sqlalchemy.sql.expression import func
bug = getattr(func, A Bug?)(1)
session.query(bug).all()
ProgrammingError: (ProgrammingError) syntax error at or near ?
LINE 1: SELECT A Bug?(1) AS A Bug?_1
'SELECT A Bug?(%(A Bug?_2)s) AS A Bug?_1' {'A Bug?_2': 1}
-Ryan P. Kelly
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
quote_function_names.patch
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Enclose each test in a transaction
On Jun 7, 2013, at 3:29 AM, Victor Varvariuc victor.varvar...@gmail.com wrote: 1. The tests code uses apilib.Session, while the tests use self.session. well depends on what you're testing, if the tests go out to logic which refers to the global apilib.Session, that's fine 2. Looks like if in code some does an additional session.rollback(), all the enclosing transaction is rolled back, and all objects created in setUp() (which use self.session) are lost. yeah this whole recipe does not accomodate tests that do a rollback() in the middle, then continue going. 3. Did i do correctly that i made the tested code use the session with test connection in transaction? Otherwise i guess sessions in the tested code and test itself would be in different transactions, and the tested code would not see changes made in setUp() seems like ? 4. Is this the best pattern for our needs? shrugs ? -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Simple [?] question about column prefixes
On Jun 7, 2013, at 9:31 AM, Richard Gerd Kuesters rich...@humantech.com.br wrote: Hi all! I'm refactoring a database schema but I need it to mantain reverse compatibility with older versions of our software - using views. But, to avoid confusion to other developers, new tables have two underscores as a prefix, like: class Base(object): @declared_attr def __tablename__(cls): return __%s % to_python_case(cls.__name__) Now I want to add column prefixes too. I know I can append to this Base object: __mapper_args__ = dict( column_prefix='_' ) But, when I inherit this new base on classes that I need to use __mapper_args__, column names probably won't have prefixes. Any ideas, perhaps an event listener to prepend the underscore into *all* column names? one idea is you can intercept how mapper() is called by declarative by overriding __mapper_cls__, like: @classmethod def __mapper_cls__(cls, *args, **kw): kw['column_prefix'] = '_' return mapper(*args, **kw) Cheers, Richard. -- 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?hl=en. 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Query and compiled_cache
On Thu, Jun 6, 2013 at 2:20 PM, Michael Bayer mike...@zzzcomputing.com wrote: diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py index c2ec72c..b458975 100644 --- a/lib/sqlalchemy/orm/query.py +++ b/lib/sqlalchemy/orm/query.py @@ -40,12 +40,15 @@ from ..sql import ( __all__ = ['Query', 'QueryContext', 'aliased'] -def _generative(*assertions): +def _generative(*assertions, maintain_baked=False): Mark a method as generative. @util.decorator def generate(fn, *args, **kw): self = args[0]._clone() +if not maintain_baked: +del self._baked_cache +del self._baked_context for assertion in assertions: assertion(self, fn.func_name) fn(self, *args[1:], **kw) @@ -1157,7 +1160,7 @@ class Query(object): self._lockmode = mode -@_generative() +@_generative(maintain_baked=True) def params(self, *args, **kwargs): add values for bind parameters which may have been specified in filter(). That doesn't seem to be enough. subqueryload seems to be using the wrong query still, after clearing the baked context, resulting in some very wrong sharing of connections between threads (I'm getting some very fun segmentation faults). I think it's with_parent, it clears the baked context through a filter, but the filter is called on the wrong query (the global query instance I use as template), that has the wrong session attached or something. This is my current BakedQuery: class BakedQuery(sqlalchemy.orm.query.Query): _baked_context = None _baked_cache = None def _clone(self): rv = super(BakedQuery, self)._clone() try: del rv._baked_context del rv._baked_cache except AttributeError: pass return rv def params(self, *p, **kw): rv = super(BakedQuery, self).params(*p, **kw) rv._baked_context = self._baked_context rv._baked_cache = self._baked_cache return rv def with_session(self, *p, **kw): rv = super(BakedQuery, self).with_session(*p, **kw) rv._baked_context = self._baked_context rv._baked_cache = self._baked_cache return rv @sqlalchemy.orm.query._generative() def bake_as(self, name, cache): Freeze the statement used by this Query. if name not in cache: cache[name] = context = self._compile_context() del context.session del context.query self._baked_context = cache[name] self._baked_cache = cache def _compile_context(self, **kw): if self._baked_context is not None: QueryContext = sqlalchemy.orm.query.QueryContext context = QueryContext.__new__(QueryContext) context.__dict__.update(self._baked_context.__dict__) context.query = self context.session = self.session # need to fix these names, urg context.attributes = context._attributes = context.attributes.copy() return context else: return super(BakedQuery, self)._compile_context(**kw) def _execute_and_instances(self, querycontext): if self._baked_cache is not None: self = self.execution_options(compiled_cache=self._baked_cache) return super(BakedQuery, self)._execute_and_instances(querycontext) And I invoke it like: def some_function(query = blabla.bake_as(blablah)): return query.with_session(S).params(...).first() This code still breaks if I don't use a baked template: def some_function(query = blabla): return query.with_session(S).bake_as(blablah).params(...).first() -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Query and compiled_cache
On Fri, Jun 7, 2013 at 1:56 PM, Claudio Freire klaussfre...@gmail.com wrote: This is my current BakedQuery: class BakedQuery(sqlalchemy.orm.query.Query): F**k gmail again. Why the F+!k doesn't it show me that it'll bork all whitespace before I hit send... I smell a bug report coming... class BakedQuery(sqlalchemy.orm.query.Query): ._baked_context = None ._baked_cache = None . .def _clone(self): .rv = super(BakedQuery, self)._clone() .try: .del rv._baked_context .del rv._baked_cache .except AttributeError: .pass .return rv . .def params(self, *p, **kw): .rv = super(BakedQuery, self).params(*p, **kw) .rv._baked_context = self._baked_context .rv._baked_cache = self._baked_cache .return rv . .def with_session(self, *p, **kw): .rv = super(BakedQuery, self).with_session(*p, **kw) .rv._baked_context = self._baked_context .rv._baked_cache = self._baked_cache .return rv . .@sqlalchemy.orm.query._generative() .def bake_as(self, name, cache): .Freeze the statement used by this Query. . .if name not in cache: .cache[name] = context = self._compile_context() .del context.session .del context.query .self._baked_context = cache[name] .self._baked_cache = cache . .def _compile_context(self, **kw): .if self._baked_context is not None: .QueryContext = sqlalchemy.orm.query.QueryContext .context = QueryContext.__new__(QueryContext) .context.__dict__.update(self._baked_context.__dict__) .context.query = self .context.session = self.session .# need to fix these names, urg .context.attributes = context._attributes = context.attributes.copy() .return context .else: .return super(BakedQuery, self)._compile_context(**kw) . .def _execute_and_instances(self, querycontext): .if self._baked_cache is not None: .self = self.execution_options(compiled_cache=self._baked_cache) .return super(BakedQuery, self)._execute_and_instances(querycontext) -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Simple [?] question about column prefixes
Thank you Mike!
That brings me to another question:
Let's say I have created a simple table (well, I have):
class Language(Base):
language_id = Column(Integer, Sequence('language_id_seq',
optional=True), primary_key=True)
language = Column(String(5), unique=True, default='undef')
The problem is (using pdb):
(Pdb) lang = Language(language='test')
(Pdb) lang
Language(language='test')
(Pdb) dir(lang)
['__class__', '__delattr__', '__dict__', '__doc__', '__format__',
'__getattribute__', '__hash__', '__init__', '__mapper__',
'__mapper_cls__', '__module__', '__new__', '__reduce__',
'__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__',
'__subclasshook__', '__table__', '__tablename__', '__weakref__',
'_cls_id', '_decl_class_registry', '_has', '_language', '_language_id',
'_sa_class_manager', '_sa_instance_state', 'id_', 'language',
'language_id', 'metadata', 't']
(Pdb) session.add(lang)
(Pdb) session.commit()
(Pdb) lang2 = Language(language='foo')
(Pdb) session.add(lang2)
(Pdb) session.commit()
*** IntegrityError: (IntegrityError) column language is not unique
u'INSERT INTO ht__language (language) VALUES (?)' ('undef',)
(Pdb) session.rollback()
(Pdb) session.query(Language).all()
[Language(language='test')]
(Pdb) lang2 = Language()
(Pdb) lang2.language = 'foo'
(Pdb) session.add(lang2)
(Pdb) session.commit()
*** IntegrityError: (IntegrityError) column language is not unique
u'INSERT INTO ht__language (language) VALUES (?)' ('undef',)
(Pdb) session.rollback()
(Pdb) lang2 = Language()
(Pdb) lang2._language = 'foo'
(Pdb) session.add(lang2)
(Pdb) session.commit()
(Pdb) session.query(Language).all()
[Language(), Language(language='test')]
If I change the Language class to this:
class Language(Base):
language_id = Column(Integer, Sequence('language_id_seq',
optional=True), primary_key=True)
language = Column('language', String(5), unique=True, default='undef')
The same happens. Curiously:
(Pdb) session.query(Language).all()
[Language(), Language(language='test')]
(Pdb) map(lambda a: a._language, session.query(Language).all())
[u'foo', u'undef']
Is there a problem using this mapper configuration with declarative bases?
Cheers,
Richard.
On 06/07/2013 11:46 AM, Michael Bayer wrote:
On Jun 7, 2013, at 9:31 AM, Richard Gerd Kuesters
rich...@humantech.com.br mailto:rich...@humantech.com.br wrote:
Hi all!
I'm refactoring a database schema but I need it to mantain reverse
compatibility with older versions of our software - using views.
But, to avoid confusion to other developers, new tables have two
underscores as a prefix, like:
class Base(object):
@declared_attr
def __tablename__(cls):
return __%s % to_python_case(cls.__name__)
Now I want to add column prefixes too. I know I can append to this
Base object:
__mapper_args__ = dict(
column_prefix='_'
)
But, when I inherit this new base on classes that I need to use
__mapper_args__, column names probably won't have prefixes. Any
ideas, perhaps an event listener to prepend the underscore into *all*
column names?
one idea is you can intercept how mapper() is called by declarative by
overriding __mapper_cls__, like:
@classmethod
def __mapper_cls__(cls, *args, **kw):
kw['column_prefix'] = '_'
return mapper(*args, **kw)
Cheers,
Richard.
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
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?hl=en.
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] [ANN] Modified WindowedRangeQuery recipe
Hello. Resending because the original e-mail does not seem to make it to the mailing list. Apologise for any duplicates. Here we go... Hello. I modified the recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery to better suit my needs. Perhaps someone else will find this useful: CODE BEGIN from sqlalchemy.orm import subqueryload from sqlalchemy.sql.expression import distinct def windowed_query(filter_query, data_query, column, window_size): Perform (a correct) yield_per() operation. See WindowedQuery.yield_per() for more. EXAMPLE: gen = windowed_query( filter_query=session.query(Foo).filter(Foo.name.like(u'%foo%')), data_query=session.query(Foo).options(Foo.bars), column=Foo.id, window_size=50, ) for each_foo in gen: print each_foo.name for each_bar in each_foo.bars: print each_bar return WindowedQuery(filter_query, data_query, column).yield_per(window_size) class WindowedQuery(object): Perform (a correct) yield_per() operation. def __init__(self, filter_query, data_query, column): # A query with NO options(...) and NO order_by(...). MUST contain all # necessary join() and filter() to limit the result set as desired. self._filter_query = filter_query # A simple query with options() to fetch the actual data. self._data_query = data_query # id column of the (main) result ORM class. self._column = column def yield_per(self, window_size): Process results in chunks. Steps: * Obtain ids of ALL result rows via self._filter_query. * Divide ids to chunks of equal size and perform ONE query for EACH chunk to fetch the data via self._data_query. A chunk is determined by the test q.filter(self._column.in_(chunk)). This is the only way that works in presence of the read-committed isolation level. q = self._data_query for each_window in self._windows(window_size): for each_result in q.filter(each_window): yield each_result def _windows(self, window_size): chunk = [] chunk_size = 0 for each in self._q_column(): if chunk_size window_size: chunk.append(each) chunk_size += 1 if chunk_size == window_size: yield self._window_for_chunk(chunk) chunk = [] chunk_size = 0 if chunk_size 0: yield self._window_for_chunk(chunk) def _q_column(self): # distinct() ensures that each id is returned at most once despite # a possible multiplying effect of a join(). return self._filter_query.with_entities(distinct(self._column)) def _window_for_chunk(self, chunk): return self._column.in_(chunk) ## CODE END ## MOTIVATION: I have learned recently that Query.yield_per() does not work nicely in combination with subqueryload(). The above recipe fixes that. Unfortunately its usage is not as elegant and simple as q.yield_per(...). If you have any idea how to accomplish the same with ONE query only (in SA 0.7.9): def windowed_query(query, column, window_size): query --magic- filter_query query --magic- data_query ... I would very much like to hear about it. PERFORMANCE: My first tests suggest that it might be one order of magnitude better than the Query.yield_per() we use now. Note also that yield_per() with subqueryload() was still about twice as fast as the same query without yield_per(). But this will be highly dependent on the query I guess. WARNING: We do not use this in the production yet. Use at your own risk. Happy SA hacking, Ladislav Lenart -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Simple [?] question about column prefixes
OK, the prefix is on the mapped attribute name, not the column.So
Language(_language = 'foo').
Guess you're looking for the opposite, huh. This gets more weird but this
should work, the main difficulty is limiting the columns being altered to just
those within a certain class hierarchy, if you wanted to do it based on
table.name.startswith('__') that would be much simpler:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
Base = declarative_base()
@event.listens_for(Column, before_parent_attach)
def attach(col, table, **kw):
if table.name in prefix_tables:
# the trick here is that col.key remains
# without the underscore
col.name = _ + col.name
prefix_tables = set()
class PrefixStuff(object):
# need to use a metaclass here because we have no event
# that will give you the __tablename__ + Language class
# before the Table is actually created
class __metaclass__(type(Base)):
def __init__(self, classname, bases, dict_):
prefix_tables.add(dict_.get('__tablename__'))
if classname == 'PrefixStuff':
return type.__init__(self, classname, bases, dict_)
else:
return type(Base).__init__(self, classname, bases, dict_)
class Language(PrefixStuff, Base):
__tablename__ = 'language'
language_id = Column(Integer, primary_key=True)
language = Column(String(5), unique=True, default='undef')
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
sess.add(Language(language='asdf'))
sess.commit()
On Jun 7, 2013, at 1:40 PM, Richard Gerd Kuesters rich...@humantech.com.br
wrote:
Thank you Mike!
That brings me to another question:
Let's say I have created a simple table (well, I have):
class Language(Base):
language_id = Column(Integer, Sequence('language_id_seq', optional=True),
primary_key=True)
language = Column(String(5), unique=True, default='undef')
The problem is (using pdb):
(Pdb) lang = Language(language='test')
(Pdb) lang
Language(language='test')
(Pdb) dir(lang)
['__class__', '__delattr__', '__dict__', '__doc__', '__format__',
'__getattribute__', '__hash__', '__init__', '__mapper__', '__mapper_cls__',
'__module__', '__new__', '__reduce__', '__reduce_ex__', '__repr__',
'__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__table__',
'__tablename__', '__weakref__', '_cls_id', '_decl_class_registry', '_has',
'_language', '_language_id', '_sa_class_manager', '_sa_instance_state',
'id_', 'language', 'language_id', 'metadata', 't']
(Pdb) session.add(lang)
(Pdb) session.commit()
(Pdb) lang2 = Language(language='foo')
(Pdb) session.add(lang2)
(Pdb) session.commit()
*** IntegrityError: (IntegrityError) column language is not unique u'INSERT
INTO ht__language (language) VALUES (?)' ('undef',)
(Pdb) session.rollback()
(Pdb) session.query(Language).all()
[Language(language='test')]
(Pdb) lang2 = Language()
(Pdb) lang2.language = 'foo'
(Pdb) session.add(lang2)
(Pdb) session.commit()
*** IntegrityError: (IntegrityError) column language is not unique u'INSERT
INTO ht__language (language) VALUES (?)' ('undef',)
(Pdb) session.rollback()
(Pdb) lang2 = Language()
(Pdb) lang2._language = 'foo'
(Pdb) session.add(lang2)
(Pdb) session.commit()
(Pdb) session.query(Language).all()
[Language(), Language(language='test')]
If I change the Language class to this:
class Language(Base):
language_id = Column(Integer, Sequence('language_id_seq', optional=True),
primary_key=True)
language = Column('language', String(5), unique=True, default='undef')
The same happens. Curiously:
(Pdb) session.query(Language).all()
[Language(), Language(language='test')]
(Pdb) map(lambda a: a._language, session.query(Language).all())
[u'foo', u'undef']
Is there a problem using this mapper configuration with declarative bases?
Cheers,
Richard.
On 06/07/2013 11:46 AM, Michael Bayer wrote:
On Jun 7, 2013, at 9:31 AM, Richard Gerd Kuesters rich...@humantech.com.br
wrote:
Hi all!
I'm refactoring a database schema but I need it to mantain reverse
compatibility with older versions of our software - using views.
But, to avoid confusion to other developers, new tables have two
underscores as a prefix, like:
class Base(object):
@declared_attr
def __tablename__(cls):
return __%s % to_python_case(cls.__name__)
Now I want to add column prefixes too. I know I can append to this Base
object:
__mapper_args__ = dict(
column_prefix='_'
)
But, when I inherit this new base on classes that I need to use
__mapper_args__, column names probably won't have prefixes. Any ideas,
perhaps an event listener to prepend the underscore into *all* column names?
one idea is you can intercept how mapper() is called by declarative by
Re: [sqlalchemy] Simple [?] question about column prefixes
Woah, I may have wondered that when I thought on attaching an event, but
yes I was thinking the opposite when using it like the underscores in
the table name level.
I can't see how this was easier - lol - but it makes sense to me :)
Thanks Mike! This should get into the SA examples or recipes!
Best regards,
Richard.
On 06/07/2013 03:13 PM, Michael Bayer wrote:
OK, the prefix is on the mapped attribute name, not the column. So
Language(_language = 'foo').
Guess you're looking for the opposite, huh. This gets more weird
but this should work, the main difficulty is limiting the columns
being altered to just those within a certain class hierarchy, if you
wanted to do it based on table.name.startswith('__') that would be
much simpler:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
Base = declarative_base()
@event.listens_for(Column, before_parent_attach)
def attach(col, table, **kw):
if table.name in prefix_tables:
# the trick here is that col.key remains
# without the underscore
col.name = _ + col.name
prefix_tables = set()
class PrefixStuff(object):
# need to use a metaclass here because we have no event
# that will give you the __tablename__ + Language class
# before the Table is actually created
class __metaclass__(type(Base)):
def __init__(self, classname, bases, dict_):
prefix_tables.add(dict_.get('__tablename__'))
if classname == 'PrefixStuff':
return type.__init__(self, classname, bases, dict_)
else:
return type(Base).__init__(self, classname, bases, dict_)
class Language(PrefixStuff, Base):
__tablename__ = 'language'
language_id = Column(Integer, primary_key=True)
language = Column(String(5), unique=True, default='undef')
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
sess.add(Language(language='asdf'))
sess.commit()
On Jun 7, 2013, at 1:40 PM, Richard Gerd Kuesters
rich...@humantech.com.br mailto:rich...@humantech.com.br wrote:
Thank you Mike!
That brings me to another question:
Let's say I have created a simple table (well, I have):
class Language(Base):
language_id = Column(Integer, Sequence('language_id_seq',
optional=True), primary_key=True)
language = Column(String(5), unique=True, default='undef')
The problem is (using pdb):
(Pdb) lang = Language(language='test')
(Pdb) lang
Language(language='test')
(Pdb) dir(lang)
['__class__', '__delattr__', '__dict__', '__doc__', '__format__',
'__getattribute__', '__hash__', '__init__', '__mapper__',
'__mapper_cls__', '__module__', '__new__', '__reduce__',
'__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__',
'__subclasshook__', '__table__', '__tablename__', '__weakref__',
'_cls_id', '_decl_class_registry', '_has', '_language',
'_language_id', '_sa_class_manager', '_sa_instance_state', 'id_',
'language', 'language_id', 'metadata', 't']
(Pdb) session.add(lang)
(Pdb) session.commit()
(Pdb) lang2 = Language(language='foo')
(Pdb) session.add(lang2)
(Pdb) session.commit()
*** IntegrityError: (IntegrityError) column language is not unique
u'INSERT INTO ht__language (language) VALUES (?)' ('undef',)
(Pdb) session.rollback()
(Pdb) session.query(Language).all()
[Language(language='test')]
(Pdb) lang2 = Language()
(Pdb) lang2.language = 'foo'
(Pdb) session.add(lang2)
(Pdb) session.commit()
*** IntegrityError: (IntegrityError) column language is not unique
u'INSERT INTO ht__language (language) VALUES (?)' ('undef',)
(Pdb) session.rollback()
(Pdb) lang2 = Language()
(Pdb) lang2._language = 'foo'
(Pdb) session.add(lang2)
(Pdb) session.commit()
(Pdb) session.query(Language).all()
[Language(), Language(language='test')]
If I change the Language class to this:
class Language(Base):
language_id = Column(Integer, Sequence('language_id_seq',
optional=True), primary_key=True)
language = Column('language', String(5), unique=True,
default='undef')
The same happens. Curiously:
(Pdb) session.query(Language).all()
[Language(), Language(language='test')]
(Pdb) map(lambda a: a._language, session.query(Language).all())
[u'foo', u'undef']
Is there a problem using this mapper configuration with declarative
bases?
Cheers,
Richard.
On 06/07/2013 11:46 AM, Michael Bayer wrote:
On Jun 7, 2013, at 9:31 AM, Richard Gerd Kuesters
rich...@humantech.com.br mailto:rich...@humantech.com.br wrote:
Hi all!
I'm refactoring a database schema but I need it to mantain reverse
compatibility with older versions of our software - using views.
But, to avoid confusion to other developers, new tables have two
underscores as a prefix, like:
class Base(object):
@declared_attr
def __tablename__(cls):
return __%s % to_python_case(cls.__name__)
Now I want to add column prefixes too. I know I can append to this
Base object:
Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
Am 07.06.2013, 00:05 Uhr, schrieb Michael Bayer mike...@zzzcomputing.com: can you show me the alternate design you have in mind? I'm not sure what normalize to favorites with strict 1:1 looks like. this is all just my own curiosity. the issue at hand is something different. Well, I guess I asked for it. Pseudo-tables things ( thing_id SERIAL PRIMARY KEY ) groups ( group_id SERIAL PRIMARY KEY ) groups_things( thing_id INTEGER FOREIGN KEY REFERENCES (things.thing_id), group_id INTEGER FOREIGN KEY REFERENCES (groups.group_id) ) favourites (I'm a limey) are just another relation. favourites( thing_id INTEGER PRIMARY KEY FOREIGN KEY REFERENCES (things.thing_id), group_id INTEGER FOREIGN KEY REFERENCES (groups.group_id) ) Depending on how you look at it, favourites are just another relation and could have additional attributes like colour, or simply behave like a sub-class of groups. Am I missing something big in the original question as to why this isn't a reasonable solution? Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
Am 07.06.2013, 01:13 Uhr, schrieb Andy aml...@gmail.com: I may be misunderstanding the question, but the reason that having a favorite is optional is because I'm using mysql and mysql doesn't supported deferred constraints. Oh, I feel your pain! But you are using an engine that at leasts pretends to support foreign key constraints. If not, book yourself in at the next asylum! So if favorite were NOT NULL, then there would be no way to create the thing. Which is why it should be a relation all of its own. I've a sneaking suspicion that I'm being incredibly dense and missing something obvious. Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] My mapped tables are missing insert, update methods
After reading the documentation, I am under the impression that my mapped tables should have the methods insert() and update(). For example, see here: http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html?highlight=update#sqlalchemy.schema.Table.update But my mapped tables don't have those methods; why might that be? User class 'blah.models.User' User.insert Traceback (most recent call last): File stdin, line 1, in module AttributeError: type object 'User' has no attribute 'insert' User.update Traceback (most recent call last): File stdin, line 1, in module AttributeError: type object 'User' has no attribute 'update' Thanks, Mike -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] My mapped tables are missing insert, update methods
Your User class is mapped to a Table. It's not the Table itself. To get the update method, you need to access User.__table__.update On Fri, Jun 7, 2013 at 4:12 PM, Michael Nachtigal michael.nachti...@catalinamarketing.com wrote: After reading the documentation, I am under the impression that my mapped tables should have the methods insert() and update(). For example, see here: http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html?highlight=update#sqlalchemy.schema.Table.update But my mapped tables don't have those methods; why might that be? User class 'blah.models.User' User.insert Traceback (most recent call last): File stdin, line 1, in module AttributeError: type object 'User' has no attribute 'insert' User.update Traceback (most recent call last): File stdin, line 1, in module AttributeError: type object 'User' has no attribute 'update' Thanks, Mike -- 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?hl=en. 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Connect to SQL Server (with pyodbc) getting error: sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default dri
Thank you!
That got me going.
For what it's worth, I was using python 3.3 and trying to generate a custom
connection string wtih the following command:
import urllib
string = urllib.quote_plus('DRIVER={SQL
Server};Server=Server;Database=DB;Trusted_Connection=Yes')
conn = pyodbc.PyODBCConnector()
url = make_url('mssql+pyodbc://?odbc_connect='+string)
connect_args = conn.create_connect_args(url)
print(connect_args)
Which generated:
AttributeError: 'module' object has no attribute 'quote_plus'
I swtiched to python 2.7 and it worked fine. If the above issue is a
general problem in python 3, It might be worth noting the the documentation
example will not work in that version.
I'm getting the feeling that python3 is not well supported yet.
Thank you again for your help,
~Victor
On Thursday, June 6, 2013 12:38:03 PM UTC-5, Michael Bayer wrote:
I will show you a short program that you can use to experiment with the
Pyodbc connector - the purpose of this program is to illustrate what
SQLAlchemy will send to pyodbc.connect():
from sqlalchemy.connectors import pyodbc
from sqlalchemy.engine.url import make_url
conn = pyodbc.PyODBCConnector()
url = make_url('mssql+pyodbc://DSN=py_test; Trusted_Connection=Yes')
connect_args = conn.create_connect_args(url)
print(connect_args)
This returns:
[['dsn=DSN=py_test; Trusted_Connection=Yes;Trusted_Connection=Yes'], {}]
where you can see there's an extra DSN= in there.
There's a large number of connection examples for Pyodbc here:
http://docs.sqlalchemy.org/en/rel_0_8/dialects/mssql.html#additional-connection-examples
You can see, that for standard TrustedConnection, you only need the dsn
name:
create_engine('mssql+pyodbc://mydsn')
which our test script shows the arguments as:
[['dsn=mydsn;Trusted_Connection=Yes'], {}]
looks good to me, so good luck !
On Jun 5, 2013, at 9:47 PM, Victor Reichert vfr...@gmail.comjavascript:
wrote:
Hello World!
This is my first foray into python and SQL Alchemy, and I'm spinning my
wheels. I'm running the code below and am able to connect to my DB and
query data without error.
import pyodbc
cnxn = pyodbc.connect('DSN=py_test; Trusted_Connection=Yes')
However, when I try
import sqlalchemy
engine = sqlalchemy.create_engine('mssql+pyodbc://DSN=py_test;
Trusted_Connection=Yes')
result = engine.execute(SELECT * FROM dbo.test_table)
I receive the following error, I am running python 3.3 on 32 bit Windows 7
Enterprise
Traceback (most recent call last):
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 757, in
_do_get
return self._pool.get(wait, self._timeout)
File C:\Python33\lib\site-packages\sqlalchemy\util\queue.py, line 166,
in get
raise Empty
sqlalchemy.util.queue.Empty
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File C:\Python33\lib\site-packages\sqlalchemy\engine\strategies.py,
line 80, in connect
return dialect.connect(*cargs, **cparams)
File C:\Python33\lib\site-packages\sqlalchemy\engine\default.py, line
285, in connect
return self.dbapi.connect(*cargs, **cparams)
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data
source name not found and no default driver specified (0)
(SQLDriverConnect)')
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File
C:\Users\V114804\workspace\Remetrica_Simulated_ILC_WW_AP_20130520_ELT\Src\Test_DB_Connection.py,
line 11, in module
result = engine.execute(SELECT * FROM dbo.test_table)
File C:\Python33\lib\site-packages\sqlalchemy\engine\base.py, line
1613, in execute
connection = self.contextual_connect(close_with_result=True)
File C:\Python33\lib\site-packages\sqlalchemy\engine\base.py, line
1661, in contextual_connect
self.pool.connect(),
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 272, in
connect
return _ConnectionFairy(self).checkout()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 425, in
__init__
rec = self._connection_record = pool._do_get()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 777, in
_do_get
con = self._create_connection()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 225, in
_create_connection
return _ConnectionRecord(self)
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 318, in
__init__
self.connection = self.__connect()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 368, in
__connect
connection = self.__pool._creator()
File C:\Python33\lib\site-packages\sqlalchemy\engine\strategies.py,
line 87, in connect
) from e
sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [Microsoft][ODBC
Driver Manager] Data source name not found and no default driver specified
(0) (SQLDriverConnect)') None None
I have also
Re: [sqlalchemy] Connect to SQL Server (with pyodbc) getting error: sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default dri
On Jun 7, 2013, at 6:11 PM, Victor Reichert vfr...@gmail.com wrote:
Thank you!
That got me going.
For what it's worth, I was using python 3.3 and trying to generate a custom
connection string wtih the following command:
import urllib
string = urllib.quote_plus('DRIVER={SQL
Server};Server=Server;Database=DB;Trusted_Connection=Yes')
conn = pyodbc.PyODBCConnector()
url = make_url('mssql+pyodbc://?odbc_connect='+string)
connect_args = conn.create_connect_args(url)
print(connect_args)
Which generated:
AttributeError: 'module' object has no attribute 'quote_plus'
I swtiched to python 2.7 and it worked fine. If the above issue is a general
problem in python 3, It might be worth noting the the documentation example
will not work in that version.
I'm getting the feeling that python3 is not well supported yet.
not for pyodbc in 0.8, but in 0.9 (git master) it's working pretty well (no
2to3 step either).
Thank you again for your help,
~Victor
On Thursday, June 6, 2013 12:38:03 PM UTC-5, Michael Bayer wrote:
I will show you a short program that you can use to experiment with the
Pyodbc connector - the purpose of this program is to illustrate what
SQLAlchemy will send to pyodbc.connect():
from sqlalchemy.connectors import pyodbc
from sqlalchemy.engine.url import make_url
conn = pyodbc.PyODBCConnector()
url = make_url('mssql+pyodbc://DSN=py_test; Trusted_Connection=Yes')
connect_args = conn.create_connect_args(url)
print(connect_args)
This returns:
[['dsn=DSN=py_test; Trusted_Connection=Yes;Trusted_Connection=Yes'], {}]
where you can see there's an extra DSN= in there.
There's a large number of connection examples for Pyodbc here:
http://docs.sqlalchemy.org/en/rel_0_8/dialects/mssql.html#additional-connection-examples
You can see, that for standard TrustedConnection, you only need the dsn
name:
create_engine('mssql+pyodbc://mydsn')
which our test script shows the arguments as:
[['dsn=mydsn;Trusted_Connection=Yes'], {}]
looks good to me, so good luck !
On Jun 5, 2013, at 9:47 PM, Victor Reichert vfr...@gmail.com wrote:
Hello World!
This is my first foray into python and SQL Alchemy, and I'm spinning my
wheels. I'm running the code below and am able to connect to my DB and
query data without error.
import pyodbc
cnxn = pyodbc.connect('DSN=py_test; Trusted_Connection=Yes')
However, when I try
import sqlalchemy
engine = sqlalchemy.create_engine('mssql+pyodbc://DSN=py_test;
Trusted_Connection=Yes')
result = engine.execute(SELECT * FROM dbo.test_table)
I receive the following error, I am running python 3.3 on 32 bit Windows 7
Enterprise
Traceback (most recent call last):
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 757, in
_do_get
return self._pool.get(wait, self._timeout)
File C:\Python33\lib\site-packages\sqlalchemy\util\queue.py, line 166,
in get
raise Empty
sqlalchemy.util.queue.Empty
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File C:\Python33\lib\site-packages\sqlalchemy\engine\strategies.py, line
80, in connect
return dialect.connect(*cargs, **cparams)
File C:\Python33\lib\site-packages\sqlalchemy\engine\default.py, line
285, in connect
return self.dbapi.connect(*cargs, **cparams)
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data
source name not found and no default driver specified (0)
(SQLDriverConnect)')
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File
C:\Users\V114804\workspace\Remetrica_Simulated_ILC_WW_AP_20130520_ELT\Src\Test_DB_Connection.py,
line 11, in module
result = engine.execute(SELECT * FROM dbo.test_table)
File C:\Python33\lib\site-packages\sqlalchemy\engine\base.py, line 1613,
in execute
connection = self.contextual_connect(close_with_result=True)
File C:\Python33\lib\site-packages\sqlalchemy\engine\base.py, line 1661,
in contextual_connect
self.pool.connect(),
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 272, in
connect
return _ConnectionFairy(self).checkout()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 425, in
__init__
rec = self._connection_record = pool._do_get()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 777, in
_do_get
con = self._create_connection()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 225, in
_create_connection
return _ConnectionRecord(self)
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 318, in
__init__
self.connection = self.__connect()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 368, in
__connect
connection = self.__pool._creator()
File C:\Python33\lib\site-packages\sqlalchemy\engine\strategies.py, line
87, in connect
) from e
[sqlalchemy] AssociationProxy's behavior with == None seems unintuitive
Right now a filter clause AssociationProxy == None
Consider the following code:
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
color = Column(String)
def __init__(self, color):
self.color = color
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('table_a.id'))
a_re = relationship('A', backref='b_re')
a_color = association_proxy('a_re', 'color')
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(engine)
session = Session()
Base.metadata.create_all(engine)
b1 = B()
b2 = B()
b3 = B()
b1.a_color = 'blue'
session.add_all([b1, b2, b3])
q = session.query(B).filter(B.a_color == None).all()
p = session.query(B).filter(B.a_color != None).all()
assert q == []
assert set(p) == set([b1, b2, b3])
I find it surprising that, when only b1 really has a color string through
B.a_color, filtering for B.a_color == None doesn't return b2 and b3, and
filtering for B.a_color != None returns b2 and b3 at all. The latter is
especially unintuitive.
Now I know what's going on:
print B.a_color == None
EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL)
print B.a_color != None
NOT (EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL))
The == clause requires that the relationship a_re has a valid target in the
first place before checking for a_re.color IS NULL, and the != is just the
negation of that. I understand that this is the desired action when the
right side is some non-null value like blue, but in this case it should
be implemented along the lines of
print B.a_color == None
table_b.a_id IS NULL
OR
EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL)
print B.a_color != None
table_b.a_id IS NOT NULL
AND
NOT (EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL))
On a similar note, is there a way to get a_re.has() or equivalent through
only a_color? This can be important when a_re is supposed to be a private
variable, and only the string a_color is exposed. I originally thought that
!= None would do the trick but that doesn't work, as I've shown here.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
