bobthemac bobthemac - 1 year ago 129
MySQL Question

Flask-peewee to Flask-sqlalchmey - Operand should contain 1 column(s)

I am having an issue with this error Operand should contain 1 column(s), I get that is about their being two fields in the subquery but from my code and using flask-sqlalchemy I can't work out what is going wrong. I am converting my application from flask-peewee to flask-sqlalchemy and it is this one issue that I can't work out.

Here it the main query code the first one is my new sql-alchemy query and the other is the peewee one.

return UserBeer.query.filter(or_( < self.following(), UserBeer.username == self)).order_by(UserBeer.tried_date.desc()).limit(5)

//Peewee Query
return, UserBeer).join(Beer).where(
(UserBeer.username << self.following()) |
(UserBeer.username == self)).order_by(UserBeer.tried_date.desc()).limit(5)

The part of the query that is causing the issue is the call to
if I remove that the sqlalchemy query works here is the code for that query bellow is the contents of

// SQLAlchmey
return Relationship.query.filter(Relationship.from_user == self)
return (, on=Relationship.to_user).where(Relationship.from_user == self))

I know the second query I am asking for two different things but it seems like you declare the relationships in the models in SQLAlchemy here is the relationship model I think it is right but not sure. Also my user model not sure if there is something missing.

class Relationship(db.Model):
id = db.Column(db.Integer, primary_key=True)
from_user_id = db.Column(db.Integer, db.ForeignKey(''))
to_user_id = db.Column(db.Integer, db.ForeignKey(''))
from_user = db.relationship('User', backref=db.backref('relationships', lazy='joined'), foreign_keys=from_user_id)
to_user = db.relationship('User', backref=db.backref('related_to', lazy='joined'), foreign_keys=to_user_id)

class User(UserMixin, db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(), unique=True)
email = db.Column(db.String(), unique=True)
bio = db.Column(db.String(160))
password = db.Column(db.String(200))
joined_at = db.Column(db.DateTime(),
is_admin = db.Column(db.Boolean(), default=False)
roles = db.relationship('Role', secondary=roles_users,
backref=db.backref('users', lazy='dynamic'))

I thought the whole error might be useful when I was looking it over again

OperationalError: (_mysql_exceptions.OperationalError) (1241, 'Operand should contain 1 column(s)') [SQL: u'SELECT AS user_beer_id, user_beer.username_id AS user_beer_username_id, user_beer.beer_id AS user_beer_beer_id, user_beer.rating AS user_beer_rating, user_beer.description AS user_beer_description, user_beer.recommend AS user_beer_recommend, user_beer.tried_date AS user_beer_tried_date \nFROM user_beer \nWHERE < (SELECT AS relationship_id, relationship.from_user_id AS relationship_from_user_id, relationship.to_user_id AS relationship_to_user_id \nFROM relationship \nWHERE %s = relationship.from_user_id) OR %s = user_beer.username_id ORDER BY user_beer.tried_date DESC \n LIMIT %s'] [parameters: (1L, 1L, 5)]

Answer Source

OperationalError: (_mysql_exceptions.OperationalError) (1241, 'Operand should contain 1 column(s)')

tells you that you're trying to compare a list of columns to one user id column.

return UserBeer.query.filter(or_( < self.following(), UserBeer.username == self)).order_by(UserBeer.tried_date.desc()).limit(5)

in the code above is one column value and self.following() is multiple column value.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download