CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
user3078629
user3078629

Reputation: 149

How to prevent UUID primary key for new SQLAlchemy objects being created with the same value

I have a for loop that creates a new "row" object, populates the attributes with data before committing the object to a table in a Postgres database. The table (and therefore object) I'm inserting into takes a primary key of UUID, only this value remains the same after the first iteration of the loop in all newly created row objects after the first commit.

I'm a bit lost for a solution, however I think it may have something to do with the way I'm handling the database session. In writing this I have also noticed I'm using the same variable name (new_user) in both the invite_users and invite_user function. While Python would deem them to be in different scopes (I think), I'm wondering whether the SQLALchemy session would?

Note that I've cut out a lot of code that I think is superfluous in the context of the problem - mainly more columns etc. Also the invite_user function is used elsewhere hence invite_users is just for bulk "inviting".

Here is a snippet showing the start of the table class definition and the Column definition:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects import postgresql
import uuid

Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id = Column(postgresql.UUID(as_uuid=True), default=uuid.uuid4(), primary_key=True)
    email = Column(String, unique=True)

Here is the function I use to create and destroy sessions: 

from contextlib import contextmanager
from sqlalchemy import create_engine

@contextmanager
def db_session(db_url):
    engine = create_engine(db_url, convert_unicode=True)
    connection = engine.connect()
    db_session = scoped_session(sessionmaker(autocommit=False, autoflush=True, bind=engine))
    yield db_session
    db_session.close()
    connection.close()

Here is the part of a function that iterates through a JSON array of objects:

def invite_users(json_dict):
    exceptions = {}

    with db_session(environ['CONNECTION_STRING']) as session:
        for new_user in json_dict['users']:
            try:
                invite_user(
                    session,
                    info['email_address']
                )
            # I'm catching exceptions and storing them to handle them later
            except Exception as e:
                exceptions[user_info['email_address']] = e
                pass

Here is the invite_user function that adds the row to the session and attempts to commit it:

from project.database import * # this contains the User table class above
from project.exceptions import *

def invite_user(session, email):

    new_user = User(
        email=email
    )

    session.add(new_user)

    try:
        session.commit()
    except exc.IntegrityError as e:
        session.rollback()
        raise DuplicateViolation(f"User already invited") from None

So I loop through the email addresses in the dict (json_dict['emails']). I then pass each email to invite user along with the current database session. I do this to avoid creating a session per invite_user call as it seems more sensible from a performance perspective than creating a new session in the invite_user function, as that will result in a lot of them being created and destroyed.

I thought that my Column definition would sufficiently handle the generation of new UUIDs with each commit of a User row object. However, if I pass the invite_users function multiple email addresses, the first user gets added with a fresh UUID and the second user gets assigned the same UUID. If I pass it one email address everything is fine.

I don't want to rely on querying the database for existing rows. I'm relying entirely on database constraints to prevent duplication, with exception handling used to report errors back to the user.

Upvotes: 4

Views: 6358

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55599

The sqlalchemy docs for Column's default argument state:

A scalar, Python callable, or ColumnElement expression representing the default value for this column, which will be invoked upon insert if this column is otherwise not specified in the VALUES clause of the insert.

So rather than providing uuid.uuid4(), which will create a scalar(constant) value, provide only the callable uuid.uuid4, so that it is invoked for each insert.

There is further discussion of defaulting values in Column Insert/Update Defaults

Upvotes: 10

Related Questions