sqlalchemy: how to add a table row with autoincrement in mysql

Question

i am using sqlalchemy 0.8 with mysql 5.5

I have a simple table whose ORM definition looks like this

class TrackingTable(db.Model):

    __tablename__ = 'tracking_table'

    trackid = db.Column(db.BigInteger,primary_key=True)
    custid = db.Column(db.String(20), db.ForeignKey('customer.id'))
    tracktime = db.Column(db.DateTime ,nullable=False)
    formdata = db.Column(db.String(100),nullable=False)

I am assuming that ( as per the docs) trackid is the primary key with is BIGINT type hence it will get auto incremented.

But when I try to add a record in db

updateRecord = TrackingTable(custid='002',tracktime='2013-02-02',formdata='logged in')

db_session.add(updateRecord)
db_session.flush()
db_session.commit()

It gives a warning Warning: Field 'trackid' doesn't have a default value

And it always takes a value of 0 for trackid, as a result the second addition always fails with error IntegrityError: (IntegrityError) (1062, "Duplicate entry '0' for key 'PRIMARY'") 'INSERT INTO tracking_table (custid, tracktime, formdata)

Pl help me fix this issue. Ideally I would like this to be a incremented value to be handled by database but I cannot figure out how to achieve this.

Thanks in advance

Answer 1

The most like cause of this is that there is an existing table named tracking_table in the database which has a defined the primary key column with a default value of zero but without setting autoincrement, like this (some columns omitted):

CREATE TABLE `tracking_table` (
  `trackid` int(11) NOT NULL DEFAULT 0,
  `formdata` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`trackid`)
)

Because the table already exists SQLAlchemy will not attempt to create it. Attempting to write to the table will trigger the observed exception once the table contains a single row with trackid equal to zero.

There at least two ways to solve the problem:

• set auto increment on the trackid column; if there are no rows in the table, or a single row with trackid equal to zero, this will work

  ALTER TABLE tracking_table MODIFY COLUMN  trackid int(11) auto_increment
  

  if there are already rows present it's more complicated

  ALTER TABLE tracking_table DROP CONSTRAINT PRIMARY KEY;
  ALTER TABLE tracking_table ADD COLUMN id int(11) primary key auto_increment;
  ALTER TABLE tracking_table DROP COLUMN trackid;
  ALTER TABLE tracking_table CHANGE COLUMN id trackid int(11) auto_increment;
  

  if there is a foreign key relationship then it will be even trickier.

• Drop the table and recreate it, either in the database or doing using SQLALchemy's metadata methods. Again, foreign key relations will complicate matters.