# 这是一个示例 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 帮助
上面是示例代码(python小白,代码格式一团乱麻,并且功能也不完全,比如字段超长问题,我只修正了测试过程中的发现的字段)
但是大体功能已经实现,记录一下