CODEWITHSUNDEEP
pythonmysqlsqlalchemyprimary-key
user2497586
user2497586

Reputation:

Configuring MySQL to allow generation of Primary Key values

This may be a relatively simple question to answer. But, I'll include all of my code just for completeness.

I'm generating alpha-numeric primary keys for my MySQL tables using the class found here. However, when I upload a row to the database, I get this error:

FlushError: Instance <User at 0x1d47110> has a NULL identity key.  If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values.  Ensure also that this flush() is not occurring at an inappropriate time, such as within a load() event.

So, here is the code when I use to GUID code from SQLAlchemy source:

User.py

from app import db
from app.custom_db.GUID import GUID

class User(db.Model):
  __tablename__ = 'users'
  id = db.Column(GUID(), primary_key = True)
  name = db.Column(db.String(40), unique = True)
  email_stub = db.Column(db.String(30), unique = True)

  def __init__(self, name, email_stub):
    self.name = name
    self.email_stub = email_stub

  def __repr__(self):
    return '<User %r>' % self.name

When I just use db.Integer, it works fine. But, like I said I want to user alpha-numeric uuid4() primary keys. How can I make my MySQL database not complain when I do so?

Upvotes: 2

Views: 2692

Answers (1)

zzzeek
zzzeek

Reputation: 75137

you need to assign a default generation function to the primary key. Here we'll also use a simplified version of the backend-agnostic GUID type example so that we see real uuids:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import uuid

Base = declarative_base()

class GUID(TypeDecorator):
    impl = String(32)

    def process_bind_param(self, value, dialect):
        if value is not None:
            return "%.32x" % value
        else:
            return MNone

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        else:
            return uuid.UUID(value)

class User(Base):
    __tablename__ = 'users'
    id = Column(GUID(), primary_key=True, default=uuid.uuid4)
    name = Column(String(40), unique=True)

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)  # just for testing here
Base.metadata.create_all(e)

s = Session(e)

s.add_all([User(name='u1'), User(name='u2'), User(name='u3')])
s.commit()

for guid in s.query(User.id):
    print(guid)

Upvotes: 4

Related Questions