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

发布于 2024-10-08 13:03:17 字数 548 浏览 1 评论 0原文

这是我的代码。我想找到一种方法将查询结果作为字典列表而不是元组列表返回。看起来 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()

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

別甾虛僞 2024-10-15 13:03:17

Bindvars 用于执行查询，例如

按名称（给定命名参数）

cursor = self.db.cursor()
cursor.execute("SELECT bookName，作者来自 Id=:bookId 的书籍" , bookId="155881")
打印光标.bindnames()

将打印：['BOOKID']

按给定值列表的位置

cursor = self.db.cursor()
cursor.prepare("插入书籍(bookId、书名、作者、价格)values(:1, :2, :3, :4)")
光标.executemany（无，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()

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

回复收藏 0 原文

盛装女皇 2024-10-15 13:03:17

这是一个快速而肮脏的过程。感觉发帖有更好的方法。

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

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

回复收藏 0 原文

~没有更多了~