PythonETL背景介绍
一、背景：
1 、数据孤岛-现象
各业务数据不互通，无法发挥数据的利用价值。
2、数据仓库-解决
通过工具将零散数据集中存储（Hadoop的hdfs组件工具）、分析（mapreduce或spark），最大限度挖掘数据价值。
3、 ETL的引出-具体
ETL:Extract-Transform-Load，数据从源头抽取（输入）、转换（处理）、加载（输出）到目的地。即对数据进行高效收集。常在数仓中用，不仅限用在数仓。
4、 ETL实现
ETL实现工具（简单型）：Kettle、Datax、Sqoop
工具优势：简单易用，配置方便，无需代码鼠标拖拽
工具劣势：不灵活，特定任务难以实现
代码Python、Java、Scala完成ETL开发（复杂型）
优势：自由处理
劣势：开发时间长
二、常见数据格式：
CSV、JSON、XML
1、CSV格式（Comma-Separated Values）逗号分隔值，又名字符分隔值，常用半角逗号（,）或制表符（/t），也可用其他符号。
将txt文件改为csv（修改文件属性后缀：计算机-查看-文件扩展名）本质上仍是文本文件，但格式上是csv（文件类型不只取决于后缀），ctrl+h（替换符号）。
csv可以有表头Header(关系到后续处理)，有则将首行加载为列名，否则直接处理。
id,name,age#可有可无 1,天,99 2,地,100
2、JSON格式（JavaScriptObjectNotation）JavaScript对象表示法。Key-value型，与Python中的字典格式一一对应，是字典的静态格式，是字典的字符串形态。
JSON类似XML，存储和交换文本的语法，比XML更小、快、解析。
{
"sites":{
{"name":"菜鸟教程","url":"www.bird.com"},
{"name":"微博","url":"www.weibo.com"}
}
}
{
"name":"lisa",`
"age":10`,
"like":{"music","run"}`
`}`
3、XML格式（eXtensibleMarkupLanguage）可扩展标记语言。
XML所有标签成对出现，有始有终。
XML传输和存储数据（如用在Hadoop、spark和Hive的配置文件格式），注意把修改的配置项放在特定范围内。
#xml通过作开头，作为结束的配置项格式，note项目有4个子项
<?xml version="1.0" encoding="UTF-8"?>#XML必带
<note>
<to>Beijing</to>
<from>Qinhuangdao</from>
<heading>book</heading>
<body>Remember me!</body>
<note>
#xml无缝转为Json
{
"note":{
"to":"Beijing",
"from":"Qinhuangdao",
"heading":"book",
"body":"Remember me!"
}
}
采集JSON，生成CSV。
三、 数据结构类型：
常见数据：非结构、半结构、结构化数据，数据结构VS数据类型
1、结构化数据：
判断：可按行列存入到MySQL/Excel中。数据有表结构（schema，对一种数据结构的描述，数据结构即规范化，定义列名和列类型）。
| information | ||
|---|---|---|
| name(string) | age(int) | address(string) |
| Lisa | 20 | Shanghai |
常见结构化数据：可用表结构、MySQL/Excel/CSV中的数据。JSON不一定是结构化，根据内容判断，如简单的Key-Value是，复杂嵌套不是（无法用二维表表示）。
20230815，0101-0104（63min），3h。
2、半结构化数据：
判断：数据本身有特定描述形式/组织形式，但不能二维表达。如XML无法无损转化为二维形式。
<!--xml-->
<configuration>
<property>
<name>dfs.datanode.data.dir.perm</name>
<value>700</value>
</property>
<property>
<name>dfs.namenode.name.dir</name>
<value>/data/nn</value>
</property>
<configuration>
<!--YAML-->
companies:
-
id:1
name:company1
price:200w
-
id:2
name:company2
price:300w
<!--ini-->
[win32**.mapi]
dsp=*
cl=*
libs=*
[win32**.exchange]
#重复以上
JSON：获取数据之前是不知道表结构（因为key:value中的value可以是字符串、数字等，也可以是嵌套）。有的JSON无法用固定schema描述，只能描述部分，不能描述全部。
# 获取的数据
{"name": "zhangsan", "age": 10, "like": ["football", "music"]}
{"string": "string", "string": int, "string": Array[String]} # 表结构
# 以下数据的表结构和上面的数据的表结构同，但表结构能力有限，不能完全约束数据，可能产生歧义？
{"title": "developer", "level": 10, "like": ["football", "music"]}
3、非结构化数据：
判断：完全无法用schema定义
举例：代码、程序日志、协议、二进制、音图视频、PPT
4、数据结构和数据类型的区别
数据类型：一个具体的数据类型
数据结构：一堆数据组合一起形成的数据整体
数据结构可以包含各种各样的数据类型。
四、 需求分析：
背景说明，需采集的数据，在哪，输到哪？
1、背景说明：
#JSON格式化软件https://cn.bing.com/-搜json格式化-找线上
零售公司的数据案例
#，JSON数据文件，收银机里的一个订单记录
"discountRate": 1,
"storeShopNo": "None",
"dayOrderSeq": 37,
"storeDistrict": "芙蓉区",
"isSigned": 0,
"storeProvince": "湖南省",
"origin": 0,
"storeGPSLongitude": "undefined",
"discount": 0,
"storeID": 1766,
"productCount": 1,
"operatorName": "OperatorName",
"operator": "NameStr",
"storeStatus": "open",
"storeOwnUserTel": 12345678910,
"payType": "cash",
"discountType": 2,
"storeName": "亿户超市郭一一食品店",
"storeOwnUserName": "OwnUserNameStr",
"dateTS": 1542436490000,
"smallChange": 0,
"storeGPSName": "None",
"erase": 0,
"product": [
{
"count": 1,
"name": "南京特醇",
"unitID": 8,
"barcode": "6901028300056",
"pricePer": 12,
"retailPrice": 12,
"tradePrice": 11,
"categoryID": 1
}
],
"storeGPSAddress": "None",
"orderID": "154243648991517662217",
"moneyBeforeWholeDiscount": 12,
"storeCategory": "normal",
"receivable": 12,
"faceID": "",
"storeOwnUserId": 1694,
"paymentChannel": 0,
"paymentScenarios": "OTHER",
"storeAddress": "StoreAddress",
"totalNoDiscount": 12,
"payedTotal": 12,
"storeGPSLatitude": "undefined",
"storeCreateDateTS": 1540793134000,
"storeCity": "长沙市",
"memberID": "0"
}
2、需采集的数据：
三份数据源（以零售业务为例）:
（1）JSON：订单业务
在哪：通过网络发到后台的订单信息，存JSON中。
（2）后台MySQL：商品信息
在哪：存储商品的条码信息，存后台MySQL中。
（3）后台文本文件：log日志的服务业务数据（服务器运行性能指标）
在哪：记录后台的被访问信息，存在后台日志文件中。（启动数据产生的模拟器来产生日志数据）
三份数据都输出到MySQL（数仓技术）和CSV中。
2023.8.16,0105-0107（34min），1.5h
项目需求1：JSON数据处理I
一、JSON数据文件
1、生成逻辑
后端服务器定期写一个JSON文件（每次生成1M左右）。
我们任务-定期读取并处理源源不断的JSON（源数据-待采集-内存）。
tip-不重复处理已处理过的文件。方法-已处理的文件存文件或数据库表中，存数据库好处（把文件名存数据库的表中）-安全不易受损有专业运维+便捷（存储或修改只需改SQL）。
科普区分：内存（！=内部存储），正确的内存=计算机的Memory。hdfs分布式文件系统大小取决于机器数量。
2、JSON数据格式
3、采集和处理方式
建立元数据库存储元数据（元数据metadata即描述数据的数据，如属性，数本身据属于业务数据）-用来存储是否被采集过的状态信息，避免重复采集。在案例中，被处理文件的文件名就是元数据。
二、项目开发-初始化
1、项目中使用MySQL
在项目中接触三个数据库：在企业中三个数据库分别在不同服务器（电脑）上操作，模拟学习时暂时在一个MySQL中使用。
-
提供数据的数据源库
-
元数据管理的库
-
数据处理的目的地库（通过ETL转化处理后的数据）
右键-New-Query Console？安装数据库软件
导入源数据：通过datagroup?打开数据库-自动在数据库显示-右键run-Edit Configuration-Target data source+-python-etl下的source_data-apply-run。
2、创建项目的PyCharm工程
Pure Python-选择目录(python-etl)-Previously configred interpreter解释器-创建4个package（其下有init—py文件，Directory下无）
-
config（记录ETL工程的配置信息）
-
model（记录数据模型）
-
test（做单元测试）
-
util（记录工具方法）
三、项目开发-日志模块
1、日志介绍
日志就是程序在运行时，程序当前状态，通过对外输出信息来确定。对外输出的这个信息，就是`程序的运行日志。
2、logging模块
logging是Python自带的日志输出工具，类似于print语句，可控制输出级别。先编写基本日志模块代码，供业务逻辑代码使用。
2.1日志级别
logging：标准的日志模块，支持日志的级别控制（ logging每个具体级别都有一个数字代码）。Directory-Python file。
import logging#不需要导入包，python自带
#复杂计算
a=100
logging.debug("你好，我是debug，计算结果是："+str(a))
logging.info("你好，我是info")
logging.warning("你好，我是warning")
logging.error("你好，我是error")
logging.fatal("你好，我是fatal")
#输出时默认输出waring及以上级别（warn,30），可灵活设置级别，但小于被设置级别的日志不会输出
#快捷键：settings-Editor-Plugins-IdeaVim插件
#输出
WARNING:root:你好 我是 warning
ERROR:root:你好 我是 error
CRITICAL:root:你好 我是 fatal
-
50CRITICAL(FATAL)：灾难级别，挂了
-
40ERROR:错误级别，有错但不一样影响运行
-
30WARN:警告级别，有可能有隐患
-
20INFO:信息级别，输出普通信息
-
10DEBUG:调试、话痨级别，输出非常详细的日志
-
NOTSRT=0
2023.8.17，0108-0111（83min），3h。
2.2logging基本使用
可以控制允许哪个级别的日志被输出。
logging的常规使用方式（以下代码在新创建的learning目录下的test_logging完成。注释一段文字的方法？）
#logging的基本使用
#1.获取logger对象
logger=logging.getLogger('test')#获取一个logging对象
fmt=logging.Formatter("%(asctime)s-[%(levelname)s]-%(filename)s[%(lineno)d]:%(message)s")#格式化
#2.设置logger对象属性
#2.1把日志输出到终端
stream_handler=logging.StreamHandler()#获取一个将日志输出到控制台的Handler对象
stream_handler.setFormatter(fmt)
logger.addHandler(stream_handler)#给logger对象添加了将日志输出到控制台的功能
#2.2把日志输出到文件
file_handler=logging.FileHandler(
filename="D:\1.notebook\ETL\logs\test.log",mode="a",encoding="utf-8")#路径必须存在
file_handler.setFormatter(fmt)
logger.addHandler(file_handler)
3.使用对象属性
logger.setLevel(10)#控制输出级别
logger.debug("你好，我是debug")
logger.info("你好，我是info")
logger.warning("你好，我是warning")
logger.error("你好，我是error")
logger.fatal("你好，我是fatal")
#for i in range(10):
#logger.info("你好，我是info "+str(i))
#文件的操作模式：r,w,a,rb,wb
2.3logging输出格式控制
问题引入：若日志输出在终端，不小心把终端关闭，如何调试。把日志写入到日志文件。 #?终端输出后报错，别人调试时自己把窗口关了，再复现错误需运行很久。
详见以上代码的把日志输出到文件，设置文件格式，运行完成后，Filename的路径里产生日志文件
logging中的formatter，提前设定好日志中要展示的信息。#print("%s", % "abc")
import logging
logger=logging.gerlogger("test")
#fmt=logging.Formatter("%(name)s")
#终端输出，test
fmt=logging.Formatter("%(name)s-%(levelname)s-%(message)s")
#构造formatter
stream_handler=logging.StreamHandler()
stream_handler.setFormatter(fmt)
logger.addHandler(stream_handler)
logger.setlevel(20)
logger.info("你好，我是info")
附录:Formatter参数列表 %(name)s Name of the logger (logging channel) %(levelno)s 日志的等级数字 %(levelname)s 日志的等级字符串 %(pathname)s 日志的路径 %(filename)s 输出日志的文件名称 %(module)s Module (name portion of filename) %(lineno)d 输出日志的代码的行数 %(funcName)s 方法名称 %(created)f Time when the LogRecord was created (time.time() return value) %(asctime)s 输出日志的时间 %(msecs)d Millisecond portion of the creation time %(relativeCreated)d Time in milliseconds when the LogRecord was created, relative to the time the logging module was loaded (typically at application startup time) %(thread)d Thread ID (if available) %(threadName)s Thread name (if available) %(process)d Process ID (if available) %(message)s 日志的正文信息 """
2.4logging输出到文件中
输出到文件，只需把Handler替换成文件Handle。说明如下：logging有2个常用Handler
-
StreamHandlre:logging.StreamHandler
-
FileHandler:logging.FileHandler(参数1，参数2，参数3)
-
参数1：文件路径
-
参数2：模式，“a”是追加，“w”是覆盖
-
参数3：编码，一般UTF-8
问题引入：每次都在文件中打开，比较繁琐，可以同时在终端和文件中输出。
日志模块梳理：
20230818,0112-0114（59min)，3h
待复习3、准备logging工具
4、logging工具单元测试
4.1单元测试
4.2结果验证方法
四、项目开发-处理文件工具
梳理ETL业务逻辑：
-
查看文件列表：在存放被采集JSON数据的文件夹中
-
筛选新文件：对比MySQL已记录的信息
-
读取JSON文件：执行ETL操作（打开读取文件，提取文件信息，写入目的地）
-
记录已处理文件：将已被处理JSON记录到MySQL数据库
1、创建处理文件工具
做测试learning-test_os.py
import os
#os库
#输出目录中所有文件和文件夹
files=os.listdir("D:/2.project/data/pyetl-data-logs/json/"))#不支持子目录查询
#python判断文件、目录，os.path.isdir()
for file in files:
print(file,os.path.isdir("D:/2.project/data/pyetl-data-logs/json/"+file))#如果不加绝对路径，判断错误？
#json父目录：相对路径..，绝对路径D:/2.project/data/pyetl-data-logs
需求：让工具支持查询目录中的文件功能（预判能力，预判后面的日志存储方式变化，只是有一定扩展性和健壮性，不能过度设计）
思路：递归查询目录文件：获取当前目录下文件名，如果是具体文件，append；如果是目录，传给函数，继续获取文件。介绍斐波那契：做测试learning-fib.py
def fib(num):
if num==1 or num==2:
return 1;
else:
return fib(num-1)+fib(num-2)
print(fib(20))
2、常见处理文件单元测试
#coding:utf8
import os
form util.logging-util import init_logger
logger=init_logger()#加日志工具
def get_dir_files_list(path="./",recursive=False):
"""获取path目录中所有文件名称
#path为被判断文件夹的路径，默认当前路径
#recursive是否递归，根据需求设定
#return:list对象，存储文件的路径
"""
#1.获取制定目录中的文件名称
#API返回指定path下的文件和文件夹，只接收绝对路径
dir_names=os.listdir(path)
logger.debug(dir_names)#加日志工具
#2.判断是文件还是文件夹，循环取每个名称判断
files=[]#定义一个list，记录文件
for dir_name in dir_names:
logger.debug(dir_name)#加日志工具
absolute_path=f"{path}/{dir_name}"#f的意思是？
if not os.path.isdir(absolute_path):
files.append(absolute)
logger.debug(files)#加日志工具
else:
logger.debug(absolute_path)#加日志工具
if recursive:
files+=get_dir_file_list(absolute_path,true)#拼接内层的返回list列，到当前层的list，或者absolute_path用path+dir_name+"/"表示
return files
print(get_dir_files_list("D:/2.project/data/pyetl-data-logs/json/"))#反斜线，最后是反斜线
#for i in range(1,10):a+=[i]
2、创建处理文件单元测试
耦合：不同事情紧密联系在一起（比如代码放的测试老师电脑的路径）
解耦合：让不同事情不紧密联系（如在工程根目录下test_dir文件下建立）#directory
在项目的test目录下创建
test_dir/
inner1/
3
4
inner2/
5
1
2
#动态获取项目目录，一开始不要写死
os.getcwd()获取当前代码所在的目录
os.dirname() 获取指定文件/目录的上一级目录
print(os.path.dirname(os.getcwd())) 即可获取到项目目录D:\project\bigdata\python-etl
#os.path.basename
import os
file_name="D:/2.project/data/pyetl-data-logs/json/x01.log"
print(os.path.basename(file_name))
#输出x01.log
#dirname+basename
"D:/logs/1.log"
os.dirname("D:/logs/1.log") 得到的是 "D:/logs/"
os.basename("D:/logs/1.log")得到的是 "1.log"
单元测试：在test包中，创建test_file_util.py
#coding:utf8
#python3.0之后默认都是coding:utf8
form unittest import TestCase
from util.file_util import get_dir_files_list
class TestFileUtil(TestCase):#自己起名字
def setUp(self)->None:?
self.project_root_path=os.dirname(os.getcwd())
pass#?
def test_get_dir-files_list(self):
test_path=f"{self.project_root_path}/test/test_dir/"
#1.测试不递归,最后加/
result=get_dir_files_list(test_path,recursive=False)
files=[]
for i in result:
files.appened(os,basename(i))
files.sort()#排序以便与以下一致
self.assertEqual(["1","2"],files)
#2.测试递归
result=get_dir_files_list(test_path,recursive=True)
files=[]
for i in result:
files.appened(os,basename(i))
files.sort()#排序以便与以下一致
self.assertEqual(["1","2","3","4","5"],files)
pass
五、项目开发-MySQL工具
功能描述：和MySQL内记录的信息做对比，判断哪些文件是新文件，需要采集的。
1、准备配置信息
2、编写MySQL工具类
util包下创建mysql_util
把工具函数定义在类中，可以减少建立类似连接的耗时操作，代码比较规范项目涉及的数据库：元数据库，数据源库，目的地库。
将创建的工具类总需实现以下函数。
-
1 建立连接和关闭连接
-
2 执行查询操作
-
3 执行sql语句，不关心返回结果（一个是带提交，不带提交）
-
4 查询表是否存在
-
5 查询表是否存在，不存在创建表
实现的两种方式：
-
1.不写类，将功能写成方法，重复编写
-
2.写一个class类，将方法写到类里面
-
选第二种理由：要维护链接，而class有一个成员变量就是链接，只要class对象没有被销毁，链接成功后，可以持续使用。而直接使用无类的方法的链接，是一次性的。
演示pymysql基本使用，learing下的test_mysql
import pymsql
conn=pymysql.Connection(host="localhost",port=306,user="root",password="mysql",charset="utf8",autocommit=False)
#用DataGrip连接数据库，测试操作
#conn切换数据库
conn.select_db("test")
#获取游标对象
cursor=conn.cursor()
#执行SQL语句
cursor.execute("select * from tb_stu")
result=cursor.fetchall()
print(result)
#执行插入语句
cursot.execute("insert into tb_stu values(3,"李四")")#直接打印此句返回的是影响的行数，1
conn.commit()#加上此句是因为设置autocommit为False后，执行插入语句后，只能放在缓存中，无法插入数据库中
cursor.execute("select * from tb_stu")
print("fetchall",cursor.fetchall())
#获取数据库表
cursor.execute("SHOW TABLES")
result=cursor.fetchall()
print result
if ("tb_stu",) in result:#注意result存放的元组形式，所以带查询的内容需要有,
print("存在")
else:
print("不存在")
#上图演示如何插入数据库数据，以及用cmd连接数据库
pymysql中Connection有一个autocommit的参数，设置为True后，就不必手动提交commit；否则需手动提交，不提交的话，数据写在缓存中，没有写入数据库。
正式编写MySQL工具类，util下的mysql_util。
"""与mysql操作相关的工具都在此文件"""
import pymysql
from util.logging_util import init_logger
from config import project_config as pc#导包的方式？
logger=init_logger()
class MySQLUtil:
def _init_(self,
host=pc.metadata_host,
port=pc.metadata_port,
user=pc.metadata_user,
password=pc.metadata_password,
charset=pc.mysql_charset,):#初始化,优化传参，设置默认值
self.conne=pymysql.Connection(
host=host,#host="localhost",#主机IP?
port=port,#port=3306,#端口号？
user=user,#user="root",
password=password,#password="mysql",
charset=charset,#charset="utf8",#字符集
autocommit=False)#不自动提交性能更优
logger.info(f"构建完成到{pc.metadata_host}:{pc.metadata_port}的数据库连接")#加入日志{conf.metadata}：{conf.metadata_port}是不是活动的？
def close_conn(self):#建立连接
if self.conn:
self.conn.close()
def select_db(self,db):#切换数据库?
self.conn.select_db(db)
def query(self,sql):#查询
cursor=self.conn.cursor()
cursor.execute(sql)
result=cursor.fetchall()
cursor.close()
logger.info(f"执行查询的SQL语句完成，查询结果有{len(result)}条，执行的查询SQL是：{sql})#加入日志
return result
def execute(self,sql):#执行SQL语句,不关心返回结果，(自动更新数据库)。
#确保修改的数据会提交到数据库中。
cursor=self.conn.cursor()
cursor.execute(sql)#此句调用了execute函数，但不确定是否写入数据库
#如果autocommit=False，还需执行commit
logger.debug(f"执行了一条SQL:{sql}")#加入日志
if self.conn.get_autocommit():
pass
else:
self.conn.commit()
cursor.close()
def execute_without_commit(self,sql):#直接执行SQL语句(手动更新数据库),不关心是否提交，如遇到大量数据，可以批量提交
cursor=self.conn.cursor()
cursot.execute(sql)
logger.debug(f"执行了一条SQL:{sql}")#加入日志
cursor.close()
def check_table_exists(self,db_name,table_name):#查询表是否存在
self.select_db(db_name)#添加切换数据库，更严谨
#cursor=self.conn.cursor()
#cursor.execute("SHOW TABLES")
#result=cursor.fetchall()#后加入此句
result=self.query("SHOW TABLES")#此句可以替代原来的4句
return (table_name,) in result#最简洁版
#cursor.close()
def check_table_exists_and_create(self,db_name,table_name,create_cols):#查询表是否存在，不存在则创建
if not self.check_table_exists(db_name,table_name):
create_sql=f"CREATE TABLE {table_name}({creat_cols})"
self.select_db(db_name)#切换数据库
self.execute( create_sql)
logger.info(f"在数据库：{db_name}中创建了表，{table_name}完成，建表语句是：{create_sql}")#加入日志
else:
logger.info(f"数据库：{db_name}中，表{table_name}已经存在，创建表的操作跳过")#加入日志
if _name_=="_main_"#直接输入main,有无此行都行
mysql_util=MySQLUtil()
mysql_util.select_db("test")
print(mysql_util.query("select * from tb_stu"))#养成好习惯，写入函数时适时做测试
mysql_util.execute("insert into tb_stu values(5,"王五")")
mysql_util.execute——without_autocommit("insert into tb_stu values(6,"刘七")")#不显示插入数据库
mysql_util.check-table_exists("test",)
print(mysqlutil.check_table_exists_and_create("test","tb_stu5","name varchar(20),age int"))
#上图演示mysql中列举所有数据库表的方法
20230820,0211-0216（75min），3.5h
实现init函数的不足与需求：前面代码把连接的数据库写死了。需求是可以通过这个MySQLUtil连接到三个数据库，需将这些设置在config中配置。
重构init(在config包下的project_config)
import time
#日志相关配置，开始部分，？
log_root_path="D:/logs/"
log_name=f"pyetl-{time.strftime("Y%m%d-%H",time.localtime(time.time()))}.log"
log_level=10
log_fommatter="%(asctime)s-[%(levelnam)s]-%(filename)s[%(lineno)d]:%(message)s"
#元数据库相关的配置，开始
mysql_charset="utf8"
#元数据管理库的配置,可通过更改配置，连接任何一个数据库
metadata_host="localhost"
metadata_user="root"
metadata_password="123456"
metadata_port=3306
#之后在mysql_util中导包，详见以上代码
3、MySQL工具类单元测试
测试分析：1.工具的一个特性是复用性，编完后注意检查bug，所以会做测试；2.解耦合思想（如测试时自创表格，数据库不动）；3.以最少测试内容覆盖全部函数，若一个函数包含多个函数，可以在测试该函数时，省略测试其包含的函数。
#语句，truncate与delete删除区别，前者是ddl语句（dql,dml,dcl），表级删除，保留表结构先删除再重建，？执行trucate是为防止之前已创建此表。
在test包下，建立test_mysql_util.py
#先写需求，再写框架
"""mysql_util单元测试"""
from unittest import TestCase#?
form util.mysql_util import MySQLUtil
class MySQLUtilTest(TestCase):
def setUp(self)->None:
self.db_util=MySQLUtil()
def test_query(self):#一次性测试多个函数
#确保解耦合，先创建一张表，查询数据，从而进行判断
self.db_util.select_db("test")#先写死
self.db_util.check_table_exists_and_create(
"test",
"for_unit_test".
"id int primary key,name varchar(255)")
self.db_util.execute("TRUNCATE for_unit_test")
self.db_util.execute("INSERT INTO for_unit_test VALUES(1,"天天"),(2，“地地)")
#数据库已准备好，准备测试
result=self.db_util.query("SELECT * FROM for_unit_test ORDER BY id")#接收
expected=((1,"天天"),(2，“地地))#写入预期值
self.asserEqual(ecpected,result)
#测试完后清理表
self.db_util.execute("DROP TABLE for_unit_test")
self.db_util.close_conn()
def test_execute_without_autocommit(self):
#先设置autocommit为true，函数行为和前面execute行为一致
self.db_util.conn.autocommit(True)
#与前面测试重复开始
self.db_util.select_db("test")#先写死
self.db_util.execute("drop table for_unit-test2")#代码优化，防止已存在此表，但字段不一致
self.db_util.check_table_exists_and_create(
"test",
"for_unit_test2",
"id int primary key,name varchar(255)")
self.db_util.execute("TRUNCATE for_unit_test2)
self.db_util.execute("INSERT INTO for_unit_test2 VALUES(1,"天天")")
#数据已准备好，准备测试
result=self.db_util.query("SELECT * FROM for_unit_test2 ORDER BY id")#接收
expected=((1,"天天"))#写入预期值
self.asserEqual(ecpected,result)
#测试完后没清理表
self.db_util.close_conn()#必须关掉，重新创建新的连接，防止缓存，否则后面测试能查询到。
#与前面测试重复结束
#专为插入数据建立连接，设置autocommit为False
new_util=MySQLUtil()
new_util.select_db("test") new_util.conn.autocommit(False) new_util.execute_without_autocommit("INSERT INTO for_unit_test2 VALUES(2,"地地")")
new_util.close.conn() #专为查询数据建立连接
new_util2=MySLQUtil()
new_util2.select_db("test")
new_util
result=new_util2.query("SELECT * FROM for_unit_test2 ORDER BY id)")
expected=((1,"天天"),)#猜刚才的非autocommit并没有写入
self.assertEqual(expected,result)
#清理单元测试残留
new_util2.execute("DROP TABLE for_unit_test2")
new_util2.close.conn()
20230821，0217-0220（59min），3.5h
六、项目开发-对比工具方法
1、读取处理过的文件
数据库认知：元数据库、数据源库、目的地库三个数据库指代的是三台不同的电脑服务器，在每个服务器下可以创建MySQL数据库
对比思路：从文件夹中读取待处理文件VS从MySQL中可以读取到已经处理过的文件。
代码思路（切换数据库；查询是否存在工作表，不存在则创建；查询工作表中的文件，将元组形式转化为列表形式）
在util下的mysql_util.py中添加以下代码（与MySQLUtil不是一个类）
def get processed_files(
#配置信息已在之前导入
#from config import project_config as pc
db_util,#实例化MSQL对象
db_name=pc.metadata_db_name,#默认固定值，设置为metadata
table_name=pc.metadata_file_monitor_table_name,#默认固定
create_cols=pc.metadata_file_mointor_table_create_cols#默认固定
):
db_util.select_db(db_name)
db_util.check_table_exists_and_create(db_name,table_name,create_cols)
result=db_util.query(f"SELECT file_name from {table_name}")#每行取一个元素
logger.debug(result)
processed_files=[]
for r in result:
processed_files.append(r[0])#拿到文件名
logger.debug(processed_files)
return processed_files
在main里
mysql_util=MySQLUtil()#默认连接元数据库
get_processed_files(mysql_util)#这里只用传入util，其他参数默认
#运行后可在metadata中看到相关表格，演示时手动添加数据，其中时间戳自动默认
#常见的约束：主键约束（非空、唯一）
#判断日志输出等级
#debug(不重要，仅仅为演示，打印),info(开发时使用，按执行过程，输出每一步执行情况),warning(如做了一个强制类型转型，虽然不匹配但没报错),error(发生异常)，fatal(异常无法处理)
#产品开发有不同阶段，开发（level级别低，方便调试）-发布（level设置高，万一出现异常可以调试）。
#配置文件,在config包下的project_config中，元数据库相关的配置
metadata_db_name="metadata"
metadata_file_monitor_table_name="file_monitor"
metadata_file_mointor_table_create_cols="""#三个双引号一般用作引入多行字符串，与""、''类似，前者换行时不会自动出现\
id INT PRIMARY KEY AUTO_INCREMENT,
file_name VARCHAR(255) UNIQUE NOT NULL COMMENT "被处理的文件名称"
process_lines INT COMMENT"本文件中有多少数据被处理"
process_times TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT "处理时间"
"""
20230822，0303-0306(48min)，2h
2、创建对应代码单元测试
在test包下的test_mysql_util中
from util.mysqlutil import MySQLUtil.get_processed_files
from config import project_config as pc
def test_get_processed_files(self):
self.db_util.select_db("test")#根据报错提示添加，此句与下句搭配
self.db_util.execute("drop table if exists "test_file_monitor"")
self.db_util.check_table_exists_and_create(
"test",
"test_file_monitor",
pc.metadata_file_monitor_table_create_cols
)
self.db_util.execute("""
INSERT INTO test_file_monitor VALUES("e:/data.log",1024)""")
result=get_processed_files(self.db_util,"test","test_file_monitor")
self.assertEqual(["e:/data.log"].result)
self.db_util.execute("drop table if exists 'test_file_monitor'")
self.db_util.close_conn()
3、创建对比工具
在util包下的file_util中
def get_new_by_compare_lists(a_list,b_list): new_list=[] for item in b_list: if item not in a_list: new_list.append(item) return new_list #实现方式2：两个集合相减，先将列表转集合，结果是在第一个集合中且不在第二个集合中的元素 def get_new_by_compare_list1(a_list,b_list): a_set=set(a_list) b_set=set(b_list) result=list(b_set-a_set)#最后得转列表.有个隐患，集合转为列表后，顺序不一定与对比的列表同 result.sort()#直接在原表上排序，返回值为空，不能直接return return result
4、创建对比工具单元测试
在test包下的file_util中
def test_new_by_comparelists(self): a_list= ['e:/a.txt', 'e:/b.txt'] b_list=['e:/a.txt', 'e:/b.txt', 'e:/c.txt', 'e:/d.txt'] result1=get_new_by_compare_lists(a_list,b_list) self.assertEqual(['e:/c.txt', 'e:/d.txt']，result1) result2=get_new_by_compare_lists(a_list,b_list) self.assertEqual(['e:/c.txt', 'e:/d.txt']，result2)
七、项目开发-业务主逻辑
思路：已知全部源文件名，和已处理并存入元数据库的名字，找未处理的文件名
1.业主主逻辑代码实现
在项目根目录下创建json_service.py文件，具体思路：
-
1）读取json目录下文件，生成一个列表b，
-
2）读取元数据库file_monitor中已经处理过的文件，生成一个列表a，
-
3）将两个列表相减，得到需要处理的
"""处理json数据的主要逻辑代码"""
from util.file_util import get_dir_files_list,get_new_by_compare_lists
from util.mysql_util import MySQLUtil.get_processed_files
from util.logging_util import init_logger
from config import project_config as pc
if _name_=="_main_":#有没有都可
logger=init_logger()
logger.info("读取JSON数据处理，程序开始执行了......")
#1.通过file_util中的函数获取json目录中的文件
files=get_dir_files_list(pc.json_data_root_path)
logger.info(f"判断json的文件夹，发现有如下文件“{files}")
#2读取元数据库中file_monitor中已经处理过的文件，生成一个列表
meta_util=MySQLUtil()
processed_files=get_processed_files(meta_util)
logger.info(f"查询MySQL，找到有如下文件已经被处理过了：{processed_files}")
#3 这两个列表相减，得到需要的文件
need_to_process_files=get_new_by_compare_lists(processed_files,files)
logger.info(f"经过对比mysql元数据库，找出如下文件供我们处理：{need_to_process_files}")
#在config包下的project_config中配置json json_data_root_path="D:/2.project/data/pyetl-data-logs/json/"#注意斜线,路径中的“\t要写成r"\t"或“\\t”。改成自己的路径。路径结尾一定加/，否则会出现拼接错误
project-etl代码梳理
20230823，0307-0311(76min)，3h
项目需求1：JSON数据处理II
目的：处理JSON文件内部信息
八、项目开发-扩展知识点
1.用模型记录数据
处理json数据，希望存储到csv mysql中。处理时需要先把json数据加载到计算机内存，需要有一个容器承载这些数据，再把这些数据写入到csv mysql
实现方式有两种：
-
python字典存储（虽然简单快捷但处理数据量有限）。
-
使用模型（类、class）存储（效率高，结构清晰）。
在learning包下建立test_model.py
class Person:
def _init_(self,name,age,address,sep=","):
self.name=name
self.age=age
self.address=address
self.sep=sep
def to_csv(self):
return f"{self.name}{self.sep}{self.age}{self.sep}{self.address}"
def generate_insert_sql(self):
return f"INSERT INTO person VALUES('{self.name}',{self.age},'{self.address}')"#加''因为是字符串
print(p1, p1.name, p1.age, p1.address)
print(p2, p2.name, p2.age, p2.address)
print(p1.to_csv())
print(p1.generate_insert_sql())
"""自己测试输出的插入语句可行性(在数据库中测试)
CREATE TABLE person(
name VARCHAR(20),
age INT,
address VARCHAR(100)
)
INSERT INTO person VALUES("张三",20."深圳")
"""
2.时间戳的概念（timestamp)
用一个数字来记录时间，这个数字是从UTC时间：1970-01-01 00:00:00开始，到某个时刻过了多少秒（毫秒）。
python里处理时间戳的库：time模块，（还有datatime、pandas等了解即可）。
time.localtime
在learning包下的test-time.py中
import time
print(time.strftime("%Y-%m-%d% H:%M:%S"))
print(time.strftime("%Y-%m-%d% H:%M:%S"),time.localtime(time.time()))#与上句结果一样，多了个转换
print(time.time())#获取秒级时间戳，10位
print(time.time()*1000)#获取毫级时间戳，13位
print(time.localtime(time.time()))#打印详细时间信息，还包括季度、一年中第几天等
3.Python中时间戳转换
在util包下的time_util.py中，把输入ts按制定格式转成字符串
#和时间戳相关代码
import time
def ts10_to_date_str(ts,format="%Y-%m-%d %H:%M:%S"):
#输入10位时间戳
#文件名中不能有
#time.strftime两种用法
#直接获取当前的时间戳转成的字符串，time.strftime("%Y-%m-%d %H:%M:%S")
#获取指定时间戳转成的字符串time.strftime("%Y-%m-%d %H:%M:%S"，time.localtime(ts))
return time.strftime(format,time.localtime(ts))
def ts13_to_data_str(ts,format="%Y-%m-%d %H:%M:%S"):
#输入13位时间戳
#return time.strftime(format,int(ts/1000))
return ts10_to_date_str(int(ts/1000),format=format)
创建对应代码单元测试
在test包下的test_time_util中
from unittest import TestCase#?
from util.time_util import ts10_to_date_str,ts12_to_date_str
class TimeUtilTest(TestCase):
def setUp(self)->None:
pass
def test_ts10_to_date_str(self):
#1692847583
#2023-08-24 11:26:23
ts=1692847583
result=ts10_to_date_str(ts)
self.assertEqual("2023-08-24 11:26:23",result)
def test_ts13_to_date_str(self):
#1692847583000
#2023-08-24 11:26:23
ts=1692847583000
result=ts13_to_date_str(ts)
self.assertEqual("2023-08-24 11:26:23",result)
20230824，0312-0314(55min)，2h（至少）
九、项目开发-模型设计
订单模型构造思路
1）一条订单数据示例，针对JSON数据我们设计2个模型存储数据：订单模型（OrdersModel）和产品模型（OrdersDetailModel）。
2）最终写入数据库时，需创建两张表：
订单表可以作为主键的： "orderID": "154245064025224198149"
订单详情作为主键的：联合主键，用订单表的orderID和barcode一起作为主键
{
"discountRate": 1,
"storeShopNo": "None",
"dayOrderSeq": 10,
"storeDistrict": "芙蓉区",
"isSigned": 0,
"storeProvince": "湖南省",
"origin": 0,
"storeGPSLongitude": "112.97851",
"discount": 0,
"storeID": 2419,
"productCount": 7,
"operatorName": "OperatorName",
"operator": "NameStr",
"storeStatus": "open",
"storeOwnUserTel": 12345678910,
"payType": "cash",
"discountType": 2,
"storeName": "肖之艳南食店",
"storeOwnUserName": "OwnUserNameStr",
"dateTS": 1542450646000,
"smallChange": 0,
"storeGPSName": "None",
"erase": 0,
"product": [{
"count": 1,
"name": "伊利安慕希黄桃燕麦味酸奶200g",
"unitID": 2,
"barcode": "6907992513560",
"pricePer": 7,
"retailPrice": 7,
"tradePrice": 0,
"categoryID": 11
}, {
"count": 1,
"name": "京牛香素食烤鸭90g",
"unitID": 7,
"barcode": "6935814600112",
"pricePer": 2.5,
"retailPrice": 2.5,
"tradePrice": 0,
"categoryID": 10
}, {
"count": 1,
"name": "QQ糖双色可乐味70g",
"unitID": 0,
"barcode": "6920952712254",
"pricePer": 3.5,
"retailPrice": 3.5,
"tradePrice": 0,
"categoryID": 1
}, {
"count": 1,
"name": "奥利奥夹心原味高原装116g",
"unitID": 0,
"barcode": "6901668005717",
"pricePer": 6,
"retailPrice": 6,
"tradePrice": 0,
"categoryID": 1
}, {
"count": 1,
"name": "阿胶枣",
"unitID": 4,
"barcode": "6959435300091",
"pricePer": 4,
"retailPrice": 4,
"tradePrice": 0,
"categoryID": 1
}, {
"count": 1,
"name": "润口香甜王米风味香肠",
"unitID": 0,
"barcode": "6902890237754",
"pricePer": 7,
"retailPrice": 7,
"tradePrice": 0,
"categoryID": 1
}, {
"count": 1,
"name": "好丽友派巧克力味6枚204g",
"unitID": 3,
"barcode": "6920907800944",
"pricePer": 9,
"retailPrice": 9,
"tradePrice": 0,
"categoryID": 10
}],
"storeGPSAddress": "None",
"orderID": "154245064025224198149",
"moneyBeforeWholeDiscount": 39,
"storeCategory": "normal",
"receivable": 39,
"faceID": "",
"storeOwnUserId": 2364,
"paymentChannel": 0,
"paymentScenarios": "OTHER",
"storeAddress": "StoreAddress",
"totalNoDiscount": 39,
"payedTotal": 39,
"storeGPSLatitude": "28.187935",
"storeCreateDateTS": 1542358458000,
"storeCity": "长沙市",
"memberID": "0"
}
1.新增MySQL表的相关配置
在在project_config.py中
#在目的地数据库的相关配置 target_host=metadata_host target_user=metadata_user target_password=metadata_ target_port=metadata_port target_db_name="retail" # JSON数据采集后，写入MySQL，存储订单相关的表，表名是： target_orders_table_name = "orders" # orders表的建表语句的列信息 target_orders_table_create_cols = \ f"order_id VARCHAR(255) PRIMARY KEY, " \ f"store_id INT COMMENT '店铺ID', " \ f"store_name VARCHAR(30) COMMENT '店铺名称', " \ f"store_status VARCHAR(10) COMMENT '店铺状态(open,close)', " \ f"store_own_user_id INT COMMENT '店主id', " \ f"store_own_user_name VARCHAR(50) COMMENT '店主名称', " \ f"store_own_user_tel VARCHAR(15) COMMENT '店主手机号', " \ f"store_category VARCHAR(10) COMMENT '店铺类型(normal,test)', " \ f"store_address VARCHAR(255) COMMENT '店铺地址', " \ f"store_shop_no VARCHAR(255) COMMENT '店铺第三方支付id号', " \ f"store_province VARCHAR(10) COMMENT '店铺所在省', " \ f"store_city VARCHAR(10) COMMENT '店铺所在市', " \ f"store_district VARCHAR(10) COMMENT '店铺所在行政区', " \ f"store_gps_name VARCHAR(255) COMMENT '店铺gps名称', " \ f"store_gps_address VARCHAR(255) COMMENT '店铺gps地址', " \ f"store_gps_longitude VARCHAR(255) COMMENT '店铺gps经度', " \ f"store_gps_latitude VARCHAR(255) COMMENT '店铺gps纬度', " \ f"is_signed TINYINT COMMENT '是否第三方支付签约(0,1)', " \ f"operator VARCHAR(10) COMMENT '操作员', " \ f"operator_name VARCHAR(50) COMMENT '操作员名称', " \ f"face_id VARCHAR(255) COMMENT '顾客面部识别ID', " \ f"member_id VARCHAR(255) COMMENT '顾客会员ID', " \ f"store_create_date_ts TIMESTAMP COMMENT '店铺创建时间', " \ f"origin VARCHAR(255) COMMENT '原始信息(无用)', " \ f"day_order_seq INT COMMENT '本订单是当日第几单', " \ f"discount_rate DECIMAL(10, 5) COMMENT '折扣率', " \ f"discount_type TINYINT COMMENT '折扣类型', " \ f"discount DECIMAL(10, 5) COMMENT '折扣金额', " \ f"money_before_whole_discount DECIMAL(10, 5) COMMENT '折扣前总金额', " \ f"receivable DECIMAL(10, 5) COMMENT '应收金额', " \ f"erase DECIMAL(10, 5) COMMENT '抹零金额', " \ f"small_change DECIMAL(10, 5) COMMENT '找零金额', " \ f"total_no_discount DECIMAL(10, 5) COMMENT '总价格(无折扣)', " \ f"pay_total DECIMAL(10, 5) COMMENT '付款金额', " \ f"pay_type VARCHAR(10) COMMENT '付款类型', " \ f"payment_channel TINYINT COMMENT '付款通道', " \ f"payment_scenarios VARCHAR(15) COMMENT '付款描述(无用)', " \ f"product_count INT COMMENT '本单卖出多少商品', " \ f"date_ts TIMESTAMP COMMENT '订单时间', " \ f"INDEX (receivable), INDEX (date_ts)" # JSON数据采集后，写入MySQL，存储订单详情（带商品信息的）相关的表，表名是： target_orders_detail_table_name = "orders_detail" # orders_detail表的建表语句的列信息 target_orders_detail_table_create_cols = \ f"order_id VARCHAR(255) COMMENT '订单ID', " \ f"barcode VARCHAR(255) COMMENT '商品条码', " \ f"name VARCHAR(255) COMMENT '商品名称', " \ f"count INT COMMENT '本单此商品卖出数量', " \ f"price_per DECIMAL(10, 5) COMMENT '实际售卖单价', " \ f"retail_price DECIMAL(10, 5) COMMENT '零售建议价', " \ f"trade_price DECIMAL(10, 5) COMMENT '贸易价格(进货价)', " \ f"category_id INT COMMENT '商品类别ID', " \ f"unit_id INT COMMENT '商品单位ID(包、袋、箱、等)', " \ f"PRIMARY KEY (order_id, barcode)"
2.OrdersModel
2.1构建模型
用json库加载字符串演示（在learning包中的test_model.py中）。最繁琐的是输入字段，易出错。里面不含product的model。
import json
data="""
{
"orderID":"1233123123123312",#注意别少个逗号
"storeProvince": "湖南省",
"payType": "wechat",
"storeName": "湖南平价特产总汇",
"dateTS": 1542458768000,
"product": [
{
"count": 3,
"name": "伊利优酸乳草莓味250ml",
"unitID": 3,
"barcode": "6907992100012",
"pricePer": 2.5,
"retailPrice": 2.5,
"tradePrice": 1.8,
"categoryID": 11
},
{
"count": 3,
"name": "巧克力面包",
"unitID": 4,
"barcode": "6971518660038",
"pricePer": 3,
"retailPrice": 3,
"tradePrice": 1.5,
"categoryID": 1
}
]
}
"""
print(type(data))#字符串
print(type(json.loads(data)))#字典,json字符串时Python字典的静态表示方式
json_dict=json.loads(data)#字符串转字典的方法
print(json_dict["orderID"])
print(json_dict["product"])
部分输出结果
<class 'str'>
<class 'dict'>
1233123123123312
[{'count': 3, 'name': '伊利优酸乳草莓味250ml', ···}, {'count': 3, 'name': '巧克力面包', ···}]
2.2构建订单类
在model包下建立retail_orders_model.py
class OrderModel: def _init_(self,data:str):#data:str意思是设定输入格式，是建议类型，不是强制。python是动态类型的语言，输入int可能也不会报错 data=json.loads(data) self.discount_rate = data['discountRate'] # 折扣率 self.store_shop_no = data['storeShopNo'] # 店铺店号（无用列） self.day_order_seq = data['dayOrderSeq'] # 本单为当日第几单 self.store_district = data['storeDistrict'] # 店铺所在行政区 self.is_signed = data['isSigned'] # 是否签约店铺（签约第三方支付体系） self.store_province = data['storeProvince'] # 店铺所在省份 self.origin = data['origin'] # 原始信息（无用） self.store_gps_longitude = data['storeGPSLongitude'] # 店铺GPS经度 self.discount = data['discount'] # 折扣金额 self.store_id = data['storeID'] # 店铺ID self.product_count = data['productCount'] # 本单售卖商品数量 self.operator_name = data['operatorName'] # 操作员姓名 self.operator = data['operator'] # 操作员ID self.store_status = data['storeStatus'] # 店铺状态 self.store_own_user_tel = data['storeOwnUserTel'] # 店铺店主电话 self.pay_type = data['payType'] # 支付类型 self.discount_type = data['discountType'] # 折扣类型 self.store_name = data['storeName'] # 店铺名称 self.store_own_user_name = data['storeOwnUserName'] # 店铺店主名称 self.date_ts = data['dateTS'] # 订单时间 self.small_change = data['smallChange'] # 找零金额 self.store_gps_name = data['storeGPSName'] # 店铺GPS名称 self.erase = data['erase'] # 是否抹零 self.store_gps_address = data['storeGPSAddress'] # 店铺GPS地址 self.order_id = data['orderID'] # 订单ID self.money_before_whole_discount = data['moneyBeforeWholeDiscount'] # 折扣前金额 self.store_category = data['storeCategory'] # 店铺类别 self.receivable = data['receivable'] # 应收金额 self.face_id = data['faceID'] # 面部识别ID self.store_own_user_id = data['storeOwnUserId'] # 店铺店主ID self.payment_channel = data['paymentChannel'] # 付款通道 self.payment_scenarios = data['paymentScenarios'] # 付款情况（无用） self.store_address = data['storeAddress'] # 店铺地址 self.total_no_discount = data['totalNoDiscount'] # 整体价格（无折扣） self.payed_total = data['payedTotal'] # 已付款金额 self.store_gps_latitude = data['storeGPSLatitude'] # 店铺GPS纬度 self.store_create_date_ts = data['storeCreateDateTS'] # 店铺创建时间 self.store_city = data['storeCity'] # 店铺所在城市 self.member_id = data['memberID'] # 会员ID
2.3添加to_csv功能：
在model包下添加retail_orders_model.py
from json
from util import time_util
def to_csv(self,sep=","):
self.check_and_transform_area()#将省市区无意义内容转化为未知。此句不需在generate_insert_sql中调用，因为转换函数改的是类的成员变量，属性已经修改，后续先执行to_csv后执行generate——insert_sql
csv_line = \#换行符，不会别识别
f"{self.order_id}{sep}" \
f"{self.store_id}{sep}" \
f"{self.store_name}{sep}" \
f"{self.store_status}{sep}" \
f"{self.store_own_user_id}{sep}" \
f"{self.store_own_user_name}{sep}" \
f"{self.store_own_user_tel}{sep}" \
f"{self.store_category}{sep}" \
f"{self.store_address}{sep}" \
f"{self.store_shop_no}{sep}" \
f"{self.store_province}{sep}" \
f"{self.store_city}{sep}" \
f"{self.store_district}{sep}" \
f"{self.store_gps_name}{sep}" \
f"{self.store_gps_address}{sep}" \
f"{self.store_gps_longitude}{sep}" \
f"{self.store_gps_latitude}{sep}" \
f"{self.is_signed}{sep}" \
f"{self.operator}{sep}" \
f"{self.operator_name}{sep}" \
f"{self.face_id}{sep}" \
f"{self.member_id}{sep}" \
f"{time_util.ts13_to_date_str(self.store_create_date_ts)}{sep}" \
f"{self.origin}{sep}" \
f"{self.day_order_seq}{sep}" \
f"{self.discount_rate}{sep}" \
f"{self.discount_type}{sep}" \
f"{self.discount}{sep}" \
f"{self.money_before_whole_discount}{sep}" \
f"{self.receivable}{sep}" \
f"{self.erase}{sep}" \
f"{self.small_change}{sep}" \
f"{self.total_no_discount}{sep}" \
f"{self.payed_total}{sep}" \
f"{self.pay_type}{sep}" \
f"{self.payment_channel}{sep}" \
f"{self.payment_scenarios}{sep}" \
f"{self.product_count}{sep}" \
f"{time_util.ts13_to_date_str(self.date_ts)}"
return csv_line
if __name__ == '__main__': #自测
data="""
{"discountRate": 1, "storeShopNo": "None", "dayOrderSeq": 10, "storeDistrict": "芙蓉区", "isSigned": 0, "storeProvince": "湖南省", "origin": 0, "storeGPSLongitude": "112.97851", "discount": 0, "storeID": 2419, "productCount": 7, "operatorName": "OperatorName", "operator": "NameStr", "storeStatus": "open", "storeOwnUserTel": 12345678910, "payType": "cash", "discountType": 2, "storeName": "肖之艳南食店", "storeOwnUserName": "OwnUserNameStr", "dateTS": 1542450646000, "smallChange": 0, "storeGPSName": "None", "erase": 0, "product": [{"count": 1, "name": "伊利安慕希黄桃燕麦味酸奶200g", "unitID": 2, "barcode": "6907992513560", "pricePer": 7, "retailPrice": 7, "tradePrice": 0, "categoryID": 11}, {"count": 1, "name": "京牛香素食烤鸭90g", "unitID": 7, "barcode": "6935814600112", "pricePer": 2.5, "retailPrice": 2.5, "tradePrice": 0, "categoryID": 10}, {"count": 1, "name": "QQ糖双色可乐味70g", "unitID": 0, "barcode": "6920952712254", "pricePer": 3.5, "retailPrice": 3.5, "tradePrice": 0, "categoryID": 1}, {"count": 1, "name": "奥利奥夹心原味高原装116g", "unitID": 0, "barcode": "6901668005717", "pricePer": 6, "retailPrice": 6, "tradePrice": 0, "categoryID": 1}, {"count": 1, "name": "阿胶枣", "unitID": 4, "barcode": "6959435300091", "pricePer": 4, "retailPrice": 4, "tradePrice": 0, "categoryID": 1}, {"count": 1, "name": "润口香甜王米风味香肠", "unitID": 0, "barcode": "6902890237754", "pricePer": 7, "retailPrice": 7, "tradePrice": 0, "categoryID": 1}, {"count": 1, "name": "好丽友派巧克力味6枚204g", "unitID": 3, "barcode": "6920907800944", "pricePer": 9, "retailPrice": 9, "tradePrice": 0, "categoryID": 10}], "storeGPSAddress": "None", "orderID": "154245064025224198149", "moneyBeforeWholeDiscount": 39, "storeCategory": "normal", "receivable": 39, "faceID": "", "storeOwnUserId": 2364, "paymentChannel": 0, "paymentScenarios": "OTHER", "storeAddress": "StoreAddress", "totalNoDiscount": 39, "payedTotal": 39, "storeGPSLatitude": "28.187935", "storeCreateDateTS": 1542358458000, "storeCity": "长沙市", "memberID": "0"}
"""
o=OrderModel(data=data)
print(o.to_csv())
20230825，0315-0316(46min)，1.5h
2.4添加转换无意义字符串功能
存在问题：原始数据中很多空字段需判断：
检查字符串是否是空内容的方法
检查字符串是否是空内容，是则返回“NULL"，否则非空返回字符串的方法。
#在类里定义函数，至少有一个参数self。
在util包下创建str_util.py
"""字符串处理相关的代码都在这个文件中""" def check_null(data):#如果data有意义，返回False，无意义返回True。 #无意义的定义：空串""、“none"、"null"、"undefined" if not data: return True #代码能跑到此，说明不是空串 data=data.lower() if data=="none" or data="null" or data="undefined":#根据实际情况判断 return True return False
创建单元测试：在test包下的test_str_util.py中
from unittest import TestCase from util.str_util import check_null class StrUtilTest(TestCase):#? def setUp(self)->None:#相当于init（用pycharm框架时，先调用setUp），但不是（当仅仅将其当成类，使用函数里面对象时，对象是不存在的，需要先手动调用这个函数） """self.a=1#做测试用 pass sut=StrUtilTest() sut.setUp()#加上此句，后面才能打印出a。 print(sut.a)""" pass def test_check_null(self): s=None result=check_null(s) self.assertTrue(result) def test_check_null(self): s="None" result=check_null(s) self.assertTrue(result) def test_check_null(self): s="NONE" result=check_null(s) self.assertTrue(result) def test_check_null(self): s="null" result=check_null(s) self.assertTrue(result) def test_check_null(self): s="" result=check_null(s) self.assertTrue(result) def test_check_null(self): s="undefined" result=check_null(s) self.assertTrue(result) def test_check_null(self): s="有意义的字符串" result=check_null(s) self.assertFales(result)
添加转换无意义字符串功能
在retail_order_model.py文件中，
#转换无意义的省市区 def check_and_transform_area(self): if str_util.check_null(self.store_province): self.store_province="未知省份" if str_util.check_null(self.store_city): self.store_province="未知城市" if str_util.check_null(self.store_district): self.store_province="未知县区"
2.5添加写入MySQL功能
在model包下的retail_orders_model.py中，
from util import str_util
def generate_insert_sql(self):
"""
将模型转化为一条insert sql语句
return 字符串
"""
#ignore意思是如果有此数据，可忽略，不报错
sql = f"INSERT IGNORE INTO {pc.target_orders_table_name}(" \
f"order_id,store_id,store_name,store_status,store_own_user_id," \
f"store_own_user_name,store_own_user_tel,store_category," \
f"store_address,store_shop_no,store_province,store_city," \
f"store_district,store_gps_name,store_gps_address," \
f"store_gps_longitude,store_gps_latitude,is_signed," \
f"operator,operator_name,face_id,member_id,store_create_date_ts," \
f"origin,day_order_seq,discount_rate,discount_type,discount," \
f"money_before_whole_discount,receivable,erase,small_change," \
f"total_no_discount,pay_total,pay_type,payment_channel," \
f"payment_scenarios,product_count,date_ts" \
f") VALUES(" \
f"'{self.order_id}', " \#加上‘’因为是int类型
f"{self.store_id}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_name)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_status)}, " \
f"{self.store_own_user_id}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_own_user_name)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_own_user_tel)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_category)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_address)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_shop_no)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_province)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_city)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_district)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_gps_name)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_gps_address)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_gps_longitude)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.store_gps_latitude)}, " \
f"{self.is_signed}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.operator)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.operator_name)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.face_id)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.member_id)}, " \
f"'{time_util.ts13_to_date_str(self.store_create_date_ts)}', " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.origin)}, " \
f"{self.day_order_seq}, " \
f"{self.discount_rate}, " \
f"{self.discount_type}, " \
f"{self.discount}, " \
f"{self.money_before_whole_discount}, " \
f"{self.receivable}, " \
f"{self.erase}, " \
f"{self.small_change}, " \
f"{self.total_no_discount}, " \
f"{self.payed_total}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.pay_type)}, " \
f"{self.payment_channel}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.payment_scenarios)}, " \
f"{self.product_count}, " \
f"'{time_util.ts13_to_date_str(self.date_ts)}')"
return sql
解析insert ignore，作用：防止重复插入同一数据时报错
USE metadata;
INSERT INTO file_monitor(file_name,process_lines)VALUES("x09",20);
INSERT IGNORE INTO file_monitor(file_name,process_lines)VALUES("x09",20);
#第一条改为x08，正确运行（说明已经入库），再改为x08报错，而第二条重复插入不报错
在数据库查看某个表的详细列的方式
use 数据库名字
show CREATE TABLE 某个表
#插入数据库的语句中值部分，如果是string类型，加' '
2.6添加返回SQL中NULL功能
在util包下的str_util.py中
def check_str_null_and_transform_to_sql_null(data):
"""判断传入的字符串是否是无意义的，如果是返回MySQL的NULL
否则返回格式化的字符串
"""
if check_null(str(data)):#字符串化
return "NULL"
else:# 内容有意义，返回'内容本身'
return f"'{data}'"
在test包下的test_str_util.py下测试
import util.str_util import check_null,check_str_null_and_transform_to_sql_null
def test_check_str_null_and_transform_to_sql_null(self):
s=""
result=check_str_null_and_transform_to_sql_null(s)
self.assertEqual("NULL",result)
s="None"
result=check_str_null_and_transform_to_sql_null(s)
self.assertEqual("NULL",result)
s="null"
result=check_str_null_and_transform_to_sql_null(s)
self.assertEqual("NULL",result)
s="undefined"
result=check_str_null_and_transform_to_sql_null(s)
self.assertEqual("NULL",result)
s="张三"
result=check_str_null_and_transform_to_sql_null(s)
self.assertEqual("'张三'",result)
2.7产生结果演示
（检查数据是否可以正常运行）
在model包下的retail_orders_model.py中
import config.project_config as pc
json_str='{}'
model=OrderModel(json_str)
print(model.to_csv())
print(model.genetate_insert_sql())
20230826，0402-0408(82min)，3.1h
3.OrdersDertailModel
创建数据表三范式：有主键，无依赖，？
3.1构建模型
订单详情模型，包含订单ID+商品信息
在retail_orders_model.py中
class OrderDetailModel:
def _init_(self,data:str):
data=json.loads(data)
self.order_id=data["orderID"]#为后续结合barcode做主键
self.products_detail=[]
order_detail_product=data["product"]
for p in order_detail_product:
self.products_detail.append(SingleProductSoldModel(self,order_id,product_detail_dict))
def generate_insert_sql(self):
#sql语句的前半部分
sql=f"INSERT IGNORE INTO {pc.target_orders_detail_table_name}("\
f"order_id,barcode,name,count,price_per,retail_price,trade_price,category_id,unit_id)VALUES"
#sql语句的后半部分
for model in self.products_detail:#机器怎么识别model是SingleProductSoldModel，通过for循环？
sql+="("
sql += f"'{model.order_id}', " \
f"{str_util.check_str_null_and_transform_to_sql_null(model.barcode)}, " \#判断非空
f"{str_util.check_str_null_and_transform_to_sql_null(model.name)}, " \
f"{model.count}, " \
f"{model.price_per}, " \
f"{model.retail_price}, " \
f"{model.trade_price}, " \
f"{model.category_id}, " \
f"{model.unit_id}"
sql+="), "#)后有个空格
sql=sql[:-2]#切去）和空格
def to_csv(self):
csv_line=""
for model in self.products_detail:
csv_line+=model.to_csv()
csv_line+="\n"
return csv_line
class SingleProductSoldModel:
def init_(self,order_id,product_detail_dict):#product_detail_dict是什么？怎么调用的？
self.order_id = order_id # 订单ID
self.name = product_detail_dict["name"] # 商品名称
self.count = product_detail_dict["count"] # 商品售卖数量
self.unit_id = product_detail_dict["unitID"] # 单位ID
self.barcode = product_detail_dict["barcode"] # 商品的条码
self.price_per = product_detail_dict["pricePer"] # 商品卖出的单价
self.retail_price = product_detail_dict["retailPrice"] # 商品建议零售价
self.trade_price = product_detail_dict["tradePrice"] # 商品建议成本价
self.category_id = product_detail_dict["categoryID"] # 商品类别ID
def to_csv(self,sep=","):
"""生成一条csv数据，分隔符默认逗号"""
csv_line = \
f"{self.order_id}{sep}" \
f"{self.barcode}{sep}" \
f"{self.name}{sep}" \
f"{self.count}{sep}" \
f"{self.price_per}{sep}" \
f"{self.retail_price}{sep}" \
f"{self.trade_price}{sep}" \
f"{self.category_id}{sep}" \
f"{self.unit_id}"
return csv_line
main
json_str='{}'#参照之前写入的
order_detal_model=OrderDetailModel(json_str)
print(order_detail_model.to_csv())
print(order_detail_model.generate_insert_sql())
十、项目开发-业务主逻辑
1.填充主逻辑代码，写出csv
两种读取文件方式区别演示
with open("D:/2.project/data/pyetl-data-logs/json/x01",encoding="UTF-8") as f:
lines=f.readlines()#全部读取，占较大内存
for line in lines[:2]:
print(line)
for line in open("D:/2.project/data/pyetl-data-logs/json/x01",encoding="UTF-8"):#迭代读取，不会一次性读完，节约内存
print(line)
break
主逻辑梳理
每次处理时：
会处理若干个json文件：x09、x10、x11
每个json文件有1024笔订单
每笔订单有若干个商品
20230827，0409-0412(37min)，2.6h
在独立的json_service.py中继续添加
from config import project_config as pc
from model.retail_orders_model import OrderModel,OrderDetailModel
#步骤二
#1.通过for循环取文件
for file in need_to_process_files:
logger.debug(f"当前处理{file}")
order_Model_list=[]
order_detail_model_list=[]
#2.获取文件中每行的订单信息，构造两个订单模型
for line in open(file,encoding="utf-8"):
#logger.debug(f"正在处理{file},内容{line}")
#替换掉行尾的换行符
line=line.replace("\n","")#具体替换哪里？不替换又有什么差别？
order_Model=OrderModel(line)#先导入包
order_Model_list.appened(order_Model)
order_detail_model=OrderDetailModel(line)
order_Model_detail_list.appened(order_detail_Model)
#增加数据转换功能，即对数据进行过滤
reserved_model=[]
for model in order_model_list:
if model.receivable<=10000:
reserved_model.appened(model)
else:
logger.debug(f"该订单异常：order_id:{model.order_id}{model.to_csv()}")
#3.创建两个CSV文件，准备写入
order_csv_write_f=open(
file=pc.retail_output_csv_root_path+pc.retail_orders_output_csv_file_name,mode="a",encoding="utf-8"
)#处理文件的handler。mode由原来的w改为a,因为前者会产生覆盖现象，即一个csv文件中最多1024行
order_detail_csv_write_f=open(
file=pc.retail_output_csv_root_path+pc.retail_orders_detail_output_csv_file_name,mode="a",encoding="utf-8"
)#file_name怎么自动替换
#4.写入CSV文件
#4.1写入订单表,从过滤后的结果中取
for model in reserved_model:
line=model.to_csv()
line+="\n"
order_csv_write_f.write(line)
order_csv_write_f.close()
#4.2写入订单详情表
for model in order_detail_model_list:
for single_product_model in model.products_detail:
line=sing_product_model.to_csv()
line+="\n"
order_detail_csv_write_f.write(line)
order_detail_csv_write_f.close()
logger.info(f"完成了CSV备份文件的写出，写出到了：{conf.retail_outut_csv_root_path}")
在project.config.py中，
csv相关的配置
# 写出CSV的根目录配置
retail_output_csv_root_path = "D:/2.project/data/pyetl-data-logs/output/csv/"
#TODO，考虑扩展代码，自动创建目录
# 写出订单模型，CSV的文件名字配置
retail_orders_output_csv_file_name = f'orders-{time.strftime("%Y%m%d-%H%M%S", time.localtime(time.time()))}.csv'
# 写出订单详情（带商品信息）模型，CSV的文件名字配置
retail_orders_detail_output_csv_file_name = f'orders-detail-{time.strftime("%Y%m%d-%H%M%S", time.localtime(time.time()))}.csv'
主逻辑代码重构
一般默认规则：主代码一屏20行，一行最多80字符
重构目的：在main中按照功能分成一个个小函数，能抓住主要逻辑，把握全局。
重构：得到待处理文件
在json_service.py中的步骤一
-
选中代码块，右键Refactor-Extract Method进行函数封装
-
def部分：get_process_file函数中有global全局变量（need_to_process_files），可以不定义全局变量，让与后续代码有关的变量（meta_util,need_to_process_files）以return返回
-
main部分：替换为meta_util,need_to_process_files=get_process_file()
#重构之后 def get_process_file(meta_util):#新增参数，受以下“写入MySQL”部分中重构影响 #logger=init_logger() #logger.info("读取JSON数据处理，程序开始执行了......") #1.通过file_util中的函数获取json目录中的文件 files=get_dir_files_list(pc.json_data_root_path) logger.info(f"判断json的文件夹，发现有如下文件“{files}") #2读取元数据库中file_monitor中已经处理过的文件，生成一个列表 #meta_util=MySQLUtil() processed_files=get_processed_files(meta_util) logger.info(f"查询MySQL，找到有如下文件已经被处理过了：{processed_files}") #3 这两个列表相减，得到需要的文件 need_to_process_files=get_new_by_compare_lists(processed_files,files) logger.info(f"经过对比mysql元数据库，找出如下文件供我们处理：{need_to_process_files}")
重构：建立订单和详情的模型
在json_service.py中的步骤二重构build_models(file)
#原文
order_Model_list=[]
order_detail_model_list=[]
#2.获取文件中每行的订单信息，构造两个订单模型
for line in open(file,encoding="utf-8"):
#logger.debug(f"正在处理{file},内容{line}")
#替换掉行尾的换行符
line=line.replace("\n","")#具体替换哪里？不替换又有什么差别？
order_Model=OrderModel(line)#先导入包
order_Model_list.appened(order_Model)
order_detail_model=OrderDetailModel(line)
order_Model_detail_list.appened(order_detail_Model)
#重构build_models():
#函数中
#参数：在def中build_models(file)#file为参数
#全局变量：删去der_model_list,order_detail_model_list,line
#返回值：order_model_list,order_detail_model_list
#代码中
#替代:order_model_list,order_detail_model_list=build_models(file)
#在for file in need_to_process_files: 循环前新加以下两个全局变量，因为若只在for循环里体现，后续引用时可能会显示无定义。用处来源：写入MySQL部分
order_model_list=[],
order_detail_model_list=[]
重构：订单异常数据处理
在json_service.py中的步骤二重构file_except_data(order_model_list)
#原文
#增加数据转换功能，即对数据进行过滤
reserved_model=[]
for model in order_model_list:
if model.receivable<=10000:
reserved_model.appened(model)
else:
logger.debug(f"该订单异常：order_id:{model.order_id}{model.to_csv()}")
#重构file_except_data()
#函数中
#参数：order_model_list
#全局变量：删
#返回值：reserved_model
#代码中
reserved_model=file_except_data(order_model_list)
重构：打开订单和订单详情文件
在json_service.py中的步骤二重构open_order_file()
#原文 #3.创建两个CSV文件，准备写入 order_csv_write_f=open( file=pc.retail_output_csv_root_path+pc.retail_orders_output_csv_file_name,mode="a",encoding="utf-8" )#处理文件的handler。mode由原来的w改为a,因为前者会产生覆盖现象，即一个csv文件中最多1024行 order_detail_csv_write_f=open( file=pc.retail_output_csv_root_path+pc.retail_orders_detail_output_csv_file_name,mode="a",encoding="utf-8" ) #重构open_order_file() #函数中 #参数： #全局变量：删 #返回值：order_csv_write_f，order_detail_csv_write_f #代码中 order_csv_write_f，order_detail_csv_write_f=open_order_file()
重构：文件写入csv
在json_service.py中的步骤二重构write_data_to_csv(reserved_model,order_detail_model_list)
#原文 #4.1写入订单表,从过滤后的结果中取 for model in reserved_model: line=model.to_csv() line+="\n" order_csv_write_f.write(line) order_csv_write_f.close() #4.2写入订单详情表 for model in order_detail_model_list: for single_product_model in model.products_detail: line=sing_product_model.to_csv() line+="\n" order_detail_csv_write_f.write(line) order_detail_csv_write_f.close() #重构write_data_to_csv() #函数中 #参数：reserved_model,order_detail_model_list #全局变量：删 #返回值： #代码中 write_data_to_csv(reserved_model,order_detail_model_list)
2.填充主逻辑代码，完全写成
2.1重构MySQLUtil
之前修改链接已完成，思路：将连接数据库的信息从类代码处移到config配置文件中，初始化时，传递给参数。
2.2写入MySQL
在json_service.py中，继续
#步骤三：把数据写入MySQL中，（目的地数据库） #1.建立MySQL连接 target_db_util=MySQLUtil( host=pc.target_host, port=pc.target_port, uesr=pc.target_user, password=pc.target_password ) #2.判断order表orderdetail表是否存在，不存在则创建 target_db_util.check_table_exists_and_create( db_name=pc.target_db_name, table_name=pc.target_orders_table_name, create_cols=pc.target_orders_table_create_cols ) target_db_util.check_table_exists_and_create( db_name=pc.target_db_name table_name=pc.target_orders_detail_table_name, create_cols=pc.target_orders_detail_table_create_cols ) #3.把数据写入表中 for model in reserved_model: insert_sql=model.generate_insert_sql() target_db_util.select_db(pc.target_db_name) #target_db_util.execute(insert_sql)执行速度比下行慢，因为每次执行插入语句都需要提交一次 target_db_util.execute_without_autocommit(insert_sql)#最后一起提交插入语句，效率高 for model in order_detail_model_list: insert_sql=model.generate_insert_sql() target_db_util.select_db(pc.target_db_name) #target_db_util.execute(insert_sql)速度比下一句慢 target_db_util.execute_without_autocommit(insert_sql) target_db_util.conn.commit() # #运行完成后，数据库中的两个表内容会补充 #运行后，数据库中的表只包含1024条数据，因为以上最后两段for循环对齐每个file的for循环内部
主逻辑代码重构
def build_db_util():
meta_util=MySQLUtil()
target_db_util=MySQLUtil(
host=pc.target_host,
port=pc.target_port,
uesr=pc.target_user,
password=pc.target_password
)
return meta_util, target_db_util
def check_target_table_and_create():
#2.判断order表orderdetail表是否存在，不存在则创建
target_db_util.check_table_exists_and_create(
db_name=pc.target_db_name,
table_name=pc.target_orders_table_name,
create_cols=pc.target_orders_table_create_cols
)
target_db_util.check_table_exists_and_create(
db_name=pc.target_db_name
table_name=pc.target_orders_detail_table_name,
create_cols=pc.target_orders_detail_table_create_cols
)
def write_data_to_mysql(target_db_util,reserved_model,order_detail_model_list)
for model in reserved_model:
insert_sql=model.generate_insert_sql()
target_db_util.select_db(pc.target_db_name)
#target_db_util.execute(insert_sql)执行速度比下行慢，因为每次执行插入语句都需要提交一次
target_db_util.execute_without_autocommit(insert_sql)#最后一起提交插入语句，效率高
for model in order_detail_model_list:
insert_sql=model.generate_insert_sql()
target_db_util.select_db(pc.target_db_name)
#target_db_util.execute(insert_sql)速度比下一句慢
target_db_util.execute_without_autocommit(insert_sql)
target_db_util.conn.commit()
#以上代码为重构部分
if __name__ == '__main__':
# 步骤一
logger = init_logger()
logger.info("读取JSON数据处理，程序开始执行了......")
meta_util, target_db_util = build_db_util()
need_to_process_files = get_process_file(meta_util)#改参数，同时改def
# 步骤二 写csv文件
# 1 通过for循环取出每个文件
order_model_list = []
order_detail_model_list = []
# 定义一个变量，统计所有文件的行数
global_count = 0
# 定义一个字典，存储处理的文件相关数据
processed_files_record_dict = {}
for file in need_to_process_files:
logger.debug(f'当前处理{file}')
order_model_list, order_detail_model_list, file_processed_lines_count = build_models(file)
# 这里时进行统计文件行数，作用为后续写入元数据库服务
processed_files_record_dict[file] = file_processed_lines_count
global_count += file_processed_lines_count
# 这里时进行数据过滤
reserved_model = filt_except_data(order_model_list)
# 4 写入到csv文件中
write_data_to_csv(reserved_model, order_detail_model_list)
logger.info(f"完成了CSV备份文件的写出，写出到了：{pc.retail_output_csv_root_path}")
#自以下重构代码
#步骤三：把数据写入MySQL中，（目的地数据库）
"""原代码转移到步骤一及其函数中
#1.建立MySQL连接#不必每个文件都连接一次
target_db_util=MySQLUtil(
host=pc.target_host,
port=pc.target_port,
uesr=pc.target_user,
password=pc.target_password
)
"""
check_target_table_and_create()#重构以下
"""
#2.判断order表orderdetail表是否存在，不存在则创建
target_db_util.check_table_exists_and_create(
db_name=pc.target_db_name,
table_name=pc.target_orders_table_name,
create_cols=pc.target_orders_table_create_cols
)
target_db_util.check_table_exists_and_create(
db_name=pc.target_db_name
table_name=pc.target_orders_detail_table_name,
create_cols=pc.target_orders_detail_table_create_cols
)
"""
write_data_to_mysql(target_db_util,reserved_model,order_detail_model_list)#重构以下
"""
#3.把数据写入表中
for model in reserved_model:
insert_sql=model.generate_insert_sql()
target_db_util.select_db(pc.target_db_name)
#target_db_util.execute(insert_sql)执行速度比下行慢，因为每次执行插入语句都需要提交一次
target_db_util.execute_without_autocommit(insert_sql)#最后一起提交插入语句，效率高
for model in order_detail_model_list:
insert_sql=model.generate_insert_sql()
target_db_util.select_db(pc.target_db_name)
#target_db_util.execute(insert_sql)速度比下一句慢
target_db_util.execute_without_autocommit(insert_sql)
target_db_util.conn.commit()
"""
20230828，0413-0417(78min)，4h
目的：将处理完的文件名写入元数据库
在json.service.py中继续
#步骤四
#将处理过的文件名写入到元数据库中
#需要file name和process_lines（在for lines 中每循环一次加一）
#修改步骤二封装的build_models
def build_models(file):
order_Model_list=[]
order_detail_model_list=[]
file_processed_lines_count=0#新加计算行数
#2.获取文件中每行的订单信息，构造两个订单模型
for line in open(file,encoding="utf-8"):
#logger.debug(f"正在处理{file},内容{line}")
file_processed_lines_count+=0#新加，统计当前文件行数
line=line.replace("\n","")
order_Model=OrderModel(line)
order_Model_list.appened(order_Model)
order_detail_model=OrderDetailModel(line)
order_Model_detail_list.appened(order_detail_Model)
return order_Model_list,order_Model_detail_list,file_processed_lines_count
#在main中，build_models返回值新增file_processed_lines_count
#在for file 循环前增加统计每个文件行数的字典变量，在循环下新加统计计数
main
global_count=0#定义一个变量，存储所有行数据
processd_files_record_dict={}#for file循环前定义一个字典，存储处理的文件相关数据
for file in need_to_process_files:
file_processed_lines_count=0
order_Model_list,order_Model_detail_list,file_processed_lines_count=build_models(file)
processed_files_record_dict[file]=file_processed_lines_count
global_count+=file_processed_lines_count
#继续之前的reserved_model等
#步骤四
#将处理过的文件名写入到元数据库中,在for file循环外
"""
打印字典数据
a={"key1":1,"key2":2}
for k in a.keys():
print(k)
for (key,value) in a.items():
print(key,value)
"""
for filename,lines in processed_files_record_dict.itmes():
insert_sql=f"INSERT INTO {pc.metadata_file_monitor_table_name} (file_name,processed_lines)"\
f"VALUES('{filename}',{lines})"#字符串加''
meta_util.execute(insert_sql)
meta_util_close.conn()
target_db_util_close_conn()
封装步骤四
#原代码
for filename,lines in processed_files_record_dict.itmes():
insert_sql=f"INSERT INTO {pc.metadata_file_monitor_table_name} (file_name,processed_lines)"\
f"VALUES('{filename}',{lines})"#字符串加''
meta_util.execute(insert_sql)
#封装后
#函数中
def write_metadata_to_metadata(meta_util,processed_files_record_dict):
for filename,lines in processed_files_record_dict.itmes():
insert_sql=f"INSERT INTO {pc.metadata_file_monitor_table_name} (file_name,processed_lines)"\
f"VALUES('{filename}',{lines})"
meta_util.execute(insert_sql)
#main中
write_metadata_to_metadata(meta_util,processed_files_record_dict)
json主逻辑梳理（概念代码块）
main
初始化logger
1.build_db_util创建两个数据库连接（元数据库和目的地数据库），返回两个util对象
2.get_processe_file接收元数据util，读取指定JSON目录，读取元数据库中的元数据表，进行对比，返回待处理文件名称
3.定义订单列表和订单详情列表，总订单数，已处理过的文件的行数（字典形式）。统计行数作用是为写入元数据库准备。
4.for file循序
5.build_models接受每个文件，
从给定文件名中读取JSON每行数据，
每行一笔订单，转成Order_model对象，
每个订单多个商品，把订单中product转成Order_detail_model对象
每个商品SingeProductSoldModel对象
把Json数据分别存储到三个模型对象中
返回订单列表、订单详情表和每个file的行数
6.统计每个file行数，及所有file行数
7.file_except_data进行数据过滤,
返回reserved_model
8.write_data_to_csv，接收reserved_model和order_detail_model_list,
分别把订单信息写入csv,
open_order_file，打开两个csv文件，等待写入
#以上为步骤二写csv文件
#步骤三，把数据写入mysql
9.check_target_table_and_create():检查待写入的数据表是否存在，如果不存在则创建，
两个表为目的数据库中存取订单和订单详情的表
10.write_data_to_mysql：
把两个模型的数据写入mysql.注意是选择每条信息提交一次，还是写完所有数据提交一次
#步骤四：写入元数据库
write_metadata_to_metadb，把处理过的文件名和行数写入数据库中
11.关闭两个数据库连接
项目需求2：MySQL数据处理
一、前期准备
导入简化版sql到datagrip中：被采集的数据为barcode，将被采集的数据导入到MySQL数据源库source_data中
数据运行：拖进datagrip，右键run，弹出Edit Configuration-选择target data source里的+，打开source_data-点击run，数据库source_data自动生成一个sys_barcode。‘
了解表结构，尤其其中的updata_at，记录更新的商品及时间戳
二、采集的要求
采集json数据特点：不重复采集，会记录采集过的文件名称
MySQL数据采集：新增内容，更新的内容，同时在目的地中进行更新。即增量更新。
三、实现步骤
1.实现思路
基于被采集的表的字段：updata_at
这个字段存储是数据源数据库中每次修改（更新）或者插入（新增）的数据的时间戳，每次采集时记录当前采集时这个批次中最大的时间戳。
实现增量更新采集：
-
下次采集时，SELECT SQL按照updata_at升序，
-
采集完成后，将当前批次最大的时间记录在MySQL的元数据库中，
-
下一次采集数据时，从元数据库的这个时间戳进行比较，只采集大于等这个时间戳的数据。
2.实现代码
2.1新增工具方法
目的：替换字符串中的特殊字符。因为这些字符串后续在构造sql句时使用，如果 ''、""存在可能会影响写的sql语句（不匹配）。导致代码执行时语法错误。
在util包下的str_util.py中
def clean_str(data:str):
#去除字符串中的特殊字符
if check_null(data):
return data#后续对无意义字符串处理
data=data.replace("'"."")
data=data.replace("""."")
data=data.replace("\\"."")
data=data.replace(";"."")
data=data.replace(","."")
data=data.replace("@"."")
return data
在util包下的str_util.py中，添加一个函数，
def check_number_null_and_transform_to_sql_null(data): """检查数据是否是空，空的则返回mysql中的NULL """ if data and not check_null(str(data)): return data else: return "NULL"#返回sql意义上的NULL
2.2新增模型
单独创建一个文件，因为这里是一个新的数据源（之前采集的是json），在这个文件中按照数据特点构造模型。后续采集日志数据时可以类比进行编码。
从datagrip执行，可以快速获得建表字段（前提是表本身存在数据库中）
#右键source_data数据库-New-Query Console use source_data; show CREATE TABLE sys_barcode; #直接复制相应字段的Create Table
在model包下新建barcode_model.py
#这个文件中存储的是从mysql中读取数据的模型
from util import str_util
from config import project_config as pc
class BarcodeModel:
def _init_(self, code=None, name=None, spec=None, trademark=None,
addr=None, units=None, factory_name=None, trade_price=None,
retail_price=None, update_at=None, wholeunit=None,
wholenum=None, img=None, src=None):
self.code = code
self.name = str_util.clean_str(name)
self.spec = str_util.clean_str(spec)
self.trademark = str_util.clean_str(trademark)
self.addr = str_util.clean_str(addr)
self.units = str_util.clean_str(units)
self.factory_name = str_util.clean_str(factory_name)
self.trade_price = trade_price
self.retail_price = retail_price
self.update_at = update_at
self.wholeunit = str_util.clean_str(wholeunit)
self.wholenum = wholenum
self.img = img
self.src = src
def to_csv():
csv_line = \
f"{self.code}{sep}" \
f"{self.name}{sep}" \
f"{self.spec}{sep}" \
f"{self.trademark}{sep}" \
f"{self.addr}{sep}" \
f"{self.units}{sep}" \
f"{self.factory_name}{sep}" \
f"{self.trade_price}{sep}" \
f"{self.retail_price}{sep}" \
f"{self.update_at}{sep}" \
f"{self.wholeunit}{sep}" \
f"{self.wholenum}{sep}" \
f"{self.img}{sep}" \
f"{self.src}"
return csv_line
def generate_insert_sql(self):
#replace与insert区别：如果数据存在，前者替换，后者报错
sql = f"REPLACE INTO {pc.target_barcode_table_name}(" \
f"code,name,spec,trademark,addr,units,factory_name,trade_price," \
f"retail_price,update_at,wholeunit,wholenum,img,src) VALUES(" \
f"'{self.code}', " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.name)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.spec)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.trademark)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.addr)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.units)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.factory_name)}, " \
f"{str_util.check_number_null_and_transform_to_sql_null(self.trade_price)}, " \
f"{str_util.check_number_null_and_transform_to_sql_null(self.retail_price)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.update_at)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.wholeunit)}, " \
f"{str_util.check_number_null_and_transform_to_sql_null(self.wholenum)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.img)}, " \
f"{str_util.check_str_null_and_transform_to_sql_null(self.src)}" \
f")"
return sql
2.3修改配置文件
在config包下的project_config中
# 采集barcode业务，写入MySQL的表名 target_barcode_table_name = "barcode" target_barcode_table_create_cols = """ `code` varchar(50) PRIMARY KEY COMMENT '商品条码', `name` varchar(200) DEFAULT '' COMMENT '商品名称', `spec` varchar(200) DEFAULT '' COMMENT '商品规格', `trademark` varchar(100) DEFAULT '' COMMENT '商品商标', `addr` varchar(200) DEFAULT '' COMMENT '商品产地', `units` varchar(50) DEFAULT '' COMMENT '商品单位(个、杯、箱、等)', `factory_name` varchar(200) DEFAULT '' COMMENT '生产厂家', `trade_price` DECIMAL(50, 5) DEFAULT 0.0 COMMENT '贸易价格(指导进价)', `retail_price` DECIMAL(50, 5) DEFAULT 0.0 COMMENT '零售价格(建议卖价)', `update_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `wholeunit` varchar(50) DEFAULT NULL COMMENT '大包装单位', `wholenum` int(11) DEFAULT NULL COMMENT '大包装内装数量', `img` varchar(500) DEFAULT NULL COMMENT '商品图片', `src` varchar(20) DEFAULT NULL COMMENT '源信息', INDEX (update_at) """ # 数据源数据库的配置 source_host = metadata_host source_user = metadata_user source_password = metadata_password source_port = metadata_port source_db_name = "source_data" source_barcode_data_table_name = "sys_barcode"
#演示exit
import sys#？
main:
print("Hello World")
sys.exit(1)#目的是显示到达此步时，不正常退出
#输出结果
"Hello World"
Process finished with exit code 1
#一般正常执行的exit是0，不正常是1
20230829，0418-0507(84min)，4.1h
2.4主业务逻辑代码
在project_config.py中，元数据库中
#barcode业务，updata_at字段的监控表的名称 metadata_barcode_table_name="barcode_monitor" #barcode业务，updata_at字段的监控表的建表语句的列信息 #time_record字段，存储的是采集的批次数据中updata_at字段的最大值，而不是采集行为发生的时间戳 metadata_barcode_table_create_cols="id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',"\ "time_record TIMESTAMP NOT NULL COMMENT '本次采集记录的最大时间'," "gather_line_count INT NULL COMMENT '本次采集条数'"
在工程根目录下，新建mysql_service.py
#采集mysql数据的主业务逻辑
from util.logging_util import init_logger
from util.mysql_util import MySQLUtil
from model.barcode_model import BarcodeModel
from config import project_config as pc
import sys
def build_db_util():
metadata_db_util=MySQLUtil()#元数据库
source_db_util=MySQLUtil(#数据源数据库
host=pc.source_host,
port=pc.source_port,
user=pc.source_user,
password=pc.source_password
)
target_db_util=MySQLUtil(#目的地数据库
host=pc.target_host,
port=pc.target_port,
user=pc.target_user,
password=pc.target_password
)
return metadata_db_util,source_db_util,target_db_util
def check_table_exists_or_exit(source_db_util):
source_db_util.select_db(pc.source_db_name)
if not source_db_util.check_table_exists(pc.source_db_name,pc.source_barcode_data_table_name):
logger.errot(":数据源库：{pc.source_db_name}中不存在数据源表：{pc.source_barcode_data_table_name},"f"无法采集，程序退出，请找人要去")
sys.exit(1)#表示程序停止运行，传入0表示是正常停止，非0是异常停止
def check_target_table_exists(target_db_util):
target_db_util.select_db(pc.target_db_name)
target_db_util.check_table_exists_and-create(
db_name=target_db_name,
table_name=pc.target_barcode_table_name,
create_cols=pc.target_barcode_table_create_cols
)
def get_last_updata_time(metadata_db_util):
metadata_db_util.select_db(pc.metadata_db_name)
#取updata_at最大时间有两种情况
#取到，后续根据最大时间过滤数据源数据库中的数据
#取不到，说明是第一次运行，同时创建元数据表，取出数据源数据库中的所有数据
last_updata_time=None
if not metadata_db_util.check_table_exists(pc.metadata_db_name,pc.metadata_barcode_table_name):
#进入这个分支说明不存在
metadata_db_util.check_table_exists_and_create(
db_name=pc.metadata_db_name,
table_name=pc.metadata_barcode_table_name,
create_cols=pc.metadata_barcode_table_create_cols
)
else:
query_sql=f"SELECT time_record FROM {pc.metadata_barcode_table_name} ORDER BY time_record DESC LIMIT 1"
result=metadata_db_util.query(
query_sql
)#重复判断是否存在，后续可以优化为一个exist表和create表
#result二维元组(("具体时间"),),，每个小元组都是一行数据，并且这个元组中只有一个值，也就是时间
if len(result)!=0:
last_updata_time=result[0][0]
return last_updata_time
def get_source_data_by_last_updata_time(source_db_util,last_updata_time):
if last_updata_time:
sql=f"SELECT * FROM {pc.source_barcode_data_table_name} WHERE updataAT >='{last_updata_time}'"\
f"ORDER BY updataAT"#updataAT是什么？
else:
sql=f"SELECT * FROM {pc.source_barcode_data_table_name} ORDER BY updataAT"
source_db_util.select_db(pc.source_db_name)
result=source_db_util.query(sql)
return result
def build_models(result):
barcode_models = []
for single_line_result in result:
# single_line_result是元组，存储了一条结果的列
# 查询是SELECT * 查询，结果中的第一个列 第二个列 第N个列是谁，和数据库的表结构顺序一致
code = single_line_result[0]
name = single_line_result[1]
spec = single_line_result[2]
trademark = single_line_result[3]
addr = single_line_result[4]
units = single_line_result[5]
factory_name = single_line_result[6]
trade_price = single_line_result[7]
retail_price = single_line_result[8]
update_at = str(single_line_result[9]) # single_line_result[9]是读取的updateAt时间，类型是datetime，转换成字符串
wholeunit = single_line_result[10]
wholenum = single_line_result[11]
img = single_line_result[12]
src = single_line_result[13]
# 构建BarcodeModel
model = BarcodeModel(
code=code,
name=name,
spec=spec,
trademark=trademark,
addr=addr,
units=units,
factory_name=factory_name,
trade_price=trade_price,
retail_price=retail_price,
update_at=update_at,
wholeunit=wholeunit,
wholenum=wholenum,
img=img,
src=src
)
barcode_models.append(model)
return barcode_models
def write_data_to_mysql(target_db_util，barcode_models):
max_last_updata_time="2023-08-31 00:00:00"
target_db_util_select_db(pc.target_db_name)
count=0#定义计数器，目的是每隔1000条数据提交一次
for model in barcode_models:
if model.updata_at>max_last_updata_time:
max_last_updata_time=model.updata_at
insert_sql=model.generate_insert_sql()
target_db_util.execute_without_autocommit(insert_sql)#可以测试删去without
#批量提交，保证提交的数目，又不会因为内存有限导致代码崩溃
count+=1
if count % 1000==0:
target_db_util.conn.commit()
logger.info(f"从数据源：{pc.source_db_name}库，读取表：{pc.source.barcode_data_table_name}",f"当前写入目标表：{conf.target_barcode_table_name}数据有：{count}行")
target_db_util.conn.commit()#避免遗漏数据，最后提交一批不满1000的整数倍的数据#可以测试删去without,此句删除。并且删去数据库中retail的barcode，metadata的barcode_monitor
logger.info(f"从数据源：{pc.source_db_name}库，读取表：{pc.source.barcode_data_table_name}",f"当前写入目标表：{conf.target_barcode_table_name}数据有：{count}行")
return max_last_updata_time
def write_data_to_csv(barcode_models):
barcode_csv_write_f=open(
file=pc.barcode_out_csv_root_path+pc.barcode_orders_output_csv_file_name,
mode="a"
encoding+"utf8"
)
count=0
for model in barcode_models:
csv_line=model.to_csv()
barcode_csv_write_f.write(csv_line+'\n')
count+=1
if count % 1000 ==0:
barcode_csv_write_f.flush()
logger.info(f"从数据源：{pc.source_db_name}库，读取表：{pc.source_barcode_data_table_name}，"
f"写出CSV到：{barcode_csv_write_f.name}， 当前写出：{count}行。")
barcode_csv_write_f.close()
logger.info(f"从数据源：{pc.source_db_name}库，读取表：{pc.source_barcode_data_table_name}，"
f"写出CSV到：{barcode_csv_write_f.name}， 当前写出：{count}行。")
main
#步骤一：初始化
#1.1 logger
logger=init_logger()
#1.2 构造数据库工具对象，三个
"""重构
metadata_db_util=MySQLUtil()#元数据库
source_db_util=MySQLUtil(#数据源数据库
host=pc.source_host,
port=pc.source_port,
user=pc.source_user,
password=pc.source_password
)
target_db_util=MySQLUtil(#目的地数据库
host=pc.target_host,
port=pc.target_port,
user=pc.target_user,
password=pc.target_password
)
"""
metadata_db_util,source_db_util,target_db_util=build_db_util()
#步骤二：从数据源数据库读取数据
#2.1判断数据源表是否存在，不存在则退出
"""重构
source_db_util.select_db(pc.source_db_name)
if not source_db_util.check_table_exists(pc.source_db_name,pc.source_barcode_data_table_name):
logger.errot(":数据源库：{pc.source_db_name}中不存在数据源表：{pc.source_barcode_data_table_name},"f"无法采集，程序退出，请找人要去")
sys.exit(1)#表示程序停止运行，传入0表示是正常停止，非0是异常停止
"""
check_table_exists_or_exit(source_db_util)
#2.2判断目的地表是否存在，不存在则创建
"""
target_db_util.select_db(pc.target_db_name)
target_db_util.check_table_exists_and-create(
db_name=target_db_name,
table_name=pc.target_barcode_table_name,
create_cols=pc.target_barcode_table_create_cols
)
"""
check_target_table_exists(target_db_util)
#2.3.1读取元数据库存储的上一批次updata_at最大时间
"""
metadata_db_util.select_db(pc.metadata_db_name)
#取updata_at最大时间有两种情况
#取到，后续根据最大时间过滤数据源数据库中的数据
#取不到，说明是第一次运行，同时创建元数据表，取出数据源数据库中的所有数据
last_updata_time=None
if not metadata_db_util.check_table_exists(pc.metadata_db_name,pc.metadata_barcode_table_name):
#进入这个分支说明不存在
metadata_db_util.check_table_exists_and_create(
db_name=pc.metadata_db_name,
table_name=pc.metadata_barcode_table_name,
create_cols=pc.metadata_barcode_table_create_cols
)
else:
query_sql=f"SELECT time_record FROM {pc.metadata_barcode_table_name} ORDER BY time_record DESC LIMIT 1"
result=metadata_db_util.query(
query_sql
)#重复判断是否存在，后续可以优化为一个exist表和create表
#result二维元组(("具体时间"),),，每个小元组都是一行数据，并且这个元组中只有一个值，也就是时间
if len(result)!=0:
last_updata_time=result[0][0]
"""
last_updata_time=get_last_updata_time(metadata_db_util)
#2.3.2根据时间读取数据源数据中在范围内的数据
"""
if last_updata_time:
sql=f"SELECT * FROM {pc.source_barcode_data_table_name} WHERE updataAT >='{last_updata_time}'"\
f"ORDER BY updataAT"
else:
sql=f"SELECT * FROM {pc.source_barcode_data_table_name} ORDER BY updataAT"
source_db_util.select_db(pc.source_db_name)
result=source_db_util.query(sql)
"""
result=get_source_data_by_last_updata_time(source_db_util,last_updata_time)
#测试result里面有无数据
print(len(result))
for r in result:
print(r)
break
#步骤三：构造模型
"""
barcode_models=[]
for single_line_result in result:
code = single_line_result[0]
name = single_line_result[1]
spec = single_line_result[2]
trademark = single_line_result[3]
addr = single_line_result[4]
units = single_line_result[5]
factory_name = single_line_result[6]
trade_price = single_line_result[7]
retail_price = single_line_result[8]
update_at = str(single_line_result[9]) # single_line_result[9]是读取的updateAt时间，类型是datetime，转换成字符串.#时间戳转化为字符串，如何比较字符串大小
wholeunit = single_line_result[10]
wholenum = single_line_result[11]
img = single_line_result[12]
src = single_line_result[13]
# 构建BarcodeModel
model = BarcodeModel(
code=code,
name=name,
spec=spec,
trademark=trademark,
addr=addr,
units=units,
factory_name=factory_name,
trade_price=trade_price,
retail_price=retail_price,
update_at=update_at,
wholeunit=wholeunit,
wholenum=wholenum,
img=img,
src=src
)
barcode_models.append(model)
return barcode_models
"""
barcode_models=build_models(result)
#single_line_result是元组，存储了一条结果的列？
#循环取出每一行数据，每一行数据内构造一个模型对象，每一行数据都存储在这个对象中
#步骤四：写入数据
#4.1写入数据到目的地数据库
#每条数据都需判断并记录最大时间
"""重构
max_last_updata_time="2023-08-31 00:00:00"
target_db_util_select_db(pc.target_db_name)
count=0#定义计数器，目的是每隔1000条数据提交一次
for model in barcode_models:
if model.updata_at>max_last_updata_time:
max_last_updata_time=model.updata_at
insert_sql=model.generate_insert_sql()
target_db_util.execute_without_autocommit(insert_sql)
#批量提交，保证提交的数目，又不会因为内存有限导致代码崩溃
count+=1
if count % 1000==0:
target_db_util.conn.commit()
logger.info(f"从数据源：{pc.source_db_name}库，读取表：{pc.source.barcode_data_table_name}",f"当前写入目标表：{conf.target_barcode_table_name}数据有：{count}行")
target_db_util.conn.commit()#避免遗漏数据，最后提交一批不满1000的整数倍的数据
logger.info(f"从数据源：{pc.source_db_name}库，读取表：{pc.source.barcode_data_table_name}",f"当前写入目标表：{conf.target_barcode_table_name}数据有：{count}行")
"""
max_last_updata_time=write_data_to_mysql(target_db_util，barcode_models)#与json.service.py内的函数同名，不在同一文件，无妨
#4.2写入数据到csv
"""重构
barcode_csv_write_f=open(
file=pc.barcode_out_csv_root_path+pc.barcode_orders_output_csv_file_name,
mode="a"
encoding+"utf8"
)
count=0
for model in barcode_models:
csv_line=model.to_csv()
barcode_csv_write_f.write(csv_line+'\n')
count+=1
if count % 1000 ==0:
barcode_csv_write_f.flush()
logger.info(f"从数据源：{pc.source_db_name}库，读取表：{pc.source_barcode_data_table_name}，"
f"写出CSV到：{barcode_csv_write_f.name}， 当前写出：{count}行。")
barcode_csv_write_f.close()
logger.info(f"从数据源：{pc.source_db_name}库，读取表：{pc.source_barcode_data_table_name}，"
f"写出CSV到：{barcode_csv_write_f.name}， 当前写出：{count}行。")
"""
write_data_to_csv(barcode_models)
#步骤五：记录MySQL元数据（这批次的最大时间），到元数据库中
metadata_util.select_db(pc.metadata_db_name)#是否是metadata_db_util
sql = f"INSERT INTO {pc.metadata_barcode_table_name}(" \
f"time_record, gather_line_count) VALUES(" \
f"'{max_last_update_time}', " \
f"{result_len}" \
f")"
metadata_util.execute(sql)
metadata_util.close_conn()
target_util.close_conn()
source_util.close_conn()
logger.info("读取MySQL数据，写入目标MySQL和CSV程序执行完成")
20230830，0508-0511(64min)，3h
将数据源采集到目的地目的：解决数据孤岛
在4.2写入数据到csv时，配置project_config.py中
#Barcode业务的相关配置
#生成Csv的路径，
barcode_output_csv_root_path="D:/2.project/data/pyetl-data-logs/output/csv/"
#写出订单模型，CSV的文件名字配置
barcode_orders_output_csv_file_name=f"barcode-{time.strftime{"%Y%m%d-%H%M%S",time.localtime}}"
将时间戳转化为字符串后，字符串按字典序比较大小
#按字典序比较，即挨个字符比较，比较对应位置的ASCII #从大到小排列 "1" "2" "11" "`12" "24" "3" "200" "50" "1" "11" "12" "2" "24" "200" "3" "50"
flush演示：flush目的在于刷新缓存，确认数据不在内存中，而是写进磁盘
#with自带关闭功能，在关闭时自动调用flush
with open("a.txt","w") as f:
f.write('abc\n')
f=open("a1.txt","w")
f.write("def/n")
f.close()
f=open("a2.txt","w")
f.write("def/n")
f.flush()
import time#设置不让文件自动停止运行
f=open("a3.txt","w")
f.write("def/n")
time.sleep(30)
项目需求3：后台日志数据处理
一、日志数据介绍
在工程下建立目录Directory，名为simulator，在里面建立backend_logs_simlulaor.py
# coding:utf8
"""
后端服务写出log日志的模拟数据生成器
"""
import datetime
import random
import time
single_log_lines = 1024 # 一个logs文件生成多少行数据
generate_files = 5 # 一次运行生成多少个文件
output_path = "D:/2.project/data/pyetl-data-logs/backend_logs/"
log_level_array = ['WARN', 'WARN', 'WARN', 'INFO', 'INFO', 'INFO', 'INFO', 'INFO', 'INFO', 'INFO', 'INFO',
'INFO', 'INFO', 'INFO', 'INFO', 'INFO', 'INFO', 'INFO', 'INFO', 'INFO', 'INFO', 'INFO', 'INFO',
'ERROR']
backend_files_name = ['barcode_service.py', 'barcode_service.py', 'barcode_service.py',
'orders_service.py', 'orders_service.py', 'orders_service.py', 'orders_service.py',
'orders_service.py', 'orders_service.py',
'shop_manager.py', 'shop_manager.py',
'user_manager.py', 'user_manager.py', 'user_manager.py',
'goods_manager.py', 'goods_manager.py', 'goods_manager.py', 'goods_manager.py',
'goods_manager.py', 'goods_manager.py',
'base_network.py', 'base_network.py',
'event.py', 'event.py', 'event.py', 'event.py', 'event.py', 'event.py', 'event.py']
visitor_areas = {
'北京市': ['海淀区', '大兴区', '丰台区', '朝阳区', '昌平区', '海淀区', '怀柔区'],
'上海市': ['静安区', '黄浦区', '徐汇区', '普陀区', '杨浦区', '宝山区', '浦东新区', '浦东新区'],
'重庆市': ['万州区', '万州区', '涪陵区', '渝中区', '沙坪坝区', '九龙坡区', '南岸区'],
'江苏省': ['南京市', '南京市', '南京市', '苏州市', '苏州市', '无锡市', '常州市', '宿迁市', '张家港市'],
'安徽省': ['阜阳市', '阜阳市', '六安市', '合肥市', '合肥市', '合肥市', '池州市', '铜陵市', '芜湖市'],
'山东省': ['济南市', '济南市', '青岛市', '青岛市', '青岛市', '菏泽市'],
'湖北省': ['武汉市', '武汉市', '武汉市', '十堰市', '荆州市', '恩施土家族苗族自治州'],
'广东省': ['广州市', '广州市', '广州市', '深圳市', '深圳市', '深圳市', '珠海市'],
'天津市': ['和平区', '河东区', '河西区', '武清区', '宝坻区'],
'湖南省': ['长沙市', '长沙市', '长沙市', '长沙市', '长沙市', '长沙市', '长沙市', '株洲市', '张家界市', '常德市', '益阳市'],
'浙江省': ['杭州市', '杭州市', '湖州市', '绍兴市', '舟山市', '金华市', '嘉兴市', '丽水市']
}
visitor_province = ['北京市', '上海市', '重庆市', '江苏省', '安徽省', '山东省', '湖北省', '广东省', '天津市', '湖南省', '浙江省']
response_flag = [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0]
response_for_error_flag = [1, 1, 1, 1, 1, 0]
for j in range(0, generate_files):
write_file_path = f'{output_path}{datetime.datetime.now().strftime("%Y-%m-%d-%H-%M-%S")}.log'
with open(write_file_path, 'w', encoding="UTF-8") as f:
for i in range(single_log_lines):
date_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f")
log_level = log_level_array[random.randint(0, len(log_level_array) - 1)]
file_name = backend_files_name[random.randint(0, len(backend_files_name) - 1)]
if not log_level == "ERROR":
if response_flag[random.randint(0, len(response_flag) - 1)] == 1:
response_time = random.randint(0, 1000)
else:
response_time = random.randint(1000, 9999)
else:
if response_for_error_flag[random.randint(0, len(response_for_error_flag) - 1)] == 1:
response_time = random.randint(0, 1000)
else:
response_time = random.randint(1000, 9999)
province = visitor_province[random.randint(0, len(visitor_province) - 1)]
city = visitor_areas[province][random.randint(0, len(visitor_areas[province]) - 1)]
log_str = f"{date_str}\t[{log_level}]\t{file_name}\t响应时间:{response_time}ms\t{province}\t{city}\t" \
f"这里是日志信息......"
f.write(log_str)
f.write("\n")
print(f"本次写出第: {j + 1}个文件完成, 文件为: {write_file_path}, 行数:{single_log_lines}")
time.sleep(1)
结果
数据读出后，写入csv和目的地数据库
构造模型（注意字段）：字符串转时间戳，取日志级别，文件名，相应时间只要毫秒，省，市
一行数据存一个对象，写csv，目的地数据，
写元数据，即被记录的数据
1.数据模拟器
2.数据结构
二、实现步骤
1.步骤
2.实现代码
数据可视化
一、什么是BI
1.商业智能
BI（BusinessIntelligence）即商业智能，它是一套完整的解决方案，用来将企业中现有的数据进行有效的整合，快速准确的提供报表并提出决策依据，帮助企业做出明智的业务经营决策。
通过指标的展示助力决策
2.FineBI
使用FineBI进行数据分析的一般步骤 即：
-
数据准备
-
数据加工(数据处理)
-
可视化分析(生成各种图表)
-
仪表板(把各种图表整合在一起)
-
分享仪表板
3.功能简介
二、FineBI下载安装
1.FineBI下载
jdk:java development kit，安装jdk-8u241-windows-x64，添加环境变量（复制jdk目录，Windows设置）。打开cmd：win+r，根据输出界面判断配置成功。选择内置数据库三。
2.FineBI安装
3.FineBI的启动
4.使用流程
仪表板-我的自助数据表
系统管理-配置工作
数据准备-添加业务包（可重命名）-点进去添加表
三、连接数据
1.数据来源
2.连接数据的基本步骤
管理系统-数据连接-数据连接管理-新建数据连接-MySQL-数据连接名称-数据库名称（retail）-主机localhost-端口3306-用户名密码-编码自动-测试连接-
数据准备-右击数据列表-抽取数据-更取消-返回-业务包（finebl使用）-点进去-添加表-数据库表-自动出现3个已有数据库表
四、创建仪表盘
1.每日销售
在仪表板-新建仪表板（finebi使用）-组件-抽取数据-finebi使用-选表-确定-维度（字符串类型）指标（数值类型）-图表类型-首排第四个自定义-选择图形属性（如柱状图）-将订单时间拖到横轴，总价格拖到纵轴-右上角进入仪表盘-
图表美化（右上角编辑）-改名称（编辑标题）-订单日期拖到编辑样式中的颜色-纵轴可以多拖指标-图表类型改为折线图-组件样式（选择是否展示标题、图例等，背景改透明，图片）
2.热销产品词云图
添加组件-选表-图表类型（abcd）-文本（商品名称）-大小（数量）-颜色（商品名称）-结果过滤器（商品名称）过滤不合适的文本-过滤-添加条件-选择字段-商品名称-不属于-（选择不想要的名称）-
3.地图
添加组件-图形类型自定义-填充地图-维度里的店铺所在省（地理角色-省市自治区）-经纬度分别拖到横轴纵轴-图形属性-标签（店铺所在省？记录数，相对位置决定图表上展示位置）-颜色（店铺所在省）-组件样式-都不显示-背景。
课程来源：【程序员四次元ポケット的个人空间-哔哩哔哩】 https://b23.tv/dgnWYRc
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
