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