先扯点别的, 2017年我有篇博客讨论过天干地支的计算, 偶尔试了下用Python来表达,没想到函数特别简单.
学习资源 <利用python进行数据分析> 第2版(因为纸版的翻译得不好,有人另译了一遍放在网上供人学习用)
链接1 , 同时译者在简书 网站上也有张贴, 访问更快. https://www.jianshu.com/p/04d180d90a3f
回到正题,聊聊配方管理数据库. 一般来讲,配方在电脑中存在形式有以下几种:
文本形式的, 这种直接用文本串来表示,没有转化为数字.这是最简单最初级的. 这种形式没法进行数值查询比较,不便使用,只是个存档而已.
把配方转化为归一化的百分比(即各成分加起为总和为100%). 这种大多数人会用Excel来保存.具体的实现中,又有几种形式:
2.1零散的Excel文件, 分别存放在不同的文件中或表单中.这种方式好处是很简单,把文件堆在一个目录下就可以了.但缺点是查找起来不太方便,如果要查找含有某个特定成分的配方,需要跨多个文件查找,比较起来也不方便.因此也是比较初级的阶段.
2.2将配方整理成数据库形式.一行放一个配方信息, 配方成分或某个指标以列的形式来提供.这种其实就是把Excel当成数据库在用了.这个是较为成熟的方法, Excel大家都比较熟悉,容易上手, 查找起来很方便,还可以进行多组分查找筛选,含量区间筛选(如果配方成分含量以数值形式保存的话).但Excel当成数据库用有一个很大的不足:一旦错误选择了对某列筛选,可能原来在第1行的跑到了第5行,第3行的跑到了第2行, 配方就完全乱套了,这是最大的风险. 如果能够避免排序操作(比如完全用代码来操作Excel数据表),这个风险可避免.
把配方数据整理成数据库来管理.这是最合适的形式,也是本文聊的内容. 只是大多数使用Office的人只用Word/Excel/Powerpoint, 对Access往往不熟悉. 其实配方管理是非常典型的小型数据库应用场景, 特别适合用关系型数据库来设计(如Access, SQLite等).
一般来讲,配方数据库会包括以下几类信息:
3.1 文本类信息,比如 配方的型号,别名,应用场景,设计意图,特殊要求,配方的文本形式等.这类可以用数据库的文本类字段来保存. 根据内容的不同,设计不同的字段大小来容纳. 而且尽量使用Unicode格式的文本字段以完善支持中文字符.这里面其实可以细分为两类字段
3.1.1 定长字段.如配方型号,别名这种, 往往会限定它的字符长度.这时设计一个定长(比如说20字节)的就够用了.定长字段可以用来做为主键,以防止出现重复的值.
3.1.2 变长字段.比如测试结果,开发背景,备注等.可能要保存或长或短的文本.这时用变长的字段比较合适.Access中我喜欢用memo类型,在SQLite中可以使用NvarChar, Text等
3.1.3 代理字段 因为有些配方中可能涉及到一些特殊的添加剂或没有料想到的新物质, 平时很少用, 特意为它设一个字段比较浪费空间,而且碰到新的成分每增加一个都要修改一次数据库的字段名,比较麻烦.后来我就设计了一个代理字段.比如说用3个代理字段Agent,Agent2,Agent3来应对一个配方中的3种可能的新物质, 再用一个文本字段AgentData以JSON格式保存各成分的信息(名称,CAS号,用量,归类等). 存入的时候不管什么新成分,只要不超过3个,我都可以存下, 具体的内容则通过解析对应的AgentData来还原出成分与含量.这种做法一劳永逸的解决了很多新物质在数据库中存放的问题.
3.1.4 如果你保存的数据需要同时保留它的测试条件或说明文字,并且不需要对数据进行数值比对(通常条件变动频繁的数据也不好一起比对),则可以用文本型字段来保存,这样数值与文本写在一起,输入上比较方便.
3.1.5 如果需要保存比较复杂的信息, 比如上面3.1.3讲到的代理信息,包括了多种类型的信息, 可以定义JSON结构或XML Schema(或DTD), 这两种格式适合用来保存不同类型的复杂信息, 以长文本形式保存在字段中. 写入或读取之前进行转换即可.
3.2 数值类信息,比如各成分的含量(EC, PC, DMC,LIPF6等), 电导率, 密度等数值. 由于电解液用到的材料比较多,可能需要设计几十个成分字段才够用. 一般来讲,字段值如果是百分数的话, 准确到小数点后4位足够了.(例如12.5000), 使用单精度/双精度/浮点数等都可以. 日期型字段本质上也是数值类,但我比较喜欢用文本类字段来保存日期,因为不常用日期来进行检索,文本就够用了,而且容错性能很好. 但数据库中也有专门的日期类型. 另外布尔型字段也算数值类字段.
3.2.1小技巧:建议内部设置一个求和字段,对导入到数据库的各成分总和进行验算,以防止有时字段不存在信息丢失配方最终没有归一而你不能发现. Access高版本有一个计算字段可以用. 其它数据库中好象没有这样的字段,但可以通过查询或函数来实现.
3.2.2小技巧2:成分含量的字段可以考虑默认为0,表示没有添加. 当然也可以为空值(NULL或None),不过对多个配方进行计算时空值可能导致计算失效,需要预先处理.比如在文末的HDF5文件读出来时,使用了dropna或fillna以去除空值或替换空值.
3.2.3小技巧3: 注意某些字段名可能会与数据库的保留字重名,这样可能导致意外的错误.注意预防就好了.我在前面有一篇讲MySQL的文章中提到过.
3.3 二进制数据. 在Access数据库中,可以用"OLE对象" 类型,或是"附件"类型来存储一些二进制数据.但直接将二进制数据存放在数据库中容易导致数据库文件迅速发胖,容易导致数据文件损坏.如果可能的话,二进制文件存放在目录下,在数据库中保存它的目录位置或超链接这种简单文本, 这种设计更强壮一些.比如NoteExpress中管理文献就是采用这种方式. 其它数据库中也有BLOB字段可用,不过我没有用过.
既然数据库中涉及到的字段类型有好多种, 那种仅支持数值型的表格(如Origin)或Python中的Numpy数据表是不够用的. 很多人为什么选择Excel,就是因为Excel什么类型都可以放,用起来就非常灵活.也正是因为Excel在同一列中也可以存放不同的类型,这样又容易出现同一列内数据类型的混乱.
其实,关系型数据库是最适合这类场景的, 它实现了对同一行数据的锁定,随意排序也不会打乱,又要求同一列数据类型必须一致, 保证了数据类型相同,易于处理. 利用SQL语句还可以对数据进行各种灵活的筛选,功能足够强大. 我测试过用Access文件(MDB或ACCDB), Excel文件(xls或xlsm),Sqlite文件(db), MySQL数据库, 都是可以用来管理配方的. Access和Excel用VBA + ADODB来访问处理, SQLite与MySQL我是用xlwings+Python加上相应的驱动(sqlite3库 或 PyMysql库)来访问,都很方便.
在学习过Pandas教程(链接2)之后, 发现Pandas库除了用于分析数据, 功能十分强大以外, 它还可以把数据存放起来进行读或写, 同一行数据可以锁定,同一列的数据类型相同, 就像数据库软件一般, 因此也可以用来保存配方数据和进行相应的数据处理. 它可以存储为多种格式, 比如说HDF5格式(类型table),而且检索起来比较方便.
下面就展示一个读取HDF5数据库并进行筛选的例子(Excel+Python+xlwings+Pandas)
其实如果你要实现配方管理,用EXCEL VBA 通过 ADODB 管理 ACCESS 是最简单的方式. 以上示例不必管它.
