Hi Michael,
Thank you very much for your fast response. I will give it a try.
I tried to remove unneeded stuff but I think that the problem is in the
track_changes mixin as I never got this error before using it.
If you need more stuff or do find something, I'd be happy to know.
BTW: for initialization, I use:
DB_URL="oracle://user:pass@url/db"
engine = create_engine(DB_URL, echo=False, pool_recycle=3600)
Session = scoped_session(sessionmaker(bind=engine, expire_on_commit=False))
session = tracked_session(Session())
Base = declarative_base()
Cheers,
Ofir
On Thursday, April 30, 2015 at 7:03:10 PM UTC+3, Michael Bayer wrote:
>
>
>
> On 4/30/15 11:00 AM, Ofir Herzas wrote:
>
> Hi,
> I'm using sqlalchemy 0.9.7 and cx_oracle 5.1.3 and every once in a while
> (inconsistent), I get the following error:
>
> Traceback (most recent call last):
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>
> line 1919, in flush
> self._flush(objects)
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>
> line 2037, in _flush
> transaction.rollback(_capture_exception=True)
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py",
>
> line 60, in __exit__
> compat.reraise(exc_type, exc_value, exc_tb)
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>
> line 2001, in _flush
> flush_context.execute()
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py",
>
> line 372, in execute
> rec.execute(self)
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py",
>
> line 526, in execute
> uow
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py",
>
> line 65, in save_obj
> mapper, table, insert)
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py",
>
> line 602, in _emit_insert_statements
> execute(statement, params)
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
> line 729, in execute
> return meth(self, multiparams, params)
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
>
> line 321, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
> line 826, in _execute_clauseelement
> compiled_sql, distilled_params
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
> line 978, in _execute_context
> context._fetch_implicit_returning(result)
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
>
> line 815, in _fetch_implicit_returning
> ipk.append(row[c])
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py",
>
> line 331, in _key_fallback
> expression._string_or_unprintable(key))
> NoSuchColumnError: "Could not locate column in row for column '
> t_shift_employee_change.id'"
> [30/Apr/2015:15:55:17] EXCEPTION
> # ---- EXCEPTION DESCRIPTION BEGIN ---- #
> # ---- Type ---- #
> NoSuchColumnError
> # ---- Detail ---- #
> "Could not locate column in row for column 't_shift_employee_change.id'"
> # ---- Traceback ---- #
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>
> line 1919, in flush
> self._flush(objects)
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>
> line 2037, in _flush
> transaction.rollback(_capture_exception=True)
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py",
>
> line 60, in __exit__
> compat.reraise(exc_type, exc_value, exc_tb)
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>
> line 2001, in _flush
> flush_context.execute()
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py",
>
> line 372, in execute
> rec.execute(self)
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py",
>
> line 526, in execute
> uow
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py",
>
> line 65, in save_obj
> mapper, table, insert)
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py",
>
> line 602, in _emit_insert_statements
> execute(statement, params)
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
> line 729, in execute
> return meth(self, multiparams, params)
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
>
> line 321, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
> line 826, in _execute_clauseelement
> compiled_sql, distilled_params
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
> line 978, in _execute_context
> context._fetch_implicit_returning(result)
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
>
> line 815, in _fetch_implicit_returning
> ipk.append(row[c])
> -
> File
> "/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py",
>
> line 331, in _key_fallback
> expression._string_or_unprintable(key))
> # ---- EXCEPTION DESCRIPTION END ---- #
>
> I saw several posts about this issue but I didn't find a solution.
> Restarting my service seems to fix the issue for a while.
>
> BTW: This error is linked to a mixin class I've created for
> track-changes based on the version mixin from
>
> *
> http://docs.sqlalchemy.org/en/latest/_modules/examples/versioned_history/history_meta.html
>
> <http://docs.sqlalchemy.org/en/latest/_modules/examples/versioned_history/history_meta.html>
>
> *
>
>
> this error refers to a wide variety of situations where the ORM is running
> a query that is failing to compose correctly. It's a bug in SQLAlchemy.
> That you are getting this within an INSERT statement from the RETURNING
> clause in oracle strongly suggests this is a bug in the Oracle dialect.
> If you can post minimal mappings and the stack trace here as a bug report
> that would be very helpful.
>
> Note also that the Oracle dialect doesn't do very well with composite
> primary keys in conjunction with RETURNING. There may have been some
> limitations I came across in my testing here, so there is a note at
> http://docs.sqlalchemy.org/en/rel_1_0/dialects/oracle.html#returning-support
> regarding this. While I still want to know how you're getting exactly
> this kind of error since it would be nice to anticipate it better, the
> section here illustrates the implicit_returnining=False flag that would
> resolve these errors you're getting.
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> 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.
from track_changes import Tracked
import sqlalchemy as sa
def before_shift_employee_change_commit(cls, deleted):
if deleted:
cls.field4 = False
class ShiftEmployee(Base, Tracked):
__tablename__ = "t_shift_employee"
__table_args__ = (sa.UniqueConstraint('field1', 'field2', 'field3'),)
__track__ = {
'keep': ['field1', 'field2', 'field3'],
'cols': ['field4'],
'before_commit': before_shift_employee_change_commit
}
id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' % __tablename__), nullable=False, primary_key=True)
field1 = sa.Column(sa.BigInteger, nullable=False)
field2 = sa.Column(sa.BigInteger, nullable=False)
field3 = sa.Column(sa.Date, nullable=False)
field4 = sa.Column(sa.Boolean, nullable=False)
@classmethod
def get(cls, field1=None, field3=None, field2=None, **kwargs):
try: field3 = field3.date()
except: pass
kwargs = {k:v for k,v in kwargs.items() if hasattr(ShiftEmployee, k)}
try:
item = cls._get_from_db(field1, field3, field2)
updated = False
except sa.orm.exc.NoResultFound:
try: # create and add a new item
updated = True
item = cls(field1=field1, field3=field3, field2=field2, **kwargs)
session.add(item)
session.flush()
except sa.exc.IntegrityError:
session.rollback()
try: item = cls._get_from_db(field1, field3, field2)
except: raise
except: # something else happened
session.rollback()
raise
if not updated:
for k, v in kwargs.items():
if getattr(item, k) != v:
setattr(item, k, v)
return item
@classmethod
def _get_from_db(cls, field1=None, field3=None, field2=None):
return session.query(cls).\
filter(cls.field1 == field1).\
filter(cls.field3 == field3).\
filter(cls.field2 == field2).\
one()
"""
Tracked mixin class and other utilities.
Generated from the Version mixin (http://docs.sqlalchemy.org/en/latest/_modules/examples/versioned_history/history_meta.html)
"""
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import mapper, attributes, object_mapper
from sqlalchemy.orm.exc import UnmappedColumnError
from sqlalchemy.orm.properties import RelationshipProperty
from sqlalchemy import Table, Column, ForeignKeyConstraint, Integer, DateTime, BigInteger, SmallInteger, Sequence
from sqlalchemy import event, util
import datetime
def col_references_table(col, table):
for fk in col.foreign_keys:
if fk.references(table):
return True
return False
def _is_tracked_col(col, table, type='cols'):
try: tracked_cols = table.__track__[type]
except: tracked_cols = []
return "track_meta" not in col.info and col.key in tracked_cols
def _history_mapper(local_mapper):
cls = local_mapper.class_
# set the "active_history" flag
# on column-mapped attributes so that the old version
# of the info is always loaded (currently sets it on all attributes)
for prop in local_mapper.iterate_properties:
getattr(local_mapper.class_, prop.key).impl.active_history = True
super_mapper = local_mapper.inherits
super_history_mapper = getattr(cls, '__history_mapper__', None)
polymorphic_on = None
super_fks = []
def _col_copy(col):
orig = col
col = col.copy()
orig.info['history_copy'] = col
col.unique = False
col.default = col.server_default = None
return col
properties = util.OrderedDict()
if not super_mapper or local_mapper.local_table is not super_mapper.local_table:
cols = []
track_meta = {"track_meta": True} # add column.info to identify columns specific to tracked changes
for column in local_mapper.local_table.c:
if not _is_tracked_col(column, cls) and not _is_tracked_col(column, cls, 'keep'):
continue
col = _col_copy(column)
if super_mapper and col_references_table(column, super_mapper.local_table):
super_fks.append(
(col.key, list(super_history_mapper.local_table.primary_key)[0])
)
cols.append(col)
if column is local_mapper.polymorphic_on:
polymorphic_on = col
orig_prop = local_mapper.get_property_by_column(column)
# carry over column re-mappings
if len(orig_prop.columns) > 1 or orig_prop.columns[0].key != orig_prop.key:
properties[orig_prop.key] = tuple(col.info['history_copy'] for col in orig_prop.columns)
if super_mapper and 'changed_by_fk' in cls.__track__:
try: super_fks.append(('changed_by', cls.__track__['changed_by_fk']))
except: pass
__tablename__ = local_mapper.local_table.name + '_change'
# "id" stores a unique change ID. This column is required.
cols.insert(0, Column('id', BigInteger, Sequence('%s_id_seq' % __tablename__), primary_key=True, info=track_meta))
# "changed_at" column stores the UTC timestamp of when the
# change was made.
# This column is optional and can be omitted.
cols.append(Column('changed_at', DateTime, default=datetime.datetime.now, info=track_meta))
# "changed_by" column stores the user id who
# was responsible for this change row.
# This column is optional and can be omitted.
cols.append(Column('changed_by', BigInteger, info=track_meta))
if super_fks:
cols.append(ForeignKeyConstraint(*zip(*super_fks)))
table = Table(
__tablename__,
local_mapper.local_table.metadata,
*cols,
schema=local_mapper.local_table.schema
)
else:
# single table inheritance. take any additional columns that may have
# been added and add them to the history table.
for column in local_mapper.local_table.c:
if column.key not in super_history_mapper.local_table.c:
col = _col_copy(column)
super_history_mapper.local_table.append_column(col)
table = None
if super_history_mapper:
bases = (super_history_mapper.class_,)
if table is not None:
properties['changed_at'] = (
(table.c.changed_at, ) +
tuple(super_history_mapper.attrs.changed_at.columns)
)
else:
bases = local_mapper.base_mapper.class_.__bases__
tracked_cls = type.__new__(type, "%sChange" % cls.__name__, bases, {})
m = mapper(
tracked_cls,
table,
inherits=super_history_mapper,
polymorphic_on=polymorphic_on,
polymorphic_identity=local_mapper.polymorphic_identity,
properties=properties
)
cls.__history_mapper__ = m
class Tracked(object):
@declared_attr
def __mapper_cls__(cls):
def map(cls, *arg, **kw):
mp = mapper(cls, *arg, **kw)
_history_mapper(mp)
return mp
return map
def tracked_objects(iter):
for obj in iter:
if hasattr(obj, '__history_mapper__'):
yield obj
def track_change(obj, session, user_id, deleted=False):
obj_mapper = object_mapper(obj)
history_mapper = obj.__history_mapper__
history_cls = history_mapper.class_
attr = {}
obj_changed = False
for om, hm in zip(obj_mapper.iterate_to_root(), history_mapper.iterate_to_root()):
if hm.single:
continue
for hist_col in hm.local_table.c:
track = _is_tracked_col(hist_col, obj)
if not track and not _is_tracked_col(hist_col, obj, 'keep'):
continue
obj_col = om.local_table.c[hist_col.key]
# get the value of the
# attribute based on the MapperProperty related to the
# mapped column. this will allow usage of MapperProperties
# that have a different keyname than that of the mapped column.
try:
prop = obj_mapper.get_property_by_column(obj_col)
except UnmappedColumnError:
# in the case of single table inheritance, there may be
# columns on the mapped table intended for the subclass only.
# the "unmapped" status of the subclass column on the
# base class is a feature of the declarative module.
continue
# expired object attributes and also deferred cols might not
# be in the dict. force it to load no matter what by
# using getattr().
old_value = getattr(obj, prop.key)
try: default_value = obj_col.default.arg
except: default_value = None
# A 3-tuple of added, unchanged and deleted values
a, u, d = attributes.get_history(obj, prop.key)
attr[hist_col.key] = old_value
if track:
if (a and default_value != a[0]) or (d and default_value != d[0]) or (u and old_value != u[0]):
obj_changed = True
if not obj_changed:
# not changed, but we have relationships. OK
# check those too
for prop in obj_mapper.iterate_properties:
if isinstance(prop, RelationshipProperty) and \
attributes.get_history(
obj, prop.key,
passive=attributes.PASSIVE_NO_INITIALIZE).has_changes():
for p in prop.local_columns:
if p.foreign_keys:
obj_changed = True
break
if obj_changed is True:
break
if not obj_changed:
return
attr['changed_by'] = user_id
hist = history_cls()
for key, value in attr.items():
setattr(hist, key, value)
try: obj.__track__['before_commit'](hist, deleted)
except: raise
session.add(hist)
def tracked_session(session):
@event.listens_for(session, 'before_flush')
def before_flush(session, flush_context, instances):
user_id = getattr(session, 'user_id', None)
for obj in tracked_objects(session.new):
track_change(obj, session, user_id)
for obj in tracked_objects(session.dirty):
track_change(obj, session, user_id)
for obj in tracked_objects(session.deleted):
track_change(obj, session, user_id, deleted=True)
return session
