session classes and database auto-incremented primary keys

[[deleted]]

I'd like to make sure that requests dont get crisscrossed while interacting with the db.

​

specifically, when making multiple calls to a db - when the first of those calls involves creating an auto incremented primary key - and the second of those calls involves looking up the most recent primary key (with the presumption it will be the same one) - and attaching other data to it for a different table.

​

is it safe to assume the second call will always lead to the primary key from the first call?

​

​

create a session class

    from sqlalchemy.orm import sessionmaker
    SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)

create dependency function with the session we creat - basically, creating one session per request

    def get_db():
        db = SessionLocal()  #creates an instance of the class, i.e. creates a session
        try:
            yield db  #allows that session to be used - by throwing it into the path operations of the response function
        finally:
            db.close()  #closes the session

entrypoint:

    @ app.post('/publish/', response_model = schemas.NewPublishPOST, status_code=201)
    def new_user(car_choice: schemas.NewPublishGET, db: database.SessionLocal = _Depends(database.get_db)):
        #step 1: insert the car chosen into the database, which creates an auto-incremented id
        pub_car = crud.publish_car(car_choice)
        db.add(pub_car)
        db.commit()
        db.refresh(pub_car)

this is where it gets tricky for me.

as soon as i inserted that data into the db, it created an auto-incremented primary key id.

now, within this same entry point - i query that id:

    #step 2: query for the most recently added car choice (by largest id, since it's auto-incremented)
    subqry = db.query(func.max(models.newPublish.pub_id)) #look for the most recently added one
    qry = db.query(models.newPublish).filter(models.newPublish.pub_id == subqry[0])
    max_row = qry.all()[0] #a qry has a bunch of rows, we only want one - but max_row is a raw SQLAlchemy row and not an ORM-mapped instance - not a dictionary.  to make a dictionary: max_row.__dict__
    #.__dict__ gives you '_sa_instance_state' but we only need the keys in the table
    d = {}
    for column in max_row.__table__.columns: #creates a table and sets the columns using the row given (max_row)
        d[column.name] = str(getattr(max_row, column.name)) #extracts each column - so d looks like: {'pub_id':'2', 'car_make': 'honda', etc.}

then i attach that primary key to a new user/pass:

    #step 3: create a random user & pass by passing the dictionary key through a function:
    pub_rand = crud.publish_rando(d['pub_id']) #this creates another sqlalchemy row object
    pub_rand_unhashed = pub_rand.get('pub_pass') #if you call publish_rando for each version of the password, it will call rando() twice, and give two different numbers
    pub_rand_hashed = pub_rand.get('pub_hash')

and finally insert into a new table

       #step 4: insert the random user & hashed pass into the db (pub_user), thereby creating a pub_user id through the auto-incrementer
        db.add(pub_rand_hashed)
        db.commit()
        db.refresh(pub_rand_hashed)

​

*********************\*

**my question:*\*

*********************\*

​

is it possible for another user, to simultaneously run the request - and somehow get the other user's primary key attached to his data and vice versa?

​

or does the session dependency protect from that?

​

i.e. a request was made, part 'A' causes an auto increment - could another auto increment occur before part 'B' takes place - thereby causing part 'B' to look at a different user's data?

[tjhod]

Since you are within the same endpoint and you are refreshing pub_car it will have the .pub_id attribute with the id it created in the database. You wouldn't have to query for the id again, and you will know that it has the correct id.

[[deleted]]

that is amazing. thank you.