Esempio n. 1
0
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
Esempio n. 2
0
 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())
Esempio n. 3
0
 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]
Esempio n. 4
0
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()
Esempio n. 6
0
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()
Esempio n. 7
0
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()
Esempio n. 9
0
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()
Esempio n. 10
0
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)
Esempio n. 11
0
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
Esempio n. 13
0
 def __init__(self, db={}):
     self._lazy_save_obj = []
     self._lazy_counter = 0
     #self.db = SQLite.create() # XXX
     self.db = MySQL.create(db=db)
Esempio n. 14
0
"""

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',
Esempio n. 15
0
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
Esempio n. 16
0
#!/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
Esempio n. 17
0
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()
Esempio n. 18
0
#!/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)
Esempio n. 19
0
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)