How to do multiple query in the same connection?

[kamelzcs]

For example, after insert a row, I want to get its id, using the SELECT CURRVAL('member_id_seq'), which would require the second query be in the same connection with the first one. But I dont know how to implement that using momoko.

I tried:

cursor = yield momoko.Op(self.db.execute, 'INSERT INTO "member" ("membername") VALUES("foo") )
cursor = yield momoko.Op(self.db.execute, 'SELECT CURRVAL("member_id_seq")')

it will give such error:

OperationalError: currval of sequence "member_id_seq" is not yet defined in this session

I think the reason is the second query is not the same connection as the first insert commands.

cursor = yield momoko.Op(self.db.execute, 'INSERT INTO "member" ("membername") VALUES("foo") )
cursor.execute("SELECT CURRVAL("member_id_seq")")
cursor.fetchone()

It will give such error:

ProgrammingError: no results to fetch

The cursor.execute is asynchronous, the result is not available when the fetch* method is called.

I tried to use

yield momoko.Op(cursor.execute, "SELECT CURRVAL("member_id_seq")")

It would give the

TypeError: 'callback' is an invalid keyword argument for this function

Eventhough I know it would be fine to use the insert into ... returning id command on this specific target, but what is the general way to do multiple queries on the same connection?

[kamelzcs]

I found the Manual connection management part.

Thanks for your great library!

[haizaar]

Yes, manual connection management is the way to go.

Glad you liked Momoko.

On Fri, Nov 28, 2014, 12:18 kamelzcs notifications@github.com wrote:

Closed #85 #85.

—
Reply to this email directly or view it on GitHub
#85 (comment).

[bduhan]

FYI you can also do this in one query with INSERT ... RETURNING. Here is an example:

import logging
from tornado import ioloop, options
import momoko

options.parse_command_line()
loop = ioloop.IOLoop.instance()
db = momoko.Pool(dsn=DSN, size=3)

def res(cursor, error):
    logging.info("inserted: %r", cursor.fetchone())

def test1():
    db.execute('INSERT INTO binarydata (datetimeread) VALUES (now()) RETURNING id', callback=res)

pc = ioloop.PeriodicCallback(test1, 1000)
pc.start()

loop.start()