def save_user_run(cnn, self, target_distance, user_name, all_data): user_id = DbConnector.get_user_id(self, user_name) cur = cnn.cursor() insert_query = MySQLQuery.into(runs_table).columns( 'user_id', 'target_distance_m', 'start_time', 'end_time').insert([ user_id, int(target_distance), all_data[0][0], all_data[-1][0] ]) cur.execute(insert_query.get_sql()) cnn.commit() run_id = cur.lastrowid insert_query2 = MySQLQuery.into(run_data_table).columns( 'run_id', 'distance_m', 'elapsed_time_s').insert( [Parameter('?'), Parameter('?'), Parameter('?')]) start_time = datetime.datetime.strptime( all_data[0][0], '%Y-%m-%dT%H:%M:%S.%f+00:00').replace(tzinfo=pytz.UTC) for i, row in enumerate(all_data): data_point_time = datetime.datetime.strptime( row[0], '%Y-%m-%dT%H:%M:%S.%f+00:00').replace(tzinfo=pytz.UTC) time_diff = data_point_time - start_time elapsed_time_s = time_diff.seconds + time_diff.microseconds / 1000000.0 params = [ run_id, Utils.calc_total_distance_m(self, i), elapsed_time_s ] q = insert_query2.get_sql() cur.execute(q, params)
def createDance(self, danceID=None, properties=None): if (properties != None): properties.pop("id", None) t = Table("Dance") if (danceID == None): q = Query.into(t).columns(*properties.keys()).insert( *properties.values()) else: q = Query.into(t).columns("id", *properties.keys()).insert( danceID, *properties.values()) logger.debug(f'executing insert string: {str(q)}') with self.connection.cursor() as cur: #TODO combine these into single atomic statement? cur.execute(str(q)) if (danceID != None): rowCount = cur.execute( Query.from_(t).select("*").where(id == danceID).get_sql()) else: rowCount = cur.execute( Query.from_(t).select("*").where( id == LAST_INSERT_ID()).get_sql()) if (rowCount): rows = cur.fetchall() return rows[0] else: return None with self.connection.cursor() as cur: #TODO combine these into single atomic statement? cur.execute(f'INSERT INTO `Dance` () VALUES ()') cur.execute("SELECT * FROM `Dance` where id = LAST_INSERT_ID()") rows = cur.fetchall() return rows[0]
def insert_tweets_sql(tweets): field = ['id', 'retweet_count', 'favorite_count', 'lang', 'text'] extra_field = ['tweeted_by', 'raw_json', 'retweeted_status', 'created_at'] getter = attrgetter(*field) table = Table('tweets') def params_of(tweet): retweeted_status = str(tweet.retweeted_status) \ if tweet.retweeted_status else None raw_json = str(tweet) created_at = ctime_to_mysql_datetime(tweet.created_at) values = getter(tweet) + \ (tweet.user.id, raw_json, retweeted_status, created_at) return values return ( str(Query.into(table) .columns(field + extra_field) .insert(*[Parameter('%s') for _ in range(len(field) + len(extra_field))]) .on_duplicate_key_update( table.lang, Values(table.lang))), [params_of(t) for t in tweets])
def save(self, entity): dict_copy = deepcopy(self._fields) dict_copy.pop('id') entity_as_dict = asdict(entity) entity_values = [ entity_as_dict[key] for key in entity_as_dict if key in dict_copy ] bare_query = Query.into( self._table).columns(*dict_copy.values()).insert(*entity_values) query = query_to_str(bare_query) entity_id = None try: self._cursor.execute(query) except DBError as e: logging.error( f"Repository [{self._table}]: save -> [{e.errno}]{e.msg}") self._connection.rollback() else: self._connection.commit() entity_id = self._cursor.lastrowid logging.info( f"Repository [{self._table}]: save -> Saved {entity} with id={entity_id}" ) return entity_id
def write_preset(conn, queryin, descriptin): # the use of this function assumes there exists some Table # called 'Presets' where the first column is an # UNSIGNED AUTO_INCREMENT PRIMARY KEY labeled 'id' # and the second column is a VARCHAR NOT NULL labeled 'querval' # and the third column is a VARCHAR NOT NULL labeled 'description' # to use this method you must pass in a connection, # a preset query, and a description of what the query achieves # it will automatically write it to the bottom of the table cursor = conn.cursor() quer = "ALTER TABLE Presets DROP COLUMN id;" cursor.execute(quer) quer = "ALTER TABLE Presets ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY NOT NULL FIRST;" cursor.execute(quer) extable = Table('Presets') q = MySQLQuery.into(extable).columns("querval", "description").insert( queryin, descriptin) print(q) quer = str(q) cursor.execute(quer) cursor = conn.cursor() quer = "ALTER TABLE Presets DROP COLUMN id;" cursor.execute(quer) quer = "ALTER TABLE Presets ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY NOT NULL FIRST;" cursor.execute(quer)
def test_on_duplicate_key_ignore_update(self): q = (MySQLQuery.into("abc").insert( 1, [1, "a", True]).on_duplicate_key_ignore()) self.assertEqual( "INSERT INTO `abc` VALUES (1,[1,'a',true]) ON DUPLICATE KEY IGNORE", str(q))
def get_register_user_query(table_name, user): field_names = tuple(k for k in user) field_values = tuple(user[k] for k in field_names) users = Table(table_name) q = Query.into(users).columns(*field_names).insert(*field_values) return str(q)
def save_all(self, entities): dict_copy = deepcopy(self._fields) dict_copy.pop('id') entities_as_dicts = [asdict(entity) for entity in entities] query_builder = Query.into(self._table).columns(*dict_copy.values()) for entity_as_dict in entities_as_dicts: entity_values = [ entity_as_dict[key] for key in entity_as_dict if key in dict_copy ] query_builder = query_builder.insert(*entity_values) query = query_to_str(query_builder) rows_count = 0 try: self._cursor.execute(query) except DBError as e: logging.error( f"Repository [{self._table}]: save_all -> [{e.errno}]{e.msg}") self._connection.rollback() else: self._connection.commit() rows_count = self._cursor.rowcount logging.info( f"Repository [{self._table}]: save_all -> Saved {rows_count} entities" ) return rows_count
def test_insert_ignore(self): query = MySQLQuery.into(self.table_abc).insert(1).ignore().on_duplicate_key_update(self.table_abc.baz, False) self.assertEqual( "INSERT IGNORE INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `baz`=false", str(query), )
def test_insert_multiple_columns_on_duplicate_update_one_with_different_value(self): query = MySQLQuery.into(self.table_abc).insert(1, "a").on_duplicate_key_update(self.table_abc.bar, "b") self.assertEqual( "INSERT INTO `abc` VALUES (1,'a') ON DUPLICATE KEY UPDATE `bar`='b'", str(query), )
def test_on_duplicate_key_update_update(self): q = (MySQLQuery.into("abc").insert( 1, [1, "a", True]).on_duplicate_key_update(self.table_abc.a, 'b')) self.assertEqual( "INSERT INTO `abc` VALUES (1,[1,'a',true]) ON DUPLICATE KEY UPDATE `a`='b'", str(q))
def build_query_insert(table, key_duplicate, list_values): """ Build query Insert :param key_duplicate : boolean in oredr to have dupliacte key on query :param table : table name :param list_values: list of values to insert return query """ table = Table(table) values_table = [] for i in range(len(list_values)): values_table.append(tuple(list_values[i])) q = BuildInjectQuery.function_insert( MySQLQuery.into(table).insert(values_table[0]), values_table, table, 0) if key_duplicate: q = q.on_duplicate_key_update(table.lei, Values(table.lei)) \ .on_duplicate_key_update(table.entity_legalname, \ Values(table.entity_legalname)) \ .on_duplicate_key_update(table.entity_entity_status, \ Values(table.entity_entity_status)) \ .on_duplicate_key_update(table.entity_legalname, \ Values(table.entity_legalname)) \ .on_duplicate_key_update(table.entity_otherentity_names, \ Values(table.entity_otherentity_names)) \ .on_duplicate_key_update(table.entity_other_addresses, \ Values(table.entity_other_addresses)) \ .on_duplicate_key_update(table.entity_legal_address_firstaddressline, \ Values(table.entity_legal_address_firstaddressline)) \ .on_duplicate_key_update(table.entity_legal_address_city, \ Values(table.entity_legal_address_city)) \ .on_duplicate_key_update(table.entity_legal_address_region, \ Values(table.entity_legal_address_region)) \ .on_duplicate_key_update(table.entity_legal_address_country, \ Values(table.entity_legal_address_country)) \ .on_duplicate_key_update(table.entity_legal_address_postalcode, \ Values(table.entity_legal_address_postalcode)) \ .on_duplicate_key_update(table.entity_headquarter_address_firstaddressline, \ Values(table.entity_headquarter_address_firstaddressline)) \ .on_duplicate_key_update(table.entity_headquarter_address_city, \ Values(table.entity_headquarter_address_city)) \ .on_duplicate_key_update(table.entity_headquarter_address_region, \ Values(table.entity_headquarter_address_region)) \ .on_duplicate_key_update(table.entity_headquarter_address_country, \ Values(table.entity_headquarter_address_country)) \ .on_duplicate_key_update(table.entity_headquarter_address_postalcode, \ Values(table.entity_headquarter_address_postalcode)) \ .on_duplicate_key_update(table.registration_initial_registration_date, \ Values(table.registration_initial_registration_date)) \ .on_duplicate_key_update(table.registration_registration_status, \ Values(table.registration_registration_status)) \ .on_duplicate_key_update(table.registration_next_renewal_date, \ Values(table.registration_next_renewal_date)) \ .on_duplicate_key_update(table.registration_validation_sources, \ Values(table.registration_validation_sources)) return str(q)
def test_insert_none_skipped(self): query = ( MySQLQuery.into(self.table_abc) .insert() .on_duplicate_key_update(self.table_abc.baz, False) ) self.assertEqual("", str(query))
def skill_insert(cleaned_text): skill_table = Table('skill') skill_columns = 'cleaned_text' insert_skill = MySQLQuery.into(skill_table).columns(skill_columns).insert( cleaned_text) skillsdb_cursor.execute((str(insert_skill))) print(insert_skill) return skillsdb_cursor.lastrowid
def test_conflict_handlers_update(self): with self.assertRaises(QueryException): ( MySQLQuery.into("abc") .insert(1, [1, "a", True]) .on_duplicate_key_ignore() .on_duplicate_key_update(self.table_abc.a, 'b') )
def insert_user(cnn, self, user: str, passw: str) -> bool: cur = cnn.cursor() insert_query = MySQLQuery.into(users_table) \ .columns('name', 'password') \ .insert(user, Utils.string_to_base_64_string(self, passw)) cur.execute(insert_query.get_sql()) return True
def synonym_insert(skill_id, synonym_text, is_original): synonym_table = Table('synonym') synonym_columns = ['skill_id`', '`synonym_text`', '`is_original'] insert_synonym = MySQLQuery.into(synonym_table).columns( ",".join(synonym_columns)).insert(skill_id, synonym_text, is_original) skillsdb_cursor.execute(str(insert_synonym)) print(insert_synonym) return skillsdb_cursor.lastrowid
def test_insert_one_column(self): query = (MySQLQuery.into( self.table_abc).insert(1).on_conflict().do_update( self.table_abc.foo, self.table_abc.foo)) self.assertEqual( "INSERT INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `foo`=`foo`", str(query), )
def test_insert_one_column_using_values(self): query = (MySQLQuery.into( self.table_abc).insert(1).on_duplicate_key_update( self.table_abc.foo, Values(self.table_abc.foo))) self.assertEqual( "INSERT INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `foo`=VALUES(`foo`)", str(query), )
def test_insert_selected_columns_on_duplicate_update_one(self): query = MySQLQuery.into(self.table_abc) \ .columns(self.table_abc.foo, self.table_abc.bar, self.table_abc.baz) \ .insert(1, 'a', True) \ .on_duplicate_key_update(self.table_abc.baz, False) self.assertEqual( 'INSERT INTO `abc` (`foo`,`bar`,`baz`) VALUES (1,\'a\',true) ON DUPLICATE KEY UPDATE `baz`=false', str(query))
def record_wheel_rotation(cnn, self) -> bool: cur = cnn.cursor() tz = pytz.timezone('UTC') insert_query = MySQLQuery.into(raw_data_table) \ .columns('timestamp') \ .insert(datetime.datetime.now(tz)) cur.execute(insert_query.get_sql()) return True
def test_insert_ignore(self): query = (MySQLQuery.into( self.table_abc).insert(1).on_conflict().do_update( self.table_abc.baz, False)) self.assertEqual( "INSERT INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `baz`=false", str(query), )
def test_insert_multiple_columns_on_duplicate_update_one_with_expression(self): query = ( MySQLQuery.into(self.table_abc).insert(1, 2).on_duplicate_key_update(self.table_abc.bar, 4 + F("bar")) ) # todo sql expression? not python self.assertEqual( "INSERT INTO `abc` VALUES (1,2) ON DUPLICATE KEY UPDATE `bar`=4+`bar`", str(query), )
def test_insert_one_column_multi_element_array(self): query = (MySQLQuery.into(self.table_abc).insert( (1, ), (2, )).on_duplicate_key_update(self.table_abc.foo, self.table_abc.foo)) self.assertEqual( "INSERT INTO `abc` VALUES (1),(2) ON DUPLICATE KEY UPDATE `foo`=`foo`", str(query), )
def test_insert_multiple_columns_on_duplicate_update_multiple(self): query = (MySQLQuery.into( self.table_abc).insert(1, "a", "b").on_conflict().do_update( self.table_abc.bar, "b").do_update(self.table_abc.baz, "c")) self.assertEqual( "INSERT INTO `abc` VALUES (1,'a','b') ON DUPLICATE KEY UPDATE `bar`='b',`baz`='c'", str(query), )
def test_insert_selected_columns_on_duplicate_update_one(self): query = (MySQLQuery.into(self.table_abc).columns( self.table_abc.foo, self.table_abc.bar, self.table_abc.baz).insert( 1, "a", True).on_conflict().do_update(self.table_abc.baz, False)) self.assertEqual( "INSERT INTO `abc` (`foo`,`bar`,`baz`) VALUES (1,'a',true) ON DUPLICATE KEY UPDATE `baz`=false", str(query), )
def test_insert_selected_columns_on_duplicate_update_one(self): query = MySQLQuery.into(self.table_abc) \ .columns(self.table_abc.foo, self.table_abc.bar, self.table_abc.baz) \ .insert(1, 'a', True) \ .on_duplicate_key_update(self.table_abc.baz, False) self.assertEqual( 'INSERT INTO `abc` (`foo`,`bar`,`baz`) VALUES (1,\'a\',true) ON DUPLICATE KEY UPDATE `baz`=false', str(query) )
def test_insert_multiple_columns_on_duplicate_update_one_with_expression_using_original_field_value( self, ): query = (MySQLQuery.into(self.table_abc).insert( 1, "a").on_duplicate_key_update( self.table_abc.bar, fn.Concat(self.table_abc.bar, "update"))) self.assertEqual( "INSERT INTO `abc` VALUES (1,'a') ON DUPLICATE KEY UPDATE `bar`=CONCAT(`bar`,'update')", str(query), )
def test_insert_multiple_columns_on_duplicate_update_one_with_same_value( self): query = (MySQLQuery.into(self.table_abc).insert( 1, "a").on_duplicate_key_update(self.table_abc.bar, Values(self.table_abc.bar))) self.assertEqual( "INSERT INTO `abc` VALUES (1,'a') ON DUPLICATE KEY UPDATE `bar`=VALUES(`bar`)", str(query), )
def test_insert_multi_rows_chained_mixed_on_duplicate_update_multiple(self): query = MySQLQuery.into(self.table_abc) \ .insert((1, 'a', True), (2, 'b', False)) \ .insert(3, 'c', True) \ .on_duplicate_key_update(self.table_abc.foo, self.table_abc.foo) \ .on_duplicate_key_update(self.table_abc.bar, Values(self.table_abc.bar)) self.assertEqual( 'INSERT INTO `abc` VALUES (1,\'a\',true),(2,\'b\',false),(3,\'c\',true) ' 'ON DUPLICATE KEY UPDATE `foo`=`foo`,`bar`=VALUES(`bar`)', str(query) )
def test_upsert(self): q = ( MySQLQuery.into("abc") .insert(1, "b", False) .as_("aaa") .on_conflict(self.table_abc.id) .do_update("abc") ) self.assertEqual( "INSERT INTO `abc` VALUES (1,'b',false) AS `aaa` ON DUPLICATE KEY UPDATE `abc`=`aaa`.`abc`", str(q), )
def test_insert_multi_rows_chained_mixed_on_duplicate_update_multiple( self): query = MySQLQuery.into(self.table_abc) \ .insert((1, 'a', True), (2, 'b', False)) \ .insert(3, 'c', True) \ .on_duplicate_key_update(self.table_abc.foo, self.table_abc.foo) \ .on_duplicate_key_update(self.table_abc.bar, Values(self.table_abc.bar)) self.assertEqual( 'INSERT INTO `abc` VALUES (1,\'a\',true),(2,\'b\',false),(3,\'c\',true) ' 'ON DUPLICATE KEY UPDATE `foo`=`foo`,`bar`=VALUES(`bar`)', str(query))
def test_insert_none_skipped(self): query = MySQLQuery.into(self.table_abc).insert().on_duplicate_key_update(self.table_abc.baz, False) self.assertEqual('', str(query))
def test_insert_ignore(self): query = MySQLQuery.into(self.table_abc).insert(1).ignore().on_duplicate_key_update(self.table_abc.baz, False) self.assertEqual('INSERT IGNORE INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `baz`=false', str(query))