ZedRunner/zedrunner_store.py at main · KshitizGIT/ZedRunner · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
from mysql.connector import connect, Error
from config import Database


class ZedRunnerStore:
    def __init__(self, logger):
        self.logger = logger

    def __get_connection(self):
        connection = connect(
                host = Database.get("host"),
                user= Database.get('user'),
                database = Database.get('database'),
                password =Database.get('password')
                )
        return connection

    def horse_exists(self, horse_info):
        query_horse = "SELECT 1 from horses where horse_id = %s"%(horse_info['horse_id'])

        with self.__get_connection() as connection:
            with connection.cursor() as cursor:
                self.logger.debug(f"Query: {query_horse}")
                cursor.execute(query_horse)
                data = cursor.fetchall()
                if data:
                    self.logger.info("Horse information already exists")
                    return True
                else:
                    return False

    def store_horses(self, horse_datas):
        list_of_ids = [d[11] for d in horse_datas]
        format_strings = ','.join(['%s'] * len(list_of_ids))
        delete_horses_query = """
        DELETE FROM horses where horse_id in (%s)
        """%format_strings
        insert_horses_query = """
        INSERT INTO horses(bloodline, breed_type , breeding_counter , career_first ,
                        career_second , career_third , class , genotype, hashinfo_color ,
                        hashinfo_hexcode , hashinfo_name , horse_id , horse_type , img_url,
                        is_approved_for_racing, is_in_stud, is_on_racing_contract , last_stud_duration,
                        last_stud_timestamp , mating_price , next_breeding_date , number_of_races ,
                        owner , parents_father  , parents_mother  , rating  , super_coat  ,
                        tx , tx_date ,win_rate  )
        VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
        """
        with self.__get_connection() as connection:
            with connection.cursor() as cursor:
                self.logger.debug(f"Query: {delete_horses_query} with parameters {tuple(list_of_ids)}")
                cursor.execute(delete_horses_query,tuple(list_of_ids))
                self.logger.debug(f"Query: {insert_horses_query} with parameters {horse_datas}")
                cursor.executemany(insert_horses_query, horse_datas)
                connection.commit()

    def store_races(self, races_data):
        list_of_ids = [d[10] for d in races_data]

        format_strings = ','.join(['%s'] * len(list_of_ids))
        delete_races_query = "DELETE From races where race_id in (%s)"%format_strings

        insert_races_query = """INSERT INTO races(city, class, country_code, fee,
                          length, name, prizepool_first,
                          prizepool_second, prizepool_third,
                          prizepool_total , race_id, start_time,
                          status, weather)
        VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
        """

        with self.__get_connection() as connection:
            with connection.cursor() as cursor:
                self.logger.debug(f"Query: {delete_races_query} with parameters {tuple(list_of_ids)}")
                cursor.execute(delete_races_query, tuple(list_of_ids) )
                self.logger.debug(f"Query: {insert_races_query} with parameters {races_data}")
                cursor.executemany(insert_races_query, races_data)
                connection.commit()

    def store_races_result(self, races_data):
        list_of_race_ids = set([d[0] for d in races_data])
        format_strings = ','.join(['%s'] * len(list_of_race_ids))
        delete_races_query = "DELETE From races_results where race_id in (%s)"%format_strings

        insert_races_query = """
        INSERT INTO races_results( race_id, horse_id ,
                                   finish_time , final_position ,
                                   name , gate , owner_address ,
                                   bloodline , gender , breed_type ,
                                   gen , races , coat , win_rate ,
                                   career , hex_color , img_url ,
                                   class , stable_name )
        VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
        """
        with self.__get_connection() as connection:
            with connection.cursor() as cursor:
                self.logger.debug(f"Query: {delete_races_query} with parameters {tuple(list_of_race_ids)}")
                cursor.execute(delete_races_query, tuple(list_of_race_ids))
                self.logger.debug(f"Query: {insert_races_query} with parameters {races_data}")
                cursor.executemany(insert_races_query, races_data)
                connection.commit()

    def race_exists(self, race_info):
        query_race = "SELECT 1 from races where race_id = '%s'"%(race_info['node']['race_id'])

        with self.__get_connection() as connection:
            with connection.cursor() as cursor:
                self.logger.debug(f"Query: {query_race}")
                cursor.execute(query_race)
                data = cursor.fetchall()
                if data:
                    self.logger.info("Race information already exists.")
                    return True
                else:
                    return False

    def distinct_owner_address(self):
        query_owner_address = """select distinct owner_address from races_results"""
        with self.__get_connection() as connection:
            with connection.cursor() as cursor:
                self.logger.debug(f"Query: {query_owner_address}")
                cursor.execute(query_owner_address)
                data = cursor.fetchall()
                return data

    def stable_exists(self, horse_info):
        query_horse = "SELECT 1 from stables where horse_id = %s"%(horse_info['horse_id'])

        with self.__get_connection() as connection:
            with connection.cursor() as cursor:
                self.logger.debug(f"Query: {query_horse}")
                cursor.execute(query_horse)
                data = cursor.fetchall()
                if data:
                    self.logger.info("Stable information already exists")
                    return True
                else:
                    return False

    def store_stables(self, horse_datas):
        list_of_ids = [d[11] for d in horse_datas]
        format_strings = ','.join(['%s'] * len(list_of_ids))
        delete_horses_query = """
        DELETE FROM stables where horse_id in (%s)
        """%format_strings
        insert_horses_query = """
        INSERT INTO stables(bloodline, breed_type , breeding_counter , career_first ,
                        career_second , career_third , class , genotype, hashinfo_color ,
                        hashinfo_hexcode , hashinfo_name , horse_id , horse_type , img_url,
                        is_approved_for_racing, is_in_stud, is_on_racing_contract , last_stud_duration,
                        last_stud_timestamp , mating_price , next_breeding_date , number_of_races ,
                        owner , parents_father  , parents_mother  , rating  , super_coat  ,
                        tx , tx_date ,win_rate  )
        VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
        """
        with self.__get_connection() as connection:
            with connection.cursor() as cursor:
                self.logger.debug(f"Query: {delete_horses_query} with parameters {tuple(list_of_ids)}")
                cursor.execute(delete_horses_query,tuple(list_of_ids))
                self.logger.debug(f"Query: {insert_horses_query} with parameters {horse_datas}")
                cursor.executemany(insert_horses_query, horse_datas)
                connection.commit()