Returning incremented id from mysql query?
Hi all, I'm looking for a way to get the id generated when I insert data into a table with an auto-incremented key? I've found vague references to this, but assuming I do an insert from a python script, calling an SQL method like: container.SQL.insertSomething(name="bob") how would I get the id. Thanks very much, hope someone can help me with this. Alec Munro
On Wednesday, August 14, 2002, at 07:21 PM, Alec Munro wrote:
I'm looking for a way to get the id generated when I insert data into a table with an auto-incremented key? I've found vague references to this, but assuming I do an insert from a python script, calling an SQL method like: container.SQL.insertSomething(name="bob") how would I get the id.
Thanks very much, hope someone can help me with this.
I have a python script that I call after inserting a new record; it takes the name of the table as its parameter, named 'tcTable'. The script reads: laRes = context.lastInsertID(tcTable=tcTable) lastID = laRes[0][0] return lastID 'lastInsertID' is a Z SQL method that also takes the name of the table as a parameter. Its code is: select last_insert_id() from <dtml-var tcTable> Note that this syntax is specific to MySQL; other databases have similar calls to retrieve the last created ID. ___/ / __/ / ____/ Ed Leafe http://leafe.com/ http://foxcentral.net
You can use like this in sql(suppose it named sql_subscriptions_insert): insert into subscriptions( user_id, ... ) values ( <dtml-sqlvar user_id type=int>, ... ) </dtml-if> <dtml-var sql_delimiter> select currval('subs_subscription_id_seq') as subscription_id and in python script: subscription_id = context.sql_subscriptions_insert(request)[0]['subscription_id'] EL> On Wednesday, August 14, 2002, at 07:21 PM, Alec Munro wrote:
I'm looking for a way to get the id generated when I insert data into a table with an auto-incremented key? I've found vague references to this, but assuming I do an insert from a python script, calling an SQL method like: container.SQL.insertSomething(name="bob") how would I get the id.
Thanks very much, hope someone can help me with this.
-- Best regards, www.uralfirm.ru Dmitry Matveev mailto:matveev@uralfirm.ru
I was afraid you would say that. I know about the mysql function, but I have some concerns about it. Primarily, I want to make sure I always get the right record id, even if two users are inserting at the same time. I assume this could be done using an InnoDB table, because transactions would allow me to bundle up all the instructions and execute them as a batch (this is what transactions are good for, right?). But outside of that, what are my options to ensure that I get the right Id. Or, there may be another way around this. Basically, I want the user to fill out one form, and then different parts of that form go into different tables of the database. These tables are related by id. So if there is some way to do an insert into multiple tables at once, and set a field in one to be equal to a field being auto incremented in another, that would also do. Alec Munro -----Original Message----- From: Ed Leafe [mailto:ed@leafe.com] Sent: August 14, 2002 8:46 PM To: Alec Munro Cc: zope@zope.org Subject: Re: [Zope] Returning incremented id from mysql query? On Wednesday, August 14, 2002, at 07:21 PM, Alec Munro wrote:
I'm looking for a way to get the id generated when I insert data into a table with an auto-incremented key? I've found vague references to this, but assuming I do an insert from a python script, calling an SQL
method like: container.SQL.insertSomething(name="bob") how would I get the id.
Thanks very much, hope someone can help me with this.
I have a python script that I call after inserting a new record; it takes the name of the table as its parameter, named 'tcTable'. The script reads: laRes = context.lastInsertID(tcTable=tcTable) lastID = laRes[0][0] return lastID 'lastInsertID' is a Z SQL method that also takes the name of the table as a parameter. Its code is: select last_insert_id() from <dtml-var tcTable> Note that this syntax is specific to MySQL; other databases have similar calls to retrieve the last created ID. ___/ / __/ / ____/ Ed Leafe http://leafe.com/ http://foxcentral.net
Hmm, just did some reading on last_insert_id, and it says that it works on a per-connection basis. How are Zope's connections handled? Is it simply one connection that various SQL scripts can use when they need it? How about if a single SQL method contained multiple statements? Will these statements all be fed into the connection at once, or is there a possibility of another statement from another file sneaking in between two of them. -----Original Message----- From: Ed Leafe [mailto:ed@leafe.com] Sent: August 14, 2002 8:46 PM To: Alec Munro Cc: zope@zope.org Subject: Re: [Zope] Returning incremented id from mysql query? On Wednesday, August 14, 2002, at 07:21 PM, Alec Munro wrote:
I'm looking for a way to get the id generated when I insert data into a table with an auto-incremented key? I've found vague references to this, but assuming I do an insert from a python script, calling an SQL
method like: container.SQL.insertSomething(name="bob") how would I get the id.
Thanks very much, hope someone can help me with this.
I have a python script that I call after inserting a new record; it takes the name of the table as its parameter, named 'tcTable'. The script reads: laRes = context.lastInsertID(tcTable=tcTable) lastID = laRes[0][0] return lastID 'lastInsertID' is a Z SQL method that also takes the name of the table as a parameter. Its code is: select last_insert_id() from <dtml-var tcTable> Note that this syntax is specific to MySQL; other databases have similar calls to retrieve the last created ID. ___/ / __/ / ____/ Ed Leafe http://leafe.com/ http://foxcentral.net
Hello Alec, My experience has been that all sql statements that are executed within one request are all serialized through a single connection. Now, we use postgres instead of mysql, so there is a possiblity that your da works differently. But, we depend on this behavior for this very reason (to be able to get inserted sequence numbers). Also, at least in postgres everything works within a transaction, so we have to be working in the same connection for the duration of a request. Hope that helps, -Chris On Thursday 15 August 2002 10:22 am, Alec Munro wrote:
Hmm, just did some reading on last_insert_id, and it says that it works on a per-connection basis. How are Zope's connections handled? Is it simply one connection that various SQL scripts can use when they need it? How about if a single SQL method contained multiple statements? Will these statements all be fed into the connection at once, or is there a possibility of another statement from another file sneaking in between two of them.
-----Original Message----- From: Ed Leafe [mailto:ed@leafe.com] Sent: August 14, 2002 8:46 PM To: Alec Munro Cc: zope@zope.org Subject: Re: [Zope] Returning incremented id from mysql query?
On Wednesday, August 14, 2002, at 07:21 PM, Alec Munro wrote:
I'm looking for a way to get the id generated when I insert data into a table with an auto-incremented key? I've found vague references to this, but assuming I do an insert from a python script, calling an SQL
method like: container.SQL.insertSomething(name="bob") how would I get the id.
Thanks very much, hope someone can help me with this.
I have a python script that I call after inserting a new record; it takes the name of the table as its parameter, named 'tcTable'. The script reads:
laRes = context.lastInsertID(tcTable=tcTable) lastID = laRes[0][0] return lastID
'lastInsertID' is a Z SQL method that also takes the name of the
table as a parameter. Its code is:
select last_insert_id() from <dtml-var tcTable>
Note that this syntax is specific to MySQL; other databases have similar
calls to retrieve the last created ID.
___/ / __/ / ____/ Ed Leafe http://leafe.com/ http://foxcentral.net
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
-- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com
On Thursday, August 15, 2002, at 10:22 AM, Alec Munro wrote:
Hmm, just did some reading on last_insert_id, and it says that it works on a per-connection basis. How are Zope's connections handled? Is it simply one connection that various SQL scripts can use when they need it? How about if a single SQL method contained multiple statements? Will these statements all be fed into the connection at once, or is there a possibility of another statement from another file sneaking in between two of them.
I don't know the guts of what goes on in Zope, but AFAIK this is the only way to get the ID of a newly-inserted record. I've been using it for years now without a problem, and this is how every source I've ever read says to do it. And yes, a Zope process will use a single connection, and other processes won't interfere with the value returned by last_insert_id(), as it is connection-specific. And as far as multiple inserts from the same Zope instance, well, I can't imagine that they could use the same connection at the same time; there must be some sort of internal queueing going on there. ___/ / __/ / ____/ Ed Leafe http://leafe.com/ http://foxcentral.net
participants (4)
-
Alec Munro -
Chris Kratz -
Dmitry Matveev -
Ed Leafe