python学习日记（2）：pandas数据转换以及写入dbf

最新推荐文章于 2024-07-01 20:58:38 发布
发飙的书童
最新推荐文章于 2024-07-01 20:58:38 发布
阅读量636
点赞数 10
文章标签： python 学习 pandas
本文链接：https://blog.csdn.net/weixin_46674296/article/details/135762467
版权
# 这是一个示例 Python 脚本。
import numpy as np
# 按 Shift+F10 执行或将其替换为您的代码。
# 按 双击 Shift 在所有地方搜索类、文件、工具窗口、操作和设置。

import dbf
import pandas as pd
import cx_Oracle
from 卫统sql import Parrot

# 链接并查询数据
def wt_huoqushuju():

    # 指定Oracle驱动
    cx_Oracle.init_oracle_client(lib_dir=r'C:\Users\anaconda3\envs\django_py38\instantclient_11_2')

    # 创建连接池
    engine = cx_Oracle.connect("saaaaa", "aaaaaa", "192.168.1.99:8848/Oracle10")
    print(engine)
    print('日期填写格式示例：2023-10-01')
    kaishi = input('开始日期:')
    jieshu = input('结束日期:')
    # 实例化对象
    blu = Parrot(kaishi,jieshu)
    # print(blu.sql)
    sql_string = blu.sql

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

    # 得到数据
    df = pd.read_sql(sql_string, con=engine,dtype=leixing)

    # area = df2['id'].str.slice(0, 6)


    # 除去超出长度的字段
    for i in range(1,8):
        a = 'SSJCZBM'+str(i)
        df[a] = df[a].str.slice(0,10)
        print(a)
    df['DZ'] = df['DZ'].str.slice(0, 20)
    df['WBYY'] = df['WBYY'].str.slice(0, 40)
    df['GZDWJDZ'] = df['GZDWJDZ'].str.slice(0, 20)

    # 将None转换成nan
    df2 = df.replace(np.nan ,None)

    # 转换指定列的数据类型
    df2['XSECSTZ'] = df2['XSECSTZ'].astype('str')
    df2['XSERYTZ'] = df2['XSERYTZ'].astype('str')
    df2['SWHZSJ'] = df2['SWHZSJ'].astype('str')

    # 转换超出字符长度的float字符型数据
    linshi = pd.DataFrame()
    # 按‘.’进行字符拆分，设置新的df进行保存
    linshi[['SWHZSJ', '1']] = df2['SWHZSJ'].str.split('.', expand=True)
    df2['SWHZSJ'] = linshi['SWHZSJ']
    # 此处主要转换指定类型为str列的nan，None数据，防止字节超长
    df2.replace('nan', '', inplace=True)
    df2.replace('None', '', inplace=True)
    print( df2.dtypes)
    # df2['ZYCS'] = df2['ZYCS'].astype(np.str)
    # df3 = df2.replace(np.nan ,None)

    # 输出各列的类型
    # column_types = df.dtypes.to_dict()
    # a = 0
    # for column, data_type in column_types.items():
    #     # print(f"Column '{a}':'{column}': {data_type}")
    #     print(f"'{column}': {data_type},")
    #     a=a+1


    # df1 = df.replace(np.nan, '', regex=True)
    # for i in df.values:
    #     a = tuple(i)
    #     print(i,type(i),a,type(a))


    # print(df.columns)
    # engine.dispose()  # 关闭连接池

    # 指定dbf模板文件的格式
    geshi = '''username C(40);ylfkfs C(2);jkkh C(10);zycs C(4);bah C(10);xm C(20);xb C(1);csrq C(10);nl N(3,0);gj C(3);bzyzsnl N(2,0);
                xsecstz C(7);xserytz C(7);csd C(90);gg C(40);mz C(2);sfzh C(18);zy C(2);hy C(2);xzz C(80);dh C(16);yb1 C(6);hkdz C(80);
                yb2 C(6);gzdwjdz C(40);dwdh C(16);yb3 C(10);lxrxm C(20);gx C(1);dz C(40);dh2 C(16);rytj C(1);rysj C(10);rysjs N(2,0);
                rykb C(8);rybf C(4);zkkb C(8);cysj C(10);cysjs N(2,0);cykb C(8);cybf C(4);sjzyts C(4);mzzd C(80);jbbm C(16);zyzd C(80);
                jbdm C(16);rybq C(1);qtzd8 C(80);jbdm8 C(16);rybq8 C(1);qtzd1 C(80);jbdm1 C(16);rybq1 C(1);qtzd9 C(80);jbdm9 C(16);
                rybq9 C(1);qtzd2 C(80);jbdm2 C(16);rybq2 C(1);qtzd10 C(80);jbdm10 C(16);rybq10 C(1);qtzd3 C(80);jbdm3 C(16);rybq3 C(1);
                qtzd11 C(80);jbdm11 C(16);rybq11 C(1);qtzd4 C(80);jbdm4 C(16);rybq4 C(1);qtzd12 C(80);jbdm12 C(16);rybq12 C(1);
                qtzd5 C(80);jbdm5 C(16);rybq5 C(1);qtzd13 C(80);jbdm13 C(16);rybq13 C(1);qtzd6 C(80);jbdm6 C(16);rybq6 C(1);qtzd14 C(80);
                jbdm14 C(16);rybq14 C(1);qtzd7 C(80);bdm7 C(16);rybq7 C(1);qtzd15 C(80);jbdm15 C(16);rybq15 C(1);wbyy C(80);h23 C(16);
                blzd C(80);jbmm C(16);blh C(10);ywgm C(80);gmyw C(80);swhzsj C(2);xx C(1);rh C(1);kzr C(20);rys C(20);zzys C(20);
                zyys C(20);zrhs C(20);jxys C(20);sxys C(20);bmy C(20);bazl C(1);zkys C(20);zkhs C(20);zkrq C(10);ssjczbm1 C(10);
                ssjczrq1 C(10);ssjb1 C(1);ssjczmc1 C(100);sz1 C(20);yz1 C(20);ez1 C(20);qkdj1 C(1);qkyhlb1 C(1);mzfs1 C(16);mzys1 C(20);
                ssjczbm2 C(10);ssjczrq2 C(10);ssjb2 C(1);ssjczmc2 C(100);sz2 C(20);yz2 C(20);ez2 C(20);qkdj2 C(1);qkyhlb2 C(1);mzfs2 C(16);
                mzys2 C(20);ssjczbm3 C(10);ssjczrq3 C(10);ssjb3 C(1);ssjczmc3 C(100);sz3 C(20);yz3 C(20);ez3 C(20);qkdj3 C(1);qkyhlb3 C(1);
                mzfs3 C(16);mzys3 C(20);ssjczbm4 C(10);ssjczrq4 C(10);ssjb4 C(1);ssjczmc4 C(100);sz4 C(20);yz4 C(20);ez4 C(20);qkdj4 C(1);
                qkyhlb4 C(1);mzfs4 C(16);mzys4 C(20);ssjczbm5 C(10);ssjczrq5 C(10);ssjb5 C(1);ssjczmc5 C(100);sz5 C(20);yz5 C(20);ez5 C(20);
                kdj5 C(1);qkyhlb5 C(1);mzfs5 C(16);mzys5 C(20);ssjczbm6 C(10);ssjczrq6 C(10);ssjb6 C(1);ssjczmc6 C(100);sz6 C(20);yz6 C(20);
                ez6 C(20);qkdj6 C(1);qkyhlb6 C(1);mzfs6 C(16);mzys6 C(20);ssjczbm7 C(10);ssjczrq7 C(10);ssjb7 C(1);ssjczmc7 C(100);sz7 C(20);
                yz7 C(20);ez7 C(20);qkdj7 C(1);qkyhlb7 C(1);mzfs7 C(16);mzys7 C(20);lyfs C(1);yzzy_yljg C(60);wsy_yljg C(60);sfzzyjh C(1);
                md C(60);ryq_t N(4,0);ryq_xs N(2,0);ryq_f N(2,0);ryh_t N(4,0);ryh_xs N(2,0);ryh_f N(2,0);zfy N(9,2);zfje N(9,2);ylfuf N(9,2);
                zlczf N(9,2);hlf N(9,2);qtfy N(9,2);blzdf N(9,2);syszdf N(9,2);yxxzdf N(9,2);lczdxmf N(9,2);fsszlxmf N(9,2);wlzlf N(9,2);
                sszlf N(9,2);maf N(9,2);ssf N(9,2);kff N(9,2);zyzlf N(9,2);xyf N(9,2);kjywf N(9,2);zcyf N(9,2);zcyf1 N(9,2);xf N(9,2);
                bdblzpf N(9,2);qdblzpf N(9,2);nxyzlzpf N(9,2);xbyzlzpf N(9,2);hcyyclf N(9,2);yyclf N(9,2);ycxyyclf N(9,2);qtf N(9,2)
                '''
    # 创建test.dbf文件 共两列 name 字符串 长度25；age 数值 长度3
    table = dbf.Table(filename='test.dbf', field_specs=geshi, codepage='cp936')
    # 修改为读写模式
    table.open(mode=dbf.READ_WRITE)
    print(table)

    # 输出数据
    for i in df2.values:
        # print(type(i))

        # 用于创建要追加的数据必须是元组、字典、记录或模板;而不是<class 'list'>
        a = []
        for j in i:
            # print(j,type(j))
            a.append(j)
            # print(j,type(j))
        # 列表转元组
        b = tuple(a)
        # print(a,type(a))
        print(b)
        table.append(b)
        # print(i,type(i))
    # 关闭dbf文件
    table.close()
    # df.to_excel('AAA.xlsx',index=False)
    engine.close()  # 关闭连接池
    print('关闭')
    # return df


# 数据格式转换
def wt_geshi():

    # 打开DBF模板文件
    db = dbf.Dbf('20221229调整了两个字段问题自定长度模板 - 副本.dbf', new=False)

    # 获取df数据并写入DBFwejain
    df = wt_huoqushuju()
    print('df数据展示',df)
    # for i in df.values:
    #     print(i)
    for i in df.index:
        rec = dbf.DbfRecord(db.header)
        for j in df.columns:

            # 判断是否为空，为空则跳过
            if pd.isnull(df.loc[i, j]): # pd.isnull当为空时则为返回True
                print('buweikong', df.loc[i, j])
            else:
                print('weikong', df.loc[i, j])
                rec[bytes(j, 'cp936')] = df.loc[i, j]
        db.append(rec)

    # 关闭 dbf 文件
    db.close()

# 打开并展示dbf文件
# def wt_zhanshiDBF():
#
#     # 打开dbf文件
#     data = DBF('aaa - 副本.dbf')
#     # 查看DBF字段信息
#     print('表字段',data.field_names)
#     print('表字段的详细信息',data.fields)
#     df = pd.DataFrame(iter(data))  # 转化为df格式
#     print(df)
#     for i in df.values:
#         print(i)

# 创建DBF文件
def wt_chuangjian():
    pass


# 按装订区域中的绿色按钮以运行脚本。
if __name__ == '__main__':
    # wt_geshi()
    wt_huoqushuju()
    # wt_zhanshiDBF()
# 访问 https://www.jetbrains.com/help/pycharm/ 获取 PyCharm 帮助