title: How to prevent UUID primary key for new SQLAlchemy objects being created with the same value
body: <p>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. </p>
  
  <p>I'm a bit lost for a solution, however <strong>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 (<code>new_user</code>) in both the <code>invite_users</code> and <code>invite_user</code> function. While Python would deem them to be in different scopes (I think), I'm wondering whether the SQLALchemy session would?</strong></p>
  
  <p><em>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 <code>invite_user</code> function is used elsewhere hence <code>invite_users</code> is just for bulk "inviting".</em></p>
  
  <p>Here is a snippet showing the start of the table class definition and the <code>Column</code> definition:</p>
  
  <pre class="lang-py prettyprint-override"><code>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)
  </code></pre>
  
  <p>Here is the function I use to create and destroy sessions: </p>
  
  <pre class="lang-py prettyprint-override"><code>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()
  </code></pre>
  
  <p>Here is the part of a function that iterates through a JSON array of objects:</p>
  
  <pre class="lang-py prettyprint-override"><code>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
  </code></pre>
  
  <p>Here is the <code>invite_user</code> function that adds the row to the session and attempts to commit it:</p>
  
  <pre class="lang-py prettyprint-override"><code>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
  </code></pre>
  
  <p>So I loop through the email addresses in the dict (<code>json_dict['emails']</code>). I then pass each email to invite user along with the current database <code>session</code>. I do this to avoid creating a session per <code>invite_user</code> call as it seems more sensible from a performance perspective than creating a new <code>session</code> in the <code>invite_user</code> function, as that will result in a lot of them being created and destroyed.</p>
  
  <p><strong>I thought that my <code>Column</code> definition would sufficiently handle the generation of new UUIDs with each commit of a <code>User</code> row object. However, if I pass the <code>invite_users</code> 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</strong>.</p>
  
  <p>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.</p>