3

When I try to use cursor.primaryKeys("tablename") then exception occurs:

Error: ('IM001', '[IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function (0) (SQLPrimaryKeys)')

list(cursor.columns(table='tablename')) does not reveal primary keys either.

denfromufa
  • 5,610
  • 13
  • 81
  • 138
  • here is solution in .NET: http://stackoverflow.com/questions/862749/how-to-get-the-primary-key-of-an-ms-access-table-in-c-sharp – denfromufa Jun 08 '16 at 20:38

2 Answers2

5

For Access ODBC we can usually* get the Primary Key columns via the .statistics() method of the pyodbc cursor object:

crsr = conn.cursor()
table_name = 'MyTable'
# dict comprehension: {ordinal_position: col_name}
pk_cols = {row[7]: row[8] for row in crsr.statistics(table_name) if row[5]=='PrimaryKey'}
print(pk_cols)  # e.g., {1: 'InvID', 2: 'LineItem'}

*EDIT: This approach assumes that the primary key index for the table is named PrimaryKey. That is true if the table is created using the MS Access table builder (GUI) but is not true if the table is created using DDL (i.e., CREATE TABLE …). In those cases the primary key index will have a name like Index_EA5344E1_0942_445C so the above method won't work, but we can use ACE DAO instead:

import win32com.client  # needs `pip install pywin32`


def get_access_primary_key_columns(db_path, table_name):
    db_engine = win32com.client.Dispatch("DAO.DBEngine.120")
    db = db_engine.OpenDatabase(db_path)
    tbd = db.TableDefs(table_name)
    for idx in tbd.Indexes:
        if idx.Primary:
            return [fld.Name for fld in idx.Fields]


if __name__ == "__main__":
    print(
        get_access_primary_key_columns(
            r"C:\Users\Public\Database1.accdb", "team"
        )
    )
    # ['city', 'prov']
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • what is the equivalent of `OleDbSchemaGuid.Primary_Keys` and `Columns["COLUMN_NAME"].Ordinal`? Right now you have hard-coded indices 5, 7, and 8 from statistics. – denfromufa Jun 09 '16 at 14:59
  • 2
    @denfromufa - Those are the column indices as defined in the documentation for the `.statistics` method of the [cursor object](https://github.com/mkleehammer/pyodbc/wiki/Cursor). You could create your own variables, e.g., `ordinal_position_col = 7`, but aside from making the code a bit more self-documenting it would amount to the same thing. – Gord Thompson Jun 09 '16 at 15:21
  • 2
    actually it is defined by ODBC driver from Microsoft: https://msdn.microsoft.com/en-us/library/ms711022(VS.85).aspx – denfromufa Jun 09 '16 at 15:25
  • @denfromufa - Good reference. Thanks for posting it. – Gord Thompson Jun 09 '16 at 15:34
2

Here is solution using pythonnet and Oledb Jet driver. Note that this does not preserve the order of primary keys as columns:

import clr
import System
import System.Data.OleDb
from System.Data.OleDb import OleDbSchemaGuid

def getKeyNames(tableName, mdbname):
    conn = System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;"
                         "Data source={}".format(mdbname))
    conn.Open()
    returnList=[]
    mySchema = (conn).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
        [None, None, tableName])
    columnOrdinalForName = mySchema.Columns["COLUMN_NAME"].Ordinal
    for r in mySchema.Rows:
        returnList.append(r.ItemArray[columnOrdinalForName])
        conn.Close()
    return returnList

getKeyNames(table_name,mdbname)
denfromufa
  • 5,610
  • 13
  • 81
  • 138