Re: [sqlalchemy] alembic del sys.modules[module_id] leading to orm mapper error

[Will Angenent]

The code was just to reproduce the exact SQLA error by doing what alembic and 
SQLA are doing and additionally determining when the garbage collector runs. 
The theory is that garbage gets collected somewhere in between 
list(_mapper_registry) in configure_mappers and what happens in 
_process_dependent_arguments. The classes aren’t GC’ed since they are in use by 
the list statement, but by the time the callable is called, the module data has 
gone.

I don’t consider this a bug in SQLA nor alembic individually. However, using 
the combination of the two has shown to be problematic. The randomness of the 
problem made it especially difficult to diagnose. This is probably such an edge 
case that it’s not worth spending any more time on. I’m happy I understand 
what’s going on now at least.

Thanks,
Will

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] alembic del sys.modules[module_id] leading to orm mapper error

[Will Angenent]

my/orm/mapper.py",
 line 1893, in _with_polymorphic_mappers
configure_mappers()
  File 
"/Users/wangenent/code/sqlalchemy/weak-refs/venv/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py",
 line 2768, in configure_mappers
mapper._post_configure_properties()
  File 
"/Users/wangenent/code/sqlalchemy/weak-refs/venv/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py",
 line 1710, in _post_configure_properties
prop.init()
  File 
"/Users/wangenent/code/sqlalchemy/weak-refs/venv/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py",
 line 183, in init
self.do_init()
  File 
"/Users/wangenent/code/sqlalchemy/weak-refs/venv/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py",
 line 1629, in do_init
self._setup_join_conditions()
  File 
"/Users/wangenent/code/sqlalchemy/weak-refs/venv/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py",
 line 1704, in _setup_join_conditions
can_be_synced_fn=self._columns_are_mapped
  File 
"/Users/wangenent/code/sqlalchemy/weak-refs/venv/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py",
 line 1972, in __init__
self._determine_joins()
  File 
"/Users/wangenent/code/sqlalchemy/weak-refs/venv/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py",
 line 2076, in _determine_joins
"specify a 'primaryjoin' expression." % self.prop)
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between 
parent/child tables on relationship Table2.table1_stuff - there are no foreign 
keys linking these tables.  Ensure that referencing columns are associated with 
a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Thanks,
Will


> On 23 Feb 2016, at 02:43, Mike Bayer  wrote:
> 
> To be honest I might just say to solve it that way, with the gc.collect(), at 
> least if you still had this problem.   That would explain why the ORM still 
> sees those mappings, they just weren't garbage collected yet.   
> 
> On Feb 22, 2016, at 3:23 PM, Will Angenent  <mailto:w.angen...@gmail.com>> wrote:
> 
>> Hi,
>> 
>> You’re definitely on to something when you say it’s related to garbage 
>> collection and weak references. If I add this:
>> 
>> import gc
>> gc.disable()
>> 
>> the problem vanishes. I’ve been spending the last couple of hours trying to 
>> reduce the code to the smallest possible case. I’ve reached the point though 
>> that I can’t remove any more code without making the problem vanish. There’s 
>> still plenty of code cruft left that I can’t copy/paste, so I can’t easily 
>> break it down into a tiny example. To answer your question though, I’ve 
>> whittled down the contents of the migration file into this:
>> 
>> ---
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy import Column, String, ForeignKey, Table, Integer
>> from sqlalchemy.orm import relationship
>> 
>> revision = '3a81b8b842d3'
>> down_revision = None
>> branch_labels = None
>> depends_on = None
>> 
>> Base = declarative_base()
>> 
>> x_tag_to_resource = Table(
>> 'tag_to_resource', Base.metadata,
>> Column('tag_id', ForeignKey('tags.id', ondelete='CASCADE'),
>>primary_key=True, index=True),
>> Column('resource_id', ForeignKey('resources.id', ondelete='CASCADE'),
>>primary_key=True, index=True)
>> )
>> 
>> 
>> class XTag(Base):
>> __tablename__ = 'tags'
>> id = Column(Integer, primary_key=True)
>> text = Column(String, nullable=False)
>> 
>> 
>> def non_lambda_tag_to_resource():
>> # This needs to be here, otherwise the sys import itself
>> # can get garbage collected in the erroneous case.
>> import sys
>> sys.stderr.write('*** x_tag_to_resource=%s\n' % x_tag_to_resource)
>> sys.stderr.write('*** name=%s\n' % __name__)
>> return x_tag_to_resource
>> 
>> 
>> class XResource(Base):
>> __tablename__ = 'resources'
>> id = Column(Integer, primary_key=True)
>> tags = relationship("XTag", secondary=non_lambda_tag_to_resource,
>> backref='resources')
>> 
>> —
>> I don’t think there’s anything wrong with the above though. There also isn’t 
>> mixing between models in the application and models in the migration; so 
>> that doesn’t explain references sticking in some cases.
>> 
>> By the way, this isn’t an urgent thing. I made the problem go

Re: [sqlalchemy] alembic del sys.modules[module_id] leading to orm mapper error

[Will Angenent]

Hi,

You’re definitely on to something when you say it’s related to garbage 
collection and weak references. If I add this:

import gc
gc.disable()

the problem vanishes. I’ve been spending the last couple of hours trying to 
reduce the code to the smallest possible case. I’ve reached the point though 
that I can’t remove any more code without making the problem vanish. There’s 
still plenty of code cruft left that I can’t copy/paste, so I can’t easily 
break it down into a tiny example. To answer your question though, I’ve 
whittled down the contents of the migration file into this:

---
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, ForeignKey, Table, Integer
from sqlalchemy.orm import relationship

revision = '3a81b8b842d3'
down_revision = None
branch_labels = None
depends_on = None

Base = declarative_base()

x_tag_to_resource = Table(
'tag_to_resource', Base.metadata,
Column('tag_id', ForeignKey('tags.id', ondelete='CASCADE'),
   primary_key=True, index=True),
Column('resource_id', ForeignKey('resources.id', ondelete='CASCADE'),
   primary_key=True, index=True)
)


class XTag(Base):
__tablename__ = 'tags'
id = Column(Integer, primary_key=True)
text = Column(String, nullable=False)


def non_lambda_tag_to_resource():
# This needs to be here, otherwise the sys import itself
# can get garbage collected in the erroneous case.
import sys
sys.stderr.write('*** x_tag_to_resource=%s\n' % x_tag_to_resource)
sys.stderr.write('*** name=%s\n' % __name__)
return x_tag_to_resource


class XResource(Base):
__tablename__ = 'resources'
id = Column(Integer, primary_key=True)
tags = relationship("XTag", secondary=non_lambda_tag_to_resource,
backref='resources')

—
I don’t think there’s anything wrong with the above though. There also isn’t 
mixing between models in the application and models in the migration; so that 
doesn’t explain references sticking in some cases.

By the way, this isn’t an urgent thing. I made the problem go away by deleting 
the bad migration; we didn’t need it any more. My interest is purely academic. 
I’m just worried this might happen again. Mostly though, I don’t like _not_ 
understanding what’s going on.

Thanks,
Will


> On 22 Feb 2016, at 01:59, Mike Bayer  wrote:
> 
> Well what makes no sense is why these mappers and classes would be present at 
> all.  These classes are strictly defined within an alembic migration, and 
> make **no** callouts to any other mapped structures in the application, 
> correct? If so, when the module is garbage collected, all of the things 
> inside of it would be as well. The mapper registry is weak referencing so the 
> _configure_mappers() step shouldn't see it.
> 
> If, OTOH, this mapping has something like a *backref* to some model in the 
> application, that would totally leave a dangling reference.
> 
> Can I have an example of an exact mapping I can stick into an Alembic 
> migration to see this happening otherwise?
> 
> 
> 
> On 02/21/2016 02:19 PM, Will Angenent wrote:
>> Hi Mike,
>> 
>> Thanks for your quick response yet again! Here’s the stack trace.
>> 
>> tests/integration/test_database.py:14: in test_database_is_up_to_date
>> create_test_db(session)
>> __init__.py:111: in create_test_db
>> pd_utils.do_import(dtype='locations', ifile=yaml_file)
>> ../utils/provider_data/__init__.py:54: in do_import
>> inserted, updated = getattr(self, 'import_%s' % item)(ifile)
>> ../utils/provider_data/__init__.py:22: in import_locations
>> return import_locations(self.session, ifile)
>> ../utils/provider_data/locations.py:190: in import_locations
>> Location).filter_by(
>> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py:1260:
>> in query
>> return self._query_cls(entities, self, **kwargs)
>> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:110:
>> in __init__
>> self._set_entities(entities)
>> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:120:
>> in _set_entities
>> self._set_entity_selectables(self._entities)
>> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:150:
>> in _set_entity_selectables
>> ent.setup_entity(*d[entity])
>> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:3250:
>> in setup_entity
>> self._with_polymorphic = ext_info.with_polymorphic_mappers
>> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py:747:
>> in _

Re: [sqlalchemy] alembic del sys.modules[module_id] leading to orm mapper error

[Will Angenent]

I forgot to mention, we’re not using clear_mappers().

Thanks,
Will

> On 21 Feb 2016, at 19:19, Will Angenent  wrote:
> 
> Hi Mike,
> 
> Thanks for your quick response yet again! Here’s the stack trace.
> 
> tests/integration/test_database.py:14: in test_database_is_up_to_date
> create_test_db(session)
> __init__.py:111: in create_test_db
> pd_utils.do_import(dtype='locations', ifile=yaml_file)
> ../utils/provider_data/__init__.py:54: in do_import
> inserted, updated = getattr(self, 'import_%s' % item)(ifile)
> ../utils/provider_data/__init__.py:22: in import_locations
> return import_locations(self.session, ifile)
> ../utils/provider_data/locations.py:190: in import_locations
> Location).filter_by(
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py:1260:
>  in query
> return self._query_cls(entities, self, **kwargs)
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:110: 
> in __init__
> self._set_entities(entities)
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:120: 
> in _set_entities
> self._set_entity_selectables(self._entities)
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:150: 
> in _set_entity_selectables
> ent.setup_entity(*d[entity])
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:3250: 
> in setup_entity
> self._with_polymorphic = ext_info.with_polymorphic_mappers
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py:747:
>  in __get__
> obj.__dict__[self.__name__] = result = self.fget(obj)
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py:1893: 
> in _with_polymorphic_mappers
> configure_mappers()
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py:2756: 
> in configure_mappers
> mapper._post_configure_properties()
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py:1710: 
> in _post_configure_properties
> prop.init()
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py:183:
>  in init
> self.do_init()
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py:1613:
>  in do_init
> self._setup_join_conditions()
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py:1688:
>  in _setup_join_conditions
> can_be_synced_fn=self._columns_are_mapped
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py:1956:
>  in __init__
> self._determine_joins()
> ../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py:2060:
>  in _determine_joins
> "specify a 'primaryjoin' expression." % self.prop)
> E   NoForeignKeysError: Could not determine join condition between 
> parent/child tables on relationship Resource.tags - there are no foreign keys 
> linking these tables.  Ensure that referencing columns are associated with a 
> ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
> 
>> The sys.modules activity is not really the primary cause, it's that alembic 
>> makes use of a module object in a temporary way.  
> Absolutely agree. What I did to diagnose this was to replace 
> secondary=lambda: tag_to_resource with secondary=non_lambda_tag_to_resource, 
> using this:
> 
> def non_lambda_tag_to_resource():
> import sys
> sys.stderr.write('*** tag_to_resource=%s\n' % tag_to_resource)
>  #
> sys.stderr.write('*** name=%s\n' % __name__)  
>#
> return tag_to_resource
> 
> What I found is that in the bad case, both tag_to_resource and __name__ were 
> None.
> 
> Thanks,
> Will
> 
>> On 21 Feb 2016, at 19:12, Mike Bayer > <mailto:clas...@zzzcomputing.com>> wrote:
>> 
>> 
>> 
>> Hi there -
>> 
>> Can you post a stack trace, and also is your test suite making use of 
>> clear_mappers() ?
>> 
>> The sys.modules activity is not really the primary cause, it's that alembic 
>> makes use of a module object in a temporary way.  
>> 
>> On Feb 21, 2016, at 1:48 PM, Will Angenent > <mailto:w.angen...@gmail.com>> wrote:
>> 
>>> Hi,
>>> 
>>> We had this interesting issue recently, and I've been trying to figure out 
>>> if we deserve this, if this is simply unavoidable, or whether it can be 
>>> considered a bug. We're using python 2.7.6, sqlalchemy 1.0.12 and alembic 
>>> 0.

Re: [sqlalchemy] alembic del sys.modules[module_id] leading to orm mapper error

[Will Angenent]

Hi Mike,

Thanks for your quick response yet again! Here’s the stack trace.

tests/integration/test_database.py:14: in test_database_is_up_to_date
create_test_db(session)
__init__.py:111: in create_test_db
pd_utils.do_import(dtype='locations', ifile=yaml_file)
../utils/provider_data/__init__.py:54: in do_import
inserted, updated = getattr(self, 'import_%s' % item)(ifile)
../utils/provider_data/__init__.py:22: in import_locations
return import_locations(self.session, ifile)
../utils/provider_data/locations.py:190: in import_locations
Location).filter_by(
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py:1260: 
in query
return self._query_cls(entities, self, **kwargs)
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:110: in 
__init__
self._set_entities(entities)
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:120: in 
_set_entities
self._set_entity_selectables(self._entities)
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:150: in 
_set_entity_selectables
ent.setup_entity(*d[entity])
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py:3250: in 
setup_entity
self._with_polymorphic = ext_info.with_polymorphic_mappers
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py:747:
 in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py:1893: 
in _with_polymorphic_mappers
configure_mappers()
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py:2756: 
in configure_mappers
mapper._post_configure_properties()
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py:1710: 
in _post_configure_properties
prop.init()
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py:183:
 in init
self.do_init()
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py:1613:
 in do_init
self._setup_join_conditions()
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py:1688:
 in _setup_join_conditions
can_be_synced_fn=self._columns_are_mapped
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py:1956:
 in __init__
self._determine_joins()
../../devenv/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py:2060:
 in _determine_joins
"specify a 'primaryjoin' expression." % self.prop)
E   NoForeignKeysError: Could not determine join condition between parent/child 
tables on relationship Resource.tags - there are no foreign keys linking these 
tables.  Ensure that referencing columns are associated with a ForeignKey or 
ForeignKeyConstraint, or specify a 'primaryjoin' expression.

> The sys.modules activity is not really the primary cause, it's that alembic 
> makes use of a module object in a temporary way.  
Absolutely agree. What I did to diagnose this was to replace secondary=lambda: 
tag_to_resource with secondary=non_lambda_tag_to_resource, using this:

def non_lambda_tag_to_resource():
import sys
sys.stderr.write('*** tag_to_resource=%s\n' % tag_to_resource)  
   #
sys.stderr.write('*** name=%s\n' % __name__)
 #
return tag_to_resource

What I found is that in the bad case, both tag_to_resource and __name__ were 
None.

Thanks,
Will

> On 21 Feb 2016, at 19:12, Mike Bayer  wrote:
> 
> 
> 
> Hi there -
> 
> Can you post a stack trace, and also is your test suite making use of 
> clear_mappers() ?
> 
> The sys.modules activity is not really the primary cause, it's that alembic 
> makes use of a module object in a temporary way.  
> 
> On Feb 21, 2016, at 1:48 PM, Will Angenent  <mailto:w.angen...@gmail.com>> wrote:
> 
>> Hi,
>> 
>> We had this interesting issue recently, and I've been trying to figure out 
>> if we deserve this, if this is simply unavoidable, or whether it can be 
>> considered a bug. We're using python 2.7.6, sqlalchemy 1.0.12 and alembic 
>> 0.8.4.
>> 
>> Summary:
>> 
>> This statement in alembic.util.pyfiles.load_python_file():
>> del sys.modules[module_id]
>> randomly causes the reference count of the module object to become zero; 
>> triggering cleanup of the object. This effectively causes all variables in 
>> the migration file to become None, leading to an sqlalchemy mapper problem 
>> initializing a mapper configuration for a many-to-many relationship in a 
>> model defined in the migration file.
>> 
>> Are we being stupid to be using the ORM in alembic migrations? If not, is it 
>> worth for me to spend mo

[sqlalchemy] alembic del sys.modules[module_id] leading to orm mapper error

[Will Angenent]

Hi,

We had this interesting issue recently, and I've been trying to figure out 
if we deserve this, if this is simply unavoidable, or whether it can be 
considered a bug. We're using python 2.7.6, sqlalchemy 1.0.12 and alembic 
0.8.4.

Summary:

This statement in alembic.util.pyfiles.load_python_file():
del sys.modules[module_id]
randomly causes the reference count of the module object to become zero; 
triggering cleanup of the object. This effectively causes all variables in 
the migration file to become None, leading to an sqlalchemy mapper problem 
initializing a mapper configuration for a many-to-many relationship in a 
model defined in the migration file.

Are we being stupid to be using the ORM in alembic migrations? If not, is 
it worth for me to spend more time on this? Is there any way to get this to 
behave non-randomly? More details are below.

Thanks,
Will

Long version...

What happened is that someone in my team added an alembic migration. He 
used the sqlalchemy ORM and used a declarative_base with a couple of model 
files to get the job done. The migration was fine and everyone was happy. 
Then, about a week later, I added an import statement in a totally 
unrelated area of code, and suddenly running alembic upgrade starting 
failing with a ORM mapper error. I didn't spend much time on it, but 
refactored a couple of things and the problem vanished.

Then a couple of days later, our tests started failing with the same error. 
We had a closer look and found the failure to be random. The inclusion of 
the import statment seemed to trigger the random behavior. It wasn't just 
the import statement though, other changes, such as removing a property in 
an ORM class could make the problem appear or go away. What we were doing 
in this particualr failure mode, is running py.test which would, in order:

- import this random 3rd party module
- use the alembic API to upgrade to ensure a postgres database is up to date
- later on, in an unrelated test, do a query, triggering the initialization 
of the mappings and crashing

At first, I thought it might be a problem with sqlalchemy. Spurred on by 
this comment in mapper.py:

# initialize properties on all mappers
# note that _mapper_registry is unordered, which
# may randomly conceal/reveal issues related to
# the order of mapper compilation

I added a couple of sorted() statements throughout the code, but it made no 
difference. Finally, I found that the problem was a lambda function in a 
relationship with a secondary. Something like e.g.

tag_to_resource = Table(
'tag_to_resource', Base.metadata,
Column('tag_id', ForeignKey('tags.id', ondelete='CASCADE'),
   primary_key=True, index=True),
Column('resource_id', ForeignKey('resources.id', ondelete='CASCADE'),
   primary_key=True, index=True)
)

class Resource(Base):
__tablename__ = 'resources'
id = Column(UUIDType(binary=True), primary_key=True, default=uuid.uuid4)

tags = relationship("Tag", secondary=lambda: tag_to_resource,
backref='resources')

The lambda function called in _process_dependent_arguments() was returning 
None instead of tag_to_resource. Resulting in a:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition 
between parent/child tables on relationship Resource.tags - there are no 
foreign keys linking these tables.  Ensure that referencing columns are 
associated with a ForeignKey or ForeignKeyConstraint, or specify a 
'primaryjoin' expression.

Looking deeper I found that __name__ was also None. This kind of thing 
happens when sys.modules is messed with. I looked at the alembic code and 
found this in load_python_file():

del sys.modules[module_id]

If I remove that statement, the problem goes away.

Could it be that the reference count of the module object is becoming zero 
randomly, causing python to delete the data, as explained in this post?
http://stackoverflow.com/questions/5365562/why-is-the-value-of-name-changing-after-assignment-to-sys-modules-name

I've narrowed the problem down to a python test script, but it still 
imports a load of other stuff. I can trigger the good + bad case by just 
removing an import statement. I've been trying to get this down to a simple 
script in an attempt to prove what's going on, but the problem tends to 
come and go while I'm deleting code; making it difficult to narrow down. 
For example, I was convinced one day that the problem vanished by upgrading 
to sql alchemy 1.0.12, but the very next day the same code started failing 
again!

-- 
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.

[sqlalchemy] Re: Unexpected missing join when using joined table inheritance

[Will Angenent]

Thanks, that's helpful. We should be able to use  both solutions. Thanks 
for the clarification!

-- 
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/d/optout.

[sqlalchemy] Unexpected missing join when using joined table inheritance

[Will Angenent]

I have run into an interesting condition when using joined table 
inheritance. For example we have the typical employee, manager and engineer 
tables with the following relationships:

- an engineer is an employee
- a manager is an employee
- an employee has a manager

We find some interesting results when using this query:
session.query(Employee.id, Engineer.id).join(Engineer.manager)

The resulting SQL is:
SELECT employee.id AS employee_id, engineer.id AS engineer_id
FROM engineer, employee JOIN (employee AS employee_1 JOIN manager AS 
manager_1 ON manager_1.id = employee_1.id) ON manager_1.id = 
employee.manager_id

Notice how the outer engineer and employee tables aren't joined. I would 
have expected the engineer and employee from the query() call to be joined.

Example code:

#!/usr/bin/env python


from sqlalchemy import Column, Integer, String, Enum, __version__
from sqlalchemy import ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship


print "sqlalchemy version", __version__
engine = create_engine('sqlite:///:memory:', echo=False)
Base = declarative_base()




class Employee(Base):
__tablename__ = 'employee'


id = Column(Integer, primary_key=True)
name = Column(String, index=True, nullable=False)


manager_id = Column(
ForeignKey(
'manager.id', use_alter=True, name="manager_id_fkey"
), index=True, nullable=True)
manager = relationship('Manager', foreign_keys=[manager_id])


employee_type = Column(Enum('manager', 'engineer', name='employee_type'
))


__mapper_args__ = {
'polymorphic_identity': __tablename__,
'polymorphic_on': employee_type
}




class Manager(Employee):
__tablename__ = 'manager'


id = Column(Integer, ForeignKey('employee.id'), primary_key=True)


__mapper_args__ = {
'polymorphic_identity': __tablename__,
'inherit_condition': id == Employee.id,
}




class Engineer(Employee):
__tablename__ = 'engineer'


id = Column(Integer, ForeignKey('employee.id'), primary_key=True)


__mapper_args__ = {
'polymorphic_identity': __tablename__,
'inherit_condition': id == Employee.id,
}


Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
session = Session()


the_boss = Manager(name='the boss')
a_manager = Manager(name='a manager', manager=the_boss)
engineer1 = Engineer(name='engineer 1', manager=a_manager)
engineer2 = Engineer(name='engineer 2', manager=a_manager)


session.add(engineer1)
session.add(engineer2)
session.commit()




def test(test_number, query):
print "\nTest", test_number
print query
print query.count()


test(1, session.query(Engineer.id, Employee.id))
test(2, session.query(Employee.id, Engineer.id))
test(3, session.query(Engineer.id).outerjoin(Engineer.manager))
test(4, session.query(Employee.id).outerjoin(Engineer.manager))
test(5, session.query(Engineer.id, Employee.id).outerjoin(Engineer.manager))
test(6, session.query(Employee.id, Engineer.id).outerjoin(Engineer.manager))



The output


sqlalchemy version 1.0.8


Test 1
SELECT engineer.id AS engineer_id, employee.id AS employee_id
FROM employee JOIN engineer ON engineer.id = employee.id
2


Test 2
SELECT employee.id AS employee_id, engineer.id AS engineer_id
FROM employee JOIN engineer ON engineer.id = employee.id
2


Test 3
SELECT engineer.id AS engineer_id
FROM employee JOIN engineer ON engineer.id = employee.id LEFT OUTER JOIN 
(employee 
AS employee_1 JOIN manager AS manager_1 ON manager_1.id = employee_1.id) ON 
manager_1.id = employee.manager_id
2


Test 4
SELECT employee.id AS employee_id
FROM employee LEFT OUTER JOIN (employee AS employee_1 JOIN manager AS 
manager_1 ON manager_1.id = employee_1.id) ON manager_1.id = employee.
manager_id
4


Test 5
SELECT engineer.id AS engineer_id, employee.id AS employee_id
FROM employee JOIN engineer ON engineer.id = employee.id LEFT OUTER JOIN 
(employee 
AS employee_1 JOIN manager AS manager_1 ON manager_1.id = employee_1.id) ON 
manager_1.id = employee.manager_id
2


Test 6
SELECT employee.id AS employee_id, engineer.id AS engineer_id
FROM engineer, employee LEFT OUTER JOIN (employee AS employee_1 JOIN 
manager AS manager_1 ON manager_1.id = employee_1.id) ON manager_1.id = 
employee.manager_id
8-


This is not an issue, as swapping the columns ends up with the expected 
results. However I'd like to understand what's going on.

Thanks for your help

-- 
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/d/optout.

Re: [sqlalchemy] Query hangs after a while

[Will Brown]

The problem seems to be independent of context. Happens both when I run
python interactively from the prompt, and from within a Pyramid-based web
app.

I have not tried other connectors or raw DBAPI. Will try that.

On Sat, Jun 13, 2015 at 5:48 AM, Mike Bayer 
wrote:

>
>
> On 6/13/15 3:54 AM, Dr. wrote:
>
> I have a very simple table and query. For some reason it hangs after a few
> executions and I have to restart the application.
>
>  Code to set it up:
>
>   from sqlalchemy import (
> Column,
> Integer,
> String
> )
>
> from sqlalchemy.ext.declarative import declarative_base
> Base = declarative_base()
>
> class User(Base):
> __tablename__   = 'users'
>
> id  = Column(Integer, primary_key=True)
> pname   = Column(String)
>
>
> from sqlalchemy import create_engine
> engine = create_engine('mysql+mysqlconnector://user:pass@server/database',
> echo=True)
> Base.metadata.bind = engine
> from sqlalchemy.orm import sessionmaker
> Session = sessionmaker(bind=engine)
> session = Session()
>
>  The query I run is simply
>
>   session.query(User).all()
>
>  System:
>
>- Python 3.4.3
>- The database is a remote MySQL Ver 5.5.41-0ubuntu0.14.04.1 running
>on debian-linux-gnu on x86_64
> - MySQL Connector/Python 2.0.3
>- SQLAlchemy package: SQLAlchemy-1.0.5-py3.4.egg-info (I had the same
>problem with a ver < 1.0 so I upgraded but no improvement)
> - The clients are local, whether run on PC or Mac the problem is the
>same
>
>  Some observations:
>
>- After I run the query a few times, the program hangs.
>
>   in what context?   running the whole program from a command line each
> time?   Running the query inside of a loop inside of one program ?
> what's important here is how are connection resources being allocated and
> freed?
>
>
>
>- If I uncomment the pname field, however, it seems like it will never
>hang.
>
>
>- If I replace .all() with .first() the program won't hang
>- The general log on the MySQL server shows that the server receives
>the query so the problem is likely on the receiving end of SQLAlchemy
>- The server runs a Wordpress too which continues to function even if
>the SQLAlchemy connection hangs
>- echo True or False makes no difference
>
>
> did you try a different driver like pymysql (much more popular Py3k
> driver) ?Also what happens if you use a raw DBAPI program (e.g. with
> mysqlconnector or pymysql ?)try looking at the database (e.g.
> https://dev.mysql.com/doc/refman/5.0/en/show-engine.html) ?
>
>
>  --
> 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/d/optout.
>

-- 
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/d/optout.

Re: [sqlalchemy] Calling stored procedures in SQLAlchemy

[Will Orr]

Yes. I tried adding the autocommit execution option and calling execute, as 
well as turning autocommit off and manually calling commit.

Like I said, the stored procedure is getting run because the primary key is 
incrementing internally, (that is to say, that if my last id entry was 1, I 
run this code, and then on the next insert the id entry is 3) and the 
results are getting rolled back.

On Thursday, May 31, 2012 7:01:53 PM UTC-7, Michael Bayer wrote:
>
> did you call Session.commit() ?  otherwise you're still in an open 
> transaction, assuming default settings.
>
> Session.execute() is not the same as engine.execute(), where the latter is 
> autocommitting (assuming you also called execution_options(autocommit=True) 
> for this particular text() construct).
>
>
>
> On May 31, 2012, at 9:23 PM, Will Orr wrote:
>
> Hello all!
>
> I'm having this *exact* bug from a few years ago wrt. calling stored 
> procedures.
> https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/qA_ypVgJ1B0
>
> What makes it worse, however, is that adding the autocommit execution 
> option or explicitly starting and stopping a transaction do nothing.
>
> session.execute(text('call add_logentry(:username, :hostname, :action, 
> \'-00-00 00:00:00\')'), {
> 'username': username,
> 'hostname': hostname,
> 'action'  : action
> })
>
> There's the code. It should insert some values into some tables, however 
> those values are never inserted, though the primary key counter is 
> incremented.
>
> I'm using SQLAlchemy with ZopeTransactionExtension.
>
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To view this discussion on the web visit 
> https://groups.google.com/d/msg/sqlalchemy/-/si1vqn5kmjoJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit 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 view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/5y7_u014dn8J.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Calling stored procedures in SQLAlchemy

[Will Orr]

Hello all!

I'm having this *exact* bug from a few years ago wrt. calling stored 
procedures.
https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/qA_ypVgJ1B0

What makes it worse, however, is that adding the autocommit execution 
option or explicitly starting and stopping a transaction do nothing.

session.execute(text('call add_logentry(:username, :hostname, :action, 
\'-00-00 00:00:00\')'), {
'username': username,
'hostname': hostname,
'action'  : action
})

There's the code. It should insert some values into some tables, however 
those values are never inserted, though the primary key counter is 
incremented.

I'm using SQLAlchemy with ZopeTransactionExtension.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/si1vqn5kmjoJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Add Support for "24:00:00" with Time type

[Will Weaver]

For those that end up being interested in something like this.  For the
complete round robin I did this.

from path.to.time import Time
from psycopg2 import extensions


def cast_time(value, cur):
"""Cast postgresql Time type to a Time object"""
if value is None:
return None

return Time.parse(value)

# 1083 is the oid for postgres Time type
TIME = extensions.new_type((1083,), "TIME", cast_time)
extensions.register_type(TIME)


def adapt_time(value):
"""Adapt value coming in to something postgres can handle."""
return extensions.adapt(str(value))

extensions.register_adapter(Time, adapt_time)

-- 
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Add Support for "24:00:00" with Time type

[Will Weaver]

On Wed, Apr 18, 2012 at 10:57 AM, Michael Bayer wrote:

>
> On Apr 18, 2012, at 10:47 AM, Will wrote:
>
>
> Yes, I knew about the adapters.  A coworker of mine came up with this:
>
>...
>
> s = Session()
> c = s.connection()
>
> cast_time = lambda value, cur: Time.parse(value)
> TIME = c.dialect.dbapi.extensions.new_type((1083,), "TIME", cast_time)
>
>   c.dialect.dbapi.extensions.register_type(TIME)
>
> I should have mentioned that in the first place.  I was wondering, what's
> the recommended way to modify the dialect settings globally, so they are
> used whenever a new engine/connection is created?
>
>
> Since you're doing things that are psycopg2 specific, you could just
> import psycopg2:
>
> from psycopg2 import extensions
> extensions.register_type(...)
>
> if you wanted to keep it local to an engine, you could do a "connect"
> event to add connection scope:
>
> from psycopg2 import extensions
>
> @event.listens_for(myengine, "connect")
> def setup_time_type(dbapi_conn, conn_rec):
> extensions.register_type(TIME, dbapi_conn)
>

Awesome, I'll try that out.  Thanks for the input, and as always thanks for
the quick reply.

-- 
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Add Support for "24:00:00" with Time type

[Will]

On Wednesday, April 18, 2012 10:17:43 AM UTC-4, Michael Bayer wrote:
>
>
> On Apr 18, 2012, at 9:36 AM, Will wrote:
>
> The postgresql Time type supports times from "00:00:00" to "24:00:00" in 
> accordance with ISO 8601 <http://en.wikipedia.org/wiki/ISO_8601#Times>. 
>  The python datetime.time class does not currently support "24:00:00" but 
> it would be useful to have SQLAlchemy support that.  
>
>
> puzzled, looking at 
> http://docs.python.org/library/datetime.html#datetime.time:
>
> All arguments are optional. *tzinfo* may be None, or an instance of a 
> tzinfo 
> <http://docs.python.org/library/datetime.html#datetime.tzinfo>subclass. The 
> remaining arguments may be ints or longs, in the following 
> ranges:
>
>- 0 <= hour < 24 
>- 0 <= minute < 60 
>- 0 <= second < 60 
>- 0 <= microsecond < 100. 
>
> ?
>
> the coercion of PG's date/time fields into Python objects are a product of 
> psycopg2.   If psycopg2 isn't doing what you want here, you'd want to check 
> with that product - psycopg2 has a comprehensive system of modifying it's 
> typing behavior: 
> http://initd.org/psycopg/docs/extensions.html#sql-adaptation-protocol-objects
>
>
> I'm using SQLAlchemy 0.6.8 and was wondering if there is a way to allow it 
> to support the "24:00:00" midnight notation.
>
>
> SQLAlchemy doesn't deal with string notations when it talks to Postgresql 
> regarding date and time types.Psycopg2 handles the details of string 
> formatting.
>
>
> I've tried to make a custom type that would support it, but it seems that 
> psycopg2 will return a datetime.time class even if I define the custom 
> type to implement the Text type.
>
>
> right, this is all psycopg2.   You'd need to establish this behavior using 
> psycopg2 only first, by registering adapters as described in the above 
> document.Once you set that up SQLAlchemy just passes that data right 
> through.
>
>
Yes, I knew about the adapters.  A coworker of mine came up with this:

   ...

s = Session()
c = s.connection()

cast_time = lambda value, cur: Time.parse(value)
TIME = c.dialect.dbapi.extensions.new_type((1083,), "TIME", cast_time)

  c.dialect.dbapi.extensions.register_type(TIME)

I should have mentioned that in the first place.  I was wondering, what's 
the recommended way to modify the dialect settings globally, so they are 
used whenever a new engine/connection is created?

On Wednesday, April 18, 2012 10:17:43 AM UTC-4, Michael Bayer wrote:
>
>
> On Apr 18, 2012, at 9:36 AM, Will wrote:
>
> The postgresql Time type supports times from "00:00:00" to "24:00:00" in 
> accordance with ISO 8601 <http://en.wikipedia.org/wiki/ISO_8601#Times>. 
>  The python datetime.time class does not currently support "24:00:00" but 
> it would be useful to have SQLAlchemy support that.  
>
>
> puzzled, looking at 
> http://docs.python.org/library/datetime.html#datetime.time:
>
> All arguments are optional. *tzinfo* may be None, or an instance of a 
> tzinfo 
> <http://docs.python.org/library/datetime.html#datetime.tzinfo>subclass. The 
> remaining arguments may be ints or longs, in the following 
> ranges:
>
>- 0 <= hour < 24 
>- 0 <= minute < 60 
>- 0 <= second < 60 
>- 0 <= microsecond < 100. 
>
> ?
>
> the coercion of PG's date/time fields into Python objects are a product of 
> psycopg2.   If psycopg2 isn't doing what you want here, you'd want to check 
> with that product - psycopg2 has a comprehensive system of modifying it's 
> typing behavior: 
> http://initd.org/psycopg/docs/extensions.html#sql-adaptation-protocol-objects
>
>
> I'm using SQLAlchemy 0.6.8 and was wondering if there is a way to allow it 
> to support the "24:00:00" midnight notation.
>
>
> SQLAlchemy doesn't deal with string notations when it talks to Postgresql 
> regarding date and time types.Psycopg2 handles the details of string 
> formatting.
>
>
> I've tried to make a custom type that would support it, but it seems that 
> psycopg2 will return a datetime.time class even if I define the custom 
> type to implement the Text type.
>
>
> right, this is all psycopg2.   You'd need to establish this behavior using 
> psycopg2 only first, by registering adapters as described in the above 
> document.Once you set that up SQLAlchemy just passes that data right 
> through.
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/Oa13nLlwW5YJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Add Support for "24:00:00" with Time type

[Will]

The postgresql Time type supports times from "00:00:00" to "24:00:00" in 
accordance with ISO 8601 <http://en.wikipedia.org/wiki/ISO_8601#Times>. 
 The python datetime.time class does not currently support "24:00:00" but 
it would be useful to have SQLAlchemy support that.  I'm using SQLAlchemy 
0.6.8 and was wondering if there is a way to allow it to support the 
"24:00:00" midnight notation.

I've tried to make a custom type that would support it, but it seems that 
psycopg2 will return a datetime.time class even if I define the custom type 
to implement the Text type.


from datetime import timedelta

from sqlalchemy.types import TypeDecorator, Text


class Time(timedelta):
"""Time class that supports times between "00:00:00" and "24:00:00"
inclusive."""

SEC_PER_MIN = 60
SEC_PER_HOUR = SEC_PER_MIN * 60
SEC_PER_DAY = SEC_PER_HOUR * 24

def __new__(cls, hour=0, minute=0, second=0):
seconds = hour * cls.SEC_PER_HOUR + minute * cls.SEC_PER_MIN + 
second
instance = timedelta.__new__(cls, 0, seconds)
if not (timedelta(0) <= instance <= timedelta(1)):
raise ValueError('Values must be between 00:00:00 and 
24:00:00.')
return instance

def __str__(self):
return '{0.hour:02}:{0.minute:02}:{0.second:02}'.format(self)

def __repr__(self):
return '{0}({1.hour}, {1.minute}, {1.second})'.format(
type(self).__name__, self)

@property
def hour(self):
seconds = self.days * self.SEC_PER_DAY + self.seconds
return seconds / self.SEC_PER_HOUR

@property
def minute(self):
return self.seconds % self.SEC_PER_HOUR / self.SEC_PER_MIN

@property
def second(self):
return self.seconds % self.SEC_PER_MIN

@classmethod
def parse(cls, time):
return Time(*[int(x) for x in time.split(':')])


class TimeOfDay(TypeDecorator):
"""Time type that handles times between 00:00:00 and 24:00:00."""

impl = Text

def process_bind_param(self, value, dialect):
"""Store time as a string."""
if value is None:
return None

return str(value)

def process_result_value(self, value, dialect):
"""Return Time object."""
if value is None:
return None

return Time.parse(value)


This would work if the actual database column was Text but in the postgres 
database the column is Time.  So it seems that psycopg2 converts that 
automatically to a datetime.time when retrieving the value, which makes the 
time "24:00:00" be "00:00:00".

Thanks for any input,
-Will Weaver

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/aZWWaRa8vBgJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] default value from column

[Will Weaver]

I'm not positive about this but if you are talking about the integer column,
all defaults have to be a string or some SQLAlchemy function so you'd want:

type = sa.Column(sa.types.Integer, nullable=0, default='1')

On Wed, Jul 13, 2011 at 7:37 AM, ddarko  wrote:
> class AccountHistory(Base):
>id  = sa.Column(sa.types.Integer,
primary_key=1)
>date= sa.Column(sa.types.DateTime, nullable=0,
> default=sa.func.current_timestamp())
>type= sa.Column(sa.types.Integer, nullable=0,
default=1)
>
>def getdefault(self, name):
>return self.namedefault ?
>
> eg:
> print(AccountHistory().getdefault('type'))
> 1
>
>
> I would like to write a function that returns a default value defined
> for that column in the table.
> How does it make?
>
> --
> 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
sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit 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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] database connection leak found, believed fixed, unclear how - 0.5.5

[Will Berry]

I am using SQLAlchemy 0.5.5 which is the version packaged with Red Hat
Enterprise 6.  I am using the below class to implement transactions
using closures.  (I have excluded methods unrelated to this issue.)  I
am using psycopg2 2.0.13 ('postgres://' URLs) to talk to the database,
if that matters.

The difference between "working" and "not working" for me is this
difference in __init__.

Working:
self._engine = sqlalchemy.create_engine(URL)
self._session = sqlalchemy.orm.scoped_session(
sqlalchemy.orm.sessionmaker(self._engine,
**self._set_compatibility(sqlalchemy.__version__)))

Not working:
self._session =
sqlalchemy.orm.sessionmaker(**self._set_compatibility(sqlalchemy.__version__))
self._engine = sqlalchemy.create_engine(URL)
self._session.configure(bind=self._engine)

The problem is, if I pass poolclass=AssertionPool to create_engine, I
get an AssertionError exception after as few as three consecutive
transactions with the "non-working" code.  I have a single-threaded
process that does not use nested transactions and executes
transactions sequentially.

Basically my question is, is the observed behavior surprising, and if
not, what exactly is wrong with my "not working" code?  I'm not
certain that I really understand how sessions work.

"Working" code with portions removed that are not relevant to this
posting:

import sqlalchemy.orm

class SQLAlchemyDB:
  def _set_compatibility(self, version):
major, minor = map(int, version.split('.'))[:2]
if major > 0 or minor > 5:
  raise NotImplementedError, \
  'this module not tested against SQLAlchemy version %s' %
(version,)
smargs = {'autoflush': True}
if minor > 4:
  smargs['autocommit'] = False
  smargs['expire_on_commit'] = False  # no need to merge for
every transaction
else: # version 0.4.x
  smargs['transactional'] = True
return smargs

  def __init__(self, URL):
self._engine = sqlalchemy.create_engine(URL)
self._session =
sqlalchemy.orm.scoped_session(sqlalchemy.orm.sessionmaker(
self._engine,
**self._set_compatibility(sqlalchemy.__version__)))
self._metadata = sqlalchemy.MetaData(bind=self._engine)

  def named_table(self, tablename):
return sqlalchemy.Table(tablename, self._metadata,
autoload=True)

  def bind_class(self, freeclass, table):
sqlalchemy.orm.mapper(freeclass, table)

  def new_bound_class(self, table):
class ORMClassPrototype(object): pass
self.bind_class(ORMClassPrototype, table)
return ORMClassPrototype

  def named_orm_class(self, name):
return self.new_bound_class(self.named_table(name))

  def transaction(self, callback):
session = self._session()
if not hasattr(session, 'add'):  # compatibility with
SQLAlchemy 0.4.x
  session.add = session.save
try:
  result = callback(session)
except BaseException:
  session.rollback()
  raise
else:
  session.commit()
finally:
  session.close()
return result


Below is my log file with application-specific information scrubbed
out (I enabled logging at level logging.INFO for logger
sqlalchemy.pool).  To get this log, I used the "non-working" code and
interactively triggered the same transaction function three times in a
row.  The transaction function calls the named_orm_class method and
uses session.query a few times.  No objects created by the transaction
function have living references (in application code) after it
returns.  Note that after I shut down my application it returns the
connection to the pool.  This does not happen in the "working"
version.


2011-02-23 18:19:36 EST INFO Application starting up
2011-02-23 18:19:36 EST INFO Created new connection 
2011-02-23 18:19:36 EST INFO Connection  checked
out from pool
2011-02-23 18:19:36 EST INFO Connection  being
returned to pool
2011-02-23 18:19:36 EST INFO Connection  checked
out from pool
2011-02-23 18:19:36 EST INFO Connection  being
returned to pool
2011-02-23 18:21:43 EST INFO Beginning transaction (from
application)
2011-02-23 18:21:43 EST INFO Connection  checked
out from pool
2011-02-23 18:21:43 EST INFO Connection  being
returned to pool
2011-02-23 18:21:43 EST INFO Connection  checked
out from pool
2011-02-23 18:21:43 EST INFO Connection  being
returned to pool
2011-02-23 18:21:43 EST INFO Connection  checked
out from pool
2011-02-23 18:21:43 EST INFO Connection  being
returned to pool
2011-02-23 18:21:43 EST INFO Connection  checked
out from pool
2011-02-23 18:21:43 EST INFO Connection  being
returned to pool
2011-02-23 18:21:43 EST INFO Connection  checked
out from pool
2011-02-23 18:21:43 EST INFO Connection  being
returned to pool
2011-02-23 18:21:43 EST INFO Connection  checked
out from pool
2011-02-23 18:21:43 E

[sqlalchemy] Re: Parent model being queried when deleting Child model

[Will]

> > I have a table that has two
> > parents,
> > that is a foreign key to two tables.  One of those tables isn't needed
> > by the application, but it needed for other applications that use
> > these
> > same SQLAlchemy models.
>
> > The following example shows first a default parent/child relationship
> > with the resulting query calls.  The second example shows an example
> > using passive_deletes=True in the relationship from child to parent,
> > which I understand doesn't make too much sense but causes my desired
> > behavior.  This, also, has the resulting query calls.
>
> > Is there a better way for me to get the behavior I desire, or is this
> > a
> > bug?
>
> Its a bug.   Many-to-ones are generally cheap since they're usually already 
> loaded, but in this case the load isn't needed and this is actually a recent 
> regression as of 0.6.6, so 0.6.5 won't exhibit this behavior...of course 
> 0.6.5 has the previous issue that was fixed here but its likely not as 
> common.  This is ticket #2049 and a fix will be available shortly.
>
> Also you might want to consider linking the "parent" and "children" 
> relationships via the "back_populates" attribute, or otherwise map them at 
> once using relationship + backref.  Otherwise the ORM treats mutations in 
> each attribute as separate which could lead to doubling of operations.  If 
> you're only mutating one side (or none) then it doesn't matter much.
>
>
>

Will this bug fix cause issues with models that have a relationship
with itself (where the parent of the object is another object of the
same class)?  I applied the patch you came up with and my tests fail
on this type of relationship.

-- 
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Parent model being queried when deleting Child model

[Will]

"""
Hello,

I was curious about a default relationship behavior.  It seems that
the
default behavior when deleting a child in a relationship is to query
the parent first.  I'm not sure why.  This hampers something I'm
working on because I want to only give privileges to a program to only
what it will need to do its work.  I have a table that has two
parents,
that is a foreign key to two tables.  One of those tables isn't needed
by the application, but it needed for other applications that use
these
same SQLAlchemy models.

The following example shows first a default parent/child relationship
with the resulting query calls.  The second example shows an example
using passive_deletes=True in the relationship from child to parent,
which I understand doesn't make too much sense but causes my desired
behavior.  This, also, has the resulting query calls.

Is there a better way for me to get the behavior I desire, or is this
a
bug?

Thank you for any insight.

"""

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer


engine = create_engine('sqlite:///')


 Without passive_deletes=True ###
Base = declarative_base()


class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
children = relationship('Child', lazy='dynamic')


class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey(Parent.id), nullable=False)
parent = relationship('Parent')


Base.metadata.create_all(engine)

session = sessionmaker(bind=engine, autocommit=True)()

parent = Parent()
child = Child(parent=parent)
session.add(parent)
session.add(child)
session.flush()


session.expunge_all()


engine.echo = True
session.delete(session.query(Child).one())
session.flush()
engine.echo = False

Base.metadata.drop_all(engine)


# Ouput
"""
2011-02-11 08:16:39,242 INFO sqlalchemy.engine.base.Engine.0x...cd90
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
2011-02-11 08:16:39,243 INFO sqlalchemy.engine.base.Engine.0x...cd90
()
2011-02-11 08:16:39,243 INFO sqlalchemy.engine.base.Engine.0x...cd90
BEGIN (implicit)
2011-02-11 08:16:39,244 INFO sqlalchemy.engine.base.Engine.0x...cd90
SELECT parents.id AS parents_id
FROM parents
WHERE parents.id = ?
2011-02-11 08:16:39,244 INFO sqlalchemy.engine.base.Engine.0x...cd90
(1,)
2011-02-11 08:16:39,244 INFO sqlalchemy.engine.base.Engine.0x...cd90
DELETE FROM children WHERE children.id = ?
2011-02-11 08:16:39,244 INFO sqlalchemy.engine.base.Engine.0x...cd90
(1,)
2011-02-11 08:16:39,245 INFO sqlalchemy.engine.base.Engine.0x...cd90
COMMIT
"""


 With passive_deletes=True ###
Base = declarative_base()


class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
children = relationship('Child', lazy='dynamic')


class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey(Parent.id), nullable=False)
parent = relationship('Parent', passive_deletes=True)


Base.metadata.create_all(engine)

session = sessionmaker(bind=engine, autocommit=True)()

parent = Parent()
child = Child(parent=parent)
session.add(parent)
session.add(child)
session.flush()


session.expunge_all()


engine.echo = True
session.delete(session.query(Child).one())
session.flush()
engine.echo = False


# Output
"""
/usr/lib/pymodules/python2.6/sqlalchemy/orm/properties.py:897:
SAWarning: On Child.parent, 'passive_deletes' is normally configured
on one-to-many, one-to-one, many-to-many relationships only.
  self._determine_direction()
2011-02-11 08:16:39,252 INFO sqlalchemy.engine.base.Engine.0x...cd90
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
2011-02-11 08:16:39,252 INFO sqlalchemy.engine.base.Engine.0x...cd90
()
2011-02-11 08:16:39,253 INFO sqlalchemy.engine.base.Engine.0x...cd90
BEGIN (implicit)
2011-02-11 08:16:39,253 INFO sqlalchemy.engine.base.Engine.0x...cd90
DELETE FROM children WHERE children.id = ?
2011-02-11 08:16:39,253 INFO sqlalchemy.engine.base.Engine.0x...cd90
(1,)
2011-02-11 08:16:39,253 INFO sqlalchemy.engine.base.Engine.0x...cd90
COMMIT
"""

-- 
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] passive_deletes="all" still tries to query relationship on delete

[Will Weaver]

Sweet thanks for the quick fix as always.

On Tue, Jan 4, 2011 at 11:01 AM, Michael Bayer  wrote:
> ah you were right the first time.   The "all" concept is local dependency.py, 
> which just needs to interpret its internal values appropriately before 
> sending arguments off to attributes.py.
>
>
> On Jan 4, 2011, at 10:49 AM, Will Weaver wrote:
>
>> I made a different patch.  This one I don't believe is ideal, but I
>> think it might be the appropriate place to make the patch
>>
>> http://pastebin.com/kZbZcJ3u
>>
>> On Tue, Jan 4, 2011 at 8:58 AM, Will  wrote:
>>> Hello,
>>>
>>> I noticed that when setting passive_deletes="all" on a relationship
>>> and try to delete the parent object sqlalchemy still tries to query
>>> the child object.  For the way my models are set up I can't have the
>>> child object be queried.  I'm using a hybrid of horizontal and
>>> vertical sharding in this relationship and sometimes the corresponding
>>> table does not exist so it cannot be queried.
>>>
>>> I have a patch that seems to fix the problem but I don't understand
>>> sqlalchemy enough to know if this is the proper fix or if it should be
>>> done somewhere else.
>>>
>>> http://pastebin.com/wd2Dsdwu
>>>
>>> This particular problem does not occur with passive_deletes=True but
>>> other problems occur because I don't want existing objects to have any
>>> fields nullified either if they are loaded in sqlalchemy.
>>>
>>> I narrowed down the reason why this occurs to orm/attributes on line
>>> 383 but that doesn't seem to be the appropriate place for the fix.
>>>
>>> Thanks for any input.
>>>
>>> -Will
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups 
>>> "sqlalchemy" group.
>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>> To unsubscribe from this group, send email to 
>>> sqlalchemy+unsubscr...@googlegroups.com.
>>> For more options, visit 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 sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to 
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit 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 sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] passive_deletes="all" still tries to query relationship on delete

[Will Weaver]

I made a different patch.  This one I don't believe is ideal, but I
think it might be the appropriate place to make the patch

http://pastebin.com/kZbZcJ3u

On Tue, Jan 4, 2011 at 8:58 AM, Will  wrote:
> Hello,
>
> I noticed that when setting passive_deletes="all" on a relationship
> and try to delete the parent object sqlalchemy still tries to query
> the child object.  For the way my models are set up I can't have the
> child object be queried.  I'm using a hybrid of horizontal and
> vertical sharding in this relationship and sometimes the corresponding
> table does not exist so it cannot be queried.
>
> I have a patch that seems to fix the problem but I don't understand
> sqlalchemy enough to know if this is the proper fix or if it should be
> done somewhere else.
>
> http://pastebin.com/wd2Dsdwu
>
> This particular problem does not occur with passive_deletes=True but
> other problems occur because I don't want existing objects to have any
> fields nullified either if they are loaded in sqlalchemy.
>
> I narrowed down the reason why this occurs to orm/attributes on line
> 383 but that doesn't seem to be the appropriate place for the fix.
>
> Thanks for any input.
>
> -Will
>
> --
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] passive_deletes="all" still tries to query relationship on delete

[Will]

Hello,

I noticed that when setting passive_deletes="all" on a relationship
and try to delete the parent object sqlalchemy still tries to query
the child object.  For the way my models are set up I can't have the
child object be queried.  I'm using a hybrid of horizontal and
vertical sharding in this relationship and sometimes the corresponding
table does not exist so it cannot be queried.

I have a patch that seems to fix the problem but I don't understand
sqlalchemy enough to know if this is the proper fix or if it should be
done somewhere else.

http://pastebin.com/wd2Dsdwu

This particular problem does not occur with passive_deletes=True but
other problems occur because I don't want existing objects to have any
fields nullified either if they are loaded in sqlalchemy.

I narrowed down the reason why this occurs to orm/attributes on line
383 but that doesn't seem to be the appropriate place for the fix.

Thanks for any input.

-Will

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Getting a list of parameters from a select object Options (cont'd)

[Will Weaver]

Does the lack of a response mean that it can't be done or just that no
one knows how to do it?  Or did I not ask the question good enough?

On Tue, Dec 21, 2010 at 12:02 PM, Will  wrote:
> In continuation of the following post
>
> http://groups.google.com/group/sqlalchemy/browse_thread/thread/160870682c011611/8229a3eb9c10f870?lnk=gst&q=parameters+select#8229a3eb9c10f870
>
> Is there a way to get the columns that the bind parameters correspond
> to?  I was able to get the param values but that's half the battle.
>
> In [11]: print clause
> SELECT count(1) AS count_1
> FROM links
> WHERE :param_1 = links.foo_id AND :param_2 = ana.links.bar_id
>
> In [12]: compiled = clause.compile()
>
> In [13]: print compiled.params
> {u'param_1': 8, u'param_2': 1}
>
> --
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Getting a list of parameters from a select object Options (cont'd)

[Will]

In continuation of the following post

http://groups.google.com/group/sqlalchemy/browse_thread/thread/160870682c011611/8229a3eb9c10f870?lnk=gst&q=parameters+select#8229a3eb9c10f870

Is there a way to get the columns that the bind parameters correspond
to?  I was able to get the param values but that's half the battle.

In [11]: print clause
SELECT count(1) AS count_1
FROM links
WHERE :param_1 = links.foo_id AND :param_2 = ana.links.bar_id

In [12]: compiled = clause.compile()

In [13]: print compiled.params
{u'param_1': 8, u'param_2': 1}

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Deletion order during flush is not correct.

[Will Weaver]

Wow, this has been a problem for me for the past 3 or 4 days and took
a while to get to that example.  Defining the backrefs or the
relationships in the opposite direction did the job.  I had
intentionally left out some of the backreffed relationships because I
didn't need them for what I was working on, but it definitely is worth
it to get this working.

Thanks a lot.

-Will

On Wed, Dec 15, 2010 at 2:45 PM, Michael Bayer  wrote:
> This is an interesting edge case and I can probably ensure that the 
> dependency between Parent/Child is present in the unit of work even if there 
> is no known linkage at the Child.parent level for the objects actually 
> present - ticket #2002 is added for this.
>
> In the meantime, the uow needs to be aware of the linkage between 
> Parent->Child when flush occurs.   Adding a backref "children" to the parent 
> relationship will do it, or ensuring that child.parent is accessed before 
> emitting the flush will do it.     The usual way this kind of delete is 
> performed is the "delete" cascade is added to the "children" backref, then 
> the Parent is deleted alone, the deletes cascading to the Child objects 
> naturally.
>
> But this is a fun bug and I'll probably have a fix very soon, perhaps in 10 
> minutes or maybe not.
>
>
>
>
> On Dec 15, 2010, at 2:17 PM, Will wrote:
>
>> """
>> Hello,
>>
>> I've been recently having a problem with sqlalchemy not flushing
>> deletes in the proper order.  I've created a simple example for the
>> problem that has been occuring.  I tried to run this using sqlite and
>> it doesn't have any problems, it is only with Postgresql.
>>
>> One thing of note is that if there is only one Child it doesn't seem
>> to
>> have a problem, only when there are multiple children.  Not sure if
>> that makes a difference in the SQLAlchemy code.
>> """
>>
>> from sqlalchemy import create_engine
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy.orm import scoped_session, sessionmaker, relationship
>> from sqlalchemy.schema import Column, ForeignKey
>> from sqlalchemy.types import Integer
>>
>> #engine = create_engine('sqlite:///')
>> engine = create_engine('postgresql://test_runner@/testing_db')
>>
>> Model = declarative_base()
>>
>>
>> class Parent(Model):
>>    __tablename__ = 'parents'
>>
>>    id = Column(Integer, primary_key=True)
>>
>>
>> class Child(Model):
>>    __tablename__ = 'children'
>>
>>    id = Column(Integer, primary_key=True)
>>    parent_id = Column(Integer, ForeignKey('parents.id'),
>>                       nullable=False)
>>    parent = relationship('Parent')
>>
>>
>> def begin():
>>    """Begin transaction"""
>>    #global transaction
>>    #transaction = session.begin()
>>
>>    session.begin()
>>
>>
>> def commit():
>>    """Commit transaction"""
>>    #global transaction
>>    #transaction.commit()
>>
>>    session.commit()
>>
>>
>> Model.metadata.create_all(engine)
>>
>> parent = Parent()
>> children = [Child(parent=parent), Child(parent=parent)]
>>
>> Session = sessionmaker(bind=engine, autocommit=True)
>> session = Session()
>>
>> try:
>>    session.bind.echo = True
>>
>>    begin()
>>    session.add_all(children)
>>    session.add(parent)
>>    commit()
>>
>>
>>    begin()
>>    for child in children:
>>        session.delete(child)
>>    session.delete(parent)
>>    commit()
>>
>>    session.bind.echo = False
>> finally:
>>    Model.metadata.drop_all(engine)
>>
>>
>> """
>> From running the script I have two different outputs because it seems
>> to run the deletes in a random order so subsequent runs will behave
>> differently.
>> """
>>
>> # Example Failed Run
>> """
>> 2010-12-15 13:45:05,050 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> BEGIN (implicit)
>> 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> INSERT INTO parents DEFAULT VALUES RETURNING parents.id
>> 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> {}
>> 2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> INSERT INTO children (parent_id) VALUES (%(parent_id

[sqlalchemy] Re: Deletion order during flush is not correct.

[Will]

An update.  This problem does occur with sqlite it's just that sqlite
doesn't enforce the foreign key so it doesn't throw an exception.

# output that deletes in the proper order
2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050
BEGIN (implicit)
2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050
INSERT INTO parents DEFAULT VALUES
2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050
()
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
COMMIT
2010-12-15 14:33:52,199 INFO sqlalchemy.engine.base.Engine.0x...d050
BEGIN (implicit)
2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050
(2,)
2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050
DELETE FROM children WHERE children.id = ?
2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050
((1,), (2,))
2010-12-15 14:33:52,202 INFO sqlalchemy.engine.base.Engine.0x...d050
SELECT parents.id AS parents_id
FROM parents
WHERE parents.id = ?
2010-12-15 14:33:52,202 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050
DELETE FROM parents WHERE parents.id = ?
2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050
COMMIT



# output that deletes in the wrong order
2010-12-15 14:33:56,691 INFO sqlalchemy.engine.base.Engine.0x...6050
BEGIN (implicit)
2010-12-15 14:33:56,692 INFO sqlalchemy.engine.base.Engine.0x...6050
INSERT INTO parents DEFAULT VALUES
2010-12-15 14:33:56,692 INFO sqlalchemy.engine.base.Engine.0x...6050
()
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
COMMIT
2010-12-15 14:33:56,694 INFO sqlalchemy.engine.base.Engine.0x...6050
BEGIN (implicit)
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
SELECT parents.id AS parents_id
FROM parents
WHERE parents.id = ?
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
DELETE FROM parents WHERE parents.id = ?
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,696 INFO sqlalchemy.engine.base.Engine.0x...6050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:56,696 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
(2,)
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
DELETE FROM children WHERE children.id = ?
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
((1,), (2,))
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
COMMIT

On Dec 15, 2:17 pm, Will  wrote:
> """
> Hello,
>
> I've been recently having a problem with sqlalchemy not flushing
> deletes in the proper order.  I've created a simple example for the
> problem that has been occuring.  I tried to run this using sqlite and
> it doesn't have any problems, it is only with Postgresql.
>
> One thing of note is that if there is only one Child it doesn't seem
> to
> have a problem, only when there are multiple children.  Not sure if
> that makes a difference in the SQLAlchemy code.
> """
>
> from sqlalchemy import create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import scoped_session, sessionmaker, relationship
> from

[sqlalchemy] Deletion order during flush is not correct.

[Will]

"""
Hello,

I've been recently having a problem with sqlalchemy not flushing
deletes in the proper order.  I've created a simple example for the
problem that has been occuring.  I tried to run this using sqlite and
it doesn't have any problems, it is only with Postgresql.

One thing of note is that if there is only one Child it doesn't seem
to
have a problem, only when there are multiple children.  Not sure if
that makes a difference in the SQLAlchemy code.
"""

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer

#engine = create_engine('sqlite:///')
engine = create_engine('postgresql://test_runner@/testing_db')

Model = declarative_base()


class Parent(Model):
__tablename__ = 'parents'

id = Column(Integer, primary_key=True)


class Child(Model):
__tablename__ = 'children'

id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parents.id'),
   nullable=False)
parent = relationship('Parent')


def begin():
"""Begin transaction"""
#global transaction
#transaction = session.begin()

session.begin()


def commit():
"""Commit transaction"""
#global transaction
#transaction.commit()

session.commit()


Model.metadata.create_all(engine)

parent = Parent()
children = [Child(parent=parent), Child(parent=parent)]

Session = sessionmaker(bind=engine, autocommit=True)
session = Session()

try:
session.bind.echo = True

begin()
session.add_all(children)
session.add(parent)
commit()


begin()
for child in children:
session.delete(child)
session.delete(parent)
commit()

session.bind.echo = False
finally:
    Model.metadata.drop_all(engine)


"""
>From running the script I have two different outputs because it seems
to run the deletes in a random order so subsequent runs will behave
differently.
"""

# Example Failed Run
"""
2010-12-15 13:45:05,050 INFO sqlalchemy.engine.base.Engine.0x...f5d0
BEGIN (implicit)
2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
INSERT INTO parents DEFAULT VALUES RETURNING parents.id
2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{}
2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
children.id
2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'parent_id': 1}
2010-12-15 13:45:05,053 INFO sqlalchemy.engine.base.Engine.0x...f5d0
INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
children.id
2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'parent_id': 1}
2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
COMMIT
2010-12-15 13:45:05,055 INFO sqlalchemy.engine.base.Engine.0x...f5d0
BEGIN (implicit)
2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0
SELECT parents.id AS parents_id
FROM parents
WHERE parents.id = %(param_1)s
2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'param_1': 1}
2010-12-15 13:45:05,057 INFO sqlalchemy.engine.base.Engine.0x...f5d0
DELETE FROM parents WHERE parents.id = %(id)s
2010-12-15 13:45:05,057 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'id': 1}
2010-12-15 13:45:05,058 INFO sqlalchemy.engine.base.Engine.0x...f5d0
ROLLBACK
2010-12-15 13:45:05,061 INFO sqlalchemy.engine.base.Engine.0x...f5d0
select relname from pg_class c join pg_namespace n on
n.oid=c.relnamespace where n.nspname=current_schema() and
lower(relname)=%(name)s
2010-12-15 13:45:05,061 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'name': u'children'}
2010-12-15 13:45:05,063 INFO sqlalchemy.engine.base.Engine.0x...f5d0
select relname from pg_class c join pg_namespace n on
n.oid=c.relnamespace where n.nspname=current_schema() and
lower(relname)=%(name)s
2010-12-15 13:45:05,063 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'name': u'parents'}
2010-12-15 13:45:05,064 INFO sqlalchemy.engine.base.Engine.0x...f5d0
DROP TABLE children
2010-12-15 13:45:05,064 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{}
2010-12-15 13:45:05,066 INFO sqlalchemy.engine.base.Engine.0x...f5d0
COMMIT
2010-12-15 13:45:05,067 INFO sqlalchemy.engine.base.Engine.0x...f5d0
DROP TABLE parents
2010-12-15 13:45:05,067 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{}
2010-12-15 13:45:05,068 INFO sqlalchemy.engine.base.Engine.0x...f5d0
COMMIT
Traceback (most recent call last):
  File "sharded_session_issue.py", line 64, in 
commit(session)
  File "shard

Re: [sqlalchemy] composite property not working on a mapped select

[Will Weaver]

Cool, thank you.  Due to the bug I moved away from mapping a
selectable to do the job.  But I'll keep in mind what you've said.

On Tue, Dec 7, 2010 at 9:01 PM, Michael Bayer  wrote:
> its a bug, and theres a new ticket http://www.sqlalchemy.org/trac/ticket/1997 
> with a small patch.
>
> I would suggest maybe not using composites for this for now.   Most of what 
> composite does you can accomplish using descriptors:
>
> class Value(object):
>   �...@property
>    def custom_values(self):
>        return CustomValue(self.v1, self.v2)
>
>   �...@custom_values.setter
>    def custom_values(self, value):
>        self.v1 = value.v1
>        self.v2 = value.v2
>
> I considered replacing the mechanism of composite with the above type of 
> thing in 0.7 but there were still some edge cases that composites seem to 
> have.
>
>
>
> On Dec 7, 2010, at 1:54 PM, Will wrote:
>
>> from sqlalchemy import create_engine
>> from sqlalchemy.orm import sessionmaker, mapper, relationship,
>> composite
>> from sqlalchemy.schema import Table, Column, MetaData, ForeignKey
>> from sqlalchemy.sql import select
>> from sqlalchemy.types import Integer, Text
>>
>> engine = create_engine('sqlite:///')
>> session = sessionmaker(bind=engine, autocommit=True)()
>> metadata = MetaData(engine)
>>
>>
>> # Tables
>> descriptions_table = Table('descriptions', metadata,
>>    Column('id', Integer, primary_key=True),
>>    Column('d1', Text),
>>    Column('d2', Text),
>> )
>>
>> values_table = Table('values', metadata,
>>    Column('id', Integer, primary_key=True),
>>    Column('description_id', Integer, ForeignKey('descriptions.id'),
>>           nullable=False),
>>    Column('v1', Text),
>>    Column('v2', Text),
>> )
>>
>> desc_values = select(
>>    [values_table, descriptions_table.c.d1, descriptions_table.c.d2],
>>    descriptions_table.c.id == values_table.c.description_id
>> ).alias('descriptions_values')
>>
>>
>> # Classes
>> class Descriptions(object):
>>    pass
>>
>>
>> class Values(object):
>>    pass
>>
>>
>> class CustomValues(list):
>>
>>    def __init__(self, *args):
>>        self.extend(args)
>>
>>    def __composite_values__(self):
>>        return self
>>
>>
>> # Mappers
>> mapper(Descriptions, descriptions_table, properties={
>>    'values': relationship(Values, lazy='dynamic'),
>>    'custom_descriptions': composite(CustomValues,
>> descriptions_table.c.d1,
>>                                     descriptions_table.c.d2),
>> })
>>
>> mapper(Values, desc_values, properties={
>>    'custom_descriptions': composite(CustomValues, desc_values.c.v1,
>>                                     desc_values.c.v2),
>> })
>>
>>
>> # Testing
>> metadata.create_all()
>> engine.echo = True
>>
>>
>> descriptions = Descriptions()
>> descriptions.custom_descriptions = CustomValues('Color', 'Number')
>>
>> values1 = Values()
>> values1.custom_values = CustomValues('Red', '5')
>>
>> values2 = Values()
>> values2.custom_values = CustomValues('Blue', '1')
>>
>> descriptions.values.append(values1)
>> descriptions.values.append(values2)
>>
>> session.add(descriptions)
>> session.flush()
>
> --
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] composite property not working on a mapped select

[Will]

"""
Hello,

I've been trying to map two tables that have 25 columns each in
addition to
keys.  The parent table's (descriptions) column values represent the
key in a
dictionary and the child table's (values) column values represent the
values of
the dictionary.

i.e.:

table 'descriptions':
id SERIAL
description1 Text
description2 Text
...
description25 Text

table 'values':
id SERIAL
descriptions_id INTEGER NOT NULL FOREIGN KEY descriptions.id
value1 Text
value2 Text
...
value25 Text


I wanted to map the join of the descriptions table and the values
table to a
single class and make two composite properties that would represent
the 50
columns.

Below I stared to do a prototype of this and ran into a problem with
composite
properties not working on mapped join (select alias). I never got
around to
making the dictionary I planned.

And I know that the design of the descriptions table and values table
is not
the best but it's what I have to work with at this point.

"""
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, mapper, relationship,
composite
from sqlalchemy.schema import Table, Column, MetaData, ForeignKey
from sqlalchemy.sql import select
from sqlalchemy.types import Integer, Text

engine = create_engine('sqlite:///')
session = sessionmaker(bind=engine, autocommit=True)()
metadata = MetaData(engine)


# Tables
descriptions_table = Table('descriptions', metadata,
Column('id', Integer, primary_key=True),
Column('d1', Text),
Column('d2', Text),
)

values_table = Table('values', metadata,
Column('id', Integer, primary_key=True),
Column('description_id', Integer, ForeignKey('descriptions.id'),
   nullable=False),
Column('v1', Text),
Column('v2', Text),
)

desc_values = select(
[values_table, descriptions_table.c.d1, descriptions_table.c.d2],
descriptions_table.c.id == values_table.c.description_id
).alias('descriptions_values')


# Classes
class Descriptions(object):
pass


class Values(object):
pass


class CustomValues(list):

def __init__(self, *args):
self.extend(args)

def __composite_values__(self):
return self


# Mappers
mapper(Descriptions, descriptions_table, properties={
'values': relationship(Values, lazy='dynamic'),
'custom_descriptions': composite(CustomValues,
descriptions_table.c.d1,
 descriptions_table.c.d2),
})

mapper(Values, desc_values, properties={
'custom_descriptions': composite(CustomValues, desc_values.c.v1,
 desc_values.c.v2),
})


# Testing
metadata.create_all()
engine.echo = True


descriptions = Descriptions()
descriptions.custom_descriptions = CustomValues('Color', 'Number')

values1 = Values()
values1.custom_values = CustomValues('Red', '5')

values2 = Values()
values2.custom_values = CustomValues('Blue', '1')

descriptions.values.append(values1)
descriptions.values.append(values2)

session.add(descriptions)
session.flush()


# Output
"""
2010-12-07 13:32:04,933 INFO sqlalchemy.engine.base.Engine.0x...e690
BEGIN (implicit)
2010-12-07 13:32:04,933 INFO sqlalchemy.engine.base.Engine.0x...e690
INSERT INTO descriptions (d1, d2) VALUES (?, ?)
2010-12-07 13:32:04,933 INFO sqlalchemy.engine.base.Engine.0x...e690
('Color', 'Number')
2010-12-07 13:32:04,934 INFO sqlalchemy.engine.base.Engine.0x...e690
INSERT INTO "values" (description_id, v1, v2) VALUES (?, ?, ?)
2010-12-07 13:32:04,934 INFO sqlalchemy.engine.base.Engine.0x...e690
(1, None, None)
2010-12-07 13:32:04,934 INFO sqlalchemy.engine.base.Engine.0x...e690
INSERT INTO "values" (description_id, v1, v2) VALUES (?, ?, ?)
2010-12-07 13:32:04,935 INFO sqlalchemy.engine.base.Engine.0x...e690
(1, None, None)
2010-12-07 13:32:04,935 INFO sqlalchemy.engine.base.Engine.0x...e690
COMMIT


>From this ouput we can see that v1 and v2 are not being set, they are
being
left at None, but descriptions.custom_descriptions IS being set
properly.

I'm hoping it's just something I missed.
"""

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Non UTF8 strings retreived via mapped class - 0.5beta3

[Will Temperley]

Hi

I'm having some trouble with a UTF8 DB and strings not being
converted. I get the following:

'ascii' codec can't encode character u'\xe2' in position 142: ordinal
not in range(128).

See the code below. With the same engine, if I connect directly, I
have no problems but if I go via my mapped class (Source, also see
below), I get such errors.

Any ideas? Am I just being dumb here?

Cheers

Will


#ENGINE
engine = sqlalchemy.create_engine ('postgres://me:[EMAIL PROTECTED]:
5432/MAP', convert_unicode=True)

#DOESN'T WORK:
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
Source.metadata.create_all(engine)
results = session.query(Source).all()

#DOES WORK:
con = engine.connect()
results = con.execute("select abstract from base_source")

#THE SOURCE CLASS
class Source(Base):
__tablename__ = 'base_source'

id = Column(Integer, primary_key=True)
temp_author = Column(UnicodeText)
abstract = Column(UnicodeText)
year = Column(UnicodeText)
title = Column(UnicodeText)
journal = Column(UnicodeText)
ref_type = Column(UnicodeText)
url = Column(UnicodeText)

pr = Column(Boolean)
ihd= Column(Boolean)
vector = Column(Boolean)

def __init__(self, year, title, author, abstract, ref_type):
self.year = year
self.title = title
self.abstract = abstract
def __repr__(self):
return self.abstract

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---