def __init__(self, dbms: DBMS, db: str, schema: str, table: str): self.dbms = dbms self.connection = dbms.connect(db) self.cursor: pyodbc.Cursor = self.connection.cursor() dbms.tables.setdefault(db, {}).setdefault(schema, {})[table] = self self.db = db self.schema = schema self.table = table self._db = pk.Database(db) self._schema = pk.Schema(schema) self._table = pk.Table(table, self._schema, query_cls=self.dbms.sql) self.sqlite_table = pk.Table('{}_{}'.format(table, Table.count)) self.indexes = self.dbms.dialect.get_indexes(self.cursor, schema, table) self.columns, self.name_to_column = self.__get_columns() try: self.test_table(self.cursor) except Exception as ex: msg = 'Table {}.{}.{} not found:\nException:{}'.format( db, schema, table, ex) self.logger.error(msg) raise SemanticException(msg) self.filters = [] Table.count += 1
def get_stream_snapshot(columns, stream_id): x = pypika.Table('event') y = pypika.Table('event') query = pypika.Query.from_(x).select(*map(functools.partial(lambda x, y: getattr(x, y), x), columns)) query = query.left_join(y).on((y.name.like("%.Snapshot")) & (x.stream == y.stream) & (x.version<y.version)) #return query.where((x.stream == pypika.terms.PseudoColumn('%s')) & (y.id.isnull())).orderby(x.version) return query.where((x.stream == str(stream_id)) & (y.id.isnull())).orderby(x.version)
def leveling(char, exp, db): t = pypika.Table('lvl_exp') q = pypika.Query.from_(t).select('*').get_sql() res = db.select(q) char.exp += exp for r in res: if r[0] == char.lvl + 1 and r[1] <= char.exp: char.lvl += 1 char.recalculate_basics() t = pypika.Table('characters') q = pypika.Query.update(t).set(t.lvl, char.lvl).set( t.curr_exp, char.exp).where(t.id == char.id).get_sql() db.update(q)
def change_location(char, mmap, dx, dy, db): cur_x = char.curr_location.x cur_y = char.curr_location.y for l in LOCATIONS: if l.x == (cur_x + dx) and l.y == (cur_y + dy): global CUR_LOCATION CUR_LOCATION = l char.curr_location = l constants.BACKGROUND_COLOR = char.curr_location.rgb if dx > 0: char.x = 0 elif dx < 0: char.x = mmap.w - 1 if dy > 0: char.y = 0 elif dy < 0: char.y = mmap.h - 1 t = pypika.Table('characters') q = pypika.Query.update(t).set( t.curr_location, l.id).where(t.id == char.id).get_sql() db.update(q) mmap.random_fill(create_monsters_list(db)) return True return False
def __post_init__(self): import peewee from playhouse.postgres_ext import ArrayField from playhouse.postgres_ext import BinaryJSONField from playhouse.mysql_ext import JSONField as MySQL_JSONField from playhouse.sqlite_ext import JSONField as SQLite_JSONField super().__post_init__() self._primary_keys = {} for k, v in self.mapping2model.items(): if inspect.isclass(v) and issubclass(v, peewee.Model): self._primary_keys[k] = v._meta.primary_key.name for name, f in v._meta.fields.items(): if isinstance(f, ArrayField): self._table_cache[k]['array_fields'].add(f.name) elif isinstance( f, (BinaryJSONField, MySQL_JSONField, SQLite_JSONField)): self._table_cache[k]['json_fields'].add(f.name) for k, v in self.mapping2model.items(): if inspect.isclass(v) and issubclass(v, peewee.Model): self.mapping2model[k] = pypika.Table(v._meta.table_name) self._phg_cache = None
def init_locations(db_handler): t = pypika.Table('locations') q = pypika.Query.from_(t).select('*').get_sql() response = db_handler.select(q) for r in response: LOCATIONS.append(Location(r[0], r[1], r[2], r[3], r[4]))
def __init__(self, *args): self.game = pypika.Table('Games') self.query_builder = pypika.SQLLiteQuery.from_( self.game).select(*args if args else '*') self.params = { 'where_params': [], 'limit_params': [], # might add more }
def set_skills_to_hero(hero, db): t = pypika.Table('skills_on_chars') q = pypika.Query.from_(t).select('*').where(t.char_ == hero.id).get_sql() q = '''SELECT s.id, s.name, s.cost, s.dmg, s.on_self, s.lvl_impr, s.type, s.duration, s.asset FROM skills as s JOIN skills_on_chars as skc on s.id = skc.skill WHERE skc.char_=''' + str( hero.id) res = db.select(q) for r in res: hero.skills.append( Skill(r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7], make_pygame_image(r[8])))
def create_monsters_list(db): t = pypika.Table('monsters') q = pypika.Query.from_(t).select('*').get_sql() monster_res = db.select(q) monsters = [] # id name lvl base_health base_damage curr_location item asset for mr in monster_res: t = pypika.Table('items') q = pypika.Query.from_(t).select('*').where(t.id == mr[6]).get_sql() rrr = db.select(q)[0] rarity = mr[8] monst_count = (1 / rarity) * (MAP_W / TILE_SIZE) * (MAP_H / TILE_SIZE) / 5 monst_count = round(monst_count) for _ in range(monst_count): item_to_monst = Item(rrr[0], rrr[1], make_pygame_image(rrr[5]), rrr[2], rrr[4], rrr[3]) monsters.append( Monster(mr[0], mr[1], mr[3], mr[4], item_to_monst, 0, 0, make_pygame_image(mr[7]), mr[8], mr[9])) return monsters
def get_fc_folders(fc_number='19H086-1', minesite='FortHills', units=None, complete=False): """Example query to get all event folders for specific FC - Useful to check for existence of docs Parameters ---------- fc_number : str, optional default '19H086-1' minesite : str, optional default 'FortHills' Returns ------- [type] [description] """ from smseventlog import queries as qr query = qr.FCDetails() args = [ dict(vals=dict(MineSite=minesite), table=query.d), dict(vals=dict(FCNumber=fc_number)) ] if complete: args.append(dict(vals=dict(complete=1))) if not units is None: args.append(dict(ct=query.a.unit.isin(units))) # add extra table + wo column for query t = pk.Table('EventLog') query.q = query.q.left_join(t).on_field('UID') query.add_fltr_args(args) query.add_extra_cols([t.WorkOrder, t.Title, t.DateAdded]) df = query.get_df() # return df # list of event folders efs = [EventFolder.from_model(e=row) for row in df.itertuples()] return efs
def __post_init__(self): self.json_dumps_func = json_dumps_ex self._table_cache = { # 'mapping': { # 'array_fields': [], # 'json_fields': [], # } } for k, v in self.mapping2model.items(): if isinstance(v, str): self.mapping2model[k] = pypika.Table(v) self._table_cache[k] = { 'array_fields': set(), 'json_fields': set(), }
def instantiate(cls, **kwargs): self = super(Postgres, cls).instantiate(**kwargs) keys = self._Crud__field_values.keys() table = pypika.Table(cls._table) query = pypika.PostgreSQLQuery.into(table).columns( *keys ).insert( *[pypika.Parameter("%({})s".format(k)) for k in keys ] ).returning('*') with conn_pool.getconn() as conn: print(conn.autocommit) with conn.cursor() as curr: curr.execute(query.get_sql(), self._Crud__field_values) data = curr.fetchone() for field in self._schema.keys(): self._Crud__field_values[field] = data.get(field, None) setattr(self, field, data.get(field, None)) return self
def update_dt_exclusions_ma(units, rng_dates=None, dates=None): # set MA=0 (False) for units in given date range range t = pk.Table('DowntimeExclusions') cond = [t.Unit.isin(units)] if not rng_dates is None: cond.append(t.Date.between(*rng_dates)) if not dates is None: cond.append(t.Date.isin(dates)) q = pk.Query().update(t).set(t.MA, 0).where(pk.Criterion.all(cond)) sql = q.get_sql() cursor = db.cursor rows = cursor.execute(sql).rowcount cursor.commit() print(f'Rows updated: {rows}')
def __post_init__(self): import tortoise from tortoise.fields import JSONField super().__post_init__() for k, v in self.mapping2model.items(): if inspect.isclass(v) and issubclass(v, tortoise.models.Model): for name, f in v._meta.fields_map.items(): if f.SQL_TYPE.endswith('[]'): self._table_cache[k]['array_fields'].add(name) elif isinstance(f, JSONField): self._table_cache[k]['json_fields'].add(name) for k, v in self.mapping2model.items(): if inspect.isclass(v) and issubclass(v, tortoise.models.Model): self.mapping2model[k] = pypika.Table(v._meta.db_table) self._phg_cache = None self.is_pg = False
def create_update_query(self, columns, row, updates_location, mail_types): table_to_update = pypika.Table(self.name) update_query = pypika.Query() \ .update(table_to_update) \ .where(table_to_update.id == row["id"]) for key in columns: if key in row: update_query = update_query.set( key, Table.format_exit_string(row[key])) result_query = Table.get_sql(update_query) file_name = mail_types[row[ "id"]] if self.name == "mail_content" else self.name + '_' + str( row["id"]) update_file = open(updates_location + file_name + ".sql", 'w') update_file.write(result_query) update_file.close() print("File " + file_name + ".sql" + " successfully generated here " + updates_location) return result_query
def create_insert_query(self, row, columns_to_not_insert=[], tables_name_to_not_insert=["mail_content"]): table = pypika.Table(self.name) arg_list = tuple(sorted(row.keys())) insert_query = pypika.Query() \ .into(table) \ .columns(*arg_list) \ .insert( \ tuple( \ [ \ fn.Now() if isinstance(row[key], datetime) else "" if (key in columns_to_not_insert and self.name in tables_name_to_not_insert) else Table.format_exit_string(row[key]) if isinstance(row[key], str) else row[key] for key in sorted(row.keys()) \ ] \ ) \ ) return Table.get_sql(insert_query)
def create_update_query(self, columns, row, updates_location, mail_types): table_to_update = pypika.Table(self.name) update_query = pypika.Query() \ .update(table_to_update) \ .where(table_to_update.id == row["id"]) for key in columns: if key in row: update_query = update_query.set( key, Table.format_exit_string(row[key])) # WORKAROUND: H2 database does not support quoting for table names or column names. # Table.get_sql do not let us disabling quoting, we must use query.get_sql to do it (for now). result_query = update_query.get_sql(quote_char="") + ";" file_name = mail_types[row[ "id"]] if self.name == "mail_content" else self.name + '_' + str( row["id"]) update_file = open(updates_location + file_name + ".sql", 'w') update_file.write(result_query) update_file.close() print("File " + file_name + ".sql" + " successfully generated here " + updates_location) return result_query
def create_insert_query(self, row, columns_to_not_insert=[], tables_name_to_not_insert=["mail_content"]): table = pypika.Table(self.name) arg_list = tuple(sorted(row.keys())) insert_query = pypika.Query() \ .into(table) \ .columns(*arg_list) \ .insert( \ tuple( \ [ \ fn.Now() if isinstance(row[key], datetime) else "" if (key in columns_to_not_insert and self.name in tables_name_to_not_insert) else Table.format_exit_string(row[key]) if isinstance(row[key], str) else row[key] for key in sorted(row.keys()) \ ] \ ) \ ) # WORKAROUND: H2 database does not support quoting for table names or column names. # Table.get_sql do not let us disabling quoting, we must use query.get_sql to do it (for now). return insert_query.get_sql(quote_char="") + ";"
def main(): # INIT db = DBHandler() # id name lvl curr_exp base_health base_damage curr_location inventory hero_res = start_menu(db) while hero_res == -1: hero_res = start_menu(db) # INVENTORY t = pypika.Table('inventories') q = pypika.Query.from_(t).select('*').where(t.id == hero_res[7]).get_sql() # id capacity size inv_res = db.select(q)[0] heros_inv = Inventory(inv_res[0], inv_res[1], inv_res[2], db) # LOCATIONS init_locations(db) for l in LOCATIONS: if l.id == hero_res[6]: global CUR_LOCATION CUR_LOCATION = l # PYGAME pygame.init() pygame.key.set_repeat(100, 300) clock = pygame.time.Clock() screen = pygame.display.set_mode([DISPLAY_W, DISPLAY_H], pygame.DOUBLEBUF) mapp = Map(MAP_W, MAP_H) mapp.random_fill(create_monsters_list(db)) hero = Character(hero_res[0], hero_res[1], hero_res[2], hero_res[3], hero_res[4], hero_res[5], heros_inv, round(mapp.w / 2), round(mapp.h / 2), CUR_LOCATION, make_pygame_image("assets/new_hero.png"), hero_res[-1], hero_res[-2]) set_skills_to_hero(hero, db) constants.BACKGROUND_COLOR = hero.curr_location.rgb inventory_surf = pygame.Surface((DISPLAY_W - MAP_W, DISPLAY_H / 2)) inventory_surf.fill(INVENTORY_COLOR) skills_surf = pygame.Surface((DISPLAY_W - MAP_W, DISPLAY_H / 2)) skills_surf.fill(SKILLS_COLOR) in_inventory_mode = False in_skills_mode = False in_skill_direction_choosing_mode = False target_is_found = False target = None screen.fill(constants.BACKGROUND_COLOR) for i, it in mapp: if it != 0: screen.blit(it.img, (it.x * TILE_SIZE, it.y * TILE_SIZE)) if isinstance(it, Monster): health_bar = pygame.Surface( (TILE_SIZE * (it.health / it.base_health), int(TILE_SIZE / 10))) health_bar.fill(hex_to_rgb('#00FF00')) screen.blit(health_bar, (it.x * TILE_SIZE, (it.y * TILE_SIZE))) health_bar = pygame.Surface( (TILE_SIZE * (hero.curr_health / hero.get_total_health()), int(TILE_SIZE / 10))) health_bar.fill(hex_to_rgb('#00FF00')) screen.blit(health_bar, (hero.x * TILE_SIZE, (hero.y * TILE_SIZE) - 15)) mana_bar = pygame.Surface( (TILE_SIZE * (hero.curr_mana / hero.total_mana), int(TILE_SIZE / 10))) mana_bar.fill(hex_to_rgb('#0000FF')) screen.blit(mana_bar, (hero.x * TILE_SIZE, (hero.y * TILE_SIZE) - 10)) screen.blit(hero.img, (hero.x * TILE_SIZE, hero.y * TILE_SIZE)) screen.blit(inventory_surf, (MAP_W, 0)) screen.blit(skills_surf, (MAP_W, DISPLAY_H / 2)) draw_UI(screen, hero, in_inventory_mode, in_skills_mode, in_skill_direction_choosing_mode) pygame.display.update() running = True while running and hero.curr_health > 0: clock.tick(FPS) for e in pygame.event.get(): if e.type == pygame.QUIT: running = False if e.type == pygame.KEYDOWN: global CUR_ITEM global CUR_SKILL if e.key == pygame.K_ESCAPE: running = False break if e.key == pygame.K_i: if not in_inventory_mode: in_skills_mode = False in_inventory_mode = not in_inventory_mode CUR_ITEM = 0 if e.key == pygame.K_s: if not in_skills_mode: in_inventory_mode = False in_skills_mode = not in_skills_mode CUR_SKILL = 0 if in_inventory_mode: if e.key == pygame.K_UP: if CUR_ITEM != 0: CUR_ITEM -= 1 if e.key == pygame.K_DOWN: if CUR_ITEM < hero.inventory.size - 1: CUR_ITEM += 1 if e.key == pygame.K_g: index = hero.x + hero.y * mapp.w mmmap = mapp.cells[index] if isinstance(mmmap, Item): if hero.take_item(mmmap): mapp.cells[index] = 0 if e.key == pygame.K_d: hero.drop_item(hero.inventory.items[CUR_ITEM]) elif in_skills_mode: if e.key == pygame.K_UP: if CUR_SKILL != 0: CUR_SKILL -= 1 if e.key == pygame.K_DOWN: if CUR_SKILL < len(hero.skills) - 1: CUR_SKILL += 1 if e.key == pygame.K_RETURN: skill = hero.skills[CUR_SKILL] if skill.cost > hero.curr_mana: break if skill.type == 'heal': skill.use_on_target(hero) skill_animation(hero, hero.x, hero.y, screen) hero.curr_mana -= skill.cost in_skills_mode = False if skill.type == 'dmg': in_skill_direction_choosing_mode = True in_skills_mode = False elif in_skill_direction_choosing_mode: if e.key == pygame.K_UP: ind = hero.x + hero.y * mapp.w while ind >= 0: if isinstance(mapp.cells[ind], Monster): target_is_found = True target = mapp.cells[ind] break ind -= mapp.w elif e.key == pygame.K_DOWN: ind = hero.x + hero.y * mapp.w while ind <= mapp.h * mapp.w: if isinstance(mapp.cells[ind], Monster): target_is_found = True target = mapp.cells[ind] break ind += mapp.w elif e.key == pygame.K_LEFT: ind = hero.x + hero.y * mapp.w while ind >= hero.y * mapp.w: if isinstance(mapp.cells[ind], Monster): target_is_found = True target = mapp.cells[ind] break ind -= 1 elif e.key == pygame.K_RIGHT: ind = hero.x + hero.y * mapp.w while ind <= hero.y * mapp.w + mapp.w: if isinstance(mapp.cells[ind], Monster): target_is_found = True target = mapp.cells[ind] break ind += 1 else: target_is_found = False target = None in_skill_direction_choosing_mode = False if target_is_found: hero.curr_mana -= skill.cost skill_animation(hero, target.x, target.y, screen) hero.skills[CUR_SKILL].use_on_target(target) if target.health <= 0: mapp.cells[target.x + target.y * mapp.w] = target.item target.item.x = target.x target.item.y = target.y leveling(hero, target.exp, db) target_is_found = False target = None in_skill_direction_choosing_mode = False else: if e.key == pygame.K_UP: move(hero, mapp, 0, -1, db) if e.key == pygame.K_DOWN: move(hero, mapp, 0, 1, db) if e.key == pygame.K_LEFT: move(hero, mapp, -1, 0, db) if e.key == pygame.K_RIGHT: move(hero, mapp, 1, 0, db) if hero.curr_health <= 0: break hero.regenerate() screen.fill(constants.BACKGROUND_COLOR) for i, it in mapp: if it != 0: screen.blit(it.img, (it.x * TILE_SIZE, it.y * TILE_SIZE)) if isinstance(it, Monster): health_bar = pygame.Surface( (TILE_SIZE * (it.health / it.base_health), int(TILE_SIZE / 10))) health_bar.fill(hex_to_rgb('#00FF00')) screen.blit(health_bar, (it.x * TILE_SIZE, (it.y * TILE_SIZE))) health_bar = pygame.Surface( (TILE_SIZE * (hero.curr_health / hero.get_total_health()), int(TILE_SIZE / 10))) health_bar.fill(hex_to_rgb('#00FF00')) screen.blit(health_bar, (hero.x * TILE_SIZE, (hero.y * TILE_SIZE) - 15)) mana_bar = pygame.Surface( (TILE_SIZE * (hero.curr_mana / hero.total_mana), int(TILE_SIZE / 10))) mana_bar.fill(hex_to_rgb('#0000FF')) screen.blit(mana_bar, (hero.x * TILE_SIZE, (hero.y * TILE_SIZE) - 10)) screen.blit(hero.img, (hero.x * TILE_SIZE, hero.y * TILE_SIZE)) screen.blit(inventory_surf, (MAP_W, 0)) screen.blit(skills_surf, (MAP_W, DISPLAY_H / 2)) draw_UI(screen, hero, in_inventory_mode, in_skills_mode, in_skill_direction_choosing_mode) pygame.display.update() if hero.curr_health <= 0: print('Your character is totally dead :(') print('Make another one and good luck!')
def get_stream(columns, stream_id): e = pypika.Table('event') return pypika.Table(e).select(*columns).where(e.stream == str(stream_id)).orderby(e.version)
def start_menu(db): print('1. Choose existing character') print('2. Create new character') print('3. List existing characters') print('You always can input 0 to return to main menu') available_anses = [1, 2, 3] ans = input('> ') try: ans = int(ans) if ans not in available_anses: return -1 except ValueError: return -1 if ans == 0: return -1 if ans == 1: t = pypika.Table('characters') q = pypika.Query.from_(t).select('*').get_sql() res = db.select(q) print() for r in res: print(r[1] + ', lvl ' + str(r[2])) print() hero_name = input('hero name: ') if hero_name == '0': return -1 t = pypika.Table('characters') q = pypika.Query.from_(t).select('*').where( t.name == hero_name).get_sql() hero_res = db.select(q) try: hero_res = hero_res[0] except IndexError: print('no such character') return -1 t = pypika.Table('classes') q = pypika.Query.from_(t).select('name').where( t.id == hero_res[8]).get_sql() class_res = db.select(q) hero_res = list(hero_res) hero_res.append(class_res[0][0]) print('starting your adventure') return hero_res if ans == 2: print() name = input('Name: ') if name == '0': return -1 print('Available classes: ') t = pypika.Table('classes') q = pypika.Query.from_(t).select('*').get_sql() class_res = db.select(q) i = 0 for r in class_res: print( str(i + 1) + '.', r[1] + " (hp: " + str(r[2]) + ", dmg: " + str(r[3]) + ')') i += 1 class_ = input('No. of class: ') if class_ == '0': return -1 class_ = int(class_) class_ -= 1 # for right indexation t = pypika.Table('inventories') q = pypika.Query.into('inventories').columns( t.capacity).insert(3).get_sql() db.insert(q) q = pypika.Query.from_(t).select('id').orderby( 'id', order=Order.desc).get_sql() new_inv = db.select(q)[0] t = pypika.Table('characters') q = pypika.Query.into(t).columns( t.name, t.base_health, t.base_damage, t.curr_location, t.inventory, t.class_).insert(name, class_res[class_][2], class_res[class_][3], 1, new_inv[0], class_res[class_][0]).get_sql() db.insert(q) q = pypika.Query.from_(t).select("*").where(t.name == name).get_sql() res = db.select(q)[0] if class_res[class_][1] == 'Mage': t = pypika.Table('skills_on_chars') q = pypika.Query.into(t).columns(t.char_, t.skill).insert( res[0], 1).insert(res[0], 2).get_sql() db.insert(q) res = list(res) res.append(class_res[class_][1]) print('new character have been created') return res if ans == 3: t = pypika.Table('characters') q = pypika.Query.from_(t).select('*').get_sql() res = db.select(q) print() for r in res: print(r[1] + ', lvl ' + str(r[2])) print() return -1