准备阶段:
腾讯接口一个,飞书配置的消息机器人的webhookUrl地址;
python环境
Oracle数据库的一张表
开始吧:
1.发送飞书消息的方法,这个可以找飞书的自定义机器人使用说明获取;我们起个名字吧;fsmsg.py
import requests
import json
import hashlib
import base64
import hmac
import timedef gen_sign(timestamp, secret):# 拼接timestamp和secretstring_to_sign = '{}\n{}'.format(timestamp, secret)hmac_code = hmac.new(string_to_sign.encode("utf-8"), digestmod=hashlib.sha256).digest()# 对结果进行base64处理sign = base64.b64encode(hmac_code).decode('utf-8')return sign# 飞书机器人secret
FS_SECRET = ""
# 定义 Webhook URL
FS_WEBHOOK_URL = ''# 定义要发送的消息内容
def send_fsmsg(msg):try:message = {'content': {"text": msg},"msg_type": "text",'timestamp': '','sign': ''}timestamp = int(time.time())message['timestamp'] = timestampmessage['sign'] = gen_sign(timestamp, FS_SECRET)# 将消息内容转换为 JSON 格式message_json = json.dumps(message)# 发送 POST 请求到 Webhook URLresponse = requests.post(FS_WEBHOOK_URL, data=message_json, headers={'Content-Type': 'application/json'})# 输出响应结果print(response.text)except Exception as e:print(e)
2.包个腾讯接口调用的api吧,不知道怎么写时,可以去腾讯的sdk下载参考,起个名字tencentApi.py
import json
import os# 导入可选配置类
from tencentcloud.common import credential# pip install tencentcloud-sdk-python
# 引入实名核身结果信息增强版client
from tencentcloud.faceid.v20180301 import faceid_client, models# 通过环境变量导入AK/SK
AK = ""
SK = ""
client = Nonedef init_client():# 创建访问凭据cred = credential.Credential(AK, SK)# 创建客户端实例return faceid_client.FaceidClient(cred, "ap-shanghai")# 获取实名核身结果信息
def get_face_info(biz_token, rule_id):try:# 实名核身结果信息增强版请求参数结构体req = models.GetDetectInfoEnhancedRequest()# 传入实名核身结果信息增强版请求参数params = {"BizToken": biz_token,"RuleId": rule_id,"InfoType": "1"}req.from_json_string(json.dumps(params))# 获取实名核身结果信息增强版响应结果global clientif client is None:client = init_client()resp = client.GetDetectInfoEnhanced(req)# 输出json格式的字符串回包# print(resp.to_json_string(indent=2))return respexcept Exception as e:print(e)
3.我想重数据库查个表,然后导出Excel出来,起个名字吧main.py
import sys
import datetime
# 安装cx_Oracle模块
# pip install cx_Oracle
import cx_Oracle# 安装openpyxl模块
# pip install openpyxl
import openpyxl# 引入tencentApi.py
import tencentApi
import json
import fsmsgOL="" # 数据库地址
PATH="" # 输出地址
D_PATH="" # 下载地址def main():now = datetime.datetime.now()# 使用日期生成文件名filename = PATH + now.strftime("file_%Y-%m-%d_%H-%M-%S") + ".xlsx"# 获取昨天日期, 格式为yyyymmddyesterday = (datetime.datetime.today() + datetime.timedelta(days=-1)).strftime('%Y%m%d')print("start fetching data [" + filename + "]...")# 这里有个坑,需要下载Oracle客户端才能连接,Windows需要指定一下cx_Oracle.init_oracle_client(lib_dir="instantclient_21_9")# 链接oracle数据库conn = cx_Oracle.connect(OL)sql = "小SQL上来"# 使用cursor()方法获取操作游标cursor = conn.cursor()# 使用execute方法执行SQL语句cursor.execute(sql)columns = {desc[0]: idx for idx, desc in enumerate(cursor.description)}# 创建excelworkbook = openpyxl.Workbook()worksheet = workbook.active# 写入表头, 在columns基础上增加一列, FACE_ID# headers = list(columns.keys())headers = headers = [desc[0] for desc in cursor.description]headers.append("TYPE")headers.append("FACE_INFO")worksheet.append(headers)# 遍历数据for row in cursor:biz_token = row[columns['BIZ_TOKEN']]# 重建 每行信息row = list(row)print("fetching face info for biz_token: " + biz_token + "...")rst_face = tencentApi.get_face_info(biz_token, "2")if rst_face is not None:jsondata = rst_face.to_json_string(indent=2)row.append(jsondata)else:row.append("")row.append("")# 写入excelworksheet.append(row)# 关闭数据库连接conn.close()# 保存文件workbook.save(filename= filename)# 外网访问地址new_filename = D_PATH + filename.replace(PATH, "")# 发送飞书消息if fsmsg.FS_WEBHOOK_URL != "":fsmsg.send_fsmsg("生成:" + new_filename)if __name__ == '__main__':# 通过参数指定AK, SKtencentApi.AK = sys.argv[1] # os.getenv('AK')tencentApi.SK = sys.argv[2] # os.getenv('SK')OL = sys.argv[3] # os.getenv('OL')if len(sys.argv) >= 5:PATH = sys.argv[4] # os.getenv('PATH')if len(sys.argv) >= 6:D_PATH = sys.argv[5] # os.getenv('D_PATH')if len(sys.argv) >= 8:fsmsg.FS_SECRET = sys.argv[6] # os.getenv('FS_SECRET')fsmsg.FS_WEBHOOK_URL = sys.argv[7] # os.getenv('FS_WEBHOOK_URL')main()
启动参数
# 参数说明
AK # 腾讯AK
SK # 腾讯SK
ORACLE_URL # 数据库地址
PATH # Excel生成地址
D_PATH # 可以下载的地址
FS_SECRET # 飞书消息的SECRET
FS_WEBHOOK_URL # 飞书消息的WEBHOOK_URL
启动命令
main AK SK ORACLE_URL [PATH,D_PATH,FS_SECRET,FS_WEBHOOK_URL] >> ./log/log.log 2>&1
注意
1.开发环境初始化:yum install python3 python3-devel libaio-devel(Linux的)
2.若有源下载不了问题,需要修改源,找到或新建一个pip/pip.ini;下面是清华大学的源
[global]
index-url = https://pypi.tuna.tsinghua.edu.cn/simple
Linux下载并安装 oracle的客户端
## Linux环境时下载oracle instant client
```bash
# 下载Oracle Instant Client
curl -o oracle-basiclite.rpm https://download.oracle.com/otn_software/linux/instantclient/2110000/oracle-instantclient-basiclite-21.10.0.0.0-1.el8.x86_64.rpm# 安装Oracle Instant Client
sudo rpm -ivh oracle-basiclite.rpm --force --nodeps
# 移除安装包
rm -rf oracle-basiclite.rpm
Windows版,需要设置 cx_Oracle.init_oracle_client(lib_dir="instantclient_21_9")
去这里下载吧Oracle Instant Client Downloads
打包成Linux运行程序
# 安装pyinstaller(可能需要pip install wheel)
pip install pyinstaller# 打包
pyinstaller -F main.py
设置定时任务去执行吧
## 设置为可执行程序
chmod +x mica## Linux定时任务配置
crontab -e 打开定时任务配置
crontab -l #列出crontab任务列表
tail -f /var/log/cron #打印crontab日志(默认打印10条)
# 每次修改crontab之后会默认重启crontab
#编辑crontab服务,配合insert esc :wq(保存退出) :q!(不保存退出)等指令# 定时器配置 如下:
06 06 * * * /home/main AK SK ORACLE_URL PATH D_PATH FS_SECRET FS_WEBHOOK_URL >> /home/log/log.log 2>&1