On Tue, Jan 8, 2019 at 12:40 PM Riccardo Magliocchetti
<riccardo.magliocche...@gmail.com> wrote:
>
> Hello,
>
> I'd like to implement soft delete so I have to take into account when
> creating constraints
> the softdelete boolean column. The trick i want to implement is to get null
> for the column
> when the row is supposed to be deleted. When creating the index by hand in
> SQL it works
> just nice but in sqlalchemy i get an exception. SQLAlchemy is at 1.2.15.
>
> This is how i define the contstraint:
>
> class Foo(sa.Model):
>
> __table_args__ = (
> sa.UniqueConstraint('foobar_id', sa.func.nullif('deleted', True)),
> )
>
So you definitely can't put a string column name inside of
func.nullif, that needs to be a Column object, overall Column is
better to use for this rather than strings, so in *theory*, this would
look like:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
foobar_id = Column(Integer)
deleted = Column(Boolean)
__table_args__ = (
UniqueConstraint(foobar_id, func.nullif(deleted, True)),
)
however UniqueContraint doesn't support functions right now. Index
does, so easiest solution is to use that instead with unique=True:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
foobar_id = Column(Integer)
deleted = Column(Boolean)
__table_args__ = (
Index("my_idx", foobar_id, func.nullif(deleted, True), unique=True),
)
I'm not sure what database you are on (please specify). I can't get a
functional UNIQUE constraint to work on Postgresql, but for the index
it looks like:
CREATE UNIQUE INDEX my_idx ON a (foobar_id, nullif(deleted, true))
Per
https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index,
there is virtually no difference between these (or at least it is very
hard to tell them apart) if this is in fact PG, but perhaps you're on
SQL Server, not sure.
We can add support for functions in UniqueConstraint as well if you
want to file an enhancement ticket at
https://github.com/sqlalchemy/sqlalchemy.
If you must use UNIQUE constraint and not UNIQUE INDEX, for now you'd
have to use DDL():
event.listen(
A.__table__, "after_create",
DDL("ALTER TABLE a ADD CONSTRAINT my_uq UNIQUE (foobar_id,
nullif(deleted, true))")
)
>
> When i try to create a migration with alembic i get the following (stripped)
> stack trace:
>
> File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/schema.py",
> line 2670, in _set_parent
> self.columns.add(col)
> File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/base.py", line
> 503, in add
> "Can't add unnamed column to column collection")
> sqlalchemy.exc.ArgumentError: Can't add unnamed column to column collection
>
> Any hint on what could be wrong?
>
> Thanks in advance,
> Riccardo
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
