Discussion:
[cx-oracle-users] cx_oracle, returning insert id
kellie hobbs
2006-03-22 20:09:17 UTC
Permalink
Hello,

I am trying to get back the id of a record after inserting it into the
table. My Oracle database has a trigger that automatically creates the
id on insert. The PL/SQL statement that works for me is:
SQL> declare id int;
2 begin
3 insert into table (field) values ('foo') returning tableid into
id;
4 dbms_output.put_line(id);
5 end;
6 /

How can I run this through cx_oracle and get the id stored in a
variable so I can use it?

Many thanks.

Kellie Hobbs, UC Berkeley

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Anthony Tuininga
2006-03-22 20:45:45 UTC
Permalink
Sure. Its quite straightforward but not covered by the DB API.

# this creates the bind variable for use by Oracle
idVar = cursor.var(cx_Oracle.NUMBER)

# execute the statement exactly as you normally would,
# binding the variable you just created above
cursor.execute("""
insert into SomeTable (
SomeOtherColumn
) values (
:someValue
) returning Id
into :id""",
id = idVar,
someValue = "SomeValue")

# get the value after the statement is executed
id = idVar.getvalue()

Hope that explains things.
Post by kellie hobbs
Hello,
I am trying to get back the id of a record after inserting it into the
table. My Oracle database has a trigger that automatically creates the
SQL> declare id int;
2 begin
3 insert into table (field) values ('foo') returning tableid into
id;
4 dbms_output.put_line(id);
5 end;
6 /
How can I run this through cx_oracle and get the id stored in a
variable so I can use it?
Many thanks.
Kellie Hobbs, UC Berkeley
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
cx-oracle-users mailing list
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
kellie hobbs
2006-03-22 21:02:29 UTC
Permalink
It works! Great! Thank you.

Kellie
Post by Anthony Tuininga
Sure. Its quite straightforward but not covered by the DB API.
# this creates the bind variable for use by Oracle
idVar = cursor.var(cx_Oracle.NUMBER)
# execute the statement exactly as you normally would,
# binding the variable you just created above
cursor.execute("""
insert into SomeTable (
SomeOtherColumn
) values (
:someValue
) returning Id
into :id""",
id = idVar,
someValue = "SomeValue")
# get the value after the statement is executed
id = idVar.getvalue()
Hope that explains things.
Post by kellie hobbs
Hello,
I am trying to get back the id of a record after inserting it into
the
Post by kellie hobbs
table. My Oracle database has a trigger that automatically creates
the
Post by kellie hobbs
SQL> declare id int;
2 begin
3 insert into table (field) values ('foo') returning tableid
into
Post by kellie hobbs
id;
4 dbms_output.put_line(id);
5 end;
6 /
How can I run this through cx_oracle and get the id stored in a
variable so I can use it?
Many thanks.
Kellie Hobbs, UC Berkeley
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting
language
Post by kellie hobbs
that extends applications into web and mobile media. Attend the
live webcast
Post by kellie hobbs
and join the prime developer group breaking into this new coding
territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
Post by Anthony Tuininga
Post by kellie hobbs
_______________________________________________
cx-oracle-users mailing list
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting
language
that extends applications into web and mobile media. Attend the live
webcast
and join the prime developer group breaking into this new coding
territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
_______________________________________________
cx-oracle-users mailing list
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
kellie hobbs
2006-04-03 21:07:14 UTC
Permalink
Hello,

Thanks for your help so far. Now can you tell me how to pass the table
name and columns to be updated to the execute statement?

Thanks again.

Kellie Hobbs
Post by Anthony Tuininga
Sure. Its quite straightforward but not covered by the DB API.
# this creates the bind variable for use by Oracle
idVar = cursor.var(cx_Oracle.NUMBER)
# execute the statement exactly as you normally would,
# binding the variable you just created above
cursor.execute("""
insert into SomeTable (
SomeOtherColumn
) values (
:someValue
) returning Id
into :id""",
id = idVar,
someValue = "SomeValue")
# get the value after the statement is executed
id = idVar.getvalue()
Hope that explains things.
Post by kellie hobbs
Hello,
I am trying to get back the id of a record after inserting it into
the
Post by kellie hobbs
table. My Oracle database has a trigger that automatically creates
the
Post by kellie hobbs
SQL> declare id int;
2 begin
3 insert into table (field) values ('foo') returning tableid
into
Post by kellie hobbs
id;
4 dbms_output.put_line(id);
5 end;
6 /
How can I run this through cx_oracle and get the id stored in a
variable so I can use it?
Many thanks.
Kellie Hobbs, UC Berkeley
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting
language
Post by kellie hobbs
that extends applications into web and mobile media. Attend the
live webcast
Post by kellie hobbs
and join the prime developer group breaking into this new coding
territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
Post by Anthony Tuininga
Post by kellie hobbs
_______________________________________________
cx-oracle-users mailing list
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting
language
that extends applications into web and mobile media. Attend the live
webcast
and join the prime developer group breaking into this new coding
territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
_______________________________________________
cx-oracle-users mailing list
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Anthony Tuininga
2006-04-03 21:43:42 UTC
Permalink
You require a string like the one in the example. You cannot pass the
table name and columns to update as bind variables, if that is what
you are trying to get at. :-)
Post by kellie hobbs
Hello,
Thanks for your help so far. Now can you tell me how to pass the table
name and columns to be updated to the execute statement?
Thanks again.
Kellie Hobbs
Post by Anthony Tuininga
Sure. Its quite straightforward but not covered by the DB API.
# this creates the bind variable for use by Oracle
idVar = cursor.var(cx_Oracle.NUMBER)
# execute the statement exactly as you normally would,
# binding the variable you just created above
cursor.execute("""
insert into SomeTable (
SomeOtherColumn
) values (
:someValue
) returning Id
into :id""",
id = idVar,
someValue = "SomeValue")
# get the value after the statement is executed
id = idVar.getvalue()
Hope that explains things.
Post by kellie hobbs
Hello,
I am trying to get back the id of a record after inserting it into
the
Post by kellie hobbs
table. My Oracle database has a trigger that automatically creates
the
Post by kellie hobbs
SQL> declare id int;
2 begin
3 insert into table (field) values ('foo') returning tableid
into
Post by kellie hobbs
id;
4 dbms_output.put_line(id);
5 end;
6 /
How can I run this through cx_oracle and get the id stored in a
variable so I can use it?
Many thanks.
Kellie Hobbs, UC Berkeley
kellie hobbs
2006-04-03 21:53:34 UTC
Permalink
That was what I was trying to get at.. oh well.

Thanks.

Kellie
Post by Anthony Tuininga
You require a string like the one in the example. You cannot pass the
table name and columns to update as bind variables, if that is what
you are trying to get at. :-)
Post by kellie hobbs
Hello,
Thanks for your help so far. Now can you tell me how to pass the
table
Post by kellie hobbs
name and columns to be updated to the execute statement?
Thanks again.
Kellie Hobbs
Post by Anthony Tuininga
Sure. Its quite straightforward but not covered by the DB API.
# this creates the bind variable for use by Oracle
idVar = cursor.var(cx_Oracle.NUMBER)
# execute the statement exactly as you normally would,
# binding the variable you just created above
cursor.execute("""
insert into SomeTable (
SomeOtherColumn
) values (
:someValue
) returning Id
into :id""",
id = idVar,
someValue = "SomeValue")
# get the value after the statement is executed
id = idVar.getvalue()
Hope that explains things.
Post by kellie hobbs
Hello,
I am trying to get back the id of a record after inserting it
into
Post by kellie hobbs
Post by Anthony Tuininga
the
Post by kellie hobbs
table. My Oracle database has a trigger that automatically
creates
Post by kellie hobbs
Post by Anthony Tuininga
the
Post by kellie hobbs
SQL> declare id int;
2 begin
3 insert into table (field) values ('foo') returning tableid
into
Post by kellie hobbs
id;
4 dbms_output.put_line(id);
5 end;
6 /
How can I run this through cx_oracle and get the id stored in a
variable so I can use it?
Many thanks.
Kellie Hobbs, UC Berkeley
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting
language
that extends applications into web and mobile media. Attend the live
webcast
and join the prime developer group breaking into this new coding
territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
_______________________________________________
cx-oracle-users mailing list
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Steve Swartzlander
2006-04-03 22:51:05 UTC
Permalink
One way to get it as dynamic as you want would be to create a stored
procedure in the database that accepts the table, column, and value,
then generates the SQL as a string and runs it via EXECUTE IMMEDIATE.

Of course, you could just as easily construct the SQL string in
Python and run via execute()...

Also, if your trigger is just generating IDs, you could just make use
of the nextval property of a sequence object and generate the ID as
part of the insert, or as a separate select before the insert.

Just my $0.02...
Post by kellie hobbs
That was what I was trying to get at.. oh well.
Thanks.
Kellie
Post by Anthony Tuininga
You require a string like the one in the example. You cannot pass the
table name and columns to update as bind variables, if that is what
you are trying to get at. :-)
Post by kellie hobbs
Hello,
Thanks for your help so far. Now can you tell me how to pass the
table
Post by kellie hobbs
name and columns to be updated to the execute statement?
Thanks again.
Kellie Hobbs
Post by Anthony Tuininga
Sure. Its quite straightforward but not covered by the DB API.
# this creates the bind variable for use by Oracle
idVar = cursor.var(cx_Oracle.NUMBER)
# execute the statement exactly as you normally would,
# binding the variable you just created above
cursor.execute("""
insert into SomeTable (
SomeOtherColumn
) values (
:someValue
) returning Id
into :id""",
id = idVar,
someValue = "SomeValue")
# get the value after the statement is executed
id = idVar.getvalue()
Hope that explains things.
Post by kellie hobbs
Hello,
I am trying to get back the id of a record after inserting it
into
Post by kellie hobbs
Post by Anthony Tuininga
the
Post by kellie hobbs
table. My Oracle database has a trigger that automatically
creates
Post by kellie hobbs
Post by Anthony Tuininga
the
Post by kellie hobbs
SQL> declare id int;
2 begin
3 insert into table (field) values ('foo') returning tableid
into
Post by kellie hobbs
id;
4 dbms_output.put_line(id);
5 end;
6 /
How can I run this through cx_oracle and get the id stored in a
variable so I can use it?
Many thanks.
Kellie Hobbs, UC Berkeley
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting
language
that extends applications into web and mobile media. Attend the live
webcast
and join the prime developer group breaking into this new coding
territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
_______________________________________________
cx-oracle-users mailing list
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting
language
that extends applications into web and mobile media. Attend the
live webcast
and join the prime developer group breaking into this new coding
territory!
http://sel.as-us.falkag.net/sel?
cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
cx-oracle-users mailing list
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-------------------------------
Steve Swartzlander
***@verizon.net

Loading...