CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Xbel
Xbel

Reputation: 777

Trace insert in SQLAlchemy

I'm using flask-SQLAlchemy to write entries in a MySQL database. Those entries have a specific id field which is automatically filled by MySQL. And I would like to trace.

Let's see what I actually mean. I want to write first partially that I receive a query to my API. I write everything, but not the response, because I don't have it. Then, once I have the response, I would like to get that inserted row and write the response file:

Model:

class QueriesDB(db.Model):

    __tablename__ = 'queries'

    id_query = db.Column(INTEGER(unsigned=True), primary_key=True, nullable=False, autoincrement=True)
    id_user = db.Column(INTEGER(unsigned=True), db.ForeignKey(UsersDB.id_user), nullable=True)
    datetime = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow)
    IP = db.Column(db.String(39), nullable=False)
    header = db.Column(LONGTEXT, nullable=False)
    response = db.Column(LONGTEXT, nullable=True)
    answered = db.Column(db.Boolean, nullable=False)

How I insert it:

valid_query = QueriesDB(id_user=self.id_user, IP=self.request.remote_addr,
                        header=str(dict(self.request.headers)), answered=False)
db.session.add(valid_query)
db.session.commit()

Now I'm thinking to get that specific row by using the id_query, and write answered = True as well as the response body. But not sure what is the best way to do it since I don't actually know the id_query.

Is there a easy way to keep the id_queries while doing commit()?

This can, of course, be a design problem. What are your thoughts?

Upvotes: 0

Views: 137

Answers (1)

S.Hashiba
S.Hashiba

Reputation: 685

You should use db.session.flush(). https://docs.sqlalchemy.org/en/13/orm/session_api.html#sqlalchemy.orm.session.Session.flush

valid_query = QueriesDB(id_user=self.id_user, IP=self.request.remote_addr,
                        header=str(dict(self.request.headers)), answered=False)
db.session.add(valid_query)

db.session.flush()
# you can get autoincremented number from DB 
print(valid_query.id_query)

db.session.commit()

Upvotes: 1

Related Questions