Inserting record to mysql table, via sqlalchemy , with autoinc throws FlushError

Question

I am trying to insert a row for a table which has auto_increment as well as some foreign keys. All the foreign keys exist. But it throws error.

sqlalchemy.orm.exc.FlushError: Instance Stock at 0x9cf062c 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.

Even insertion of record via MySQL, by copy-paste SQL produced by echo=True, is executing.

Stock Class

class Stock(Base):
    __tablename__ = 'Stock'

    Code = Column('Code',String(8),primary_key=True)
    Symbol = Column('Symbol',String(128))
    ListingName = Column('ListingName',String(256))
    ListingDate = Column('ListingDate',DateTime())
    RecordAddedDate = Column('RecordAddedDate',DateTime())

    HomeCountry = Column('HomeCountry',ForeignKey('Country.Code'))
    PrimaryExchange = Column('PrimaryExchange',ForeignKey('Exchange.Code'))
    BaseCurrency = Column('BaseCurrency',ForeignKey('Currency.Code'))
    InstrumentType = Column('InstrumentType',ForeignKey('Instrument.InstrumentType'))

Record insertion

Engine = sqlalchemy.create_engine('mysql://user:pass@host/db',echo=True)
Session = sqlalchemy.orm.sessionmaker(bind=Engine)
SessionObj = Session()

NewStock = Stock()
NewStock.InstrumentType = 'Stock'
NewStock.Symbol = 'MSFT'
NewStock.ListingName = 'Microsoft'
NewStock.HomeCountry = 'IN'
NewStock.PrimaryExchange = 'NSEOI'
NewStock.BaseCurrency = 'INR'
NewStock.ListingDate = datetime.datetime.now().strftime("%Y%m%d")
NewStock.RecordAddedDate = datetime.datetime.now().strftime("%Y%m%d")

print NewStock

SessionObj.add(NewStock)
SessionObj.flush()

print NewStock.Code

Hint: what happens when you go to the MySQL shell and type SHOW CREATE TABLE Stock does it show AUTO_INCREMENT? — Burhan Khalid
– Burhan Khalid, Commented Sep 23, 2012 at 8:28

Burhan Khalid · Accepted Answer · 2012-09-23 08:34:01Z

1

Add autoincrement=True to your column.

answered Sep 23, 2012 at 8:34

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Burhan Khalid Over a year ago

It is not in your code; perhaps you should post the complete code you are using.

tuxuday Over a year ago

reckon autoincrement is optional, i didn't have it in original code but tried it after googling. updated with more code.

tuxuday · Accepted Answer · 2012-09-23 09:31:48Z

0

Got it. I had the column type as String, after converting to Integer worked fine.

answered Sep 23, 2012 at 9:31

tuxuday

3,04721 silver badges18 bronze badges

Collectives™ on Stack Overflow

Inserting record to mysql table, via sqlalchemy , with autoinc throws FlushError

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related