当前位置：文江博客话题详情

Python SQLAlchemy associative

python sqlalchemy 动态获取列名？

发布于 2024-12-28 04:09:53 字数 382 浏览 2 评论 0 原文

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine

connection = create_engine('mysql://user:passwd@localhost:3306/db').connect()

result = connection.execute("select * from table")
for v in result:
        print v['id']
        print v['name']
connection.close()

我如何动态获取表列名称？在本例中 id 和 name

原文

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine

connection = create_engine('mysql://user:passwd@localhost:3306/db').connect()

result = connection.execute("select * from table")
for v in result:
        print v['id']
        print v['name']
connection.close()

how i can get TABLES COLUMNS NAMES dynamically? in this case id and name

分享到QQ

分享到微博

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

发布评论

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

微暖i 2025-01-04 04:09:53

您可以通过调用 result.keys() 查找列，也可以通过在 for 循环内调用 v.keys() 来访问它们。

以下是使用 items() 的示例：

for v in result:
    for column, value in v.items():
        print('{0}: {1}'.format(column, value))

You can either find the columns by calling result.keys() or you can access them through calling v.keys() inside the for loop.

Here's an example using items():

for v in result:
    for column, value in v.items():
        print('{0}: {1}'.format(column, value))

回复收藏 0 原文

暗藏城府 2025-01-04 04:09:53

最直接的解决方案

没有比使用列表理解的单行解决方案更简单的了。这也是最直接的方法：

[col for col in result.keys()]
# return: ['id', 'name']

@Saul 的答案也有效，但是您需要小心仅迭代每个 cursor.description 的第一个元素，以免得到一堆。

它的效率也较低，因为您需要遍历 ResultProxy，访问 cursor.description 属性，并且每个属性仅检索索引 0 处的元素

。 Python 中的 timeit 经过 500,000 次迭代显示了速度差异（0.016 与 0.011）：

connection = create_engine('sqlite:///rcsample.db').connect()
result = connection.execute("select * from response")
def cursfunc():
    return [ i[0] for i in result.cursor.description ]
print(timeit.timeit("cursfunc()", setup="from __main__ import cursfunc", number=500000))
# return: 0.01606178

虽然建议的解决方案完成时间减少了约 30%：

connection = create_engine('sqlite:///rcsample.db').connect()
result = connection.execute("select * from response")

def keysfunc():
    return [col for col in result.keys()]
print(timeit.timeit("keysfunc()", setup="from __main__ import cursfunc", number=500000))
# return: 0.01097001

事实上，我怀疑时间在具有更多列的表上，差异可能比上面明显简化的示例更大。

实践中：键和值

在实践中，您可能希望动态打印键和值。有两种方法可以解决这个问题。第一种：

results = conn.execute('SELECT * FROM salesperson')
[{column:value for column, value in result.items()} for result in results]
# returns: [{'id': 1, 'name': 'John Doe'}, {'id': 2, 'name': 
# 'Margaret'}, {'id': 3, 'name': 'Anna'}]

或者使用解包：

rows = conn.execute('SELECT * FROM salesperson LIMIT 2').fetchall()
print([{**row} for row in rows])
# returns: [{'id': 1, 'name': 'John Doe'}, {'id': 2, 'name': 'Margaret'}]

这两种方法都是直接的和Pythonic的，同时也使程序员不必显式指定（或事先知道）列名称。

Most direct solution

Can't get any simpler than a one-liner solution using list comprehension. It also is the most direct method:

[col for col in result.keys()]
# return: ['id', 'name']

@Saul's answer also works, but you'll need to be careful about iterating over only the first element through each cursor.description, lest you get a bunch of None in each tuple of the returned list.

It also is less-efficient, because you need to iterate through the ResultProxy, access the cursor.description attribute and for each of them only retrieve the element at index 0.

Using timeit in Python with 500,000 iterations showed the speed difference (0.016 vs 0.011):

connection = create_engine('sqlite:///rcsample.db').connect()
result = connection.execute("select * from response")
def cursfunc():
    return [ i[0] for i in result.cursor.description ]
print(timeit.timeit("cursfunc()", setup="from __main__ import cursfunc", number=500000))
# return: 0.01606178

While the proposed solution completes in ~30% less time:

connection = create_engine('sqlite:///rcsample.db').connect()
result = connection.execute("select * from response")

def keysfunc():
    return [col for col in result.keys()]
print(timeit.timeit("keysfunc()", setup="from __main__ import cursfunc", number=500000))
# return: 0.01097001

In fact, my suspicion is that the time difference could be greater on a table with more columns than the obviously simplified example above.

In Practice: keys and values

In practice, you'd probably want to print both the key and values dynamically. There are two ways you can go about it. The first:

results = conn.execute('SELECT * FROM salesperson')
[{column:value for column, value in result.items()} for result in results]
# returns: [{'id': 1, 'name': 'John Doe'}, {'id': 2, 'name': 
# 'Margaret'}, {'id': 3, 'name': 'Anna'}]

Alternatively using unpacking:

rows = conn.execute('SELECT * FROM salesperson LIMIT 2').fetchall()
print([{**row} for row in rows])
# returns: [{'id': 1, 'name': 'John Doe'}, {'id': 2, 'name': 'Margaret'}]

Both of these methods are direct and pythonic, while also exempt the programmer from having to specify (or know beforehand) the columns names explicitly.

回复收藏 0 原文