Python笔记 之 根据用户订单计算商品同单概率

说明

该算法通过获取用户订单详情（未拆分订单），根据商品关联关系算法计算商品两两同单的概率，并以此为先验概率向用户推荐商品。

算法

ini配置文件

文件名：CFGOracle.ini

;Oracle数据库配置信息  
  
[orcl]  
host = 192.168.0.10 
port = 1521  
sid = orcl  
user = test  
psword = 123456

configparser辅助类

# -*- codeing = utf-8 -*-  
# @File : CCLParameter.py  
# @Author : 一起种梧桐吧  
# @Version : Python 3.10.0  
# @Software : PyCharm  
# @Time : 2022-08-25 14:23  
# @Purpose : 读取ini配置文件  
  
from configparser import ConfigParser as config  
  
  
class Parameter():  
   """读取mysql数据库配置文件'./CFGMysql.ini'"""  
   def __init__(self, section, filename, coding='utf-8'):  
      """初始化参数"""  
      self.section = section  
      self.filename = filename  
      self.coding = coding  
      self.config = config()  
      self.dict = {}  
      self.getOption()  
  
   def getOption(self):  
      """遍历option获取配置文件"""  
      try:  
         self.config.read(self.filename, self.coding)  
         for option in self.config.options(self.section):  
            self.dict[option] = self.config.get(self.section, option)  
      except Exception as err:  
         return str(err)  
      else:  
         return '成功读取"{}"文件"{}"配置'.format(self.filename, self.section)  
  
  
if __name__ == "__main__":  
   p = Parameter('ouser', './CFGMysql.ini')  
   print(p.dict)

数学排列辅助类

# -*- codeing = utf-8 -*-  
# @File : CCLPermutation.py  
# @Author : 一起种梧桐吧  
# @Version : Python 3.10.0  
# @Software : PyCharm  
# @Time : 2022-08-23 16:29  
# @Purpose :  使用Python实现排列算法  
  
class Permutation():  
   """构建数学排列，对一个列表或者元组进行数据分析"""  
   def permute(self, lst):  
      """  
      对数据源进行排列并返回排列列表  
      :param lst: 元素列表  
      :return:所有的排列组合方式  
      """      if len(lst) <= 1: return [[]]  # 列表为空或者单个元素返回空列表  
      if len(lst) == 2: return self.subset(lst)  # 元素个数【1,2】直接调用subset()  
      # 元素个数>=2循环直接调用subset()  
      results = []  
      for i in range(2, len(lst) + 1):  
         results.extend(self.subset(lst, i))  
      return results  
     
   def subset(self, lst, size=2):  
      '''  
      构建指定个数元素的排列方式列表  
      :param lst: 元素列表  
      :param size: 指定长度  
      :return: 所有的排列组合方式  
      '''      if size < 1 or not lst: return [[]]  
      results = []  
      for i in range(len(lst)):  
         pick = lst[i:i + 1]  
         rest = lst[:i] + lst[i + 1:]  
         for x in self.subset(rest, size - 1):  
            results.append(pick + x)  
      return results  
  
  
if __name__ == '__main__':  
   lt = ['a', 'b', 'c']  
   pm = Permutation()  
   print(pm.permute(lt))  
   print(pm.subset(lt, 2))

oracle辅助类

# -*- codeing = utf-8 -*-  
# @File : CCLOracle.py  
# @Author : 一起种梧桐吧  
# @Version : Python 3.10.0  
# @Software : PyCharm  
# @Time : 2022-08-25 14:57  
# @Purpose : 连接指定Oracle数据库并管理指针  
  
import cx_Oracle as oracle  
from CCLParameter import Parameter  
  
defautfile = './Cfg_oracle.ini'  
  
  
class OracleHelper():  
   """获取oracle数据库游标并管理指针"""  
   def __init__(self, section, filename='./CFGOracle.ini', coding='utf-8'):  
      """初始化并读取配置文件"""  
      self.section = section  
      self.config = Parameter(section, filename, coding)  
      self.conn = None  
      self.curs = None  
      self.getCursor()  
   def getCursor(self):  
      """尝试获取游标"""  
      try:  
         dns = oracle.makedsn(self.config.dict['host'],  
                         int(self.config.dict['port']),  
                         self.config.dict['sid'])  
         self.conn = oracle.connect(self.config.dict['user'],  
                              self.config.dict['psword'],  
                              dns)  
         self.curs = self.conn.cursor()  
      except Exception as err:  
         return str(err)  
      else:  
         return '成功连接到Oracle数据库%s' % self.section  
     
   def release(self):  
      """释放游标"""  
      if self.curs:  
         self.curs.close()  
         return '成功释放Oracle数据%s指针' % self.section  
      if self.conn:  
         self.conn.close()  
         return '成功释放Oracle数据%s连接' % self.section  
  
  
if __name__ == '__main__':  
   orcl = OracleHelper('slave01')  
   orcl.release()

计算商品关联关系

# -*- codeing = utf-8 -*-  
# @File : CCLAlgorithm.py  
# @Author : 一起种梧桐吧  
# @Version : Python 3.10.0  
# @Software : PyCharm  
# @Time : 2022-09-09 10:36  
# @Purpose : 计算商品关联关系  
  
from CCLDecorator import decorator  
from CCLOracle import OracleHelper  
from CCLPermutation import Permutation  
  
  
class Algorithm():  
   """    计算商品关联关系  """   
   def __init__(self):  
      self.defaultsize = 200000  
      self.ordercount = 10  
      self.tasks = []  
      self.relations = {}  
      
   def getTasks(self, section='orcl',  
             sql='''Select frmuser, frmtable, sqlread, touser, totable, sqlinsert  
                From ctrl_sqlcommands                Where pjectname = 'hnxhhy'                And sqltype = 'algorithm'                And flag = 1'''):  
      """根据section读取CFGOracle.ini配置文件，读取待执行任务"""  
      client = None  
      try:  
         client = OracleHelper(section)  
         self.tasks = client.curs.execute(sql).fetchall()  
      except Exception as err:  
         return str(err)  
      else:  
         return '获取任务{}条'.format(len(self.tasks))  
      finally:  
         if client: client.release()  
   
   def statistic(self, item):  
      """  
      分析订单数据获取商品之间同单数量  
      :param item:      :return:  
      """      
      frmuser, frmtable, sqlread, touser, totable, sqlinsert = range(6)  
      oracle_read = OracleHelper(item[frmuser])  
      oracle_load = OracleHelper(item[touser])  
      # item[sqlread]在oracle数据库为nclob类型，需要使用read()获取存储的字符串
      oracle_read.curs.execute(item[sqlread].read())  
      soitem = {}  
      ordercode, productid = range(2)  
      for recode in oracle_read.curs.fetchall():  
         if recode[ordercode] in soitem.keys():  
            soitem[recode[ordercode]].add(recode[productid])  
         else:  
            soitem[recode[ordercode]] = {recode[productid], }  
      pm = Permutation()  
      for val in soitem.values():  
         # 判断订单商品数量，如果商品数量小于1继续下一循环  
         if len(val)<=1:  
            continue  
         for rel in pm.subset(list(val), 2):  
            _ = '&'.join(rel)  
            if _ in self.relations.keys():  
               self.relations[_] += 1  
            else:  
               self.relations[_] = 1  
      datas = []  
      for key, val in self.relations.items():  
         # 如果同单次数小于阈值，继续下一循环  
         if val <= self.ordercount:  
            continue  
         _ = key.split('&')  
         _.append(val)  
         datas.append(_)  
      rowcount = len(datas)  
      while len(datas) >= self.defaultsize:  
         slice, datas = datas[:self.defaultsize], datas[self.defaultsize:]  
         oracle_load.curs.executemany(item[sqlinsert].read(), slice)  
         oracle_load.conn.commit()  
      else:  
         if datas:  
            oracle_load.curs.executemany(item[sqlinsert].read(), datas)  
            oracle_load.conn.commit()  
      if oracle_read: oracle_read.release()  
      if oracle_load: oracle_load.release()  
      return "成功从{}.{}传输{}条数据到{}.{}".format(item[frmuser], item[frmtable], rowcount, item[touser], item[totable])  

   def run(self):  
      self.getTasks()  
      if not self.tasks:  
         return '没有待执行任务'  
      for item in self.tasks:  
         self.statistic(item)  
      return '{}条任务执行完成'.format(len(self.tasks))  
  
  
if __name__ == '__main__':  
   worker = Algorithm()  
   worker.getTasks()  
   worker.run()