Python dbfpy and FoxPro

Question

Alright, I'm using an ancient database format here, dbf files. Don't ask why, just know a certain software decided to extend foxpro support because microsoft decided to extend foxpro support. Now, I'm getting the following error on a specific file. I have successfully loaded another file and I'm curious if there is something wrong with this database. I'm sure you probably need to look at the database to determine that, but its way to huge to post so I'll take what I can get.

Traceback (most recent call last):
  File "billsapi.py", line 250, in <module>
    x.getUsedGuns()
  File "billsapi.py", line 72, in getUsedGuns
    itemdb = dbf.Dbf('item.dbf', readOnly=True, ignoreErrors=True)
  File "C:\Python27\lib\site-packages\dbfpy\dbf.py", line 135, in __init__
    self.header = self.HeaderClass.fromStream(self.stream)
  File "C:\Python27\lib\site-packages\dbfpy\header.py", line 127, in fromStream
    _fld = fields.lookupFor(_data[11]).fromString(_data, _pos)
  File "C:\Python27\lib\site-packages\dbfpy\fields.py", line 455, in lookupFor
    return _fieldsRegistry[typeCode]
KeyError: '0'

And heres my simple code that is returning this error:

def getUsedGuns(self): 
    itemdb = dbf.Dbf('item.dbf', readOnly=True, ignoreErrors=True) 

Like I said, I can load other files with out a problem but maybe there is a work around for this specific error?

EDIT: I'd also like to point out that the file can be opened and viewed and modified in DBF View Plus.

EDIT: Solution Found. I actually ended up using the python dBase module. I think my main problem was not having the memo files around(whatever they are, it has a .fpt file extension). Heres what I am using at the moment:

from dbf.tables import VfpTable
itemdb = VfpTable('item.db')
for rec in itemdb:
    print rec['MY_COLUM_NAME']

I'd also like to point out that anyone currently still using FoxPro should be burned.

Answer 1

You should take a look at Sybase's Advantage Database Server. This product has excellent support for VFP DBF files. I have been using their ODBC drivers with Python via pyodbc for a few years, and have had excellent results using their recently released a DB-API 2.0 compliant Python driver.

I too have been put in the position where I must support DBF tables. The Advantage Database Server has been an absolute lifesaver.

Answer 2

Your traceback is dbfpy's way of telling you that your file has an unsupported-by-dbfpy field type code, 0. It's a Visual FoxPro ("VFP") thing.

This is nothing to do with memo files. Yes, if there are memo fields, they are stored in a .FPT file. foo.fpt needs to be present when you access foo.dbf.

You say """I actually ended up using the python dBase module""" ... presumably you mean Ethan Furman's dbf module, which according to its PyPI entry doesn't support null fields.

I have a DBF reading module (pydbfrw) which I've been meaning to release "one of these days". Here's an extract from its docs:

Field Type      Description  DBF variety  Python 2.x type
0 (digit zero)  _NullFlags   VFP          N/A             

Notes: This field type is used only for the hidden _NullFlags field which
is a bit mask saying which fields in the record should be interpreted as NULL.

My module implements recognising that and returning None for a field value where required. If you would like a copy of the module, find my e-mail address -- e.g. google("john machin xlrd") -- e-mail me and I'll send it to you.

Answer 3

Could be that item.dbf uses more advanced dbf capabilities that the other dbf does not. For instance, auto increment integers were introduced very late and are not supported by most odbc drivers.