why UniqueConstraint doesn’t work in flask_sqlalchemy

I want an alternative of Django’s unique_together in flask, seems UniqueConstraint is what I’m looking for, but doesn’t work for me.

here is the example:

import os
from flask import Flask
from flask_script import Manager, Shell
from flask_sqlalchemy import SQLAlchemy

basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =
    'sqlite:///' + os.path.join(basedir, 'data.sqlite')
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

manager = Manager(app)
db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'users'
    __table_args__ = tuple(db.UniqueConstraint('name', 'address'))
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), nullable=False)
    address = db.Column(db.String(64), nullable=False)

    def __repr__(self):
        return '<User (%s, %s)>' % (self.name, self.address)


def make_shell_context():
    return dict(app=app, db=db, user=User)
manager.add_command("shell", Shell(make_context=make_shell_context))


if __name__ == '__main__':
    manager.run()

Test it:

$ python test.py shell

In [1]: db.create_all()

In [2]: u1=user(name='a', address='x'); u2=user(name='a', address='x'); 
db.session.add(u1); db.session.add(u2); db.session.commit()

In [3]: user.query.all()
Out[3]: [<User (a, x)>, <User (a, x)>]

I also tried with:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), nullable=False)
    address = db.Column(db.String(64), nullable=False)
    db.UniqueConstraint('name', 'address')

not work either, what’s wrong with it?

Answer

An instance of UniqueConstraint is iterable and in this case seems to stop iteration immediately, so

tuple(db.UniqueConstraint('name', 'address'))

results in an empty tuple, when you wanted a tuple that contains 1 item, the constraint instance. Use

__table_args__ = (db.UniqueConstraint('name', 'address'), )

or any other variation instead. As to why the latter form does not work, you must apply table-level constraint objects using __table_args__ in declarative, if using names, or there will be no connection between the constraint construct and the Table backing the declarative model. If using Column objects the constraint can be created just about where ever, the connection is made through the Column object to the Table. There is also a third option: Table.append_constraint() method, which you can access through the model class’ __table__ attribute.