使用关联表时查询导致提交

Question

谁能帮助我理解以下行为？我有一个简单的架构，其中项目有许多资产和元素。资产和元素在项目范围内具有多对多关系。

我需要确保元素具有在项目范围内唯一的代码，因此在将新元素添加到 SQLAlchemy 会话之前，我想检查项目中是否已存在具有相同代码的元素。我发现，当我使用关联表来映射元素和资产之间的多对多关系时，如果查询操作没有实际将新元素提交到数据库，我就无法查询数据库。

需要明确的是，此时我还没有将此新元素添加或提交'添加到 SQLAlchemy 会话中。

from sqlalchemy import create_engine, and_
from sqlalchemy import Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relation, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy

### your credentials here ###
# _MYSQL_DB = 
# _MYSQL_USER = 
# _MYSQL_PWORD = 
# _MYSQL_SCHEMATA = 
# _MYSQL_TEST_SCHEMATA = 

testEngine = create_engine("mysql://%s:%s@%s/%s"%(  _MYSQL_USER,
                                                    _MYSQL_PWORD,
                                                    _MYSQL_DB,
                                                    _MYSQL_TEST_SCHEMATA),
                                                    pool_recycle=3600,
                                                    echo=True)

Base = declarative_base()
SQLAlchemySession = sessionmaker(autoflush=True, autocommit=False)

class Project(Base):
    __tablename__ = "SA_project_t"
    id = Column(Integer, primary_key=True, autoincrement=True)
    code = Column(String(24), nullable=False, unique=True)

class Asset(Base):
    __tablename__ = "SA_asset_t"
    id = Column(Integer, primary_key=True, autoincrement=True)
    projectId = Column(Integer, ForeignKey("SA_project_t.id"))
    project = relation("Project")
    code = Column(String(128))

element_to_asset_assoc = Table( "SA_elementToAssetAssoc_t",
                                Base.metadata,
                                Column("elementId", Integer, ForeignKey("SA_element_t.id")),
                                Column("assetId", Integer, ForeignKey("SA_asset_t.id")))

class Element(Base):
    __tablename__ = "SA_element_t"
    id = Column(Integer, primary_key=True, autoincrement=True)
    projectId = Column(Integer, ForeignKey("SA_project_t.id"))
    project = relation("Project")
    code = Column(String(256))
    assets = relation("Asset", secondary=element_to_asset_assoc, backref="elements")


Base.metadata.bind=testEngine
session = SQLAlchemySession(bind=testEngine)

Base.metadata.drop_all(checkfirst=True)
Base.metadata.create_all(testEngine, checkfirst=True)

# Create a Project, Asset and Element

project = Project()
project.code = "MyProject"
session.add(project)

asset = Asset()
asset.project = project
asset.code = "MyAsset"
session.add(asset)

element = Element()
element.project = project
element.code = "MyElement"
element.assets = [asset]
session.add(element)

session.commit()

# Now I'd like to add a new element, but first check that the 
# element's code is unique within the scope of the Project

newElement = Element()
newElement.project = project
newElement.code = "MyElement"
newElement.assets = [asset]

results = session.query(Element).filter(and_( Element.project==newElement.project,
                                            Element.code==newElement.code))

# Up until this point, newElement hasn't been inserted into the 
# database, but once I query "results.count()" I find that an INSERT
# has been perfomed.  

print results.count()

# p.s. I realize that results will contain both the original and 
# new element, but I don't expect the new element to have been
# inserted into the database at this point.

对 results 变量调用 count() 的操作是将 newElement 提交到 MySQL 数据库，这不是我的本意。如果我删除关联表，那么我会得到我期望的行为 - newElement 不会添加到数据库中。

从日志中可以看出，会话在查询开始时将关联的资源视为脏资源，并且刷新此资源会触发 newElement 的提交。这是预期的行为吗？如果是这样，有没有办法保持多对多关系，同时在这些情况下仍然查询数据库并且不触发提交。

使用 SQLAlchemy 0.7.4

Answer 1

无需 add() newElement 对象，因为当您将绑定对象分配给其关系之一时，它会自动添加到会话中。当访问 newElement.project 属性时，它会刷新到数据库（您可以使用 autoflush=False 禁用此行为）。 MySQL默认使用MyISAM引擎，该引擎会忽略事务语句。因此，所有刷新的更改都会持续存在。要启用事务支持，请将 __table_args__ = {'mysql_engine': 'InnoDB'} 添加到映射的类中。