class dbHandler():
def __init__(self):
self.mysql = MySQL()
self.influx = InfluxDB()
self.query = Query()
cfg = getConfig()
self.mysql.init_cfg(cfg)
self.influx.init_cfg(cfg)
self._con = self.mysql.get_connection()
self.query.init_db(self._con, self.influx.get_client())
def getQuery(self):
return self.query
def close(self):
return self.mysql.close()
def getallMAC(self, table):
infQuery = self.query.getInfQuery()
result = infQuery('SELECT distinct("MAC") as MAC FROM "{}";'.format(
escape(table)))
return result.raw
def getallSensor(self, table, mac):
infQuery = self.query.getInfQuery()
param = {"mac": mac}
result = infQuery('SELECT s,topic FROM "{}" WHERE MAC=$mac;'.format(
escape(table)),
bind_params=param)
return result.raw
def getallTopic(self, table, mac):
infQuery = self.query.getInfQuery()
result = infQuery('SELECT topic,MAC,status FROM "{}"'.format(
escape(table)))
for sensor in result.raw["series"][0]["values"]:
smac = sensor[2]
topic = sensor[1]
if (smac == mac):
return ['topic', topic, 'MAC', smac]
def getLastCT(self, sid, _type="ct"):
infQuery = self.query.getInfQuery()
param = {"s": sid}
result = infQuery('SELECT a FROM "{}" WHERE s=$s LIMIT 1'.format(
escape(_type)),
bind_params=param)
return result.raw
def getLastPIR(self, mac, _type="pir"):
infQuery = self.query.getInfQuery()
param = {"mac": mac}
result = infQuery(
'SELECT status FROM "{}" WHERE MAC=$mac LIMIT 1'.format(
escape(_type)),
bind_params=param)
return result.raw
def __init__(self):
self.mysql = MySQL()
self.influx = InfluxDB()
self.query = Query()
cfg = getConfig()
self.mysql.init_cfg(cfg)
self.influx.init_cfg(cfg)
self._con = self.mysql.get_connection()
self.query.init_db(self._con, self.influx.get_client())
def __init__(self):
self.mysql = MySQL()
self.mongo = MongoDB()
self.query = Query()
cfg = getConfig()
self.mysql.init_cfg(cfg)
self.mongo.init_cfg(cfg)
self._con = self.mysql.get_connection()
self._client = self.mongo.get_client()
self.query.init_db(self._con, self._client)
self._db = self._client[cfg.MONGODB_COLLECTION]
def run():
conn = MySQL.db()
cur = conn.cursor(cursor=cymysql.cursors.DictCursor)
cur.execute("SELECT * FROM user where switch=1 and enable=1")
today = datetime.datetime.now()
days = calendar.monthrange(today.year, today.month)[1]
users = cur.fetchall()
cur.close()
conn.close()
for user in users:
print datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),"*****************uid:",str(user['uid'])
service = get_service(user['uid'],[1,2])
try:
#如果当前用户包含包月服务
if service and (date_test(service['start_date'],service['end_date'],today,days)):
# 重新初始化包月流量
up_month_user(service,user['service_type'])
#包月服务不存在或者服务存在且为固定流量包月用完的需要更新为固定流量
elif not service or (service and user['month_u']+user['month_d']>=user['month_flows'] and user['service_type']==1):
service = get_service(user['uid'],[3])
if service and user['service_type'] in [1,2]:
up_flow_user(3,user['uid'])
else :
up_flow_user(0,user['uid'])
except Exception,ex:
print datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),"uid:",str(user['uid']),Exception,":",ex
time.sleep(5)
def up_user(service, type):
conn = MySQL.db()
cur = conn.cursor()
if type in [0, 3]:
cur.execute(
"""
update user
set transfer_enable= transfer_enable-u-d,
u=0,
d=0,
month_flows=%s,
service_type=%s
where uid=%s
""",
[service["transfer"], service["service_type"], service["uid"]],
)
elif type in [1, 2]:
cur.execute(
"""
update user
u=0,
d=0,
month_flows=%s,
service_type=%s
where uid=%s
""",
[service["transfer"], service["service_type"], service["uid"]],
)
conn.commit()
cur.close()
conn.close()
def ext_jd(browser, cf):
"""
browser :webdriver
"""
# 读取数据库配置信息
db_host = cf.get("db", "db_host")
db_port = cf.getint("db", "db_port")
db_user = cf.get("db", "db_user")
db_pass = cf.get("db", "db_pass")
db_db = cf.get("db", "db_db")
db_charset = cf.get("db", "db_charset")
category = cf.get("extract", "category")
issue = cf.get("extract", "issue")
comment_num = cf.getint("extract", "comment_num")
commo_num = cf.getint("extract", "commo_num")
# 读取webdriver参数
global implicitly_wait_time
implicitly_wait_time = cf.getint("browser", "implicitly_wait_time")
browser.implicitly_wait(implicitly_wait_time)
time.sleep(1)
browser.find_element_by_link_text(category).click()
browser.implicitly_wait(implicitly_wait_time)
browser.find_element_by_link_text(issue).click()
items = browser.find_elements_by_xpath("//a[@class='J_ping']")
browser.implicitly_wait(implicitly_wait_time)
mysql = MySQL(host=db_host, port=db_port, user=db_user, passwd=db_pass, db=db_db, charset=db_charset)
for i in range(0, min(len(items), commo_num)):
# 选择第几个商品
time.sleep(1)
browser.find_element_by_xpath("//div[@id='list']//li[" + str(i + 1) + "]//a").click()
browser.implicitly_wait(implicitly_wait_time)
time.sleep(1)
browser.find_element_by_id("btnAssess").click()
# browser.find_element_by_xpath("//a[@id='btnAssess']").click()
browser.implicitly_wait(implicitly_wait_time)
time.sleep(1)
for i in range(0, 3):
time.sleep(0.5)
extComment(browser, i, mysql, comment_num)
browser.find_element_by_id("m_common_header_goback").click()
browser.implicitly_wait(implicitly_wait_time)
browser.find_element_by_id("m_common_header_goback").click()
mysql.close()
def up_flow_user(uid,type):
conn = MySQL.db()
cur = conn.cursor()
cur.execute("""
update user set
service_type=%s
where uid=%s
""",[type,uid])
conn.commit()
cur.close()
conn.close()
def run():
conn = MySQL.db()
cur = conn.cursor(cursor=cymysql.cursors.DictCursor)
cur.execute("SELECT * FROM user where switch=1 and enable=1")
today = datetime.datetime.now()
days = calendar.monthrange(today.year, today.month)[1]
for user in cur.fetchall():
service = get_service(user["uid"], [1, 2])
if service and date_test(service["start_date"], service["end_date"], today, days):
up_user(service, user["service_type"])
else:
service = get_service(user["uid"], [3])
cur.close()
conn.close()
def up_month_user(service,type):
conn = MySQL.db()
cur = conn.cursor()
cur.execute("""
update user
set
month_u=0,
month_d=0,
month_flows=%s,
service_type=%s
where uid=%s
""",[service['transfer'],service['service_type'],service['uid']])
conn.commit()
cur.close()
conn.close()
def change():
with MySQL(pwd=PASSWORD) as DB:
DB.query('select id from auth_user;')
ids = DB.fetch('all')
expire = datetime.datetime.now() + datetime.timedelta(days=1)
for i in ids:
temp = id_generator()
sql = 'insert into Temp_Pass (temp,expiration,user_id) VALUE ("{}","{}","{}") ON DUPLICATE KEY UPDATE temp="{}",expiration="{}"'
DB.query(sql.format(temp, expire, i[0], temp, expire))
DB.query(
'select line_group_id from Company_Admin where id={}'.format(
i[0]))
group = DB.fetch()
DB.query('select temp from Temp_Pass where user_id={}'.format(
i[0]))
temp = DB.fetch()[0]
line(group, temp, expire)
def get_service(uid,types):
conn = MySQL.db()
today = time.strftime("%Y-%m-%d")
cur = conn.cursor(cursor=cymysql.cursors.DictCursor)
cur.execute("""
SELECT * FROM
t_user_service
where state=1
and uid=%s
and service_type in %s
and end_date>=%s
and start_date<=%s
order by end_date ASC limit 1
""",[uid,types,today,today])
data = cur.fetchone()
cur.close()
conn.close()
return data
from flask import escape
from config import Config, ProductionConfig, DevelopmentConfig
from db import MySQL, MongoDB, Query
mysql = MySQL()
mongoDB = MongoDB()
query = Query()
def html_escape(inp):
return str(escape(inp))
def toHourandMin(timeDel):
secs = timeDel.total_seconds()
hours = int(secs / 3600)
minutes = int(secs / 60) % 60
return "%02d:%02d" % (hours, minutes)
def getConfig():
cfg = ProductionConfig
if Config.ENABLE_DEV:
cfg = DevelopmentConfig
return cfg
def __init__(self, db={}):
self._lazy_save_obj = []
self._lazy_counter = 0
#self.db = SQLite.create() # XXX
self.db = MySQL.create(db=db)
"""
import os
from db import MySQL
# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATE_DIR = os.path.join(BASE_DIR, 'templates')
# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/1.11/howto/deployment/checklist/
# SECURITY WARNING: keep the secret key used in production secret!
from dbmon.settings_s import *
DB = MySQL(pwd=PASSWORD)
# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = True
ALLOWED_HOSTS = ['*']
# Application definition
INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
class dbHandler():
def __init__(self):
self.mysql = MySQL()
self.mongo = MongoDB()
self.query = Query()
cfg = getConfig()
self.mysql.init_cfg(cfg)
self.mongo.init_cfg(cfg)
self._con = self.mysql.get_connection()
self._client = self.mongo.get_client()
self.query.init_db(self._con, self._client)
self._db = self._client[cfg.MONGODB_COLLECTION]
def getQuery(self):
return self.query
def getallMAC(self, table): #/
allmac = self._db[table].find().distinct("message.MAC")
return allmac
def getallSensor(self, table, mac): #/
result = self._db[table].find(
{"$and": [{
"message.MAC": mac
}, {
"message.s": {
"$exists": True
}
}]})
alltopic = result.distinct("topic")
allnum = result.distinct("message.s")
res = []
for s in allnum:
for topic in alltopic:
res.append({"s": s, "topic": topic})
return res
def getallTopic(self, table, mac): #/
result = self._db[table].find_one(
{"$or": [{
"message.MAC": mac
}, {
"message.s": mac
}]})
return ['topic', result["topic"], 'MAC', result["message"]["MAC"]]
def getLastVAL(self, sid, mac, _type="ct"):
createdAt = 0
res = None
query = {"message.MAC": mac}
if (_type.split("_") == "dm"):
query["message.s"] = sid
rawCT = self._db[_type].find(query).sort("created_at", -1).limit(1)
for CT in rawCT:
if (CT["created_at"] > createdAt):
res = CT
createdAt = CT["created_at"]
if (_type.split("_")[0] == "dm"):
return (res["message"]["AL1"] + res["message"]["AL2"] +
res["message"]["AL3"]) / 3
return res["message"]["a"]
def getLastPIR(self, mac, _type="pir"):
createdAt = 0
res = {}
for t in self.getTable({"sensor_type": _type}):
rawPIR = self._db[t].find({
"message.MAC": mac
}).sort("created_at", -1).limit(1)
for PIR in rawPIR:
if (PIR["created_at"] > createdAt):
res = PIR
createdAt = PIR["created_at"]
return res
def getTable(self, devFind={}): #/
res = []
for _type in self._db["iot_type"].find(devFind):
name = _type["sensor_type"]
if (_type["device_type"] != ''):
name = name + "_" + _type["device_type"]
res.append(name)
return res
#!/usr/bin/python3
import urllib.request
import ssl
import time
# import MySQLdb
from bs4 import BeautifulSoup as bs
from selenium import webdriver
from pyvirtualdisplay import Display
from db import MySQL
database = MySQL()
ssl._create_default_https_context = ssl._create_unverified_context
#display = Display(visible=0, size=(800, 800))
#display.start()
path = "/usr/local/bin/chromedriver"
chrome_options = webdriver.ChromeOptions()
chrome_options.add_argument('headless')
chrome_options.add_argument('no-sandbox')
myDriver = webdriver.Chrome(path,chrome_options=chrome_options)
myDriver.get("https://www.quanmin.tv/game/all")
myDriver.implicitly_wait(10)
time.sleep(10)
#房间名 房间号 主播名 主播号 人气值 印象标签 房间链接 房间封面
#while True:
soup = bs(myDriver.page_source, "lxml")
#names = soup.find_all("span", {"class": "common_w-card_views-num"})
i = 1
def create_taobao_table(filename, conn):
"""docstring for create_taobao_table"""
taobao = Taobao("taobao")
taobao.getrecords(filename)
taobao.create_and_insert(conn)
def create_tables(conn):
create_url_sql = """
create table IF NOT EXISTS url(
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
url text NOT NULL,
content MEDIUMTEXT,
title text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
"""
create_url_index_sql = """
create index urlidx on url(url(255))
"""
conn.execute(create_url_sql)
conn.execute(create_url_index_sql)
conn.commit()
if __name__ == "__main__":
config = yaml.load(open("config/database.yml"))
db = DB(config["development"])
# create_taobao_table(sys.argv[1], db)
create_tables(db)
db.close()
#!/usr/bin/python3
import urllib.request
import ssl
import time
# import MySQLdb
from bs4 import BeautifulSoup as bs
from selenium import webdriver
from pyvirtualdisplay import Display
from db import MySQL
database = MySQL()
ssl._create_default_https_context = ssl._create_unverified_context
#display = Display(visible=0, size=(800, 800))
#display.start()
path = "/usr/local/bin/chromedriver"
#path = "C:\\chromedriver\\chromedriver.exe"
chrome_options = webdriver.ChromeOptions()
chrome_options.add_argument('headless')
chrome_options.add_argument('no-sandbox')
myDriver = webdriver.Chrome(path, chrome_options=chrome_options)
myDriver.get("http://top.youku.com/rank/detail/?m=99&type=2")
myDriver.implicitly_wait(15)
time.sleep(15)
#房间名 房间号 主播名 主播号 人气值 印象标签 房间链接 房间封面
#while True:
soup = bs(myDriver.page_source, "lxml")
#names = soup.find_all("span", {"class": "common_w-card_views-num"})
div = soup.find("div", {"class": "exp-left"})
#print(div)
def main():
db = DB(config)
uids = map(lambda x: x[0],
db.fetchall("select uid from users_have_records"))
for uid in uids:
update_session(db, uid)
