def test_mysql_query_uses_backtick_quote_chars(self):
q = MySQLQuery.from_(self.table_abc).select(
self.table_abc.foo
).groupby(
self.table_abc.foo
).having(
self.table_abc.buz == 'fiz'
)
self.assertEqual("SELECT `foo` FROM `abc` GROUP BY `foo` HAVING `buz`='fiz'", str(q))
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_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_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_mysql_query_uses_backtick_quote_chars(self):
q = MySQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
self.assertEqual("SELECT `foo` FROM `abc` GROUP BY `foo`", str(q))
def test_do_not_add_hints_to_query_if_not_supported_by_dialect(self):
query = MySQLQuery.from_('table').select('*')
query_hint = add_hints([query], 'test_hint')
self.assertEqual('SELECT * FROM `table`', str(query_hint[0]))
def test_mysql_query_uses_backtick_quote_chars(self):
q = MySQLQuery.from_('abc').select('foo', 'bar')
self.assertEqual('SELECT `foo`,`bar` FROM `abc`', str(q))
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_drop_table(self):
q = MySQLQuery.drop_table(self.table_abc)
self.assertEqual(
'DROP TABLE `abc`',
str(q),
)
def test_normal_select(self):
q = MySQLQuery.from_("abc").select("def")
self.assertEqual("SELECT `def` FROM `abc`", str(q))
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 test_update(self):
q = MySQLQuery.into("abc").insert(1, [1, "a", True])
self.assertEqual("INSERT INTO `abc` VALUES (1,[1,'a',true])", str(q))
def test_modifier_select(self):
q = MySQLQuery.from_("abc").select("def").select("ghi").modifier(
"SQL_CALC_FOUND_ROWS")
self.assertEqual("SELECT SQL_CALC_FOUND_ROWS `def`,`ghi` FROM `abc`",
str(q))
def test_distinct_select(self):
q = MySQLQuery.from_("abc").select("def").distinct()
self.assertEqual("SELECT DISTINCT `def` FROM `abc`", 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 stock_list_data(pro, engine):
"""
获取股票列表数据
"""
data = pro.stock_basic(fields=",".join(StockCompanyData.get_fields()))
size = len(data)
all_list = []
for index in range(0, size):
basic_data = [
"" if data.iloc[index][field] == None else data.iloc[index][field]
for field in StockCompanyData.get_fields()
]
all_list.append(basic_data)
redis_conn = get_redis_conn(pool_redis)
conn = pymysql.connect(user=mysql_config["user"],
password=mysql_config["password"],
database=mysql_config["database"],
charset=mysql_config["charset"])
cursor = conn.cursor()
for basic_data in all_list:
update_basic_data = "\t".join(basic_data)
pre_basic_data = redis_conn.hget("stock_basic", basic_data[0])
if pre_basic_data == update_basic_data:
continue
redis_conn.hset("stock_basic", basic_data[0], update_basic_data)
stock_basic_table = Table("stock_basic")
sql = MySQLQuery.into(stock_basic_table).insert(
basic_data).on_duplicate_key_update(
Tuple(stock_basic_table.symbol, stock_basic_table.name,
stock_basic_table.area, stock_basic_table.industry,
stock_basic_table.fullname, stock_basic_table.enname,
stock_basic_table.market, stock_basic_table.exchange,
stock_basic_table.curr_type,
stock_basic_table.list_status,
stock_basic_table.list_date,
stock_basic_table.delist_date, stock_basic_table.is_hs),
Tuple(
Values(stock_basic_table.symbol, stock_basic_table.area,
stock_basic_table.industry,
stock_basic_table.fullname,
stock_basic_table.enname, stock_basic_table.market,
stock_basic_table.exchange,
stock_basic_table.curr_type,
stock_basic_table.list_status,
stock_basic_table.list_date,
stock_basic_table.delist_date,
stock_basic_table.is_hs)))
cursor.execute(sql, )
conn.commit()
cursor.close()
conn.close()
basic_data_path = "%s/stock_basic_%s.log" % (
basic_data_config["stock_basic_path"],
time.strftime('%Y-%m-%d', time.localtime(time.time())))
with open(basic_data_path, "w+") as f:
for basic_data in all_list:
f.write("\t".join(basic_data))
f.write("\n")
return
def test_insert_none_skipped(self):
query = (MySQLQuery.into(
self.table_abc).insert().on_conflict().do_update(
self.table_abc.baz, False))
self.assertEqual("", 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 _prepare_insert_statement(self, columns: List[str]) -> str:
return str(
MySQLQuery.into(Table(self.model._meta.table)).columns(
*columns).insert('???')).replace(
"'???'", ','.join(['%s' for _ in range(len(columns))]))
def test_do_not_add_hints_to_query_if_not_supported_by_dialect(self):
query = MySQLQuery.from_('table').select('*')
query_hint = add_hints([query], 'test_hint')
self.assertEqual('SELECT * FROM `table`', str(query_hint[0]))
def test_mysql_query_uses_backtick_quote_chars(self):
q = MySQLQuery.from_('abc').select('foo', 'bar')
self.assertEqual('SELECT `foo`,`bar` FROM `abc`', str(q))
def test_mysql_query_uses_backtick_quote_chars(self):
q = MySQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
self.assertEqual('SELECT `foo` FROM `abc` GROUP BY `foo`', str(q))
def test_mysql_query_uses_backtick_quote_chars(self):
q = MySQLQuery.from_("abc").select("foo", "bar")
self.assertEqual("SELECT `foo`,`bar` FROM `abc`", str(q))
def test_mysql_query_does_not_wrap_unioned_queries_with_params(self):
query1 = MySQLQuery.from_(self.table1).select(self.table1.foo)
query2 = Query.from_(self.table2).select(self.table2.bar)
self.assertEqual('SELECT `foo` FROM `abc` UNION SELECT `bar` FROM `efg`', str(query1 + query2))
def test_mysql_query_does_not_wrap_unioned_queries_with_params(self):
query1 = MySQLQuery.from_(self.table1).select(self.table1.foo)
query2 = Query.from_(self.table2).select(self.table2.bar)
self.assertEqual('SELECT `foo` FROM `abc` UNION SELECT `bar` FROM `efg`', str(query1 + query2))
