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))
