- changed status to resolved
primaryjoin attribute with or_-operator does not work in Python 3.4 but works in Python 2.7
The following code works using Python 2.7 but when executed with Python 3.4 it throws an error:
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)
This is the error: 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 AS coupleproposal_participantID, coupleproposal.volunteerID AS coupleproposal_volunteerID \nFROM coupleproposal \nWHERE %(param_1)s = coupleproposal.participantID OR %(param_1)s = coupleproposal.volunteerID'] [parameters: {'param_1': 1}]
I'm using SQLAlchemy 1.0.12.
The SQL though seems perfectly valid to me?
Comments (2)
-
repo owner -
reporter Thanks very much! I've tried pymysql and it works!
- Log in to comment
works fine with pymysql. It's likely a bug in mysqlconnector and I'd advise using pymysql or mysqlclient which are much more widely used (mysqlconnector refuses to publish on Pypy and Oracle does not do a very good job supporting it).