How to execute a raw query with returning in sqlalchemy

Question

I have a table Ticket that has the id (autoincremental), ticket_number (trigger that reads a sequence), value and date in Oracle. And I want to do the following:

INSERT INTO TICKET (value, date) values (100, TO_DATE('07-29-2015', 'mm-dd-yyyy')) returning ticket_number into :ticket_number;

I need to do this in raw SQL, but I don't know how to get the value in sqlalchemy. is it possible?

Answer 1

I've tried this with a toy table in Postgres and it works, I think should be equivalent in Oracle, please let me know.

In [15]:

result = session.connection().execute("insert into usertable values('m6', 'kk2', 'Chile') returning username")
for r in result:
    print r
(u'm6',)

Hope it helps.

EDIT for Oracle: the one way to do it that I've found is not very elegant. It would be using the raw connection underneath SQLAlchemy connection, something like:

In [15]:

from sqlalchemy.sql import text
import cx_Oracle
​
cur = session.connection().connection.cursor()
out = cur.var(cx_Oracle.STRING)
par = { "u" : out }   ​
cur.prepare("insert into usertable values('m34', 'kk2', 'Chile') returning username into :u")
cur.execute(None, par)
​
print(out)
print(type(out))
print(out.getvalue())
​
​
<cx_Oracle.STRING with value 'm34'>
<type 'cx_Oracle.STRING'>
m34

Unfortunately, I don't think there is a way to create a cx_oracle variable instance, it is just not available in the api, see docs.

Then, there is no way to avoid creating the cursor, even if it works when you delegate more to SQLAlchemy:

In [28]:

from sqlalchemy.sql import text
import cx_Oracle
​
cur = session.connection().connection.cursor()
out = cur.var(cx_Oracle.STRING)
par = { "u" : out }
​
q = text("insert into usertable values('m43', 'kk2', 'Chile') returning username into :u")
result = session.connection().execute(q, par)
print(par["u"])
print(out)
type(out)

​<cx_Oracle.STRING with value 'm43'>
<cx_Oracle.STRING with value 'm43'>
Out[28]:
cx_Oracle.STRING

Of course, you should close the cursor in this second case (in the first one, oracle closes it). The point that there is no way to create an instance like out = cx_Oracle.STRING()

As I say, it is not very elegant, but I don't think there is a way to create an equivalent variable in SQLAlchemy. It is something that the code handles internally. I would just go for the raw connection-cursor.

Hope it helps.

EDIT2: In the code above, added out.getvalue() as suggested. Thanks!