在日常生活中利用python写脚本提升工作效率

利用在线写作工具提高效率 #生活技巧# #工作学习技巧# #写作技能训练#

如在一个日常工作中，需要执行5个SQL语句来完成修改合同号的目的，原来这5个步骤是手工执行的，如何通过python将这个5个步骤封装为5个方法动作，然后用一个类封装来实现呢？

#!/usr/bin/env python
#coding=utf-8
import pymysql
import time
import logging
from config import dbhost,dbuser,dbpasswd,dbname

logger = logging.getLogger('mytest')
logger.setLevel(logging.DEBUG)
# 创建一个handler，用于写入日志文件
fh = logging.FileHandler('test.log')
fh.setLevel(logging.DEBUG)
# 再创建一个handler，用于输出到控制台
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)
# 定义handler的输出格式
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
fh.setFormatter(formatter)
ch.setFormatter(formatter)
# 给logger添加handler
logger.addHandler(fh)
logger.addHandler(ch)

#配置数据库连接信息
#dbhost='ip'
#dbuser='dbuser'
#dbpasswd='dbpasswd'
#dbname='dbname'

db = pymysql.connect(host=dbhost,user=dbuser,password=dbpasswd,database=dbname)
cursor = db.cursor()
sql = 'select version()'
cursor.execute(sql)
data = cursor.fetchone()
print (data)
db.close()

#判断各种情况格式的合同，'号','号-1','号-2'
flag = 0
def isbaohan(hetongquanchen):
global flag
if '号-1' in hetongquanchen:
hetongquanchen = hetongquanchen.split('号-1')
flag = 1
elif '号-2' in hetongquanchen:
hetongquanchen = hetongquanchen.split('号-2')
flag = 2
elif '号-3' in hetongquanchen:
hetongquanchen = hetongquanchen.split('号-3')
flag = 3
else:
hetongquanchen = hetongquanchen.split('号')
flag = 4

return hetongquanchen

class ModifyContract:
def __init__(self,projectnum,hetongquanchen,hetongjianchen):
self.projectnum = projectnum
self.hetongquanchen = hetongquanchen
self.hetongjianchen = hetongjianchen

#查找项目主键ID
def findprojectid(self):
projectnum = self.projectnum
projectid = ''
sql='SELECT id FROM `credit_loan_apply` WHERE `apply_code` ="%s"' %(projectnum)
db = pymysql.connect(host=dbhost,user=dbuser,password=dbpasswd,database=dbname)
cursor = db.cursor()
cursor.execute(sql)
logger.info('第一步：')
logger.info(sql)
logger.info('语句执行完成 。')
data = cursor.fetchall()
db.close()
logger.info(data)
if data and len(data)==1:
for row in data:
projectid = (data[0][0])
elif data and len(data) > 1:
logger.info('\033[1;31m 第一步：查询项目主ID有多条，退出，手工处理.... \033[0m')
else:
logger.info('第一步：查询项目主ID为空.....')
return projectid

#根据项目主键ID查找合同主ID
def findcontractid(self,projectid):
contractid = ''
projectid = projectid
sql = 'SELECT id FROM `gc_contract_sign_apply` WHERE `apply_id`="%s"' %(projectid)
db = pymysql.connect(host=dbhost,user=dbuser,password=dbpasswd,database=dbname)
cursor = db.cursor()
logger.info('第二步：')
cursor.execute(sql)
logger.info(sql)
logger.info('语句执行完成。')
data = cursor.fetchall()
db.close()
logger.info(data)
if data and len(data)==1:
for row in data:
contractid = (data[0][0])
elif data and len(data) > 1:
logger.info('\033[1;31m 第二步：查询合同主ID有多条，退出，手工处理.... \033[0m')
else:
logger.info('第二步：查询合同主ID为空......')
return contractid
#根据合同主ID，查找合同主ID，修改 contract_bm
# 返回主合同id
def findcontractmasterid(self,contractid):
contractmasterid = ''
contractid = contractid
contract_bm= self.hetongjianchen
sql = 'SELECT id,contract_bm FROM `gc_contract_sign_apply_copies` WHERE `sign_apply_id`="%s"' %(contractid)
sql1 = 'UPDATE `gc_contract_sign_apply_copies` SET contract_bm="%s" WHERE `sign_apply_id`= "%s"'%(contract_bm,contractid)
db = pymysql.connect(host=dbhost,user=dbuser,password=dbpasswd,database=dbname)
logger.info('第三步：')
cursor = db.cursor()
cursor.execute(sql)
logger.info(sql)
data = cursor.fetchall()
logger.info('查找合同主ID: ')
logger.info(data)
if data and len(data) == 1:
for row in data:
contractmasterid = (data[0][0])
contract_bm = (data[0][1])
cursor.execute(sql1)
cursor.execute("commit")
logger.info(sql1)
elif data and len(data) > 1:
logger.info('\033[1;31m 第三步：通过合同主ID，查询到多条主合同ID，退出，手工处理..... \033[0m')
else:
logger.info('第三步：通过合同ID，查找主合同ID，返回值为空...')
db.close()
return contractmasterid

#根据主合同ID，查找contract_num字段，contract_name ，可能有多行
def modifycontractnumandname(self,masterid):
global flag
masterid = masterid
hetongquanchen = self.hetongquanchen
hetongquanchen = isbaohan(hetongquanchen)
flag1 = flag
logger.info('flag1:')
logger.info(flag1)
sql = 'SELECT id,contract_number,contract_name FROM `gc_contract_sign_apply_detail` WHERE `copies_id` LIKE "%s"' %(masterid)
db = pymysql.connect(host=dbhost,user=dbuser,password=dbpasswd,database=dbname)
cursor = db.cursor()
try:
cursor.execute(sql)
logger.info('第四步:')
logger.info(sql)
data = cursor.fetchall()
logger.info(data)
if data:
for row in data:
print (row)
rid = row[0]
rcontract_num = isbaohan(row[1])
rcontract_name = isbaohan(row[2])
rcontract_num[0] = hetongquanchen[0]
rcontract_name[0] = hetongquanchen[0]
if flag1 == 1:
contract_num = rcontract_num[0] + '号-1' + rcontract_num[1]
contract_name = rcontract_name[0] + '号-1' + rcontract_name[1]
elif flag1 == 2:
contract_num = rcontract_num[0] + '号-2' + rcontract_num[1]
contract_name = rcontract_name[0] + '号-2' + rcontract_name[1]
elif flag1 == 3:
contract_num = rcontract_num[0] + '号-3' + rcontract_num[1]
contract_name = rcontract_name[0] + '号-3' + rcontract_name[1]
else:
contract_num = rcontract_num[0] + '号' + rcontract_num[1]
contract_name = rcontract_name[0] + '号' + rcontract_name[1]
print (contract_num)
print (contract_name)
logger.info('contract_num: '+ contract_num)
logger.info('contract_name: '+ contract_name)
sql1 = 'UPDATE `gc_contract_sign_apply_detail` SET contract_number="%s", contract_name="%s" WHERE id = "%s"'%(contract_num,contract_name,rid)
cursor.execute(sql1)
logger.info(sql1)
cursor.execute("commit")
else:
logger.info('第四步:通过查询合同主ID返回值为空...')
except:
t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
logger.info('第四步： SQL 执行失败,执行语句为:%s'%(sql))
finally:
db.close()

## 根据项目编号，修改合同全称和合同简称
def modifycontractnameandshortname(self):
projectnum = self.projectnum
hetongquanchen = self.hetongquanchen
hetongjianchen = self.hetongjianchen
sql = 'SELECT apply_code,contract_name,contract_name_short FROM `gc_credit_result_info` WHERE `apply_code`="%s"'%(projectnum)
sql1 = 'UPDATE `gc_credit_result_info` SET contract_name="%s",contract_name_short="%s" WHERE `apply_code`= "%s"'%(hetongquanchen,hetongjianchen,projectnum)
db = pymysql.connect(host=dbhost,user=dbuser,password=dbpasswd,database=dbname)
cursor = db.cursor()
try:
cursor.execute(sql)
logger.info('第五步:')
logger.info(sql)
data = cursor.fetchall()
logger.info('根据项目编号查找合同全称，合同简称：')
logger.info(data)
print (len(data))
if data and len(data) == 1:
cursor.execute(sql1)
logger.info(sql1)
cursor.execute("commit")
elif data and len(data) > 1:
logger.info('\033[1;31m 第五步，查询多条，退出手工处理.... \033[0m')
else:
logger.info('第五步，查询为空..')

except:
logger.info('第五步：error "%s"' %(sql))
finally:
db.close()

ht=[]
with open('htbhwxg.txt','r') as f:
logger.info('第一步：通过项目编号查找项目ID，开始执行语句....')
logger.info('第二步：根据项目主键ID查找合同ID，开始执行语句...')
logger.info('第三步：通过合同ID，查找合同主ID，并修改contract_bm...')
logger.info('第四步：通过合同主ID，修改contract_num字段和contract_name 字段，可能有多行..')
logger.info('第五步：通过项目编号修改合同简称，合同全称，如果查询为空，不处理...')
while True:
line = f.readline().strip()
ht = line.split('；')
#print(ht)
lennum = len(ht)
if lennum == 4:
projectnum = ht[0].strip()
hetongquanchen = ht[2].strip()
hetongjianchen = ht[3].strip()
#print ('%s %s %s'%(projectnum,hetongquanchen,hetongjianchen))
m = ModifyContract(projectnum,hetongquanchen,hetongjianchen)
## 开始实例化并操作
logger.info('## 开始实例化操作... : %s ; %s ; %s'%(projectnum,hetongquanchen,hetongjianchen))
projectid = m.findprojectid()
if projectid:
pass
else:
logger.info('## 实例化结束 ##')
continue
contractid = m.findcontractid(projectid)
if contractid:
pass
else:
logger.info('## 实例化结束 ##')
continue
masterid = m.findcontractmasterid(contractid)
if masterid:
pass
else:
logger.info('## 实例化结束 ##')
continue
m.modifycontractnumandname(masterid)
m.modifycontractnameandshortname()
logger.info('## 实例化结束 ##')
print ()
if not line:
break
print ('###')

1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.121.122.123.124.125.126.127.128.129.130.131.132.133.134.135.136.137.138.139.140.141.142.143.144.145.146.147.148.149.150.151.152.153.154.155.156.157.158.159.160.161.162.163.164.165.166.167.168.169.170.171.172.173.174.175.176.177.178.179.180.181.182.183.184.185.186.187.188.189.190.191.192.193.194.195.196.197.198.199.200.201.202.203.204.205.206.207.208.209.210.211.212.213.214.215.216.217.218.219.220.221.222.223.224.225.226.227.228.229.230.231.232.233.234.235.236.237.238.239.240.241.242.243.244.245.246.247.248.249.250.251.252.253.254.255.256.257.258.259.260.261.262.263.264.265.266.267.268.269.

网址：在日常生活中利用python写脚本提升工作效率 https://www.yuejiaxmz.com/news/view/368829

相关内容

Python自动化脚本：实现工作生活的高效秘诀
AI助力自动化：利用AI生成BAT脚本与Python代码，简化日常生活任务
掌握这17个Python自动化操作，简化你的日常工作流程，提升工作效率！
【源码】10 个用于日常自动化的 Python 脚本！
掌握Python，高效生活：揭秘5个实用脚本，轻松解决日常编程难题！
提升写作效率的秘密武器：人工智能写作app在日常创作中的应用
python如何改变日常生活
10个Python自动化脚本，让日常任务轻松便捷！
用Python搭建实用小工具，轻松提升生活效率！
从零开始构建：Python自定义脚本自动化你的日常任务

随便看看