Using UniqueConstraint or unique=True

[Paradox]

I am trying to ensure that my table doesn't allow duplicate rows.

The table is defined (in SqlAlchemy 0.8):

class User(Base):
__tablename__ = 'user'

id = Column(Integer, primary_key=True)
lname = Column(String)
fname = Column(String)
email = Column(String)
dateentered = Column(DateTime, default=datetime.now())

UniqueConstraint('lname','fname',name='full_name')

~~~~~ etc. ~~~~

I have tried with using the above and with adding the unique constraint
to the relevant field definitions. Using the unique constraint on
individual fields makes a table that requires both the lname and the
fname field to be unique. Using the code above there seems to be no
constraints whatsoever, a session.commit() simply adds the duplicate
rows. What I am going for is something like this (using an sqlite3
database):

CREATE TABLE user (lname string, fname string, email string,
unique(lname, fname) ON CONFLICT REPLACE);

This will allow me to add multiple rows with the same lname as long as
the fnames are different for each. Is there a way to define such unique
constraints in SqlAlchemy?

thomas

[Simon King]

You need to put your UniqueConstraint in the __table_args__ class
attribute for SQLAlchemy to see it, something like this:

class User(Base):
__tablename__ = 'user'

__table_args__ = (
UniqueConstraint('lname', 'fname', name='full_name'),
)

See the examples at
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#table-configuration.
Note that __table_args__ here is a 1-element tuple - the comma on the
end of the line is important.

Hope that helps,

Simon

[Paradox]

Simon,

Thanks so much, that fixed it. I appreciate the links too, I was
following the below and it hadn't occurred to me that it was a different
situation (i.e. defining the table using MetaData rather than using the
class definition).

http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html?highlight=uniqueconstraint#sqlalchemy.schema.UniqueConstraint

thomas