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

Question

这是我的代码。我想找到一种方法将查询结果作为字典列表而不是元组列表返回。似乎 cx_oracle 支持这一点，文档中有部分讨论“绑定(bind)”。虽然我不知道它是如何工作的。

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

Bindvars 用于执行诸如

之类的查询

• 按名称(给定命名参数)

  cursor = self.db.cursor()
  cursor.execute("SELECT bookName, author from books where Id=:bookId" , bookId="155881")
  print cursor.bindnames()
  

将打印:['BOOKID']

• 按给定值列表的位置

  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()