def insert_user_data(self):
db = CommonTool.db_mysql_connect()
cursor = db.cursor()
sql_c = u"""
insert into T_SNUSER_CARTONOCCUPATIONRATIO_DATA (startdate, province,
snuserproportion, Terminalnumber) VALUES
('%s', '%s', '%s', %s)
"""
sql_e = u"""
insert into T_SNUSER_EPGSUCCESSRATE_DATA (startdate, province,
snuserproportion, Terminalnumber) VALUES
('%s', '%s', '%s', %s)
"""
sql_s = u"""
insert into T_SNUSER_SUCCESSFULPLAYBACKRATE_DATA (startdate, province,
snuserproportion, Terminalnumber) VALUES
('%s', '%s', '%s', %s)
"""
for i in self.__user_data().items():
if i[0] == 'st':
for key, value in i[1]:
cursor.execute(sql_c % (self.date, self.city, key, value))
elif i[0] == 'ert':
for key ,value in i[1]:
cursor.execute(sql_e % (self.date, self.city, key, value))
else:
for key, value in i[1]:
cursor.execute(sql_s % (self.date, self.city, key, value))
db.commit()
cursor.close()
db.close()
def insert_city_data(self):
db = CommonTool.db_mysql_connect()
cursor = db.cursor()
sql_c = u"""
insert into T_CITY_CARTONOCCUPATIONRATIO_DATA (startdate,
province, citycode, Successfulplaybackrate, Terminalnumber)VALUES
('%s', '%s', '%s', %s, %s)
"""
sql_e = u"""
insert into T_CITY_EPGSUCCESSRATE_DATA (startdate,
province, citycode, Successfulplaybackrate, Terminalnumber) VALUES
('%s', '%s',' %s', %s, %s)
"""
sql_s = u"""
insert into T_CITY_SUCCESSFULPLAYBACKRATE_DATA (startdate,
province, citycode, Successfulplaybackrate, Terminalnumber)VALUES
('%s', '%s', '%s', %s, %s)
"""
for i in self.__city_data().items():
if i[1]['pt'] == 0:
num1 = 'null'
else:
num1 = i[1]['st'] / i[1]['pt']
if i[1]['et'] == 0:
num2 = 'null'
else:
num2 = i[1]['ert'] / i[1]['et']
if i[1]['pc'] == 0:
num3 = 'null'
else:
num3 = i[1]['psc'] / i[1]['pc']
cursor.execute(sql_c % (self.date, self.city, i[0],
num1, i[1]['nbs']))
cursor.execute(sql_e % (self.date, self.city, i[0],
num2, i[1]['nbs']))
cursor.execute(sql_s % (self.date, self.city, i[0],
num3, i[1]['nbs']))
db.commit()
cursor.close()
db.close()
def insert_sn_data(self):
db = CommonTool.db_mysql_connect()
cursor = db.cursor()
sql = u"""
insert into T_SN_LIST_DATA (startdate, province, sn_id,
equipmentmodel, Probeversionnumber, Boot, STBPERIODIC,
STBALARM, Successfulplaybackrate, Cartonoccupationratio, EPGsuccessrate,
asmd, cpname, city_code, plurl, Programinfo)
values ('%s','%s', '%s', '%s', '%s', '%s', '%s', '%s', %s, %s, %s,
'%s', '%s', '%s', '%s', '%s')
"""
for dl in self.data.items():
if dl[1]['pc'] == 0:
num1 = 'null'
else:
num1 = dl[1]['psc'] / dl[1]['pc']
if dl[1]['pt'] == 0:
num2 = 'null'
else:
num2 = dl[1]['st'] / dl[1]['pt']
if dl[1]['et'] == 0:
num3 = 'null'
else:
num3 = dl[1]['ert'] / dl[1]['et']
cursor.execute(sql % (self.date, self.city, dl[0],
dl[1]['modle'],
dl[1]['version'],
json.dumps(dl[1]['iofile']),
json.dumps(dl[1]['pcfile']),
json.dumps(dl[1]['amfile']),
num1, num2, num3,
dl[1]['asmd'],
dl[1]['cpname'],
dl[1]['city'],
dl[1]['plurl'],
json.dumps(dl[1]['aifile'])))
db.commit()
cursor.close()
db.close()
# -*- coding: utf-8 -*-
import xlwt
import os
import yaml
from common.tools import CommonTool
catalog = r'C:\Users\pactera\Desktop\xinjiang_20180503_PERIODIC'
db = CommonTool(catalog)
data = db.file_list()
vl_dict = dict()
vl_dict['软探针版本'] = dict()
vl_dict['设备类型'] = dict()
vl_dict['设备SN'] = dict()
number = 1
for i in data:
if i[10] == '':
i[10] = 0
if i[11] == '':
i[11] = 0
if i[1] == 'V1.0.0':
pass
if i[1] == 'V2.0.0':
if i[-2] == '':
i[-2] = 'null'
if i[-2] not in vl_dict['软探针版本'].keys():
vl_dict['软探针版本'][i[-2]] = [int(i[10]), int(i[11]), number]
else:
vl_dict['软探针版本'][i[-2]] = [vl_dict['软探针版本'][i[-2]][0] + int(i[10]), \
# -*- coding: utf-8 -*-
import os
from common.tools import CommonTool
db = CommonTool.db_mysql_connect()
class CreateSelectTN:
def __init__(self, catalog=None, data_list=None):
self.catalog = catalog
if os.path.basename(catalog) == 'ALARM':
self.BASE_TOW = '004'
elif os.path.basename(catalog) == 'PERIODIC':
self.BASE_TOW = '002'
elif os.path.basename(catalog) == 'PROGRAMINFO':
self.BASE_TOW = '003'
else:
self.BASE_TOW = '001'
self.db = db
self.cursor = self.db.cursor()
self.data_list = data_list
d = os.path.splitdrive(catalog)
d = d[1].split('\\')
self.base_city_date = d[-2].split('_')
def obtain_sn_tn(self):
tn = 'SN_' + self.BASE_TOW + '_' + self.base_city_date[0] + '_' + self.base_city_date[1]
self.cursor.execute(u"""DROP TABLE IF EXISTS %s""" % tn)
sql_t = None
for v_l in range(len(self.data_list)):
if sql_t == None:
sql_t = """C_%s varchar(45)""" % v_l
# -*- coding: utf-8 -*-
from common.tools import CommonTool
path = r'E:\数据\河南\001'
db = CommonTool(path)
data = db.file_list()
def manufacturer_sn_v01(self):
vl_dict = {}
for i in self.data:
if len(i) < 33:
continue
if i[10] == '':
i[10] = 0
if i[11] == '':
i[11] = 0
if i[21] == '':
i[21] = 0
if i[22] == '':
i[22] = 0
# if vl_dict == {}:
# vl_dict[i[32]] = dict()
# vl_dict[i[32]][i[3]] = [int(i[10]), int(i[11]), 1, int(i[21]), int(i[22]), i[33]]
if i[32] not in vl_dict.keys():
vl_dict[i[32]] = dict()
vl_dict[i[32]][i[3]] = [
# -*- coding: utf-8 -*-
import threading
from common.tools import CommonTool
path = r'C:\Users\wj\Desktop\xinjiang_20180503_PERIODIC'
file_q = CommonTool(path)
fq = file_q.file_queue()
class SyncopateTools(threading.Thread):
def __init__(self, name):
threading.Thread.__init__(self)
self.t_name = name
def run(self):
global fq
fo = open(self.name, 'ab')
while not fq.empty():
for line in fq.get():
# try:
# data = line.decode(encoding='utf-8')
# except UnicodeEncodeError:
# data = line.decode(encoding='gbk').encode('utf8')
fo.write(line)
fo.close()
if __name__ == "__main__":
threads = []
import time
from common.ChoiceVersion import Storage
from common.tools import CommonTool
from itertools import islice
import chardet
import pymysql
import csv
import yaml
filepath = r'C:\Users\pactera\Desktop'
# filepathr = r'C:\Users\pactera\Desktop\PERIODIC'
#filepathr = r'C:\Users\pactera\Desktop\xinjiang_20180503_PERIODIC'
# filepathr = r'C:\Users\pactera\Desktop\test\PERIODIC'
filepathr = r'C:\Users\pactera\Desktop\henan_20180506\PERIODIC'
db = CommonTool(filepathr)
data = db.file_list()
# data_storage = Storage(data, filepath)
# if 'V2.' or '20' in (list(islice(data, 1))[0][1]):
# data_storage.v_2_out_excel()
# else:
# data_storage.v_1_out_excel()
# 3-SN, 2-city, 30-接入方式, 64-终端, 64-软探针版本号, 33-牌照方编码, 36-用户信息
for i in data:
print(i[3])
# i[1], i[3], i[2], i[30], i[64], i[63], i[32], i[33], i[36], i[31]
#
# print(i[1], i[2], i[30])
break
# for i in data:
# -*- coding: utf-8 -*-
import threading
from common.tools import CommonTool
from common.sctn import CreateSelectTN
# 互斥锁的声明
mutex_lock = threading.RLock()
data = CommonTool(r'C:\Users\wj\Desktop\hena_20180503\PERIODIC')
data_file = data.file_sn_list()
db_t = CreateSelectTN(r'C:\Users\wj\Desktop\hena_20180503\PERIODIC', next(data_file))
db_name = db_t.obtain_sn_tn()
db = CommonTool.db_mysql_connect()
cursor = db.cursor()
class SyncopateTools(threading.Thread):
def __init__(self, name):
threading.Thread.__init__(self)
self.t_name = name
def run(self):
global data_file
global db_name
global cursor
global db
while 1:
mutex_lock.acquire()
sql_ = ""
sql = u"""insert into %s values %s"""
try:
for i in range(7600):
sql_ += "," + str(tuple(next(data_file)))
# -*- coding: utf-8 -*-
from common.tools import CommonTool
import time
db = CommonTool.db_mysql_connect()
cursor = db.cursor()
#
# sql = u"""
# insert into T_MOBILE_CODE (CodeID, CodeName, CodeType) values (%s, %s, 1)
# """ % ()
sql = u"""
insert into T_MOBILE_CITY_CODE (CityCode, CityName) values ("%s", "%s")
"""
# data = ['城市编码', '牌照方编码', '设备型号', '接入方式_无线', '接入方式_有线', '播放方式_直播', '播放方式_点播']
# for i, val in enumerate(data):
# cursor.execute(sql % (i, val))
#
sql_2 = u"""
select * from T_MOBILE_SN_CODE where SnCode = '%s'
"""
inquiry_sql = u"""
select * from t_mobile_sn_code order by snid desc
"""
sql_1 = u"""
select * from t_mobile_city_code where cityname like '%河%'
"""
sql = u"""
insert into T_MOBILE_SN_CODE (SnCode, SNID) values ("%s", "%s")
"""