def verify_database_schema(db): if not Versions.table_exists(): db.create_tables([Versions]) if ScannedLocation.table_exists(): # Versions table didn't exist, but there were tables. This must mean the user # is coming from a database that existed before we started tracking the schema # version. Perform a full upgrade. InsertQuery(Versions, { Versions.key: 'schema_version', Versions.val: 0 }).execute() database_migrate(db, 0) else: InsertQuery(Versions, { Versions.key: 'schema_version', Versions.val: db_schema_version }).execute() else: db_ver = Versions.get(Versions.key == 'schema_version').val if db_ver < db_schema_version: database_migrate(db, db_ver) elif db_ver > db_schema_version: log.error( "Your database version (%i) appears to be newer than the code supports (%i).", db_ver, db_schema_version) log.error( "Please upgrade your code base or drop all tables in your database." ) sys.exit(1)
def test_insert(self): iq = InsertQuery(Blog, title='a') self.assertEqual(iq.sql(), ('INSERT INTO blog (title) VALUES (?)', ['a'])) self.assertEqual(iq.execute(), 1) iq = InsertQuery(Blog, title='b') self.assertEqual(iq.sql(), ('INSERT INTO blog (title) VALUES (?)', ['b'])) self.assertEqual(iq.execute(), 2)
def test_update(self): iq = InsertQuery(Blog, title='a').execute() uq = UpdateQuery(Blog, title='A').where(id=1) self.assertEqual(uq.sql(), ('UPDATE blog SET title=? WHERE id = ?', ['A', 1])) self.assertEqual(uq.execute(), 1) iq2 = InsertQuery(Blog, title='b').execute() uq = UpdateQuery(Blog, title='B').where(id=2) self.assertEqual(uq.sql(), ('UPDATE blog SET title=? WHERE id = ?', ['B', 2])) self.assertEqual(uq.execute(), 1) sq = SelectQuery(Blog).order_by('id') self.assertEqual([x.title for x in sq], ['A', 'B'])
def init_database(args, app): log.info('Connecting to MySQL database on %s:%i...', args.db_host, args.db_port) db = MyRetryDB( args.db_name, user=args.db_user, password=args.db_pass, host=args.db_host, port=args.db_port, max_connections=args.db_max_connections, stale_timeout=300, charset='utf8mb4') app.config['DATABASE'] = db flaskDb.init_app(app) db.connect() if not ProxyPool.table_exists(): create_tables(db) InsertQuery(Version, {Version.key: 'schema_version', Version.val: db_schema_version}).execute() old_schema_version = db_schema_version elif not Version.table_exists(): old_schema_version = 1 else: old_schema_version = Version.get(Version.key == 'schema_version').val if old_schema_version < db_schema_version: migrate_database(db, old_schema_version) # Last, fix database encoding verify_table_encoding(args, db) log.info('MySQL database ready.') return db
def init_database(app): log.info('Connecting to MySQL database on %s:%i...', cfg_get('db_host'), cfg_get('db_port')) db = MyRetryDB( cfg_get('db_name'), user=cfg_get('db_user'), password=cfg_get('db_pass'), host=cfg_get('db_host'), port=cfg_get('db_port'), max_connections=cfg_get('db_max_connections'), stale_timeout=300, charset='utf8mb4') app.config['DATABASE'] = db flaskDb.init_app(app) db.connect() if not Account.table_exists(): create_tables(db) InsertQuery(Version, {Version.key: 'schema_version', Version.val: db_schema_version}).execute() old_schema_version = db_schema_version elif not Version.table_exists(): old_schema_version = 1 else: old_schema_version = Version.get(Version.key == 'schema_version').val if old_schema_version < db_schema_version: migrate_database(db, old_schema_version) # Last, fix database encoding verify_table_encoding(db) return db
def add_egg_tracking(id, monster, distance): InsertQuery(raid, { raid.human_id: id, raid.pokemon_id: monster, raid.egg: 1, raid.distance: distance}).execute() db.close()
def migrate_database(db, old_ver): log.info('Detected database version {}, updating to {}...'.format(old_ver, db_schema_version)) migrator = MySQLMigrator(db) if old_ver < 2: migrate_varchar_columns(db, Account.username, Account.password, Account.email, Account.system_id, Account.tutorial_state) migrate_varchar_columns(db, Event.entity_id, Event.description) db.create_table(Version) InsertQuery(Version, {Version.key: 'schema_version', Version.val: 1}).execute() migrate( migrator.add_column('account', 'lures', SmallIntegerField(null=True)), migrator.rename_column('event', 'type', 'entity_type') ) if old_ver < 3: migrate( migrator.add_column('account', 'assigned_at', DateTimeField(index=True, null=True)) ) if old_ver <4: migrate( migrator.add_column('account','reach_lvl30_datetime', DateTimeField(index=True,null=True)) ) Version.update(val=db_schema_version).where( Version.key == 'schema_version').execute() log.info("Done migrating database.")
def process_item(self, item, spider): print(item.__dict__.get('_values')) try: iq = InsertQuery(Patents, item.__dict__.get('_values')) iq.execute() except Exception as e: print(e) return item
def add_tracking(id, monster, distance, iv): InsertQuery(monsters, { monsters.human_id: id, monsters.pokemon_id: monster, monsters.distance: distance, monsters.min_iv: iv }).execute() db.close()
def cache_insert(id, time, col): try: if args.debug: log.debug('updating {} cache'.format(col)) InsertQuery(cache, {cache.id: id, col: time}).execute() except IntegrityError: cache.update(**{col: time}).where(cache.id == id).execute() db.close()
def spawn_geocoding(id, addr, lat, lon): InsertQuery(geocoded, { geocoded.id: id, geocoded.type: 'spawn', geocoded.address: addr, geocoded.latitude: lat, geocoded.longitude: lon }).execute() db.close()
def test_delete(self): InsertQuery(Blog, title='a').execute() InsertQuery(Blog, title='b').execute() InsertQuery(Blog, title='c').execute() dq = DeleteQuery(Blog).where(title='b') self.assertEqual(dq.sql(), ('DELETE FROM blog WHERE title = ?', ['b'])) self.assertEqual(dq.execute(), 1) sq = SelectQuery(Blog).order_by('id') self.assertEqual([x.title for x in sq], ['a', 'c']) dq = DeleteQuery(Blog) self.assertEqual(dq.sql(), ('DELETE FROM blog', [])) self.assertEqual(dq.execute(), 2) sq = SelectQuery(Blog).order_by('id') self.assertEqual([x.title for x in sq], [])
def bulk_upsert(cls, data): num_rows = len(data.values()) i = 0 step = 100 while i < num_rows: log.debug("Inserting items {} to {}".format(i, min(i + step, num_rows))) InsertQuery(cls, rows=data.values()[i:min(i + step, num_rows)]).upsert().execute() i += step
def add_special(id, specialty, distance): InsertQuery( monsters, { monsters.human_id: id, monsters.pokemon_id: 9000, monsters.distance: distance, monsters.min_iv: 100, monsters.special: specialty }).execute() db.close()
def update_weather_path(id, path): geocoded.update(weather_path=path).where(geocoded.id == id).execute() try: InsertQuery(weather, { weather.area: path, weather.updated: 0 }).execute() except IntegrityError: if args.debug: log.debug('tried to update weather where it already exists') db.close()
def verify_database_schema(db): if not Versions.table_exists(): db.create_tables([Versions]) InsertQuery(Versions, { Versions.key: 'schema_version', Versions.val: db_schema_version }).execute() else: db_ver = Versions.get(Versions.key == 'schema_version').val database_migrate(db, db_ver)
def save_geocoding(id, team, address, gym_name, description, url, lat, lon): InsertQuery(geocoded, { geocoded.id: id, geocoded.type: 'raid', geocoded.team: team, geocoded.address: address, geocoded.gym_name: gym_name, geocoded.description: description, geocoded.url: url, geocoded.latitude: lat, geocoded.longitude: lon }).execute() db.close()
def bulk_upsert(cls, data): num_rows = len(data.values()) i = 0 step = 120 while i < num_rows: log.debug('Inserting items %d to %d', i, min(i + step, num_rows)) try: InsertQuery(cls, rows=data.values()[i:min(i + step, num_rows)]).upsert().execute() except Exception as e: log.warning('%s... Retrying', e) continue i += step
async def _insert_with_loop(self): id_list = [] last_id = None return_id_list = self._return_id_list for row in self._rows: last_id = await (InsertQuery(self.model_class, row) .upsert(self._upsert) .execute()) if return_id_list: id_list.append(last_id) if return_id_list: return id_list else: return last_id
def bulk_upsert_old(cls, data, db): num_rows = len(data.values()) i = 0 step = 250 max_fails = 3 fails = 0 with db.atomic(): while i < num_rows: log.debug('Inserting items %d to %d.', i, min(i + step, num_rows)) try: # Turn off FOREIGN_KEY_CHECKS on MySQL, because apparently it's # unable to recognize strings to update unicode keys for # foreign key fields, thus giving lots of foreign key # constraint errors. db.execute_sql('SET FOREIGN_KEY_CHECKS=0;') # Use peewee's own implementation of the insert_many() method. InsertQuery(cls, rows=data.values() [i:min(i + step, num_rows)]).upsert().execute() db.execute_sql('SET FOREIGN_KEY_CHECKS=1;') except Exception as e: # If there is a DB table constraint error, dump the data and # don't retry. # # Unrecoverable error strings: unrecoverable = [ 'constraint', 'has no attribute', 'peewee.IntegerField object at' ] has_unrecoverable = filter(lambda x: x in str(e), unrecoverable) if has_unrecoverable: log.warning('%s. Data is:', repr(e)) log.warning(data.items()) else: log.warning('%s... Retrying...', repr(e)) log.warning('%s', pprint.pformat(data)) time.sleep(1) fails += 1 if fails > max_fails: return continue i += step
def verify_database_schema(db): if not Versions.table_exists(): db.create_tables([Versions]) InsertQuery(Versions, { Versions.key: 'schema_version', Versions.val: db_schema_version }).execute() else: db_ver = Versions.get(Versions.key == 'schema_version').val if db_ver < db_schema_version: database_migrate(db, db_ver) elif db_ver > db_schema_version: log.error( 'Your database version (%i) appears to be newer than ' 'the code supports (%i).', db_ver, db_schema_version) sys.exit(1)
def verify_database_schema(): log.info('Connecting to MySQL database on {}:{}'.format(args.dbhost, args.dbport)) try: if not schema_version.table_exists(): create_tables() if humans.table_exists(): InsertQuery(schema_version, { schema_version.key: 'schema_version', schema_version.val: 1}).execute() if not cache.table_exists(): schema_version.update(val=2).where( schema_version.key == 'schema_version').execute() db.create_table(cache, safe=True) db.close() except OperationalError as e: log.critical("MySQL unhappy [ERROR]:% d: % s\n" % ( e.args[0], e.args[1])) exit(1)
def bulk_upsert(cls, data): num_rows = len(data.values()) i = 0 if args.db_type == 'mysql': step = 120 else: # SQLite has a default max number of parameters of 999, # so we need to limit how many rows we insert for it. step = 50 while i < num_rows: log.debug('Inserting items %d to %d', i, min(i + step, num_rows)) try: InsertQuery( cls, rows=data.values()[i:min(i + step, num_rows)]).upsert().execute() except Exception as e: log.warning('%s... Retrying', e) continue i += step
def verify_database_schema(): log.info('Connecting to MySQL database on {}:{}'.format( args.dbhost, args.dbport)) try: migrator = MySQLMigrator(db) if not schema_version.table_exists(): log.info('Creting database tables.') create_tables() if humans.table_exists(): InsertQuery(schema_version, { schema_version.key: 'schema_version', schema_version.val: 1 }).execute() if not cache.table_exists(): log.info('Upgrading to Db schema 2.') schema_version.update(val=2).where( schema_version.key == 'schema_version').execute() db.create_table(cache, safe=True) db.close() if int(get_database_version()) < 3: schema_version.update(val=3).where( schema_version.key == 'schema_version').execute() with db.execution_context(): log.info('Upgrading to Db schema 3.') db.execute_sql('SET FOREIGN_KEY_CHECKS=0;') log.debug('changing db encoding') cmd_sql_db = '''ALTER DATABASE {} DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;'''.format(args.database) db.execute_sql(cmd_sql_db) log.debug('changing collation of humans') cmd_sql_table = '''ALTER TABLE humans CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;''' db.execute_sql(cmd_sql_table) db.execute_sql('SET FOREIGN_KEY_CHECKS=1;') if int(get_database_version()) < 4: log.info('Upgrading to Db schema 4.') schema_version.update(val=4).where( schema_version.key == 'schema_version').execute() with db.execution_context(): db.execute_sql( 'ALTER TABLE geocoded MODIFY description LONGTEXT;') if int(get_database_version()) < 5: log.info('Upgrading to Db schema 5.') schema_version.update(val=5).where( schema_version.key == 'schema_version').execute() columns = db.get_columns('monsters') special_filters_migrated = False for col in columns: if 'special' in col: special_filters_migrated = True if not special_filters_migrated: special = Utf8mb4CharField(index=True, null=True, max_length=20) with db.transaction(): migrate(migrator.add_column('monsters', 'special', special)) except OperationalError as e: log.critical("MySQL unhappy [ERROR]:% d: % s\n" % (e.args[0], e.args[1])) exit(1)
def bulk_upsert(cls, data, db): rows = data.values() num_rows = len(rows) i = 0 max_fails = 3 fails = 0 # This shouldn't happen, ever, but anyways... if num_rows < 1: return # We used to support SQLite and it has a default max 999 parameters, # so we limited how many rows we insert for it. # Oracle: 64000 # MySQL: 65535 # PostgreSQL: 34464 # Sqlite: 999 step = 500 # Prepare for our query. conn = db.get_conn() cursor = db.get_cursor() # We build our own INSERT INTO ... ON DUPLICATE KEY UPDATE x=VALUES(x) # query, making sure all data is properly escaped. We use # placeholders for VALUES(%s, %s, ...) so we can use executemany(). # We use peewee's InsertQuery to retrieve the fields because it # takes care of peewee's internals (e.g. required default fields). query = InsertQuery(cls, rows=[rows[0]]) # Take the first row. We need to call _iter_rows() for peewee internals. # Using next() for a single item is not considered "pythonic". first_row = {} for row in query._iter_rows(): first_row = row break # Convert the row to its fields, sorted by peewee. row_fields = sorted(first_row.keys(), key=lambda x: x._sort_key) row_fields = map(lambda x: x.name, row_fields) # Translate to proper column name, e.g. foreign keys. db_columns = [peewee_attr_to_col(cls, f) for f in row_fields] # Store defaults so we can fall back to them if a value # isn't set. defaults = {} for f in cls._meta.fields.values(): # Use DB column name as key. field_name = f.name field_default = cls._meta.defaults.get(f, None) defaults[field_name] = field_default # Assign fields, placeholders and assignments after defaults # so our lists/keys stay in order. table = '`' + conn.escape_string(cls._meta.db_table) + '`' escaped_fields = ['`' + conn.escape_string(f) + '`' for f in db_columns] placeholders = ['%s' for escaped_field in escaped_fields] assignments = [ '{x} = VALUES({x})'.format(x=escaped_field) for escaped_field in escaped_fields ] # We build our own MySQL query because peewee only supports # REPLACE INTO for upserting, which deletes the old row before # adding the new one, giving a serious performance hit. query_string = ('INSERT INTO {table} ({fields}) VALUES' + ' ({placeholders}) ON DUPLICATE KEY UPDATE' + ' {assignments}') # Prepare transaction. with db.atomic(): while i < num_rows: start = i end = min(i + step, num_rows) name = cls.__name__ log.debug('Inserting items %d to %d for %s.', start, end, name) try: # Turn off FOREIGN_KEY_CHECKS on MySQL, because apparently it's # unable to recognize strings to update unicode keys for # foreign key fields, thus giving lots of foreign key # constraint errors. db.execute_sql('SET FOREIGN_KEY_CHECKS=0;') # Time to bulk upsert our data. Convert objects to a list of # values for executemany(), and fall back to defaults if # necessary. batch = [] batch_rows = rows[i:min(i + step, num_rows)] # We pop them off one by one so we can gradually release # memory as we pass each item. No duplicate memory usage. while len(batch_rows) > 0: row = batch_rows.pop() row_data = [] # Parse rows, build arrays of values sorted via row_fields. for field in row_fields: # Take a default if we need it. if field not in row: default = defaults.get(field, None) # peewee's defaults can be callable, e.g. current # time. We only call when needed to insert. if callable(default): default = default() row[field] = default # Append to keep the exact order, and only these # fields. row_data.append(row[field]) # Done preparing, add it to the batch. batch.append(row_data) # Format query and go. formatted_query = query_string.format( table=table, fields=', '.join(escaped_fields), placeholders=', '.join(placeholders), assignments=', '.join(assignments)) cursor.executemany(formatted_query, batch) db.execute_sql('SET FOREIGN_KEY_CHECKS=1;') except Exception as e: # If there is a DB table constraint error, dump the data and # don't retry. # # Unrecoverable error strings: unrecoverable = [ 'constraint', 'has no attribute', 'peewee.IntegerField object at' ] has_unrecoverable = filter(lambda x: x in str(e), unrecoverable) if has_unrecoverable: log.warning('%s. Data is:', repr(e)) log.warning(data.items()) else: log.warning('%s... Retrying...', repr(e)) log.warning('%s', pprint.pformat(data)) time.sleep(1) fails += 1 if fails > max_fails: return continue i += step
def register(id, name): InsertQuery(humans, {humans.id: id, humans.name: name}).execute() db.close()