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