在 PyOdbc 中获取表名和列名 [英] Getting Table and Column names in PyOdbc

问题描述

我想从 PyOdbc 游标中检索完全引用的列名.例如，假设我有 2 个简单的表:

I'd like to retrieve the fully referenced column name from a PyOdbc Cursor. For example, say I have 2 simple tables:

• Table_1(Id, < some other fields >)
• Table_2(Id, < some other fields >)

我想检索加入的数据

select * from Table_1 t1, Table2 t2 where t1.Id = t2.Id

使用pyodbc，像这样:

using pyodbc, like this:

query = 'select * from Table_1 t1, Table2 t2 where t1.Id = t2.Id'

import pyodbc
conn_string = '<removed>'
connection =  pyodbc.connect(conn_string)

cursor = connection.cursor()cursor.execute(query)

然后我想获取列名:

for row in cursor.description:
    print row[0]

但是如果我这样做，我会得到两次我不想要的 Id.理想情况下，我可以在输出中获得 t1.Id 和 t2.Id.

BUT if I do this I'll get Id twice which I don't want. Ideally I could get t1.Id and t2.Id in the output.

我想到的一些解决方案(以及为什么我不想实施它们):

Some of the solutions I've thought of (and why I don't really want to implement them):

1. 重命名查询中的列 - 在我的实际用例中，有数十个表，其中一些表的数十行更改过于频繁
2. 解析我的查询并自动生成我的 SQL 查询(基本上检查表的查询，使用 cursor.tables 函数获取列，然后用一组命名列替换 select *)- 如果我也有的话，我会这样做，但对于测试工具来说似乎有点过分

1. re-name the columns in the query - in my real-world use case there are dozens of tables, some with dozens of rows that are changed far too often
2. parse my query and automate my SQL query generation (basically checking the query for tables, using the cursor.tables function to get the columns and then replacing the select * with a set of named columns) - If I have too I'll do this, but it seems like overkill for a testing harness

有没有更好的办法?任何建议将不胜感激.

Is there a better way? Any advice would be appreciated.

推荐答案

如果不编写大量代码来完成我想做的事情，似乎是不可能的.其他答案都没有真正回答以某种相对自动的方式通过它们源自的表返回不同列名的问题.

It doesn't seem to be possible to do what I want without writing a decent amount of code to wrap it up. None of the other answers actually answered the question of returning different column names by the table they originate from in some relatively automatic fashion.

这篇关于在 PyOdbc 中获取表名和列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！