build.cogs.databasecog.DatabaseCog.db_get_common_sum() - Code Metrics - Inspection of "polishing stats code" - BonHowi/discordPogmareBot - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Branch — BonHowi (27d691)

by Bartosz

created 2021-09-29 23:54 UTC

DatabaseCog.db_get_common_sum() A

↳ Parent: build.cogs.databasecog

Complexity

Conditions

Size

Total Lines	9
Code Lines	9

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	9
dl	0
loc	9
rs	9.95
c	0
b	0
f	0
cc	2
nop	1

import os

import discord
from discord.ext import commands, tasks
from discord_slash import cog_ext, SlashContext
from modules.get_settings import get_settings
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey, \
    BigInteger, update, select, DateTime, delete
from sqlalchemy.dialects.mysql import insert
from sqlalchemy.sql import func
import cogs.cogbase as cogbase
from datetime import datetime
import pandas as pd

metadata_obj = MetaData()

member = Table('member', metadata_obj,
               Column('id', BigInteger, primary_key=True),
               Column('name', String(50), nullable=False),
               Column('display_name', String(50), nullable=False)
               )
fk_member_id = "member.id"

warn = Table('warn', metadata_obj,
             Column('id', Integer, primary_key=True),
             Column('member_id', BigInteger, ForeignKey(fk_member_id)),
             Column('reason', String(120), nullable=False),
             Column('date', DateTime, nullable=False),
             )

coords = Table('coords', metadata_obj,
               Column('id', Integer, primary_key=True),
               Column('coords', String(100), nullable=False),
               Column('monster_type', String(20), nullable=False)
               )

spots = Table('spots', metadata_obj,
              Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
              Column('legendary', Integer, default=0),
              Column('rare', Integer, default=0),
              Column('common', Integer, default=0),
              Column('event1', Integer, default=0),
              Column('event2', Integer, default=0)
              )

spots_temp = Table('spots_temp', metadata_obj,
                   Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
                   Column('legendary', Integer, default=0),
                   Column('rare', Integer, default=0),
                   Column('common', Integer, default=0),
                   Column('event1', Integer, default=0),
                   Column('event2', Integer, default=0)
                   )

spots_lege = Table('spots_lege', metadata_obj,
                   Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
                   Column('AncientLeshen', Integer, default=0),
                   Column('Archgriffin', Integer, default=0),
                   Column('CopperWyvern', Integer, default=0),
                   Column('D\'jinni', Integer, default=0),
                   Column('DungShaelmaar', Integer, default=0),
                   Column('Erynia', Integer, default=0),
                   Column('Frightener', Integer, default=0),
                   Column('GraphiteSlyzard', Integer, default=0),
                   Column('GrimHag', Integer, default=0),
                   Column('Hym', Integer, default=0),
                   Column('IceElemental', Integer, default=0),
                   Column('IceGiant', Integer, default=0),
                   Column('IceTroll', Integer, default=0),
                   Column('Katakan', Integer, default=0),
                   Column('MottledGarkain', Integer, default=0),
                   Column('Penitent', Integer, default=0),
                   Column('PlagueMaiden', Integer, default=0),
                   Column('Sandcrab', Integer, default=0),
                   Column('SilverBasilisk', Integer, default=0),
                   Column('SwampHag', Integer, default=0),
                   Column('TarryChort', Integer, default=0),
                   Column('Tormented', Integer, default=0),
                   Column('Ulfhedinn', Integer, default=0),
                   Column('UnseenElder', Integer, default=0),
                   Column('WaterDevil', Integer, default=0),
                   Column('WhiteStriga', Integer, default=0)
                   )

spots_rare = Table('spots_rare', metadata_obj,
                   Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
                   Column('Beann\'She', Integer, default=0),
                   Column('BlueForktail', Integer, default=0),
                   Column('Bruxa', Integer, default=0),
                   Column('Burier', Integer, default=0),
                   Column('Cockatrice', Integer, default=0),
                   Column('DepthLurker', Integer, default=0),
                   Column('Devourer', Integer, default=0),
                   Column('DrownedDead', Integer, default=0),
                   Column('EndregaCharger', Integer, default=0),
                   Column('EndregaWarrior', Integer, default=0),
                   Column('Farbaut', Integer, default=0),
                   Column('FireElemental', Integer, default=0),
                   Column('GarkainAlpha', Integer, default=0),
                   Column('Gernichora', Integer, default=0),
                   Column('GraySlyzard', Integer, default=0),
                   Column('GreenHarpy', Integer, default=0),
                   Column('Grimnir', Integer, default=0),
                   Column('Grottore', Integer, default=0),
                   Column('Howler', Integer, default=0),
                   Column('IgnisFatuus', Integer, default=0),
                   Column('KikimoreWarrior', Integer, default=0),
                   Column('Leshen', Integer, default=0),
                   Column('LeshenHound', Integer, default=0),
                   Column('Liho', Integer, default=0),
                   Column('Lycanthrope', Integer, default=0),
                   Column('MagmaTroll', Integer, default=0),
                   Column('NekkerShaman', Integer, default=0),
                   Column('Nightmare', Integer, default=0),
                   Column('NightSuccubus', Integer, default=0),
                   Column('Putrifier', Integer, default=0),
                   Column('RoyalFoglet', Integer, default=0),
                   Column('RoyalNekker', Integer, default=0),
                   Column('RoyalWyvern', Integer, default=0),
                   Column('RussetShaelmaar', Integer, default=0),
                   Column('Scurver', Integer, default=0),
                   Column('Shrieker', Integer, default=0),
                   Column('SpottedAlghoul', Integer, default=0),
                   Column('StoneGolem', Integer, default=0),
                   Column('Striga', Integer, default=0),
                   Column('SylvanDearg', Integer, default=0),
                   Column('Wailwraith', Integer, default=0)
                   )


class DatabaseCog(cogbase.BaseCog):
    user = get_settings("DB_U")
    password = get_settings("DB_P")
    engine = create_engine(f"mysql+mysqldb://{user}:{password}@localhost/server_database?charset=utf8mb4")
    metadata_obj.create_all(engine)
    conn = None

    def __init__(self, base):
        super().__init__(base)

        # Connect to database
        self.engine = DatabaseCog.engine
        metadata_obj.create_all(self.engine)
        self.db_update_loop.start()
        self.conn = DatabaseCog.conn

    def cog_unload(self):
        self.db_update_loop.cancel()

    # ----- BASE DATABASE OPERATIONS -----

    # Add or update member in member table
    def db_add_update_member(self, _member):
        self.conn = self.engine.connect()
        stmt = insert(member).values(
            id=_member.id, name=_member.name,
            display_name=_member.display_name)
        do_update_stmt = stmt.on_duplicate_key_update(
            name=stmt.inserted.name, display_name=stmt.inserted.display_name
        )
        self.conn.execute(do_update_stmt)
        self.conn.close()

    # Add or update spots in spots table
    def db_add_update_spots(self, spots_table, guild_member):
        self.conn = self.engine.connect()
        stmt = insert(spots_table).values(
            member_id=guild_member.id)
        do_update_stmt = stmt.on_duplicate_key_update(member_id=stmt.inserted.member_id)
        self.conn.execute(do_update_stmt)
        self.conn.close()

    # Add or refresh all guild members and spots to database
    async def db_update(self):
        self.conn = self.engine.connect()
        guild = self.bot.get_guild(self.bot.guild[0])
        self.create_log_msg(f"Refreshing member and spots tables")
        for guild_member in guild.members:
            # Member tables
            self.db_add_update_member(guild_member)
            # Spots tables
            self.db_add_update_spots(spots, guild_member)
            self.db_add_update_spots(spots_temp, guild_member)
            self.db_add_update_spots(spots_lege, guild_member)
            self.db_add_update_spots(spots_rare, guild_member)
        self.create_log_msg(f"Member and spots tables refreshed")
        self.conn.close()

    @tasks.loop(hours=12)
    async def db_update_loop(self):
        await self.db_update()
        await self.db_backup_database()

    @db_update_loop.before_loop
    async def before_db_update_loop(self):
        self.create_log_msg(f"Waiting until Bot is ready")
        await self.bot.wait_until_ready()

    # Add member to database on member join
    @commands.Cog.listener()
    async def on_member_join(self, _member):
        self.db_add_update_member(_member)
        self.db_add_update_spots(spots, _member)
        self.db_add_update_spots(spots_temp, _member)
        self.db_add_update_spots(spots_lege, _member)
        self.db_add_update_spots(spots_rare, _member)

    # Backup database
    async def db_backup_database(self):
        now = datetime.now()
        cmd = f"mysqldump -u {get_settings('DB_U')} " \
              f"--result-file=database_backup/backup-{now.strftime('%m-%d-%Y')}.sql " \
              f"-p{get_settings('DB_P')} server_database"
        os.system(cmd)
        self.create_log_msg(f"Database backed up")

    # ----- SPOTTING OPERATIONS -----

    # Update spots tables
    @classmethod
    async def db_count_spot(cls, _id: int, monster_type: str, monster_name: str):
        cls.conn = cls.engine.connect()
        cls.db_count_spot_table(spots, _id, monster_type, monster_name)
        # TODO: ...
        # cls.db_count_spot_table(spots_temp, _id, monster_type, monster_name)
        cls.conn.close()

    # TODO: make rares in events possible
    @classmethod
    def db_count_spot_table(cls, table, _id: int, monster_type: str, monster_name: str):
        cls.conn = cls.engine.connect()
        stmt = select(table.c.member_id, table.c.legendary, table.c.rare, table.c.common,
                      table.c.event1,
                      table.c.event2).where(
            table.c.member_id == _id)
        result = cls.conn.execute(stmt)
        cls.conn.close()
        counter = 0
        for nr_of_kills in result.columns(monster_type, 'legendary'):
            counter = nr_of_kills[0]
        if monster_type == "event1":
            values = cls.db_count_spot_table_event(table, _id, monster_type, counter)
        else:
            values = {f"{monster_type}": counter + 1}
        stmt = update(table).where(table.c.member_id == _id).values(values)
        cls.conn = cls.engine.connect()
        cls.conn.execute(stmt)
        cls.conn.close()

        cls.db_count_monster_spot(_id, monster_type, monster_name)

    @classmethod
    def db_count_monster_spot(cls, _id: int, monster_type: str, monster_name: str):
        bot_id = 881167775635234877
        if monster_type == "legendary":
            values_lege_member = cls.db_count_spot_table_monster(spots_lege, _id, monster_name)
            stmt = update(spots_lege).where(spots_lege.c.member_id == _id).values(values_lege_member)
            cls.conn = cls.engine.connect()
            cls.conn.execute(stmt)
            cls.conn.close()
            values_lege_total = cls.db_count_spot_table_monster(spots_lege, bot_id, monster_name)
            stmt = update(spots_lege).where(spots_lege.c.member_id == bot_id).values(values_lege_total)
            cls.conn = cls.engine.connect()
            cls.conn.execute(stmt)
            cls.conn.close()
        elif monster_type == "rare":
            values_rare_member = cls.db_count_spot_table_monster(spots_rare, _id, monster_name)
            stmt = update(spots_rare).where(spots_rare.c.member_id == _id).values(values_rare_member)
            cls.conn = cls.engine.connect()
            cls.conn.execute(stmt)
            cls.conn.close()
            values_rare_total = cls.db_count_spot_table_monster(spots_rare, bot_id, monster_name)
            stmt = update(spots_rare).where(spots_rare.c.member_id == bot_id).values(values_rare_total)
            cls.conn = cls.engine.connect()
            cls.conn.execute(stmt)
            cls.conn.close()

    @classmethod
    def db_count_spot_table_event(cls, table, _id, monster_type: str, counter: int):
        cls.conn = cls.engine.connect()
        stmt = select(table.c.member_id, table.c.legendary, table.c.rare, table.c.common,
                      table.c.event1,
                      table.c.event2).where(
            table.c.member_id == _id)
        result = cls.conn.execute(stmt)
        counter_leg = 0
        for nr_of_kills_leg in result.columns('legendary'):
            counter_leg = nr_of_kills_leg[0]
        values = {f"{monster_type}": counter + 1, "legendary": counter_leg + 1}
        cls.conn.close()
        return values

    @classmethod
    def db_count_spot_table_monster(cls, table, _id, monster_name: str):
        cls.conn = cls.engine.connect()
        stmt = select(table).where(table.c.member_id == _id)
        result = cls.conn.execute(stmt)
        counter = 0
        for nr_of_kills_leg in result.columns(f"{monster_name}"):
            counter = nr_of_kills_leg[0]
        values = {f"{monster_name}": counter + 1}
        cls.conn.close()
        return values

    # Save coords from spotting channels to database
    @classmethod
    async def db_save_coords(cls, _coords: str, _monster_type):
        cls.conn = cls.engine.connect()
        stmt = insert(coords).values(coords=_coords, monster_type=_monster_type)
        cls.conn.execute(stmt)
        cls.conn.close()

    # Clear data from spots_temp table(for events etc)
    @classmethod
    async def db_clear_spots_temp_table(cls):
        cls.conn = cls.engine.connect()
        stmt = delete(spots_temp)
        cls.conn.execute(stmt)
        cls.conn.close()

    # ----- LEADERBOARD OPERATIONS -----

    # Return total spotting stats
    @classmethod
    async def db_get_total_spots_df(cls, member_id: int, leaderboard_type: int):
        df = pd.DataFrame
        cls.conn = cls.engine.connect()
        if leaderboard_type == 1:
            stmt = select(spots_lege)
            df = pd.read_sql(stmt, cls.conn)
        elif leaderboard_type == 0:
            stmt = select(spots_rare)
            df = pd.read_sql(stmt, cls.conn)
        df = df.loc[df['member_id'] == member_id]
        cls.conn.close()
        return df

    # Return all members' spots
    @classmethod
    async def db_get_spots_df(cls):
        cls.conn = cls.engine.connect()
        stmt = select(spots.c.member_id, member.c.display_name, spots.c.legendary, spots.c.rare,
                      spots.c.common, spots.c.event1, spots.c.event2
                      ).select_from(member
                                    ).join(spots, member.c.id == spots.c.member_id)
        cls.conn.execute(stmt)
        df = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        return df

    @classmethod
    async def db_get_common_sum(cls):
        cls.conn = cls.engine.connect()
        stmt = select(func.sum(spots.c.common).label("sum"))
        result = cls.conn.execute(stmt)
        for nr_of_kills_leg in result.columns("sum"):
            sum_common = nr_of_kills_leg[0]
        cls.conn.close()
        return sum_common



    # ----- WARN OPERATIONS -----

    # Add member's warn to database
    @classmethod
    async def db_add_warn(cls, _member: int, _reason: str):
        cls.conn = cls.engine.connect()
        stmt = insert(warn).values(member_id=_member, reason=_reason, date=datetime.now())
        cls.conn.execute(stmt)
        cls.conn.close()

    # Get member's warns from database
    @classmethod
    async def db_get_warns(cls, _member: int):
        cls.conn = cls.engine.connect()
        stmt = select(warn.c.reason, warn.c.date).select_from(member).join(warn, member.c.id == warn.c.member_id).where(
            member.c.id == _member)
        result = cls.conn.execute(stmt)
        date_warn = []
        counter = 0
        for warns in result.columns("reason", "date"):
            reason_with_date = [warns[1], warns[0]]
            date_warn.append(reason_with_date)
            counter += 1
        warns_list = [': \t'.join([str(elem) for elem in sublist]) for sublist in date_warn]
        cls.conn.close()
        return warns_list, counter

    # Remove all member's warns
    @classmethod
    async def db_remove_warns(cls, _member: int):
        cls.conn = cls.engine.connect()
        stmt = delete(warn).where(warn.c.member_id == _member)
        cls.conn.execute(stmt)
        cls.conn.close()

    # ----- MEMBER OPERATIONS -----

    # Return member's spots
    @classmethod
    async def db_get_member_stats(cls, _member: int):
        cls.conn = cls.engine.connect()
        stmt = select(member.c.display_name, spots.c.legendary, spots.c.rare, spots.c.common).select_from(member).join(
            spots,
            member.c.id == spots.c.member_id).where(spots.c.member_id == _member)
        df = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        return df

    @cog_ext.cog_slash(name="changeMemberSpots", guild_ids=cogbase.GUILD_IDS,
                       description="Change member spotting stats",
                       default_permission=False,
                       permissions=cogbase.PERMISSION_ADMINS)
    async def change_member_spots(self, ctx: SlashContext, user: discord.Member, spot_type: str, number: int):
        self.conn = self.engine.connect()
        stmt = f"""UPDATE server_database.spots SET {spot_type} = {number} """ \
               f"""WHERE (member_id = {user.id});"""
        self.conn.execute(stmt)
        await ctx.send(f"{user.display_name} spots changed", hidden=True)
        self.conn.close()

    # ----- COORDS OPERATIONS -----

    # Return coords
    @classmethod
    async def db_get_coords(cls):
        cls.conn = cls.engine.connect()
        stmt = select(coords.c.id, coords.c.coords, coords.c.monster_type).select_from(coords)
        df = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        return df


def setup(bot: commands.Bot):
    bot.add_cog(DatabaseCog(bot))


1			import os
2
3			import discord
4			from discord.ext import commands, tasks
5			from discord_slash import cog_ext, SlashContext
6			from modules.get_settings import get_settings
7			from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey, \
8			BigInteger, update, select, DateTime, delete
9			from sqlalchemy.dialects.mysql import insert
10			from sqlalchemy.sql import func
11			import cogs.cogbase as cogbase
12			from datetime import datetime
13			import pandas as pd
14
15			metadata_obj = MetaData()
16
17			member = Table('member', metadata_obj,
18			Column('id', BigInteger, primary_key=True),
19			Column('name', String(50), nullable=False),
20			Column('display_name', String(50), nullable=False)
21			)
22			fk_member_id = "member.id"
23
24			warn = Table('warn', metadata_obj,
25			Column('id', Integer, primary_key=True),
26			Column('member_id', BigInteger, ForeignKey(fk_member_id)),
27			Column('reason', String(120), nullable=False),
28			Column('date', DateTime, nullable=False),
29			)
30
31			coords = Table('coords', metadata_obj,
32			Column('id', Integer, primary_key=True),
33			Column('coords', String(100), nullable=False),
34			Column('monster_type', String(20), nullable=False)
35			)
36
37			spots = Table('spots', metadata_obj,
38			Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
39			Column('legendary', Integer, default=0),
40			Column('rare', Integer, default=0),
41			Column('common', Integer, default=0),
42			Column('event1', Integer, default=0),
43			Column('event2', Integer, default=0)
44			)
45
46			spots_temp = Table('spots_temp', metadata_obj,
47			Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
48			Column('legendary', Integer, default=0),
49			Column('rare', Integer, default=0),
50			Column('common', Integer, default=0),
51			Column('event1', Integer, default=0),
52			Column('event2', Integer, default=0)
53			)
54
55			spots_lege = Table('spots_lege', metadata_obj,
56			Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
57			Column('AncientLeshen', Integer, default=0),
58			Column('Archgriffin', Integer, default=0),
59			Column('CopperWyvern', Integer, default=0),
60			Column('D\'jinni', Integer, default=0),
61			Column('DungShaelmaar', Integer, default=0),
62			Column('Erynia', Integer, default=0),
63			Column('Frightener', Integer, default=0),
64			Column('GraphiteSlyzard', Integer, default=0),
65			Column('GrimHag', Integer, default=0),
66			Column('Hym', Integer, default=0),
67			Column('IceElemental', Integer, default=0),
68			Column('IceGiant', Integer, default=0),
69			Column('IceTroll', Integer, default=0),
70			Column('Katakan', Integer, default=0),
71			Column('MottledGarkain', Integer, default=0),
72			Column('Penitent', Integer, default=0),
73			Column('PlagueMaiden', Integer, default=0),
74			Column('Sandcrab', Integer, default=0),
75			Column('SilverBasilisk', Integer, default=0),
76			Column('SwampHag', Integer, default=0),
77			Column('TarryChort', Integer, default=0),
78			Column('Tormented', Integer, default=0),
79			Column('Ulfhedinn', Integer, default=0),
80			Column('UnseenElder', Integer, default=0),
81			Column('WaterDevil', Integer, default=0),
82			Column('WhiteStriga', Integer, default=0)
83			)
84
85			spots_rare = Table('spots_rare', metadata_obj,
86			Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
87			Column('Beann\'She', Integer, default=0),
88			Column('BlueForktail', Integer, default=0),
89			Column('Bruxa', Integer, default=0),
90			Column('Burier', Integer, default=0),
91			Column('Cockatrice', Integer, default=0),
92			Column('DepthLurker', Integer, default=0),
93			Column('Devourer', Integer, default=0),
94			Column('DrownedDead', Integer, default=0),
95			Column('EndregaCharger', Integer, default=0),
96			Column('EndregaWarrior', Integer, default=0),
97			Column('Farbaut', Integer, default=0),
98			Column('FireElemental', Integer, default=0),
99			Column('GarkainAlpha', Integer, default=0),
100			Column('Gernichora', Integer, default=0),
101			Column('GraySlyzard', Integer, default=0),
102			Column('GreenHarpy', Integer, default=0),
103			Column('Grimnir', Integer, default=0),
104			Column('Grottore', Integer, default=0),
105			Column('Howler', Integer, default=0),
106			Column('IgnisFatuus', Integer, default=0),
107			Column('KikimoreWarrior', Integer, default=0),
108			Column('Leshen', Integer, default=0),
109			Column('LeshenHound', Integer, default=0),
110			Column('Liho', Integer, default=0),
111			Column('Lycanthrope', Integer, default=0),
112			Column('MagmaTroll', Integer, default=0),
113			Column('NekkerShaman', Integer, default=0),
114			Column('Nightmare', Integer, default=0),
115			Column('NightSuccubus', Integer, default=0),
116			Column('Putrifier', Integer, default=0),
117			Column('RoyalFoglet', Integer, default=0),
118			Column('RoyalNekker', Integer, default=0),
119			Column('RoyalWyvern', Integer, default=0),
120			Column('RussetShaelmaar', Integer, default=0),
121			Column('Scurver', Integer, default=0),
122			Column('Shrieker', Integer, default=0),
123			Column('SpottedAlghoul', Integer, default=0),
124			Column('StoneGolem', Integer, default=0),
125			Column('Striga', Integer, default=0),
126			Column('SylvanDearg', Integer, default=0),
127			Column('Wailwraith', Integer, default=0)
128			)
129
130
131			class DatabaseCog(cogbase.BaseCog):
132			user = get_settings("DB_U")
133			password = get_settings("DB_P")
134			engine = create_engine(f"mysql+mysqldb://{user}:{password}@localhost/server_database?charset=utf8mb4")
135			metadata_obj.create_all(engine)
136			conn = None
137
138			def __init__(self, base):
139			super().__init__(base)
140
141			# Connect to database
142			self.engine = DatabaseCog.engine
143			metadata_obj.create_all(self.engine)
144			self.db_update_loop.start()
145			self.conn = DatabaseCog.conn
146
147			def cog_unload(self):
148			self.db_update_loop.cancel()
149
150			# ----- BASE DATABASE OPERATIONS -----
151
152			# Add or update member in member table
153			def db_add_update_member(self, _member):
154			self.conn = self.engine.connect()
155			stmt = insert(member).values(
156			id=_member.id, name=_member.name,
157			display_name=_member.display_name)
158			do_update_stmt = stmt.on_duplicate_key_update(
159			name=stmt.inserted.name, display_name=stmt.inserted.display_name
160			)
161			self.conn.execute(do_update_stmt)
162			self.conn.close()
163
164			# Add or update spots in spots table
165			def db_add_update_spots(self, spots_table, guild_member):
166			self.conn = self.engine.connect()
167			stmt = insert(spots_table).values(
168			member_id=guild_member.id)
169			do_update_stmt = stmt.on_duplicate_key_update(member_id=stmt.inserted.member_id)
170			self.conn.execute(do_update_stmt)
171			self.conn.close()
172
173			# Add or refresh all guild members and spots to database
174			async def db_update(self):
175			self.conn = self.engine.connect()
176			guild = self.bot.get_guild(self.bot.guild[0])
177			self.create_log_msg(f"Refreshing member and spots tables")
178			for guild_member in guild.members:
179			# Member tables
180			self.db_add_update_member(guild_member)
181			# Spots tables
182			self.db_add_update_spots(spots, guild_member)
183			self.db_add_update_spots(spots_temp, guild_member)
184			self.db_add_update_spots(spots_lege, guild_member)
185			self.db_add_update_spots(spots_rare, guild_member)
186			self.create_log_msg(f"Member and spots tables refreshed")
187			self.conn.close()
188
189			@tasks.loop(hours=12)
190			async def db_update_loop(self):
191			await self.db_update()
192			await self.db_backup_database()
193
194			@db_update_loop.before_loop
195			async def before_db_update_loop(self):
196			self.create_log_msg(f"Waiting until Bot is ready")
197			await self.bot.wait_until_ready()
198
199			# Add member to database on member join
200			@commands.Cog.listener()
201			async def on_member_join(self, _member):
202			self.db_add_update_member(_member)
203			self.db_add_update_spots(spots, _member)
204			self.db_add_update_spots(spots_temp, _member)
205			self.db_add_update_spots(spots_lege, _member)
206			self.db_add_update_spots(spots_rare, _member)
207
208			# Backup database
209			async def db_backup_database(self):
210			now = datetime.now()
211			cmd = f"mysqldump -u {get_settings('DB_U')} " \
212			f"--result-file=database_backup/backup-{now.strftime('%m-%d-%Y')}.sql " \
213			f"-p{get_settings('DB_P')} server_database"
214			os.system(cmd)
215			self.create_log_msg(f"Database backed up")
216
217			# ----- SPOTTING OPERATIONS -----
218
219			# Update spots tables
220			@classmethod
221			async def db_count_spot(cls, _id: int, monster_type: str, monster_name: str):
222			cls.conn = cls.engine.connect()
223			cls.db_count_spot_table(spots, _id, monster_type, monster_name)
224			# TODO: ...
225			# cls.db_count_spot_table(spots_temp, _id, monster_type, monster_name)
226			cls.conn.close()
227
228			# TODO: make rares in events possible
229			@classmethod
230			def db_count_spot_table(cls, table, _id: int, monster_type: str, monster_name: str):
231			cls.conn = cls.engine.connect()
232			stmt = select(table.c.member_id, table.c.legendary, table.c.rare, table.c.common,
233			table.c.event1,
234			table.c.event2).where(
235			table.c.member_id == _id)
236			result = cls.conn.execute(stmt)
237			cls.conn.close()
238			counter = 0
239			for nr_of_kills in result.columns(monster_type, 'legendary'):
240			counter = nr_of_kills[0]
241			if monster_type == "event1":
242			values = cls.db_count_spot_table_event(table, _id, monster_type, counter)
243			else:
244			values = {f"{monster_type}": counter + 1}
245			stmt = update(table).where(table.c.member_id == _id).values(values)
246			cls.conn = cls.engine.connect()
247			cls.conn.execute(stmt)
248			cls.conn.close()
249
250			cls.db_count_monster_spot(_id, monster_type, monster_name)
251
252			@classmethod
253			def db_count_monster_spot(cls, _id: int, monster_type: str, monster_name: str):
254			bot_id = 881167775635234877
255			if monster_type == "legendary":
256			values_lege_member = cls.db_count_spot_table_monster(spots_lege, _id, monster_name)
257			stmt = update(spots_lege).where(spots_lege.c.member_id == _id).values(values_lege_member)
258			cls.conn = cls.engine.connect()
259			cls.conn.execute(stmt)
260			cls.conn.close()
261			values_lege_total = cls.db_count_spot_table_monster(spots_lege, bot_id, monster_name)
262			stmt = update(spots_lege).where(spots_lege.c.member_id == bot_id).values(values_lege_total)
263			cls.conn = cls.engine.connect()
264			cls.conn.execute(stmt)
265			cls.conn.close()
266			elif monster_type == "rare":
267			values_rare_member = cls.db_count_spot_table_monster(spots_rare, _id, monster_name)
268			stmt = update(spots_rare).where(spots_rare.c.member_id == _id).values(values_rare_member)
269			cls.conn = cls.engine.connect()
270			cls.conn.execute(stmt)
271			cls.conn.close()
272			values_rare_total = cls.db_count_spot_table_monster(spots_rare, bot_id, monster_name)
273			stmt = update(spots_rare).where(spots_rare.c.member_id == bot_id).values(values_rare_total)
274			cls.conn = cls.engine.connect()
275			cls.conn.execute(stmt)
276			cls.conn.close()
277
278			@classmethod
279			def db_count_spot_table_event(cls, table, _id, monster_type: str, counter: int):
280			cls.conn = cls.engine.connect()
281			stmt = select(table.c.member_id, table.c.legendary, table.c.rare, table.c.common,
282			table.c.event1,
283			table.c.event2).where(
284			table.c.member_id == _id)
285			result = cls.conn.execute(stmt)
286			counter_leg = 0
287			for nr_of_kills_leg in result.columns('legendary'):
288			counter_leg = nr_of_kills_leg[0]
289			values = {f"{monster_type}": counter + 1, "legendary": counter_leg + 1}
290			cls.conn.close()
291			return values
292
293			@classmethod
294			def db_count_spot_table_monster(cls, table, _id, monster_name: str):
295			cls.conn = cls.engine.connect()
296			stmt = select(table).where(table.c.member_id == _id)
297			result = cls.conn.execute(stmt)
298			counter = 0
299			for nr_of_kills_leg in result.columns(f"{monster_name}"):
300			counter = nr_of_kills_leg[0]
301			values = {f"{monster_name}": counter + 1}
302			cls.conn.close()
303			return values
304
305			# Save coords from spotting channels to database
306			@classmethod
307			async def db_save_coords(cls, _coords: str, _monster_type):
308			cls.conn = cls.engine.connect()
309			stmt = insert(coords).values(coords=_coords, monster_type=_monster_type)
310			cls.conn.execute(stmt)
311			cls.conn.close()
312
313			# Clear data from spots_temp table(for events etc)
314			@classmethod
315			async def db_clear_spots_temp_table(cls):
316			cls.conn = cls.engine.connect()
317			stmt = delete(spots_temp)
318			cls.conn.execute(stmt)
319			cls.conn.close()
320
321			# ----- LEADERBOARD OPERATIONS -----
322
323			# Return total spotting stats
324			@classmethod
325			async def db_get_total_spots_df(cls, member_id: int, leaderboard_type: int):
326			df = pd.DataFrame
327			cls.conn = cls.engine.connect()
328			if leaderboard_type == 1:
329			stmt = select(spots_lege)
330			df = pd.read_sql(stmt, cls.conn)
331			elif leaderboard_type == 0:
332			stmt = select(spots_rare)
333			df = pd.read_sql(stmt, cls.conn)
334			df = df.loc[df['member_id'] == member_id]
335			cls.conn.close()
336			return df
337
338			# Return all members' spots
339			@classmethod
340			async def db_get_spots_df(cls):
341			cls.conn = cls.engine.connect()
342			stmt = select(spots.c.member_id, member.c.display_name, spots.c.legendary, spots.c.rare,
343			spots.c.common, spots.c.event1, spots.c.event2
344			).select_from(member
345			).join(spots, member.c.id == spots.c.member_id)
346			cls.conn.execute(stmt)
347			df = pd.read_sql(stmt, cls.conn)
348			cls.conn.close()
349			return df
350
351			@classmethod
352			async def db_get_common_sum(cls):
353			cls.conn = cls.engine.connect()
354			stmt = select(func.sum(spots.c.common).label("sum"))
355			result = cls.conn.execute(stmt)
356			for nr_of_kills_leg in result.columns("sum"):
357			sum_common = nr_of_kills_leg[0]
358			cls.conn.close()
359			return sum_common
			0 ignored issues – show introduced 2021-09-29 23:56 UTC by Report Bug Copy Issue Report The variable `sum_common` does not seem to be defined in case the `for` loop on line `356` is not entered. Are you sure this can never be the case? Loading history...
360
361
362			# ----- WARN OPERATIONS -----
363
364			# Add member's warn to database
365			@classmethod
366			async def db_add_warn(cls, _member: int, _reason: str):
367			cls.conn = cls.engine.connect()
368			stmt = insert(warn).values(member_id=_member, reason=_reason, date=datetime.now())
369			cls.conn.execute(stmt)
370			cls.conn.close()
371
372			# Get member's warns from database
373			@classmethod
374			async def db_get_warns(cls, _member: int):
375			cls.conn = cls.engine.connect()
376			stmt = select(warn.c.reason, warn.c.date).select_from(member).join(warn, member.c.id == warn.c.member_id).where(
377			member.c.id == _member)
378			result = cls.conn.execute(stmt)
379			date_warn = []
380			counter = 0
381			for warns in result.columns("reason", "date"):
382			reason_with_date = [warns[1], warns[0]]
383			date_warn.append(reason_with_date)
384			counter += 1
385			warns_list = [': \t'.join([str(elem) for elem in sublist]) for sublist in date_warn]
386			cls.conn.close()
387			return warns_list, counter
388
389			# Remove all member's warns
390			@classmethod
391			async def db_remove_warns(cls, _member: int):
392			cls.conn = cls.engine.connect()
393			stmt = delete(warn).where(warn.c.member_id == _member)
394			cls.conn.execute(stmt)
395			cls.conn.close()
396
397			# ----- MEMBER OPERATIONS -----
398
399			# Return member's spots
400			@classmethod
401			async def db_get_member_stats(cls, _member: int):
402			cls.conn = cls.engine.connect()
403			stmt = select(member.c.display_name, spots.c.legendary, spots.c.rare, spots.c.common).select_from(member).join(
404			spots,
405			member.c.id == spots.c.member_id).where(spots.c.member_id == _member)
406			df = pd.read_sql(stmt, cls.conn)
407			cls.conn.close()
408			return df
409
410			@cog_ext.cog_slash(name="changeMemberSpots", guild_ids=cogbase.GUILD_IDS,
411			description="Change member spotting stats",
412			default_permission=False,
413			permissions=cogbase.PERMISSION_ADMINS)
414			async def change_member_spots(self, ctx: SlashContext, user: discord.Member, spot_type: str, number: int):
415			self.conn = self.engine.connect()
416			stmt = f"""UPDATE server_database.spots SET {spot_type} = {number} """ \
417			f"""WHERE (member_id = {user.id});"""
418			self.conn.execute(stmt)
419			await ctx.send(f"{user.display_name} spots changed", hidden=True)
420			self.conn.close()
421
422			# ----- COORDS OPERATIONS -----
423
424			# Return coords
425			@classmethod
426			async def db_get_coords(cls):
427			cls.conn = cls.engine.connect()
428			stmt = select(coords.c.id, coords.c.coords, coords.c.monster_type).select_from(coords)
429			df = pd.read_sql(stmt, cls.conn)
430			cls.conn.close()
431			return df
432
433
434			def setup(bot: commands.Bot):
435			bot.add_cog(DatabaseCog(bot))
436

BonHowi / discordPogmareBot

Branch — BonHowi (27d691)

DatabaseCog.db_get_common_sum() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like