Python 在 PyOdbc 中获取表名和列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15098747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting Table and Column names in PyOdbc
提问by Brad
I'd like to retrieve the fully referenced column name from a PyOdbc Cursor. For example, say I have 2 simple tables:
我想从 PyOdbc Cursor 中检索完全引用的列名。例如，假设我有 2 个简单的表：
Table_1(Id, < some other fields >)Table_2(Id, < some other fields >)
Table_1(Id, < some other fields >)Table_2(Id, < some other fields >)
and I want to retrieve the joined data
我想检索加入的数据
select * from Table_1 t1, Table2 t2 where t1.Id = t2.Id
using pyodbc, like this:
使用pyodbc，像这样：
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)
I then want to get the column names:
然后我想获取列名：
for row in cursor.description:
print row[0]
BUT if I do this I'll get Idtwice which I don't want. Ideally I could get t1.Idand t2.Idin the output.
但是如果我这样做，我会得到Id两次我不想要的。理想情况下，我可以得到t1.Id并t2.Id在输出中。
Some of the solutions I've thought of (and why I don't really want to implement them):
我想到的一些解决方案（以及为什么我真的不想实施它们）：
- 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
- 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
- 重命名查询中的列 - 在我的实际用例中，有几十个表，其中一些有几十行，这些行更改得太频繁了
- 解析我的查询并自动生成我的 SQL 查询（基本上检查表的查询，使用 cursor.tables 函数获取列，然后用
select *一组命名列替换） - 如果我也有，我会这样做，但是对于测试工具来说，这似乎有点矫枉过正
Is there a better way? Any advice would be appreciated.
有没有更好的办法？任何意见，将不胜感激。
采纳答案by Brad
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.
如果不编写大量代码来包装它，似乎不可能做我想做的事。其他答案都没有真正回答以某种相对自动的方式通过它们源自的表返回不同列名的问题。
回答by Geordie Jon
The PyOdbcdocs offer
该PyOdbc文档报价
# columns in table x
for row in cursor.columns(table='x'):
print row.column_name
www.PyOdbc wikiThe API docs are useful
www.PyOdbc wikiAPI 文档很有用
回答by Russell Lego
Here's how I do it.
这是我如何做到的。
import pyodbc
connection = pyodbc.connect('DSN=vertica_standby', UID='my_user', PWD='my_password', ansi=True)
cursor = connection.cursor()
for row in cursor.columns(table='table_name_in_your_database'):
print row.column_name
You have to have your DSN (data source name) set up via two files. odbc.ini and odbcinst.ini
您必须通过两个文件设置 DSN（数据源名称）。odbc.ini 和 odbcinst.ini
