我最近尝试将代码从Python2.7升级到Python3.4。在
在Python2.7中,这很简单:
class Maatje(CommonColumns):
koppelvoorstellen = db.relationship("Koppelvoorstel",
primaryjoin="or_(Maatje.id == Koppelvoorstel.deelnemerID," \
+ "Maatje.id == Koppelvoorstel.vrijwilligerID)",
cascade="all,delete-orphan")
但是,使用python3.4(以及mod帴wsgi 4.4是根据python3.4从源代码编译的),当我试图查询Maatje时,我在apache2.4错误日志中得到了一个错误:
^{pr2}$对我来说,看起来像是完全有效的SQL(但是我假设\\n会变成一个换行符)。我使用phpMyAdmin(不带参数)尝试SQL查询,并填充了参数(一个),它理解了查询并得到了有效的结果。在
我尝试过以其他形式编写primaryjoin
,例如不带字符串、使用or_u和使用“|”作为or运算符。还尝试了in_
-运算符。在
我使用的是Ubuntu14.04和mod\wsgi4.4、SQLalchemy 1.0.12和Flask SQLalchemy 2.1。在
我使用的是python3-mysql.connector
(适用于MySQL Connector/Python的Ubuntu包)。在
如果我删除其中一个等式比较(不管是哪一个),并且不使用or_
-运算符,那么它不会抛出错误。但我需要同时使用or_
-运算符。在
2016年3月6日: 现在我已经把问题隔离了一点。使用python2.7运行以下代码不会产生问题:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_ECHO'] = True
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqlconnector://username:password@127.0.0.1/primaryjoindb'
db = SQLAlchemy(app)
class Coupleproposal(db.Model):
__tablename__ = 'coupleproposal'
id = db.Column(db.Integer, primary_key=True)
participantID = db.Column(db.Integer, db.ForeignKey('buddy.id'), nullable = False)
volunteerID = db.Column(db.Integer, db.ForeignKey('buddy.id'), nullable = False)
participant = db.relationship("Buddy", foreign_keys=[participantID])
volunteer = db.relationship("Buddy", foreign_keys=[volunteerID])
__table_args__ = (db.UniqueConstraint('participantID', 'volunteerID', name='_couple_uc'),)
class Buddy(db.Model):
__tablename__ = 'buddy'
id = db.Column(db.Integer, primary_key=True)
coupleproposals = db.relationship("Coupleproposal", \
primaryjoin="(Buddy.id == Coupleproposal.participantID) | (Buddy.id == Coupleproposal.volunteerID)", \
cascade="all,delete-orphan")
db.create_all()
b1 = Buddy()
b2 = Buddy()
db.session.add(b1)
db.session.add(b2)
cp1 = Coupleproposal(participantID=1, volunteerID=1)
db.session.add(cp1)
db.session.commit()
for aBuddy in db.session.query(Buddy).all():
print(aBuddy.id)
print(aBuddy.coupleproposals)
但是使用python3.4运行代码会引发以下错误:
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%(param_1)s = coupleproposal.
volunteerID
' at line 3 [SQL: 'SELECT coupleproposal.id AS coupleproposal_id, coupleproposal.participantID
AScoupleproposal_participantID
, coupleproposal.volunteerID
AScoupleproposal_volunteerID
\nFROM coupleproposal \nWHERE %(param_1)s = coupleproposal.participantID
OR %(param_1)s = coupleproposal.volunteerID
'] [parameters: {'param_1': 1}]
如果我使用一个不同的连接器,比如pymysql,就可以了。回答如下(不是我而是SQLalchemy的回购所有者):https://bitbucket.org/zzzeek/sqlalchemy/issues/3669/primaryjoin-attribute-with-or_-operator
相关问题 更多 >
编程相关推荐