python3 dbfpy3生成从数据库查询数据并导出dbf格式

 

 

from dbfpy3 import dbf
import pyodbc
import datetime
import decimal

# 数据库连接信息
server = '123.112.222.104'
database = 'zrkjdsszhyl'
username = 'zr'
password = 'zr'
driver = '{ODBC Driver 17 for SQL Server}'
port = '1433'

    
def get_dbf_field_type(sql_type):
    if sql_type in (str, 'varchar', 'nvarchar', 'char', 'nchar', 'text', b'varchar', b'nvarchar', b'char', b'nchar', b'text'):
        return 'C'
    elif sql_type in (int, 'int', 'smallint', 'tinyint', 'bigint', b'int', b'smallint', b'tinyint', b'bigint', b'number'):
        return 'N'
    elif sql_type in (float, decimal.Decimal, 'float', 'real', 'decimal', 'numeric', b'float', b'real', b'decimal', b'numeric'):
        return 'F'
    elif sql_type in (datetime.datetime, 'date', 'datetime', 'smalldatetime', 'datetime2', b'date', b'datetime', b'smalldatetime', b'datetime2'):
        return 'D'
    else:
        return 'C'
    
def get_dbf_field_length(length):
    return length if length <= 50 else 50


def get_dbf_field_length2(length):
    return length if length <= 254 else 254
    

# 连接数据库
connection = pyodbc.connect('DRIVER='+driver+';PORT='+port+';SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+password)


# 创建游标
# 执行查询并获取结果
cursor = connection.cursor()
sql = "select USERNAME,YLFKFS,JKKH,ZYCS,BAH,XM,XB,CSRQ,NL,GJ,BZYZSNL,XSECSTZ,XSERYTZ,CSD,GG,MZ,SFZH,ZY,HY,XZZ,DH,YB1,HKDZ,YB2,GZDWJDZ,DWDH,YB3,LXRXM,GX,DZ,DH2,RYTJ,RYSJ,RYSJS,RYKB,RYBF,ZKKB,CYSJ,CYSJS,CYKB,CYBF,SJZYTS,MZZD,JBBM,ZYZD,JBDM,RYBQ,QTZD1,QTZD2,QTZD3,QTZD4,QTZD5,QTZD6,QTZD7,QTZD8,QTZD9,QTZD10,QTZD11,QTZD12,QTZD13,QTZD14,QTZD15,JBDM1,JBDM2,JBDM3,JBDM4,JBDM5,JBDM6,JBDM7,JBDM8,JBDM9,JBDM10,JBDM11,JBDM12,JBDM13,JBDM14,JBDM15,RYBQ1,RYBQ2,RYBQ3,RYBQ4,RYBQ5,RYBQ6,RYBQ7,RYBQ8,RYBQ9,RYBQ10,RYBQ11,RYBQ12,RYBQ13,RYBQ14,RYBQ15,WBYY,H23,BLZD,JBMM,BLH,YWGM,GMYW,SWHZSJ,XX,RH,KZR,ZRYS,ZZYS,ZYYS,ZRHS,JXYS,SXYS,BMY,BAZL,ZKYS,ZKHS,ZKRQ,SSJCZBM1,SSJCZBM2,SSJCZBM3,SSJCZBM4,SSJCZBM5,SSJCZBM6,SSJCZBM7,SSJCZRQ1,SSJCZRQ2,SSJCZRQ3,SSJCZRQ4,SSJCZRQ5,SSJCZRQ6,SSJCZRQ7,SSJB1,SSJB2,SSJB3,SSJB4,SSJB5,SSJB6,SSJB7,SSJCZMC1,SSJCZMC2,SSJCZMC3,SSJCZMC4,SSJCZMC5,SSJCZMC6,SSJCZMC7,SZ1,SZ2,SZ3,SZ4,SZ5,SZ6,SZ7,YZ1,YZ2,YZ3,YZ4,YZ5,YZ6,YZ7,EZ1,EZ2,EZ3,EZ4,EZ5,EZ6,EZ7,QKDJ1,QKDJ2,QKDJ3,QKDJ4,QKDJ5,QKDJ6,QKDJ7,QKYHLB1,QKYHLB2,QKYHLB3,QKYHLB4,QKYHLB5,QKYHLB6,QKYHLB7,MZFS1,MZFS2,MZFS3,MZFS4,MZFS5,MZFS6,MZFS7,MZYS1,MZYS2,MZYS3,MZYS4,MZYS5,MZYS6,MZYS7,LYFS,YZZY_YLJG,WSY_YLJG,SFZZYJH,MD,RYQ_T,RYQ_XS,RYQ_F,RYH_T,RYH_XS,RYH_F,ZFY,ZFJE,YLFUF,ZLCZF,HLF,QTFY,BLZDF,SYSZDF,YXXZDF,LCZDXMF,FSSZLXMF,WLZLF,SSZLF,MAF,SSF,KFF,ZYZLF,XYF,KJYWF,ZCYF,ZCYF1,XF,BDBLZPF,QDBLZPF,NXYZLZPF,XBYZLZPF,HCYYCLF,YYCLF,YCXYYCLF,QTF from v_erm_upper_n41"
cursor.execute(sql)
results = cursor.fetchall()


# 创建DBF文件的字段属性
fields = [
    (field_name[0], get_dbf_field_type(field_name[1]), get_dbf_field_length(field_name[3]), get_dbf_field_length2(field_name[4]))
    for field_name in cursor.description
]

# 提取所有字段名到一个列表
field_names = [field[0] for field in fields]

with dbf.Dbf("example.dbf", new=True) as example_dbf:

    for field in fields:
        example_dbf.add_field((field[1], field[0], field[2], field[3]))

    for row in results:
        record = dbf.DbfRecord(example_dbf.header)
        
        for i in range(len(row)):
            # 获取字段名和字段值
            field_name = field_names[i]
            value = [tup[1] for tup in fields if tup[0] == field_name][0]
            # record[bytes(field_name, 'gbk')] = ''
            if value == 'F': 
                if row[i] is None or row[i] == '':
                    record[bytes(field_name, 'gbk')] = 0
                else :
                    record[bytes(field_name, 'gbk')] = row[i]
            elif value == 'D':
                if row[i] is None or row[i] == '':
                    date = datetime.date(11, 1, 1)
                    record[bytes(field_name, 'gbk')] = date
                else :
                    record[bytes(field_name, 'gbk')] = row[i]
            elif value == 'N':
                if row[i] is None or row[i] == '':
                    record[bytes(field_name, 'gbk')] = 0
                else :
                    record[bytes(field_name, 'gbk')] = row[i]
            else :
                # record[field_name] = '0'
                if row[i] is None or row[i] == '':
                    record[bytes(field_name, 'gbk')] = '0'
                else :
                    record[bytes(field_name, 'gbk')] = row[i].encode("gbk", errors="ignore").decode("gbk") #.encode('unicode_escape').decode('utf-8')
        example_dbf.append(record)


# 关闭数据库连接
cursor.close()
connection.close()