如何在sqlalchemy中返回返回值执行原始查询 [英] How to execute a raw query with returning in sqlalchemy

问题描述

我有一个表 Ticket ，该表具有 id (自动递增)， ticket_number (读取序列的触发器)，值和日期在Oracle中.我想执行以下操作:

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;

我需要在原始SQL中执行此操作，但是我不知道如何在sqlalchemy中获取值.有可能吗?

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

推荐答案

我已经在Postgres中用玩具表尝试过此方法，并且它可以工作，我认为在Oracle中应该等效，请告诉我.

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',)

希望有帮助.

针对Oracle的编辑:我发现这样做的一种方法不是很好.它将使用SQLAlchemy连接下面的原始连接，例如:

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

不幸的是，我不认为有一种创建cx_oracle variable实例的方法，只是在api中不可用，请参见

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.

然后，即使您将游标委托给SQLAlchemy，也无法避免创建游标:

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

当然，在第二种情况下，您应该关闭游标(在第一种情况下，oracle将其关闭).无法创建out = 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()

就像我说的那样，它不是很优雅，但是我认为没有一种方法可以在SQLAlchemy中创建等效变量.这是代码内部处理的事情.我只是去找原始的连接光标.

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.

希望有帮助.

EDIT2 :在上面的代码中，根据建议添加了out.getvalue().谢谢！

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

这篇关于如何在sqlalchemy中返回返回值执行原始查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！