How to get datatypes of specific fields of an Access database using pyodbc?

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

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 ag开发者_JS百科ain, I'm fairly green on databases.

Does anybody know how to get the types of these fields?

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.

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 

As Mark noted in the comments you probably also want the row.data_type. The link he provided includes all the columns it provides

1. table_cat
2. table_schem
3. table_name
4. column_name
5. data_type
6. type_name
7. column_size
8. buffer_length
9. decimal_digits
10. num_prec_radix
11. nullable
12. remarks
13. column_def
14. sql_data_type
15. sql_datetime_sub
16. char_octet_length
17. ordinal_position
18. is_nullable: One of SQL_NULLABLE, SQL_NO_NULLS, SQL_NULLS_UNKNOWN.

I am not familiar with pyodbc, but I have done this in VBA in the past.

The 2 links you mentionned are for SQL Server, not for Access. To find out the data type of each field in an Access table, you can use DAO or ADOX.

Here is an example I did, in VBA with Excel 2010, where I connect to the Access database (2000 mdb format) and list the tables, fields and their datatypes (as an enum, for example '4' means dbLong). You can see in the output the system tables and, at the bottom, tables created by the user. You can easily find examples on internet for how to do something similar with ADOX. I Hope this helps.

Private Sub TableDefDao()
    Dim db As DAO.Database
    Set db = DAO.OpenDatabase("C:\Database.mdb")

    Dim t As DAO.TableDef
    Dim f As DAO.Field
    For Each t In db.TableDefs
        Debug.Print t.Name
        For Each f In t.Fields
            Debug.Print vbTab & f.Name & vbTab & f.Type
        Next
    Next
End Sub

You'll get some info from this output:

import pandas as pd
dbq='D:\....\xyz.accdb'
conn=pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=%s;" %(dbq))
    
query = 'select * from tablename'
    
dataf = pd.read_sql(query, conn)
print(list(dataf.dtypes))