Python dbfpy and FoxPro

Question

I am 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.

FYI: Memo files contain any fields whose column is declared of type MEMO. Memo columns can store extremely large, arbitrary-length strings. Because the normal table files (`*.dbf`) contain fixed-width records, memo fields don't fit nicely. Therefore, they get their own file with its own format. — Adam Paynter, Mar 11 '11 at 21:24

John Machin · Accepted Answer · 2011-03-11T22:08:20.757

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.

score 0 · Answer 2 · answered Mar 11 '11 at 18:15

0

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.

answered Mar 11 '11 at 18:15

EddieC

134
1
11

Hmm so my the dbfpy python module can't read these files and I'm out of luck? – Dalton Conley Mar 11 '11 at 18:21

score 0 · Answer 3 · answered Mar 14 '11 at 09:54

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.

score 0 · Answer 4 · answered Dec 22 '22 at 21:18

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). Here is 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']

_{This answer was posted as an edit to the question Python dbfpy and FoxPro by the OP Dalton Conley under CC BY-SA 2.5.}

Python dbfpy and FoxPro

4 Answers4

Linked