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.

Answer 2

you need to set the field value to 'AUTO_INCREMENT' and instead of passing an actual value into it pass NULL. try passing NULL first as it may already be set up as AUTO_INCREMENT.

Answer 3

cannot reproduce, works fine here:

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

Base = declarative_base()

class TrackingTable(Base):

    __tablename__ = 'tracking_table'

    trackid = Column(BigInteger, primary_key=True)
    formdata = Column(String(100), nullable=False)

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)

s = Session(e)

updateRecord = TrackingTable(formdata='logged in')

s.add(updateRecord)
s.commit()

print updateRecord.trackid

output (logging junk trimmed out):

SELECT DATABASE()
SHOW VARIABLES LIKE 'character_set%%'
SHOW VARIABLES LIKE 'sql_mode'
DESCRIBE `tracking_table`
ROLLBACK

CREATE TABLE tracking_table (
trackid BIGINT NOT NULL AUTO_INCREMENT, 
formdata VARCHAR(100) NOT NULL, 
PRIMARY KEY (trackid)
)

COMMIT
BEGIN (implicit)
INSERT INTO tracking_table (formdata) VALUES (%s)
('logged in',)
COMMIT
BEGIN (implicit)
SELECT tracking_table.trackid AS tracking_table_trackid, tracking_table.formdata AS tracking_table_formdata 
FROM tracking_table 
WHERE tracking_table.trackid = %s
(1L,)
1

Answer 4

you need to flush first your session, it then will be aware of your index position to increment from (I borrow here code sample from @zzzeek):

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class TrackingTable(Base):
    __tablename__ = 'tracking_table'
    trackid = Column(BigInteger, primary_key=True)
    formdata = Column(String(100), nullable=False)

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)
s = Session(e)
# table tracking_table already exists, flush session to get index position
s.flush()
# you can now add new record 
updateRecord = TrackingTable(formdata='logged in') 
s.add(updateRecord)
s.commit()