Twisted - Using a Deferred for another sql query

Question

I've got a twisted based network application. Now I would like to implement a new database design but I''m getting stuck with the Deferred object.

I write sessions into my database by using this two functions:

def createSession(self, peerIP, peerPort, hostIP, hostPort):
    SessionUUID = uuid.uuid1().hex
    yield self.writeSession(SessionUUID, peerIP, hostIP)

    sid = self.db.runQuery("SELECT id FROM sessions WHERE sid = %s",
                           (SessionUUID,))

    yield sid

@defer.inlineCallbacks
def writeSession(self, SessionUUID, peerIP, hostIP):
    sensorname = self.getSensor() or hostIP

    r = yield self.db.runQuery("SELECT id FROM sensors WHERE ip = %s",
                               (sensorname,))

    if r:
        id = r[0][0]
    else:
        yield self.db.runQuery("INSERT INTO sensors (ip) VALUES (%s)",
                               (sensorname,))
        r = yield self.db.runQuery("""SELECT LAST_INSERT_ID()""")
        id = int(r[0][0])

    self.simpleQuery(
        """
        INSERT INTO sessions (sid, starttime, sensor, ip)
        VALUES (%s, FROM_UNIXTIME(%s), %s, %s)
        """,
        (SessionUUID, self.nowUnix(), id, peerIP))

In short words: createSession creates an UUID for the session and calls writeSession to write this into my db. After this is written I try to select the ID of the last insert by using the UUID in the where statement and return the result.

Now my problem. To update the session information I call this function:

def handleConnectionLost(self, sid, args):
self.simpleQuery("UPDATE sessions SET endtime = now() WHERE sid = %s",
                 (sid))

As you can see I try to use the sid from createSession which is an Deferred object and not an integer. If I got this right and I add a Callback to handleConnectionLost it will run the query at this time so that I can use the value here. But this is not my only function where I need the sid. So it would be an overhead when I do every time a callback when I need the sid.

Is there a way that I can give my sid as an integer to my functions? So that I'm just running the query one time? How does it have to look like?
When I'm using a Deferred query with a now() statement. Will it use now() when I added this query to my Callbacks or will it use now() when the query is fired?

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

You can immediately get the ID after inserting a new row for later use, similar question was answered here: The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?
it will use now() when the query is fired

edited May 23 '17 at 10:24

Community

1
1

answered Jun 03 '13 at 11:59

Lazykiddy

1,525
1
11
18

1. Cause I've got other tables where I would like to use the sid as an foreign key where the UUID whould be a little bit nasty. This was the easiest part as an good example what I'm doing or what it should doing. 2. answered, thx :) – Denny Crane Jun 03 '13 at 12:08
That's true but I've still got the Problem how to return the result of this query. ;) – Denny Crane Jun 03 '13 at 13:58

Twisted - Using a Deferred for another sql query

1 Answers1