Hi all,
I am getting some unexpected behaviour when trying to update selected rows
from a query when using joint table inheritance. Using MySQL, a query that
is filtered based on items in the parent table, does not honour this filter
when updating items in the child table (all children are updated, not only
those matching the filter). It is easier to describe in code than in words.
The code is attached.
In an attempt to make it easier to run, I did try and make the script use
sqlite instead of MySQL, however in this case an entirely different error
resulted. I have therefore included both (MySQL enabled, sqlite disabled by
default).
Can anyone please tell me if there is something that I am doing wrong? is
this a bug with sqlalchemy?
Thankyou,
Mark Eastwood
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
'''
Demonstrate a (bug?) in updating records via a query
====================================================
I am getting some unexpected behaviour when trying to update selected rows from
a query when using joint table inheritance. Using MySQL, a query that is
filtered based on items in the parent table, does not honour this filter when
updating items in the child table (all children are updated, not only those
matching the filter).
In an attempt to make it easier to run, I did try and make the script use
sqlite instead of MySQL, however in this case an entirely different error
resulted. I have therefore included both (MySQL enabled, sqlite disabled by
default).
With MySQL
----------
All records within the Child table get updated. What should happen is just the
one record that matches the query gets updated
With SQLite
-----------
The query raises an OperationalError
Other Debug Info
----------------
I have tried this on two different systems with the same result. There are:
System 1:
Operating system: Windows 7 64 bit
Python version = 3.3.1
SQLAlchemy version = 0.8.0
MySQL version = 5.6.13
- included mysqlconnector from windows installer (v1.0.11)
System 2:
Operating system: Windows 7 32 bit
Python v3.3.1
SQLAlchemy v0.8.2
Mariadb version = 5.5.32
MySQLConnector version = 1.0.11
To run this script, MySQL (or Mariadb) needs to be installed and a user by the
name of 'tester' with password 'test_password' needs to have access to the
database 'test'@'localhost'. Of course, you can change these.
'''
from sqlalchemy import *
from sqlalchemy.exc import DatabaseError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
###############################################################################
# Uncomment one of the following engines
###############################################################################
#engine = create_engine('sqlite://')
engine = create_engine('mysql+mysqlconnector://%s:%s@%s:%s/%s' % \
('tester', 'test_password', '127.0.0.1', '3306', 'test'))
#------------------------------------------------------------------------------
Base = declarative_base()
Base.metadata.bind = engine
session = sessionmaker(bind=engine)()
class Parent(Base):
__tablename__ = 'parent'
id = Column(INTEGER(), primary_key=True)
name = Column(VARCHAR(255), unique=True)
type = Column(VARCHAR(255))
__mapper_args__ = {'polymorphic_identity': 'Parent',
'polymorphic_on': type}
class Child(Parent):
__tablename__ = 'child'
__mapper_args__ = {'polymorphic_identity': 'Child'}
id = Column(INTEGER(), ForeignKey(Parent.id), primary_key=True)
value = Column(INTEGER(255))
def __repr__(self):
return '%s %s' % (self.name, self.value)
###############################################################################
# Start with a clean database
###############################################################################
# MySQL warns if these tables don't exist, despite using IF EXISTS
# SQLAlchemy interprets these as errors, just ignore
try:
session.execute('DROP TABLE IF EXISTS child, parent;')
except DatabaseError:
pass
###############################################################################
# Build a simple table
###############################################################################
Base.metadata.create_all()
session.add_all([Child(name='Steven', value=1),
Child(name='Mark', value=2),
Child(name='Daniel', value=3)])
session.flush()
print('We have three records in the database')
print(session.query(Child).all())
query = session.query(Child).filter(Child.name=='Daniel')
count = query.count()
print('%d records have the name "Daniel"' % count)
###############################################################################
# The bug:
###############################################################################
updated = query.update({'value': 4})
session.flush()
print('Therefore we should only be updating %d records but ' % count +\
'we have actually updated %d records' % updated)
print('So now our table now looks like this')
print(session.query(Child).all())
