我怎样才能使cx-oracle将查询的结果绑定到一个字典而不是一个元组?[英] How can I make cx-oracle bind the results of a query to a dictionary rather than a tuple?

本文是小编为大家收集整理的关于我怎样才能使cx-oracle将查询的结果绑定到一个字典而不是一个元组?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

这是我的代码.我想找到一种方法,以作为字典列表而不是元素列表返回查询的结果.似乎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()

推荐答案

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

其他推荐答案

这是一个快速而肮脏的.感觉到更好的方法.

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

本文地址:https://www.itbaoku.cn/post/597605.html

问题描述

Here is my code. I would like to find a way to have results from a query returned as a list of dictionaries rather than list of tuples. It seems like cx_oracle supports this with parts of the documentation talking about 'binding'. Though I can't figure out how it works.

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

推荐答案

Bindvars are used to execute query such as

  • By name(given named parameters)

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

will print : ['BOOKID']

  • by position given a list of values

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

To get what you expected you could try something like that:

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

其他推荐答案

Here is a quick and dirty. Feel post a better way.

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