Python dbutils.DBConnection类代码示例

def add_post(title:str, content:str, created_by:int, tags:list, dbconnection:dbutils.DBConnection=None) -> int:
    content = base64.b64encode(content.encode()).decode()
    dbconnection.execute("INSERT INTO blog.posts (keywords, description, title, content, created_by, datetime) VALUES ('', '', '{}', '{}', {}, NOW())".format(title, content, created_by))
    post_id = dbconnection.execute("SELECT last_insert_id() FROM blog.posts")[0][0]
    for tag_id in tags:
        dbconnection.execute("INSERT INTO blog.post_tags VALUES ({}, {})".format(post_id, tag_id))
    return post_id

def get_comands(comand_id:(list, int), dbconnection:dbutils.DBConnection=None) -> Comand:
    if isinstance(comand_id, list) and len(comand_id) == 0: return list()

    if isinstance(comand_id, int):
        dbconnection.execute("SELECT * FROM comands WHERE comand_id='{}'".format(comand_id))
    elif isinstance(comand_id, list):
        dbconnection.execute(
            "SELECT * FROM comands WHERE comand_id IN ({})".format(','.join(list(map(str, comand_id)))))

    return list(map(lambda x: Comand(x, dbconnection), dbconnection.last()))

def create(email:str, dbconnection:dbutils.DBConnection=None) -> str:
    dbconnection.execute("SELECT activated, token FROM activation WHERE email='{}'".format(email))
    if len(dbconnection.last())>0:
        if dbconnection.last()[0][0]==1:
            raise ValueError('{} already activated'.format(email), email, dbconnection.last()[0][1])
        elif dbconnection.last()[0][0]==2:
            raise KeyError('{} already registrated'.format(email), email)
        else:
            dbconnection.execute("DELETE FROM activation WHERE email='{}'".format(email))
    token = modules.generate_token()
    dbconnection.execute("INSERT INTO activation (email, token) VALUES ('{}', '{}')".format(email, token))
    return token

def get(type_id, dbconnection:dbutils.DBConnection=None) -> GameType:
    if isinstance(type_id, str) and len(type_id.split(',')) > 0:
        type_id = splitstrlist(type_id)
        if len(type_id) == 1:
            type_id = type_id[0]

    if isinstance(type_id, list) and len(type_id) == 0: return list()

    if isinstance(type_id, int) and type_id != 0:
        dbconnection.execute("SELECT * FROM game_types WHERE type_id='{}'".format(type_id),
                             dbutils.dbfields['game_types'])
    elif isinstance(type_id, list):
        dbconnection.execute(
            "SELECT * FROM game_types WHERE type_id IN (" + ','.join(map(str, type_id)) + ")",
            dbutils.dbfields['game_types'])
    elif type_id == 0:
        dbconnection.execute("SELECT * FROM game_types", dbutils.dbfields['game_types'])

    if len(dbconnection.last()) == 0: return list()

    game_types = dbconnection.last()
    game_types = list(map(lambda x: GameType(x, dbconnection=dbconnection), game_types))

    if isinstance(type_id, int) and type_id != 0:
        return game_types[0]
    elif isinstance(type_id, list) or type_id == 0:
        return game_types

def get(sport_id, dbconnection:dbutils.DBConnection=None) -> list:
    if isinstance(sport_id, str) and len(sport_id.split(',')) > 0:
        sport_id = splitstrlist(sport_id)
        if len(sport_id) == 1:
            sport_id = sport_id[0]

    if isinstance(sport_id, list) and len(sport_id) == 0: return list()

    if isinstance(sport_id, int) and sport_id != 0:
        dbconnection.execute("SELECT * FROM sport_types WHERE sport_id='{}'".format(sport_id),
                             dbutils.dbfields['sport_types'])
    elif isinstance(sport_id, list):
        dbconnection.execute(
            "SELECT * FROM sport_types WHERE sport_id IN (" + ','.join(map(str, sport_id)) + ")",
            dbutils.dbfields['sport_types'])
    elif sport_id == 0:
        dbconnection.execute("SELECT * FROM sport_types", dbutils.dbfields['sport_types'])

    if len(dbconnection.last()) == 0: return list()

    sport_types = dbconnection.last()
    sport_types = list(map(lambda x: SportType(x), sport_types))

    if isinstance(sport_id, int) and sport_id != 0:
        return sport_types[0]
    elif isinstance(sport_id, list) or sport_id == 0:
        return sport_types

def get(city_id, dbconnection:dbutils.DBConnection=None) -> City:
    if isinstance(city_id, str) and len(city_id.split(',')) > 0:
        city_id = splitstrlist(city_id)
        if len(city_id) == 1:
            city_id = city_id[0]

    if isinstance(city_id, list) and len(city_id) == 0: return list()

    if isinstance(city_id, int) and city_id != 0:
        dbconnection.execute("SELECT * FROM cities WHERE city_id='{}'".format(city_id), dbutils.dbfields['cities'])
    elif isinstance(city_id, list):
        dbconnection.execute("SELECT * FROM cities WHERE city_id IN (" + ','.join(map(str, city_id)) + ")",
                             dbutils.dbfields['cities'])
    elif city_id == 0:
        dbconnection.execute("SELECT * FROM cities", dbutils.dbfields['cities'])

    if len(dbconnection.last()) == 0: return list()

    cities = dbconnection.last()
    cities = list(map(lambda x: City(x), cities))

    if isinstance(city_id, int) and city_id != 0:
        return cities[0]
    elif isinstance(city_id, list) or city_id == 0:
        return cities

def get(user_id:int, all:bool=False, type:int=-1, dbconnection:dbutils.DBConnection=None) -> list:
    query = "SELECT * FROM notifications WHERE datetime<NOW() AND user_id={}".format(user_id)
    options = list()
    if not all:
        options.append("`read`=0")
    if type >= 0:
        options.append("type={}".format(type))
    if len(options) > 0:
        query += ' AND '
        query += ' AND '.join(options)
    query += ' ORDER BY DATETIME DESC'
    if all:
        query += ' LIMIT 40'
    dbconnection.execute(query, dbutils.dbfields['notifications'])
    if len(dbconnection.last()) == 0: return list()

    notifications = dbconnection.last()
    notifications = list(map(lambda x: Notification(x, dbconnection=dbconnection), notifications))
    return notifications

def get(user_id, userlevel:int=-1, count:slice=slice(0, 20), dbconnection:dbutils.DBConnection=None) -> User:
    if isinstance(user_id, str) and len(user_id.split(',')) > 0:
        user_id = splitstrlist(user_id)
        if len(user_id) == 1:
            user_id = user_id[0]

    if isinstance(user_id, list) and len(user_id) == 0: return list()

    sql = "SELECT * FROM users WHERE user_id='{}'".format(user_id)

    if isinstance(user_id, int) and user_id != 0:
        sql = "SELECT * FROM users WHERE user_id='{}'".format(user_id)
    elif isinstance(user_id, list):
        sql = "SELECT * FROM users WHERE user_id IN (" + ','.join(map(str, user_id)) + ")"
    elif user_id == 0:
        sql = "SELECT * FROM users"

    if userlevel >= 0 or isinstance(userlevel, set):
        if user_id==0:
            sql += ' WHERE '
        else:
            sql += ' AND '
        if isinstance(userlevel, int):
            sql += " LOCATE('|{}|', userlevel) ".format(userlevel)
        else:
            ' AND '.join(map(lambda x: "LOCATE('|{}|', userlevel)".format(x), userlevel))

    sql += " ORDER BY played_games DESC"

    if user_id == 0:
        sql += " LIMIT {}, {}".format(count.start if count.start else 0, count.stop)

    dbconnection.execute(sql, dbutils.dbfields['users'])

    if len(dbconnection.last()) == 0: return list()

    users = dbconnection.last()
    users = list(map(lambda x: User(x, dbconnection=dbconnection), users))

    if isinstance(user_id, int) and user_id != 0:
        return users[0]
    elif isinstance(user_id, list) or user_id == 0:
        return users

def search(query:str, dbconnection:dbutils.DBConnection=None) -> list:
    query = list(map(lambda x: '%' + x + '%', query.split(' ')))
    sql = "SELECT user_id FROM users WHERE "
    if len(query) == 1:
        sql += "first_name LIKE '{first}' OR last_name LIKE '{first}'".format(first=query[0])
    else:
        sql += ("first_name LIKE '{first}' AND last_name LIKE '{last}'"
                " OR "
                "first_name LIKE '{last}' AND last_name LIKE '{first}'").format(first=query[0], last=query[1])
    users = dbconnection.execute(sql)
    return list(map(lambda x: x[0], users)) if len(users) > 0 else list()

    def __init__(self, game_finances_list:list, db:dbutils.DBConnection):
        self.games = game_finances_list
        self.games_dict = {game.game_id():game for game in self.games}
        self.sports = {sport.sport_id():sport for sport in sport_types.get(0, dbconnection=db)}
        self.real_games = db.execute("SELECT * FROM games WHERE game_id IN ({})".format(
            ', '.join(list(map(str, self.games_dict.keys())))), dbutils.dbfields['games']) if len(self.games)>0 else list()
        self.real_games_dict = {game['game_id']:game for game in self.real_games}
        summ = lambda field: sum([getattr(game, field)() for game in self.games])

        self.ideal_income = summ('ideal_income')
        self.empty = summ('empty')
        self.lost_empty = summ('lost_empty')
        self.notvisited = summ('notvisited')
        self.lost_notvisited = summ('lost_notvisited')
        self.notpayed = summ('notpayed')
        self.lost_notpayed = summ('lost_notpayed')
        self.real_income = summ('real_income')
        self.rent_charges = summ('rent_charges')
        self.additional_charges = summ('additional_charges')
        self.profit = summ('profit')
        self.responsible_salary = summ('responsible_salary')
        self.real_profit = summ('real_profit')

        self.game_by_responsible = group(lambda x: x.responsible_user_id(), self.games)

        a = db.execute("SELECT * FROM responsible_games_salary WHERE game_id IN ({})".format(
            ', '.join([str(game.game_id()) for game in self.games])), dbutils.dbfields['responsible_games_salary']) if len(self.games)>0 else list()
        self.salary = group(lambda x: x['user_id'], a)
        self.users_get = users.get

        self.sport_games = group(lambda x: x.sport_id(), self.games)

        self.sport_money = dict()
        for sport_id in self.sport_games:
            self.sport_money[sport_id] = sum([game.real_profit() for game in self.sport_games[sport_id]])

        a = db.execute("SELECT * FROM finance_balance WHERE user_id!=0 AND YEAR(date)={} AND MONTH(date)={}".format(
            self.games[-1].datetime().date().year, self.games[0].datetime().date().month
        ), dbutils.dbfields['finance_balance']) if len(self.games)>0 else list()
        self.user_salary = {i['user_id']:i['value'] for i in a}

def most_active(dbconnection:dbutils.DBConnection=None):
    data = dbconnection.execute("SELECT user_id, game_id FROM reports WHERE game_id in (SELECT game_id FROM games WHERE MONTH(datetime)=MONTH(NOW()) AND YEAR(datetime)=YEAR(NOW())) AND status=2")
    if len(data)==0:
        return None
    counted = dict()
    for tu in data:
        user_id, game_id = tu
        if user_id not in counted:
            counted[user_id] = 1
        else:
            counted[user_id] += 1
    best = max(counted, key=lambda x: counted[x])
    for user_id in counted:
        if user_id!=best and counted[user_id]==counted[best]:
            return None
    return {'user':get(best, dbconnection=dbconnection), 'count':counted[best]}

def get(amplua_id, dbconnection:dbutils.DBConnection=None) -> Amplua:
    # FIXME вроде как str передается иногда
    if isinstance(amplua_id, list) and len(amplua_id) == 0: return list()

    if isinstance(amplua_id, int) and amplua_id != 0:
        dbconnection.execute("SELECT * FROM ampluas WHERE amplua_id='{}'".format(amplua_id),
                             dbutils.dbfields['ampluas'])
    elif isinstance(amplua_id, list):
        dbconnection.execute("SELECT * FROM ampluas WHERE amplua_id IN (" + ','.join(map(str, amplua_id)) + ")",
                             dbutils.dbfields['ampluas'])
    elif amplua_id == 0:
        dbconnection.execute("SELECT * FROM ampluas", dbutils.dbfields['ampluas'])

    if len(dbconnection.last()) == 0: return list()

    ampluas = dbconnection.last()
    ampluas = list(map(lambda x: Amplua(x, dbconnection=dbconnection), ampluas))

    if isinstance(amplua_id, int) and amplua_id != 0:
        return ampluas[0]
    elif isinstance(amplua_id, list) or amplua_id == 0:
        return ampluas

def calc_game(game_id:int, dbconnection:dbutils.DBConnection=None) -> dict:
    game = dbconnection.execute("SELECT * FROM games WHERE game_id={}".format(game_id), dbutils.dbfields['games'])[0]
    game = Game(game, dbconnection=dbconnection)
    reports = dbconnection.execute("SELECT * FROM reports WHERE game_id={}".format(game_id), dbutils.dbfields['reports'])
    additional_charges = dbconnection.execute("SELECT * FROM additional_charges WHERE game_id={}".format(game_id), dbutils.dbfields['additional_charges'])

    finances = dict()
    finances['game_id'] = game_id
    finances['datetime'] = game.datetime()
    finances['capacity'] = game.capacity()
    finances['cost'] = game.cost()
    finances['sport_id'] = game.sport_type()
    finances['responsible_user_id'] = game.responsible_user_id()
    finances['created_by'] = game.created_by()

    finances['visited'] = len(list(filter(lambda x: x['status']!=0, reports)))
    finances['empty'] = finances['capacity']-finances['visited']
    if finances['empty']<0: finances['empty']=0
    finances['lost_empty'] = finances['empty']*finances['cost']
    finances['notvisited'] = len(list(filter(lambda x: x['status']==0, reports)))
    finances['lost_notvisited'] = finances['notvisited']*finances['cost']
    finances['notpayed'] = len(list(filter(lambda x: x['status']==1, reports)))
    finances['lost_notpayed'] = finances['notpayed']*finances['cost']

    finances['playedpayed'] = len(list(filter(lambda x: x['status']==2, reports)))
    finances['real_income'] = finances['playedpayed']*finances['cost']
    finances['ideal_income'] = finances['cost']*finances['capacity'] if finances['capacity']>0 else finances['real_income']
    finances['rent_charges'] = game.court_id(True).cost()*(game.duration()/60)
    finances['additional_charges'] = sum([i['cost'] for i in additional_charges])

    finances['profit'] = finances['real_income']-finances['additional_charges']-finances['rent_charges']

    dbconnection.execute("SELECT percents FROM responsible_salary WHERE user_id={}".format(game.responsible_user_id()))
    finances['responsible_salary'] = 0
    if len(dbconnection.last())>0:
        finances['responsible_salary'] = round((finances['profit'])*(dbconnection.last()[0][0]/100))
    finances['profit'] -= finances['responsible_salary']

    finances['real_profit'] = finances['profit'] + finances['rent_charges']

    return finances

def get_moderating_posts(dbconnection:dbutils.DBConnection=None) -> list:
    dbconnection.execute("SELECT * FROM blog.posts WHERE status=0", _fields['posts'])
    if len(dbconnection.last())==0: return list()
    return list(map(lambda x: BlogPost(x, dbconnection=dbconnection), dbconnection.last()))

def get_posts_by_tags(tag_ids:list, dbconnection:dbutils.DBConnection=None) -> list:
    dbconnection.execute("SELECT * FROM blog.posts WHERE post_id IN (SELECT post_id FROM blog.post_tags WHERE tag_id IN ({})) AND status>=0".format(', '.join(tag_ids)), _fields['posts'])
    if len(dbconnection.last())==0: return list()
    return list(map(lambda x: Tag(x), dbconnection.last()))