Sqlalchemy is much slower when primary key is not specified

Viewed 641

I am using SQLALchemy version 1.2.0b1

The table I have looks like this

class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True, autoincrement=True)
    cik = Column(String(10), nullable=False, index=True, unique=True)
    name = Column(String(71), nullable=False)

When I am inserting new values into the table and I DO specify the id

company=Company()
company.id =counter
company.cik = ...
company.name = ...

the program runs very fast. The insert code issued by the Sqlalchemy to the server is bulk insert.

If I skip on id to rely on db to generate unique id

company=Company()
company.cik = ...
company.name = ...

The code becomes as slow as proton decay and the echo shows that SQLalchemy issues insert statement for every single company item. No bulk insert.

Is there way to avoid this type of behavior and rely on the database to generate ids?

1 Answers
Related