我最近尝试将代码从Python 2.7升级到Python 3.4。

在Python 2.7中,这很简单:

class Maatje(CommonColumns):
    koppelvoorstellen = db.relationship("Koppelvoorstel",
                        primaryjoin="or_(Maatje.id == Koppelvoorstel.deelnemerID," \
                        + "Maatje.id == Koppelvoorstel.vrijwilligerID)",
                        cascade="all,delete-orphan")


但是,使用Python 3.4(以及针对Python 3.4从源代码编译的mod_wsgi 4.4)在我的Apache 2.4错误日志中尝试查询Maatje时出现错误:

[wsgi:error] [pid 6169] 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 =
koppelvoorstel.`vrijwilligerID`' at line 3 [SQL: 'SELECT
koppelvoorstel.id AS koppelvoorstel_id, koppelvoorstel.aangemaakt AS
koppelvoorstel_aangemaakt, koppelvoorstel.gewijzigd AS
koppelvoorstel_gewijzigd, koppelvoorstel.etag AS koppelvoorstel_etag,
koppelvoorstel.`deelnemerID` AS `koppelvoorstel_deelnemerID`,
koppelvoorstel.`vrijwilligerID` AS `koppelvoorstel_vrijwilligerID`,
koppelvoorstel.akkoordvrijwilliger AS
koppelvoorstel_akkoordvrijwilliger, koppelvoorstel.akkoorddeelnemer AS
koppelvoorstel_akkoorddeelnemer,
koppelvoorstel.redenafwijzingvrijwilliger AS
koppelvoorstel_redenafwijzingvrijwilliger,
koppelvoorstel.redenafwijzingdeelnemer AS
koppelvoorstel_redenafwijzingdeelnemer, koppelvoorstel.stopdatum AS
koppelvoorstel_stopdatum, koppelvoorstel.redenstoppenvrijwilliger AS
koppelvoorstel_redenstoppenvrijwilliger,
koppelvoorstel.redenstoppendeelnemer AS
koppelvoorstel_redenstoppendeelnemer \\nFROM koppelvoorstel \\nWHERE %
(param_1)s = koppelvoorstel.`deelnemerID` OR %(param_1)s =
koppelvoorstel.`vrijwilligerID`'] [parameters: {'param_1': 1}]


对我来说似乎是完全有效的SQL(不过我假设\\ n会成为换行符)。我尝试使用phpMyAdmin(不带\ n)并使用参数(一个)填充SQL查询,它可以理解查询并得出有效的结果。

我尝试用其他形式写primaryjoin,例如不带字符串,带有or_和带有“ |”作为或运算符。还尝试了in_运算符。

我正在使用带有mod_wsgi 4.4,SQLalchemy 1.0.12和Flask-SQLalchemy 2.1的Ubuntu 14.04。

我正在使用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):您的SQL语法有错误;请查看与您的MySQL服务器版本相对应的手册以获取正确的语法,以在第3行[SQL:'SELECT coupleproposal.id AS coupleproposal_id,coupleproposal。volunteerID AS participantID,coupleproposal。coupleproposal_participantID AS volunteerID \ nFROM coupleproposal \ nWHERE%(param_1)s = coupleproposal。coupleproposal_volunteerID OR%(param_1)s = coupleproposal。participantID'] [参数:{'param_1' :1}]

最佳答案

如果我使用其他连接器(例如pymysql),则可以使用。在这里回答(不是我本人,而是SQLalchemy的仓库所有者):https://bitbucket.org/zzzeek/sqlalchemy/issues/3669/primaryjoin-attribute-with-or_-operator

关于python - SQLalchemy primaryjoin属性在Python 3.4中不起作用(使用Python 2.7工作),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/35814911/

10-09 08:25
查看更多