1

I have a mysql table flight as follows -

pkid       from_city            to_city
 1         Melbourne            BuenosAires
 2         Budapest             Tokyo

Then I have my sqlalchemy model as follows - 

class Flight(Base):

    __tablename__ = 'flight'

    pkid = Column('pkid', INTEGER(unsigned=True), primary_key=True)
    from_city = Column('from_city', VARCHAR(50), nullable=False)
    to_city = Column('to_city', VARCHAR(50), nullable=False)

Now in my code, I have some flights data which I want to insert into flight table and get back the primary keys for my records even before committing.

Below is my scenario

stage 1: process the raw data, data manipulations and get the list of dictionaries ready for insertion into flight table example-

records = [{'from_city': 'Amsterdam', 'to_city': 'Toronto'},
           {'from_city': 'Mumbai', 'to_city': 'Colombo'}]

stage 2: Insert the data into flight table using SQL Alchemy

stage 3: get back the list of pkids of newly inserted records in stage 2

stage 4: Insert the data into airline table where each pkid from stage 3 is a foreign key in airline table

stage 5: commit all transactions if stage 4 is successful. Else rollback stage 2 transactions as well

From sqlalchemy documentation, I believe that core insertions in 'executemany' style is the fastest way of inserting records when compared to ORM style which makes me want to use sqlalchemy core insertion option.

But by using core insertion, will I lose the ability to get primary keys of flight records in stage 3 before actually committing? or is there any way to still get the primary keys? Considering that I have large amounts of data to be inserted, If I use ORM style, I can flush and get the primary keys but then I have to compromise on performance?

I could be using Dataframe.to_sql but it comes with auto-commit feature which I don't want

Currently I'm doing this way as follows -

Stage 1

Stage2

trans = connection.begin()
connection.execute(table.insert(), records)

Not sure how to do Stage 3. Need help

Intended to do Stage 4

Intended to do Stage 5 - trans.commit() / trans.rollback() based on Stage 4

Any suggestions or hints to do Stage 3?

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Kingz
  • 1,657
  • 3
  • 18
  • 29
  • See https://stackoverflow.com/questions/1316952/sqlalchemy-flush-and-get-inserted-id. You'll need to do a `flush()` and possibly a `refresh()`, then only `commit()` at the end. You'll need to turn off autocommit for this to work. The answer I linked contains more information, and links to additional detail. – Jim Stewart Oct 20 '18 at 12:00
  • 1
    MySQL lacks something equivalent to for example Postgresql's RETURNING, but apparently with correct settings one can hack something together with `last_insert_id` and `row_count` for bulk INSERT: https://stackoverflow.com/questions/7333524/how-can-i-insert-many-rows-into-a-mysql-table-and-return-the-new-ids, https://stackoverflow.com/questions/34200805/when-i-insert-multiple-rows-into-a-mysql-table-will-the-ids-be-increment-by-1-e – Ilja Everilä Oct 21 '18 at 13:36

0 Answers0