如何使用pyodbc获取Access数据库的特定字段的数据类型? [英] How to get datatypes of specific fields of an Access database using pyodbc?
问题描述
我正在使用pyodbc在.mbd(访问)文件中对大型数据库进行数据挖掘.
I'm using pyodbc to data-mine a big database in a .mbd (access) file.
我想创建一个新表，以从几个现有表中获取相关信息(然后将其提供给工具).
I want to create a new table taking relevant information from several existing tables (to then feed it to a tool).
我想我知道我需要传输数据的所有知识，并且我知道如何创建具有列名和数据类型的表，但是我在获取以下数据类型(INTEGER，VARCHAR等)时遇到了麻烦.我需要这些类型来兼容地创建新列.
I think I know all I need to transfer the data, and I know how to create a table given column names and datatypes, but I'm having trouble getting the datatypes (INTEGER, VARCHAR, etc.) of the respective columns in the existing tables. I need these types to create the new columns compatibly.
我在互联网上找到的内容(例如此)使我陷入无效命令的麻烦中，所以我认为这是特定于平台的问题.再说一次，我对数据库相当满意.
What I found on the internet (like this and this) is getting me into invalid-command trouble, so I think this is a platform-specific issue. Then again, I'm fairly green on databases.
有人知道如何获取这些字段的类型吗?
Does anybody know how to get the types of these fields?
推荐答案
这些文章不能为您提供帮助的原因是因为它们适用于SQL Server. SQL Server具有可以查询以获取列数据的系统表，而MS Access没有. MS Access仅允许您查询对象名称.
The reason why those articles aren't helping you is because they are for SQL Server. SQL Server has system tables that you can query to get the column data, MS Access doesn't. MS Access only lets you query the object names.
However ODBC does support getting the schema through its connection via the ODBC.SQLColumns functions.
根据此答案 PyODBC通过游标方法将其公开
According to this answer PyODBC exposes this via a cursor method
# columns in table x
for row in cursor.columns(table='x'):
print row.column_name
正如Mark在评论中指出的那样，您可能还需要row.data_type.他提供的链接包括其提供的所有列
As Mark noted in the comments you probably also want the row.data_type. The link he provided includes all the columns it provides
- table_cat
- table_schem
- 表名
- 列名
- data_type
- type_name
- column_size
- buffer_length
- decimal_digits
- num_prec_radix
- 可空
- 备注
- column_def
- sql_data_type
- sql_datetime_sub
- char_octet_length
- ordinal_position
- is_nullable:SQL_NULLABLE，SQL_NO_NULLS，SQL_NULLS_UNKNOWN之一.
- table_cat
- table_schem
- table_name
- column_name
- data_type
- type_name
- column_size
- buffer_length
- decimal_digits
- num_prec_radix
- nullable
- remarks
- column_def
- sql_data_type
- sql_datetime_sub
- char_octet_length
- ordinal_position
- is_nullable: One of SQL_NULLABLE, SQL_NO_NULLS, SQL_NULLS_UNKNOWN.
这篇关于如何使用pyodbc获取Access数据库的特定字段的数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
