CODEWITHSUNDEEP
pythonmysqldatabasesqlalchemy
Mark
Mark

Reputation: 1697

SQLAlchemy Unique Object

Say I have the following class:

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    public_key = Column(String(16), nullable=False, unique=True)
    private_key = Column(String(64), nullable=False, unique=True)

I need to have both public_key and private_key be random strings (which is not an issue) but I'm not sure the best way to make sure that

  1. the strings don't already exist anywhere else
  2. between checking for their existence and creating the object, they haven't already been created by another process.

I know I can't be the first person to have come across this but I haven't been able to find much on this. It seems that SQLAlchemy doesn't actually enforce unique itself so I'm not sure what to do. Thanks.

Upvotes: 2

Views: 1331

Answers (1)

Theron Luhn
Theron Luhn

Reputation: 4082

Realize the chances of collision are very, very, very low.

Let's say your random strings are composed up uppercase letters and numerals only. That's 36 possibilities per character. The 16 characters of your public key will net you 36^16 combinations. That's about 8 with 24 0s after it. Even if you have a million keys in your database, the chances of collision when you add a new key are 10^-19.

I wouldn't worry about it. I don't, and the random values I use (UUID4, for the most part) have a much higher chance of collision than yours. (But still astronomically low.)

If you're still concerned: SQLAlchemy may not enforce UNIQUE, but your DBMS should, which means SQLAlchemy will throw an exception if the UNIQUE constraint is violated. Can catch the exception and degrade gracefully.

Upvotes: 1

Related Questions