def test_tuple_in_using_mixed_tuples(self):
q = Query.from_(self.table_abc) \
.select(self.table_abc.foo, self.table_abc.bar) \
.where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([(1, 1), Tuple(2, 2), (3, 3)]))
self.assertEqual('SELECT "foo","bar" FROM "abc" '
'WHERE ("foo","bar") IN ((1,1),(2,2),(3,3))', str(q))
def test_tuple_equality_tuple_on_both(self):
q = Query.from_(self.table_abc) \
.select(self.table_abc.foo, self.table_abc.bar) \
.where(Tuple(self.table_abc.foo, self.table_abc.bar) == Tuple(1, 2))
self.assertEqual('SELECT "foo","bar" FROM "abc" '
'WHERE ("foo","bar")=(1,2)', str(q))
def test_tuples_in_join(self):
query = Query.from_(self.table_abc) \
.join(self.table_efg) \
.on(self.table_abc.foo == self.table_efg.bar) \
.select('*') \
.where(Tuple(self.table_abc.foo, self.table_efg.bar).isin([(1, 1), Tuple(2, 2), (3, 3)]))
self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."bar" '
'WHERE ("abc"."foo","efg"."bar") IN ((1,1),(2,2),(3,3))', str(query))
def test_tuple_is_aggregate(self):
with self.subTest(
"None if single argument returns None for is_aggregate"):
self.assertEqual(None, Tuple(0).is_aggregate)
self.assertEqual(None, Tuple(Coalesce("col")).is_aggregate)
with self.subTest(
"None if multiple arguments all return None for is_aggregate"):
self.assertEqual(None, Tuple(0, "a").is_aggregate)
self.assertEqual(
None,
Tuple(Coalesce("col"), NullIf("col2", 0)).is_aggregate)
with self.subTest(
"True if single argument returns True for is_aggregate"):
self.assertEqual(True, Tuple(Sum("col")).is_aggregate)
with self.subTest(
"True if multiple arguments return True for is_aggregate"):
self.assertEqual(True, Tuple(Sum("col"), Sum("col2")).is_aggregate)
with self.subTest(
"True when mix of arguments returning None and True for is_aggregate"
):
self.assertEqual(
True,
Tuple(Coalesce("col"), Coalesce("col2", 0),
Sum("col3")).is_aggregate)
with self.subTest(
"False when one of the arguments returns False for is_aggregate"
):
self.assertEqual(False,
Tuple(Field("col1"), Sum("col2")).is_aggregate)
def test_tuple_is_aggregate(self):
with self.subTest(
'None if single argument returns None for is_aggregate'):
self.assertEqual(None, Tuple(0).is_aggregate)
self.assertEqual(None, Tuple(Coalesce('col')).is_aggregate)
with self.subTest(
'None if multiple arguments all return None for is_aggregate'):
self.assertEqual(None, Tuple(0, 'a').is_aggregate)
self.assertEqual(
None,
Tuple(Coalesce('col'), NullIf('col2', 0)).is_aggregate)
with self.subTest(
'True if single argument returns True for is_aggregate'):
self.assertEqual(True, Tuple(Sum('col')).is_aggregate)
with self.subTest(
'True if multiple arguments return True for is_aggregate'):
self.assertEqual(True, Tuple(Sum('col'), Sum('col2')).is_aggregate)
with self.subTest(
'True when mix of arguments returning None and True for is_aggregate'
):
self.assertEqual(
True,
Tuple(Coalesce('col'), Coalesce('col2', 0),
Sum('col3')).is_aggregate)
with self.subTest(
'False when one of the arguments returns False for is_aggregate'
):
self.assertEqual(False,
Tuple(Field('col1'), Sum('col2')).is_aggregate)
def test_tuple_equality_tuple_on_right(self):
q = Query.from_(self.table_abc) \
.select(self.table_abc.foo, self.table_abc.bar) \
.where((self.table_abc.foo, self.table_abc.bar) == Tuple(1, 2))
# Order is reversed due to lack of right equals method
self.assertEqual('SELECT "foo","bar" FROM "abc" '
'WHERE (1,2)=("foo","bar")', str(q))
def test_replace_tuple_table(self):
query = (Query.from_(self.table_a).select(
self.table_a.cost,
self.table_a.revenue).where((self.table_a.cost,
self.table_a.revenue) == Tuple(1, 2)))
query = query.replace_table(self.table_a, self.table_b)
# Order is reversed due to lack of right equals method
self.assertEqual(
'SELECT "cost","revenue" FROM "b" WHERE (1,2)=("cost","revenue")',
str(query),
)
def test_render_alias_in_array_sql(self):
tb = Table("tb")
q = Query.from_(tb).select(Tuple(tb.col).as_("different_name"))
self.assertEqual(str(q), 'SELECT ("col") "different_name" FROM "tb"')
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
