SQLAlchemy with multiple primary keys does not automatically set any

Question

I had a simple table:

class test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    title = Column(String)

    def __init__(self, title):
        self.title = title

When using this table, id was set automatically. I want to add another field that is unique and efficient to search, so I added the field:

id2 = Column(String, primary_key=True)

And updated the constructor:

def __init__(self, id2, title):
    self.id2 = id2
    self.title = title

Now, id is no longer automatically set, or rather I get the error:

IntegrityError: (IntegrityError) test.id may not be NULL u'INSERT INTO test (id2, title) VALUES (?, ?)' [u'a', u'b']

Is there a way to maintain a second primary key without removing the autoincrement behavior of the first?

Answer 1

I have few problems here

1) What is a purpose of your hand-made __init__? If it does just what you wrote, you can omit constructor completely since SQLAlchemy machinery generates exactly the same constructor for all your models automagically. Although if you take some additional actions and thus have to override __init__ you probably want to call super-constructor:

def __init__(self, lalala, *args, **kwargs):
   # do something with lalala here...
   super(test, self).__init__(*args, **kwargs)
   # ...or here

2) Once you have more than one field with primary_key=True you get a model with composite primary key. Composite primary keys are not generated automatically since there is ambiguity here: how should subsequent key differ from previous?

I suspect what you're trying can be achieved using unique indexed column and not using composite key:

class test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    id2 = Column(String, index=True, unique=True)
    title = Column(String)

    # def __init__(self) is not necessary

Answer 2

I suspect the OP is simply following along the SQLAlchemy tutorials, as I have been, and has run into this problem. I have code similar to your proposed solution (that does not work):

class System(base):
  """
  Object class representing a system on which we run tools
  against
  """
  __tablename__= 'systems'
  __table_args__= {'mysql_engine':'InnoDB'}
  system_id= Column('system_id', Integer, primary_key= True)
  hostname= Column('hostname', String(200), nullable= False, unique= True)
  arch= Column('arch', String(100))
  os= Column('os', String(100))
  description= Column('description', String(100))

  def __init__(self, hostname, arch=None, os=None):
    self.hostname= hostname
    self.arch= arch
    self.os= os

  def __repr__(self):
    return "" % (self.hostname, self.arch, self.os)

I need the custom constructor to work because I want to keep hostname unique in the table but obviously want the sequence (autoincrement) ID column to be automatically maintained. I don't want to construct the System object by speciying the ID...

Help?

Cheers,

jay