python3 从MDB及GDB文件中导出EXCEL属性表

本文主要介绍如何使用pypyodbc库读写MDB文件、ogr库打开GDB文件、openpyxl库写入EXCEL文件。

具体实现代码中标注的很清楚，就不再赘述了。

#  coding:utf-8
#  design by @bill_love_3
import openpyxl
import pypyodbc

try:
    from osgeo import ogr
except ImportError:
    import ogr
import sys


class ExportCBDKXX(object):  # 导出承包地块信息表 dkbm,cbfbm,htmj,cbfmc,cbfzjhm,cbfdz,lxdh,gsshr mdb size:94908KB
    def __init__(self):
        self.gdb = ''  # gdb对象
        self.lis_mdb = []  # mdb查询结果
        self.dic_mdb = {}  # mdb过程字典
        self.list_ac = []  # 过程列表
        self.tup_gdb = ()  # gdb属性元组
        self.lis_gdb = []  # gdb属性列表
        self.lis_res = []  # 结果列表

    def Read_mdb(self, path):  # 读取MDB属性数据
        # 通过驱动连接MDB；Uid=;Pwd=;用户名；密码 可缺省
        conn = pypyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + path + ";Uid=;Pwd=;")
        # 建立数据库游标
        cursor = conn.cursor()
        # 数据库的SQL语句 是核心，读还是写都和SQL有关
        SQL_value = "select a.dkbm,a.cbfbm,a.htmj,b.cbfmc,b.cbfzjhm,b.cbfdz,b.lxdh,b.gsshr from CBDKXX a left join " \
                    "CBF b on (a.cbfbm=b.cbfbm) where dkbm is not null"  # CBDKXX 关联 CBF 查询承包方信息
        # 游标执行
        cursor.execute(SQL_value)  # type [(),(),()]
        # cursor.commit() 写入需要提交 读取可缺省
        # 读取的信息保存到类属性lis_mdb中
        self.lis_mdb = cursor.fetchall()
        # print(self.lis_mdb)

        conn.close()

    def Read_shp(self, path):  # 读取GDB图形属性
        ogr.UseExceptions()  # 使用ogr特定异常
        driver = ogr.GetDriverByName("OpenFileGDB")  # 获取驱动
        try:
            gdb = driver.Open(path, 0)  # 打开gdb文件
        except Exception as e:
            print(e)
            sys.exit()
        self.gdb = gdb
        # gdb对象的GetLayerByName方法确定图层（layer）
        layer = self.gdb.GetLayerByName("农村土地承包经营权_192639宗")
        # 获取要素数量
        FeatureCount = layer.GetFeatureCount()
        # 开始遍历要素 （按行）
        for ae in range(FeatureCount):
            oFeature = layer.GetNextFeature()
            oDefn = layer.GetLayerDefn()
            iFieldCount = oDefn.GetFieldCount()
            # 开始遍历字段 （按列）
            for iField in range(iFieldCount):
                oFieldDefn = oDefn.GetFieldDefn(iField)
                a = oFieldDefn.GetNameRef()
                c = oFeature.GetFieldAsString(iField).rstrip(' ')
                dict_ab: dict = {a: c}
                # 将{字段名称：字段值}写入list_ac列表
                self.list_ac.append(dict_ab)
        # 通过list_ac列表处理数据，整理形成[[(),(),()],[(),(),()]]元组矩阵方便写入excel
        try:
            aa = int(len(self.list_ac) / FeatureCount)
        except ZeroDivisionError:
            aa = 0
        for iii in range(FeatureCount):
            # print(iii)
            # print(self.list_ac[iii * aa: (iii + 1) * aa])
            b = self.list_ac[iii * aa: (iii + 1) * aa]
            # print(b)
            for ii in range(len(b)):
                i = b[ii]
                # print(i)
                for k, v in i.items():
                    # print(k, v)
                    if k == 'ZDDM':
                        self.tup_gdb = self.tup_gdb + (v,)
                    elif k == 'BDCDYH':
                        self.tup_gdb = self.tup_gdb + (v,)
                    elif k == 'ZL':
                        self.tup_gdb = self.tup_gdb + (v,)
                    elif k == 'ZDMJ':
                        self.tup_gdb = self.tup_gdb + (v,)
                    elif k == 'DKBM':
                        self.tup_gdb = self.tup_gdb + (v,)
                        self.lis_gdb.append(self.tup_gdb)
                        self.tup_gdb = ()
                    break

    def Save_excel(self, path):  # 写入excel方法
        # 按需求处理数据
        for t in range(len(self.lis_mdb)):
            tk = self.lis_mdb[t][0]
            tv = self.lis_mdb[t][1:8]
            self.dic_mdb[tk] = tv
        for l in range(len(self.lis_gdb)):
            ll = self.lis_gdb[l][4]
            try:
                aa = self.lis_gdb[l][0:5] + self.dic_mdb[ll]
                self.lis_res.append(aa)
            except KeyError:
                pass
        # 创建workbook：excel文件对象
        workbook = openpyxl.Workbook()

        # 添加一个工作表
        sheet = workbook.create_sheet('总表', 0)
        sheet['A1'] = '宗地代码'
        sheet['B1'] = '不动产单元号'
        sheet['C1'] = '坐落'
        sheet['D1'] = '宗地面积'
        sheet['E1'] = '地块编码'
        sheet['F1'] = '承包方编码'
        sheet['G1'] = '合同面积'
        sheet['H1'] = '承包方名称'
        sheet['I1'] = '承包方证件号码'
        sheet['J1'] = '承包方地址'
        sheet['K1'] = '联系电话'
        sheet['L1'] = '审核人'

        # 写入数据
        # print(len(self.lis_res))
        for row in self.lis_res:
            sheet.append(row)

        # 保存工作簿
        workbook.save(path)

    def Complete(self, path1, path2, path3):
        self.Read_mdb(path1)
        print('Read_mdb')  # 打印步骤1
        self.Read_shp(path2)
        print('Read_shp')  # 打印步骤2
        self.Save_excel(path3)
        print('Save_excel')  # 打印步骤3


if __name__ == "__main__":
    path1 = r'C:\Users\Documents\土地承包经营权.mdb'
    path2 = r'C:\Users\Documents\农经权核查.gdb'
    path3 = 'C:\\Users\\Documents\\承包地块信息汇总表.xlsx'
    myExp = ExportCBDKXX()
    myExp.Complete(path1, path2, path3)