使用pyodbc读取DBF文件 [英] Reading DBF files with pyodbc

问题描述

在项目中，我需要从Visual FoxPro数据库提取数据，该数据库存储在dbf文件中，我有一个数据目录，其中包含539个文件，我需要考虑到这一点，每个文件都代表一个数据库表，我已经做了一些测试，我的代码如下：

  import pyodbc 
 
 connection = pyodbc。 connect（Driver = {Microsoft Visual FoxPro Driver}; SourceType = DBF; SourceDB = P：\\Data; Exclusive = No; Collat​​e = Machine; NULL = No; DELETED = Yes）
 tables = connection .cursor（）。tables（）
表中的_：
 print _ 
  

这只打印15个表，没有明显的模式，总是相同的15个表，我认为这是因为其余的表是空的，但我检查，它的列表中的一些表（dbf文件）是空的，那么，我认为这是一个权限问题，但所有的文件具有相同的权限结构，所以，我不知道这里发生了什么。

任何光线？ ？

/ p>

解决方案

我DID IT !!!!

与我在做什么，在这里我来与我做了解决它（在第一次实施它与Ethan Furman的解决方案）

第一件事是一个驱动程序问题，事实证明，Windows的DBF驱动程序是32位程序，并运行在64位操作系统，所以，我安装了Python-amd64这是第一个问题，所以我安装了一个32位的Python。

第二个问题是库/文件问题，根据这个，VFP> 7中的dbf文件是不同的，所以我的pyodbc库不会正确读取它们，所以我尝试了一些OLE-DB库没有成功，我决定从头开始。 / p>

Googling一段时间后，我进入

基本上，我做的是以下内容：

  import win32com.client 
 
 conn = win32com.client.Dispatch（'ADODB.Connection'）
 ='C：\\Profit\\profit_a\\ARMM'
 dsn ='Provider = VFPOLEDB.1;数据源=％s'％db 
 conn.Open（dsn ）
 
 cmd = win32com.client.Dispatch（'ADODB.Command'）
 cmd.ActiveConnection = conn 
 cmd.CommandText =Select * from factura，reng_fac where factura。 fact_num = reng_fac.fact_num AND factura.fact_num = 6099; 
 
 rs，total = cmd.Execute（）＃这返回一个元组：（< RecordSet>，number_of_records）
 
 while total：
 for x in xrange （rs.Fields.Count）：
 print'％s  - > ％s'％（rs.Fields.item（x）.Name，rs.Fields.item（x）.Value）
 rs.MoveNext（）
 total = total  -  1 
  

它给了我20条记录，我用DBFCommander检查并确定

首先，您需要安装 pywin32扩展程序（32bits）和< a href =http://www.microsoft.com/en-us/download/details.aspx?id=14839 =nofollow noreferrer> Visual FoxPro OLE-DB提供程序（仅适用于32位），在我的情况下为VFP 9.0

此外，阅读de ADO文档在w3c网站

这对我有用。非常感谢您回复

的人

In a project, I need to extract data from a Visual FoxPro database, which is stored in dbf files, y have a data directory with 539 files I need to take into account, each file represents a database table, so I've been doing some testing and my code goes like this:

import pyodbc

connection = pyodbc.connect("Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=P:\\Data;Exclusive=No;Collate=Machine;NULL=No;DELETED=Yes")
tables = connection.cursor().tables()
for _ in tables:
    print _

this prints only 15 tables, with no obvious pattern, always the same 15 tables, I thought this was because the rest of the tables were empty but I checked and it some of the tables (dbf files) on the list are empty too, then, I thought it was a permission issue, but all the files have the same permission structure, so, I don't know what's happening here.

Any light??

EDIT: It is not truccating the output, the tables it list are not the 15 first or anything like that

解决方案

I DID IT!!!!

There where several problems with what I was doing so, here I come with what I did to solve it (after implementing it the first time with Ethan Furman's solution)

The first thing was a driver problem, it turns out that the Windows' DBF drivers are 32 bits programs and runs on a 64 bits operating system, so, I had installed Python-amd64 and that was the first problem, so I installed a 32bit Python.

The second issue was a library/file issue, according to this, dbf files in VFP > 7 are diferent, so my pyodbc library won't read them correctly, so I tried some OLE-DB libraries with no success and I decided to to it from scratch.

Googling for a while took me to this post which finally gave me a light on this

Basically, what I did was the following:

import win32com.client

conn = win32com.client.Dispatch('ADODB.Connection')
db = 'C:\\Profit\\profit_a\\ARMM'
dsn = 'Provider=VFPOLEDB.1;Data Source=%s' % db
conn.Open(dsn)

cmd = win32com.client.Dispatch('ADODB.Command')
cmd.ActiveConnection = conn
cmd.CommandText = "Select * from factura, reng_fac where factura.fact_num = reng_fac.fact_num AND factura.fact_num = 6099;"

rs, total = cmd.Execute() # This returns a tuple: (<RecordSet>, number_of_records)

while total:
    for x in xrange(rs.Fields.Count):
        print '%s --> %s' % (rs.Fields.item(x).Name, rs.Fields.item(x).Value)
        rs.MoveNext()
        total = total - 1

And it gave me 20 records which I checked with DBFCommander and were OK

First, you need to install pywin32 extensions (32bits) and the Visual FoxPro OLE-DB Provider (only available for 32bits), in my case for VFP 9.0

Also, it's good to read de ADO Documentation at the w3c website

This worked for me. Thank you very much to those who replied

这篇关于使用pyodbc读取DBF文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！