如何让cx-oracle将查询结果绑定到字典而不是元组？

Question

这是我的代码。我希望找到一种方法，使查询结果以字典列表而不是元组列表的形式返回。看起来cx_oracle支持这一点，文档中的某些部分谈到了“绑定”。虽然我不知道它是怎么工作的。

def connect():  
    dsn = cx_Oracle.makedsn("host", 1521, "sid")
    orcl = cx_Oracle.connect('scott/tiger@' + dsn)
    curs = orcl.cursor()
    sql = "select * from sometable"
    curs.execute(sql)
    result = curs.fetchall()
    for row in result:
        print row[13] #CATEGORY field order
        print row['CATEGORY'] # <- I want this to work ('CATEGORY' is the name of a field in the 'sometable' table)
    curs.close()

Answer 1

绑定变量用于执行查询，如

• By name(给定命名参数)

cursor.bindnames() = self.db.cursor() cursor.execute("SELECT bookName，author from books where Id=:bookId“，bookId="155881")打印光标

将打印：'BOOKID‘

给定值列表，

• by position

cursor = self.db.cursor() cursor.prepare("insert into books (bookId，title，author，price) values(:1，:2，:3，:4)") cursor.executemany(None，listOfbookwhichAreTuppleOf4Field )

要获得您期望的结果，您可以尝试这样的操作：

def connect():  
    dsn = cx_Oracle.makedsn("host", 1521, "sid")
    orcl = cx_Oracle.connect('scott/tiger@' + dsn)
    curs = orcl.cursor()
    sql = "select * from sometable"
    curs.execute(sql)
    desc = [d[0] for d in curs.description]
    result = [dict(zip(desc,line)) for line in curs]
    curs.close()

Answer 2

这里是一个快速和肮脏的。感觉发布一个更好的方式。

def connect():  
    dsn = cx_Oracle.makedsn("host", 1521, "sid")
    orcl = cx_Oracle.connect('scott/tiger@' + dsn)
    curs = orcl.cursor()
    sql = "select * from sometable"
    curs.execute(sql)
    fieldNumber = 0
    fieldNames={}
    for desc in curs.description:
        fieldNames[desc[0]]=fieldNumber
        fieldNumber+=1
    result = curs.fetchall()
    for row in result:
        print str(row[fieldNames['CATEGORY']])
    curs.close()