How to use mydb method in pytest-django
Best Python code snippet using pytest-django_python
test.py
Source:test.py 
1import os2import re3import time4import pytest5from helpers.cluster import ClickHouseCluster6from helpers.test_tools import assert_eq_with_retry, TSV7cluster = ClickHouseCluster(__file__)8node = cluster.add_instance('node', main_configs=["configs/config.d/remote_servers.xml"],9 user_configs=["configs/users.d/row_policy.xml", "configs/users.d/another_user.xml",10 "configs/users.d/any_join_distinct_right_table_keys.xml"],11 with_zookeeper=True)12node2 = cluster.add_instance('node2', main_configs=["configs/config.d/remote_servers.xml"],13 user_configs=["configs/users.d/row_policy.xml", "configs/users.d/another_user.xml",14 "configs/users.d/any_join_distinct_right_table_keys.xml"],15 with_zookeeper=True)16nodes = [node, node2]17def copy_policy_xml(local_file_name, reload_immediately=True):18 script_dir = os.path.dirname(os.path.realpath(__file__))19 for current_node in nodes:20 current_node.copy_file_to_container(os.path.join(script_dir, local_file_name),21 '/etc/clickhouse-server/users.d/row_policy.xml')22 if reload_immediately:23 current_node.query("SYSTEM RELOAD CONFIG")24@pytest.fixture(scope="module", autouse=True)25def started_cluster():26 try:27 cluster.start()28 for current_node in nodes:29 current_node.query('''30 CREATE DATABASE mydb;31 CREATE TABLE mydb.filtered_table1 (a UInt8, b UInt8) ENGINE MergeTree ORDER BY a;32 INSERT INTO mydb.filtered_table1 values (0, 0), (0, 1), (1, 0), (1, 1);33 CREATE TABLE mydb.table (a UInt8, b UInt8) ENGINE MergeTree ORDER BY a;34 INSERT INTO mydb.table values (0, 0), (0, 1), (1, 0), (1, 1);35 CREATE TABLE mydb.filtered_table2 (a UInt8, b UInt8, c UInt8, d UInt8) ENGINE MergeTree ORDER BY a;36 INSERT INTO mydb.filtered_table2 values (0, 0, 0, 0), (1, 2, 3, 4), (4, 3, 2, 1), (0, 0, 6, 0);37 CREATE TABLE mydb.filtered_table3 (a UInt8, b UInt8, c UInt16 ALIAS a + b) ENGINE MergeTree ORDER BY a;38 INSERT INTO mydb.filtered_table3 values (0, 0), (0, 1), (1, 0), (1, 1);39 CREATE TABLE mydb.`.filtered_table4` (a UInt8, b UInt8, c UInt16 ALIAS a + b) ENGINE MergeTree ORDER BY a;40 INSERT INTO mydb.`.filtered_table4` values (0, 0), (0, 1), (1, 0), (1, 1);41 CREATE TABLE mydb.local (a UInt8, b UInt8) ENGINE MergeTree ORDER BY a;42 ''')43 node.query("INSERT INTO mydb.local values (2, 0), (2, 1), (1, 0), (1, 1)")44 node2.query("INSERT INTO mydb.local values (3, 0), (3, 1), (1, 0), (1, 1)")45 yield cluster46 finally:47 cluster.shutdown()48@pytest.fixture(autouse=True)49def reset_policies():50 try:51 yield52 finally:53 copy_policy_xml('normal_filters.xml')54 for current_node in nodes:55 current_node.query("DROP POLICY IF EXISTS pA, pB ON mydb.filtered_table1")56def test_smoke():57 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[1, 0], [1, 1]])58 assert node.query("SELECT * FROM mydb.filtered_table2") == TSV([[0, 0, 0, 0], [0, 0, 6, 0]])59 assert node.query("SELECT * FROM mydb.filtered_table3") == TSV([[0, 1], [1, 0]])60 assert node.query("SELECT a FROM mydb.filtered_table1") == TSV([[1], [1]])61 assert node.query("SELECT b FROM mydb.filtered_table1") == TSV([[0], [1]])62 assert node.query("SELECT a FROM mydb.filtered_table1 WHERE a = 1") == TSV([[1], [1]])63 assert node.query("SELECT a FROM mydb.filtered_table1 WHERE a IN (1)") == TSV([[1], [1]])64 assert node.query("SELECT a = 1 FROM mydb.filtered_table1") == TSV([[1], [1]])65 assert node.query("SELECT a FROM mydb.filtered_table3") == TSV([[0], [1]])66 assert node.query("SELECT b FROM mydb.filtered_table3") == TSV([[1], [0]])67 assert node.query("SELECT c FROM mydb.filtered_table3") == TSV([[1], [1]])68 assert node.query("SELECT a + b FROM mydb.filtered_table3") == TSV([[1], [1]])69 assert node.query("SELECT a FROM mydb.filtered_table3 WHERE c = 1") == TSV([[0], [1]])70 assert node.query("SELECT c = 1 FROM mydb.filtered_table3") == TSV([[1], [1]])71 assert node.query("SELECT a + b = 1 FROM mydb.filtered_table3") == TSV([[1], [1]])72def test_join():73 assert node.query(74 "SELECT * FROM mydb.filtered_table1 as t1 ANY LEFT JOIN mydb.filtered_table1 as t2 ON t1.a = t2.b") == TSV(75 [[1, 0, 1, 1], [1, 1, 1, 1]])76 assert node.query(77 "SELECT * FROM mydb.filtered_table1 as t2 ANY RIGHT JOIN mydb.filtered_table1 as t1 ON t2.b = t1.a") == TSV(78 [[1, 1, 1, 0]])79def test_cannot_trick_row_policy_with_keyword_with():80 assert node.query("WITH 0 AS a SELECT * FROM mydb.filtered_table1") == TSV([[1, 0], [1, 1]])81 assert node.query("WITH 0 AS a SELECT a, b FROM mydb.filtered_table1") == TSV([[0, 0], [0, 1]])82 assert node.query("WITH 0 AS a SELECT a FROM mydb.filtered_table1") == TSV([[0], [0]])83 assert node.query("WITH 0 AS a SELECT b FROM mydb.filtered_table1") == TSV([[0], [1]])84def test_prewhere_not_supported():85 expected_error = "PREWHERE is not supported if the table is filtered by row-level security"86 assert expected_error in node.query_and_get_error("SELECT * FROM mydb.filtered_table1 PREWHERE 1")87 assert expected_error in node.query_and_get_error("SELECT * FROM mydb.filtered_table2 PREWHERE 1")88 assert expected_error in node.query_and_get_error("SELECT * FROM mydb.filtered_table3 PREWHERE 1")89 # However PREWHERE should still work for user without filtering.90 assert node.query("SELECT * FROM mydb.filtered_table1 PREWHERE 1", user="another") == TSV(91 [[0, 0], [0, 1], [1, 0], [1, 1]])92def test_policy_from_users_xml_affects_only_user_assigned():93 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[1, 0], [1, 1]])94 assert node.query("SELECT * FROM mydb.filtered_table1", user="another") == TSV([[0, 0], [0, 1], [1, 0], [1, 1]])95 assert node.query("SELECT * FROM mydb.filtered_table2") == TSV([[0, 0, 0, 0], [0, 0, 6, 0]])96 assert node.query("SELECT * FROM mydb.filtered_table2", user="another") == TSV(97 [[0, 0, 0, 0], [0, 0, 6, 0], [1, 2, 3, 4], [4, 3, 2, 1]])98 assert node.query("SELECT * FROM mydb.local") == TSV([[1, 0], [1, 1], [2, 0], [2, 1]])99 assert node.query("SELECT * FROM mydb.local", user="another") == TSV([[1, 0], [1, 1]])100def test_change_of_users_xml_changes_row_policies():101 copy_policy_xml('normal_filters.xml')102 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[1, 0], [1, 1]])103 assert node.query("SELECT * FROM mydb.filtered_table2") == TSV([[0, 0, 0, 0], [0, 0, 6, 0]])104 assert node.query("SELECT * FROM mydb.filtered_table3") == TSV([[0, 1], [1, 0]])105 copy_policy_xml('all_rows.xml')106 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[0, 0], [0, 1], [1, 0], [1, 1]])107 assert node.query("SELECT * FROM mydb.filtered_table2") == TSV(108 [[0, 0, 0, 0], [0, 0, 6, 0], [1, 2, 3, 4], [4, 3, 2, 1]])109 assert node.query("SELECT * FROM mydb.filtered_table3") == TSV([[0, 0], [0, 1], [1, 0], [1, 1]])110 copy_policy_xml('no_rows.xml')111 assert node.query("SELECT * FROM mydb.filtered_table1") == ""112 assert node.query("SELECT * FROM mydb.filtered_table2") == ""113 assert node.query("SELECT * FROM mydb.filtered_table3") == ""114 copy_policy_xml('normal_filters.xml')115 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[1, 0], [1, 1]])116 assert node.query("SELECT * FROM mydb.filtered_table2") == TSV([[0, 0, 0, 0], [0, 0, 6, 0]])117 assert node.query("SELECT * FROM mydb.filtered_table3") == TSV([[0, 1], [1, 0]])118 copy_policy_xml('no_filters.xml')119 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[0, 0], [0, 1], [1, 0], [1, 1]])120 assert node.query("SELECT * FROM mydb.filtered_table2") == TSV(121 [[0, 0, 0, 0], [0, 0, 6, 0], [1, 2, 3, 4], [4, 3, 2, 1]])122 assert node.query("SELECT * FROM mydb.filtered_table3") == TSV([[0, 0], [0, 1], [1, 0], [1, 1]])123 copy_policy_xml('normal_filters.xml')124 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[1, 0], [1, 1]])125 assert node.query("SELECT * FROM mydb.filtered_table2") == TSV([[0, 0, 0, 0], [0, 0, 6, 0]])126 assert node.query("SELECT * FROM mydb.filtered_table3") == TSV([[0, 1], [1, 0]])127def test_reload_users_xml_by_timer():128 copy_policy_xml('normal_filters.xml')129 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[1, 0], [1, 1]])130 assert node.query("SELECT * FROM mydb.filtered_table2") == TSV([[0, 0, 0, 0], [0, 0, 6, 0]])131 assert node.query("SELECT * FROM mydb.filtered_table3") == TSV([[0, 1], [1, 0]])132 time.sleep(1) # The modification time of the 'row_policy.xml' file should be different.133 copy_policy_xml('all_rows.xml', False)134 assert_eq_with_retry(node, "SELECT * FROM mydb.filtered_table1", [[0, 0], [0, 1], [1, 0], [1, 1]])135 assert_eq_with_retry(node, "SELECT * FROM mydb.filtered_table2",136 [[0, 0, 0, 0], [0, 0, 6, 0], [1, 2, 3, 4], [4, 3, 2, 1]])137 assert_eq_with_retry(node, "SELECT * FROM mydb.filtered_table3", [[0, 0], [0, 1], [1, 0], [1, 1]])138 time.sleep(1) # The modification time of the 'row_policy.xml' file should be different.139 copy_policy_xml('normal_filters.xml', False)140 assert_eq_with_retry(node, "SELECT * FROM mydb.filtered_table1", [[1, 0], [1, 1]])141 assert_eq_with_retry(node, "SELECT * FROM mydb.filtered_table2", [[0, 0, 0, 0], [0, 0, 6, 0]])142 assert_eq_with_retry(node, "SELECT * FROM mydb.filtered_table3", [[0, 1], [1, 0]])143def test_introspection():144 policies = [145 ["another ON mydb.filtered_table1", "another", "mydb", "filtered_table1",146 "6068883a-0e9d-f802-7e22-0144f8e66d3c", "users.xml", "1", 0, 0, "['another']", "[]"],147 ["another ON mydb.filtered_table2", "another", "mydb", "filtered_table2",148 "c019e957-c60b-d54e-cc52-7c90dac5fb01", "users.xml", "1", 0, 0, "['another']", "[]"],149 ["another ON mydb.filtered_table3", "another", "mydb", "filtered_table3",150 "4cb080d0-44e8-dbef-6026-346655143628", "users.xml", "1", 0, 0, "['another']", "[]"],151 ["another ON mydb.local", "another", "mydb", "local", "5b23c389-7e18-06bf-a6bc-dd1afbbc0a97", "users.xml",152 "a = 1", 0, 0, "['another']", "[]"],153 ["default ON mydb.filtered_table1", "default", "mydb", "filtered_table1",154 "9e8a8f62-4965-2b5e-8599-57c7b99b3549", "users.xml", "a = 1", 0, 0, "['default']", "[]"],155 ["default ON mydb.filtered_table2", "default", "mydb", "filtered_table2",156 "cffae79d-b9bf-a2ef-b798-019c18470b25", "users.xml", "a + b < 1 or c - d > 5", 0, 0, "['default']", "[]"],157 ["default ON mydb.filtered_table3", "default", "mydb", "filtered_table3",158 "12fc5cef-e3da-3940-ec79-d8be3911f42b", "users.xml", "c = 1", 0, 0, "['default']", "[]"],159 ["default ON mydb.local", "default", "mydb", "local", "cdacaeb5-1d97-f99d-2bb0-4574f290629c", "users.xml", "1",160 0, 0, "['default']", "[]"]161 ]162 assert node.query("SELECT * from system.row_policies ORDER BY short_name, database, table") == TSV(policies)163def test_dcl_introspection():164 assert node.query("SHOW POLICIES") == TSV(165 ["another ON mydb.filtered_table1", "another ON mydb.filtered_table2", "another ON mydb.filtered_table3",166 "another ON mydb.local", "default ON mydb.filtered_table1", "default ON mydb.filtered_table2",167 "default ON mydb.filtered_table3", "default ON mydb.local"])168 assert node.query("SHOW POLICIES ON mydb.filtered_table1") == TSV(["another", "default"])169 assert node.query("SHOW POLICIES ON mydb.local") == TSV(["another", "default"])170 assert node.query("SHOW POLICIES ON mydb.*") == TSV(171 ["another ON mydb.filtered_table1", "another ON mydb.filtered_table2", "another ON mydb.filtered_table3",172 "another ON mydb.local", "default ON mydb.filtered_table1", "default ON mydb.filtered_table2",173 "default ON mydb.filtered_table3", "default ON mydb.local"])174 assert node.query("SHOW POLICIES default") == TSV(175 ["default ON mydb.filtered_table1", "default ON mydb.filtered_table2", "default ON mydb.filtered_table3",176 "default ON mydb.local"])177 assert node.query(178 "SHOW CREATE POLICY default ON mydb.filtered_table1") == "CREATE ROW POLICY default ON mydb.filtered_table1 FOR SELECT USING a = 1 TO default\n"179 assert node.query(180 "SHOW CREATE POLICY default ON mydb.filtered_table2") == "CREATE ROW POLICY default ON mydb.filtered_table2 FOR SELECT USING ((a + b) < 1) OR ((c - d) > 5) TO default\n"181 assert node.query(182 "SHOW CREATE POLICY default ON mydb.filtered_table3") == "CREATE ROW POLICY default ON mydb.filtered_table3 FOR SELECT USING c = 1 TO default\n"183 assert node.query(184 "SHOW CREATE POLICY default ON mydb.local") == "CREATE ROW POLICY default ON mydb.local FOR SELECT USING 1 TO default\n"185 assert node.query("SHOW CREATE POLICY default") == TSV(186 ["CREATE ROW POLICY default ON mydb.filtered_table1 FOR SELECT USING a = 1 TO default",187 "CREATE ROW POLICY default ON mydb.filtered_table2 FOR SELECT USING ((a + b) < 1) OR ((c - d) > 5) TO default",188 "CREATE ROW POLICY default ON mydb.filtered_table3 FOR SELECT USING c = 1 TO default",189 "CREATE ROW POLICY default ON mydb.local FOR SELECT USING 1 TO default"])190 assert node.query("SHOW CREATE POLICIES ON mydb.filtered_table1") == TSV(191 ["CREATE ROW POLICY another ON mydb.filtered_table1 FOR SELECT USING 1 TO another",192 "CREATE ROW POLICY default ON mydb.filtered_table1 FOR SELECT USING a = 1 TO default"])193 assert node.query("SHOW CREATE POLICIES ON mydb.*") == TSV(194 ["CREATE ROW POLICY another ON mydb.filtered_table1 FOR SELECT USING 1 TO another",195 "CREATE ROW POLICY another ON mydb.filtered_table2 FOR SELECT USING 1 TO another",196 "CREATE ROW POLICY another ON mydb.filtered_table3 FOR SELECT USING 1 TO another",197 "CREATE ROW POLICY another ON mydb.local FOR SELECT USING a = 1 TO another",198 "CREATE ROW POLICY default ON mydb.filtered_table1 FOR SELECT USING a = 1 TO default",199 "CREATE ROW POLICY default ON mydb.filtered_table2 FOR SELECT USING ((a + b) < 1) OR ((c - d) > 5) TO default",200 "CREATE ROW POLICY default ON mydb.filtered_table3 FOR SELECT USING c = 1 TO default",201 "CREATE ROW POLICY default ON mydb.local FOR SELECT USING 1 TO default"])202 assert node.query("SHOW CREATE POLICIES") == TSV(203 ["CREATE ROW POLICY another ON mydb.filtered_table1 FOR SELECT USING 1 TO another",204 "CREATE ROW POLICY another ON mydb.filtered_table2 FOR SELECT USING 1 TO another",205 "CREATE ROW POLICY another ON mydb.filtered_table3 FOR SELECT USING 1 TO another",206 "CREATE ROW POLICY another ON mydb.local FOR SELECT USING a = 1 TO another",207 "CREATE ROW POLICY default ON mydb.filtered_table1 FOR SELECT USING a = 1 TO default",208 "CREATE ROW POLICY default ON mydb.filtered_table2 FOR SELECT USING ((a + b) < 1) OR ((c - d) > 5) TO default",209 "CREATE ROW POLICY default ON mydb.filtered_table3 FOR SELECT USING c = 1 TO default",210 "CREATE ROW POLICY default ON mydb.local FOR SELECT USING 1 TO default"])211 expected_access = "CREATE ROW POLICY another ON mydb.filtered_table1 FOR SELECT USING 1 TO another\n" \212 "CREATE ROW POLICY another ON mydb.filtered_table2 FOR SELECT USING 1 TO another\n" \213 "CREATE ROW POLICY another ON mydb.filtered_table3 FOR SELECT USING 1 TO another\n" \214 "CREATE ROW POLICY another ON mydb.local FOR SELECT USING a = 1 TO another\n" \215 "CREATE ROW POLICY default ON mydb.filtered_table1 FOR SELECT USING a = 1 TO default\n" \216 "CREATE ROW POLICY default ON mydb.filtered_table2 FOR SELECT USING ((a + b) < 1) OR ((c - d) > 5) TO default\n" \217 "CREATE ROW POLICY default ON mydb.filtered_table3 FOR SELECT USING c = 1 TO default\n" \218 "CREATE ROW POLICY default ON mydb.local FOR SELECT USING 1 TO default\n"219 assert expected_access in node.query("SHOW ACCESS")220 copy_policy_xml('all_rows.xml')221 assert node.query("SHOW POLICIES") == TSV(222 ["another ON mydb.filtered_table1", "another ON mydb.filtered_table2", "another ON mydb.filtered_table3",223 "default ON mydb.filtered_table1", "default ON mydb.filtered_table2", "default ON mydb.filtered_table3"])224 assert node.query(225 "SHOW CREATE POLICY default ON mydb.filtered_table1") == "CREATE ROW POLICY default ON mydb.filtered_table1 FOR SELECT USING 1 TO default\n"226 assert node.query(227 "SHOW CREATE POLICY default ON mydb.filtered_table2") == "CREATE ROW POLICY default ON mydb.filtered_table2 FOR SELECT USING 1 TO default\n"228 assert node.query(229 "SHOW CREATE POLICY default ON mydb.filtered_table3") == "CREATE ROW POLICY default ON mydb.filtered_table3 FOR SELECT USING 1 TO default\n"230 copy_policy_xml('no_rows.xml')231 assert node.query("SHOW POLICIES") == TSV(232 ["another ON mydb.filtered_table1", "another ON mydb.filtered_table2", "another ON mydb.filtered_table3",233 "default ON mydb.filtered_table1", "default ON mydb.filtered_table2", "default ON mydb.filtered_table3"])234 assert node.query(235 "SHOW CREATE POLICY default ON mydb.filtered_table1") == "CREATE ROW POLICY default ON mydb.filtered_table1 FOR SELECT USING NULL TO default\n"236 assert node.query(237 "SHOW CREATE POLICY default ON mydb.filtered_table2") == "CREATE ROW POLICY default ON mydb.filtered_table2 FOR SELECT USING NULL TO default\n"238 assert node.query(239 "SHOW CREATE POLICY default ON mydb.filtered_table3") == "CREATE ROW POLICY default ON mydb.filtered_table3 FOR SELECT USING NULL TO default\n"240 copy_policy_xml('no_filters.xml')241 assert node.query("SHOW POLICIES") == ""242def test_dcl_management():243 copy_policy_xml('no_filters.xml')244 assert node.query("SHOW POLICIES") == ""245 node.query("CREATE POLICY pA ON mydb.filtered_table1 FOR SELECT USING a<b")246 assert node.query("SELECT * FROM mydb.filtered_table1") == ""247 assert node.query("SHOW POLICIES ON mydb.filtered_table1") == "pA\n"248 node.query("ALTER POLICY pA ON mydb.filtered_table1 TO default")249 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[0, 1]])250 assert node.query("SHOW POLICIES ON mydb.filtered_table1") == "pA\n"251 node.query("ALTER POLICY pA ON mydb.filtered_table1 FOR SELECT USING a>b")252 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[1, 0]])253 node.query("ALTER POLICY pA ON mydb.filtered_table1 RENAME TO pB")254 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[1, 0]])255 assert node.query("SHOW POLICIES ON mydb.filtered_table1") == "pB\n"256 assert node.query(257 "SHOW CREATE POLICY pB ON mydb.filtered_table1") == "CREATE ROW POLICY pB ON mydb.filtered_table1 FOR SELECT USING a > b TO default\n"258 node.query("DROP POLICY pB ON mydb.filtered_table1")259 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[0, 0], [0, 1], [1, 0], [1, 1]])260 assert node.query("SHOW POLICIES") == ""261def test_users_xml_is_readonly():262 assert re.search("storage is readonly", node.query_and_get_error("DROP POLICY default ON mydb.filtered_table1"))263def test_tags_with_db_and_table_names():264 copy_policy_xml('tags_with_db_and_table_names.xml')265 assert node.query("SELECT * FROM mydb.table") == TSV([[0, 0], [0, 1]])266 assert node.query("SELECT * FROM mydb.filtered_table2") == TSV([[0, 0, 6, 0]])267 assert node.query("SELECT * FROM mydb.filtered_table3") == TSV([[0, 0]])268 assert node.query("SELECT * FROM mydb.`.filtered_table4`") == TSV([[1, 1]])269 assert node.query("SHOW CREATE POLICIES default") == TSV(270 ["CREATE ROW POLICY default ON mydb.`.filtered_table4` FOR SELECT USING c = 2 TO default",271 "CREATE ROW POLICY default ON mydb.filtered_table2 FOR SELECT USING c > (d + 5) TO default",272 "CREATE ROW POLICY default ON mydb.filtered_table3 FOR SELECT USING c = 0 TO default",273 "CREATE ROW POLICY default ON mydb.table FOR SELECT USING a = 0 TO default"])274def test_miscellaneous_engines():275 copy_policy_xml('normal_filters.xml')276 # ReplicatedMergeTree277 node.query("DROP TABLE mydb.filtered_table1")278 node.query(279 "CREATE TABLE mydb.filtered_table1 (a UInt8, b UInt8) ENGINE ReplicatedMergeTree('/clickhouse/tables/00-00/filtered_table1', 'replica1') ORDER BY a")280 node.query("INSERT INTO mydb.filtered_table1 values (0, 0), (0, 1), (1, 0), (1, 1)")281 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[1, 0], [1, 1]])282 # CollapsingMergeTree283 node.query("DROP TABLE mydb.filtered_table1")284 node.query("CREATE TABLE mydb.filtered_table1 (a UInt8, b Int8) ENGINE CollapsingMergeTree(b) ORDER BY a")285 node.query("INSERT INTO mydb.filtered_table1 values (0, 1), (0, 1), (1, 1), (1, 1)")286 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[1, 1], [1, 1]])287 # ReplicatedCollapsingMergeTree288 node.query("DROP TABLE mydb.filtered_table1")289 node.query(290 "CREATE TABLE mydb.filtered_table1 (a UInt8, b Int8) ENGINE ReplicatedCollapsingMergeTree('/clickhouse/tables/00-01/filtered_table1', 'replica1', b) ORDER BY a")291 node.query("INSERT INTO mydb.filtered_table1 values (0, 1), (0, 1), (1, 1), (1, 1)")292 assert node.query("SELECT * FROM mydb.filtered_table1") == TSV([[1, 1], [1, 1]])293 # DistributedMergeTree294 node.query("DROP TABLE IF EXISTS mydb.not_filtered_table")295 node.query(296 "CREATE TABLE mydb.not_filtered_table (a UInt8, b UInt8) ENGINE Distributed('test_local_cluster', mydb, local)")297 assert node.query("SELECT * FROM mydb.not_filtered_table", user="another") == TSV([[1, 0], [1, 1], [1, 0], [1, 1]])298 assert node.query("SELECT sum(a), b FROM mydb.not_filtered_table GROUP BY b ORDER BY b", user="another") == TSV(...
dboperation.py
Source:dboperation.py
1import psycopg22from datetime import datetime3class DBOferta:4 def __init__(self):5 pass6class DBOfertadetalle:7 def __init__(self):8 pass9 #QUERY PARA CLASIFICAR LA TUPLAR SEGUN DIMENSION10 def update_tuple(self,connection,ids,equipo):11 mydb = connection.connect()12 fecha_modificacion = datetime.today().strftime('%Y-%m-%d')13 try:14 15 mycursor = mydb.cursor()16 sql = 'UPDATE oferta_detalle SET ofertaperfil_id=3,f_equipo=%s,equipo =%s WHERE id_ofertadetalle IN (' + ','.join(map(str, ids)) + ')' 17 params = (fecha_modificacion,equipo)18 mycursor.execute(sql, params)19 mydb.commit()20 except (Exception, psycopg2.DatabaseError) as error:21 print ("-------------Exception, psycopg2.DatabaseError-------------------")22 print (error)23 print("OFERTA DETALLE UPDATE OFERTA_DETALLE ERROR")24 mydb.close() 25 def filtrar(self,connection,word):26 mydb = connection.connect()27 try:28 mycursor = mydb.cursor()29 sql = """select od.id_ofertadetalle30 from webscraping w inner join oferta o31 on (w.id_webscraping=o.id_webscraping)32 inner join oferta_detalle od33 on (o.id_oferta=od.id_oferta)34 left outer join ofertaperfil_tipo opt35 on (od.ofertaperfil_id=opt.ofertaperfil_id)36 where o.id_estado is null and opt.ofertaperfil_id is null and ind_activo is null37 and ( position( %s in trim(descripcion_normalizada))>038 and %s=139 --or position('VISITA DE INMUEBLES.' in trim(descripcion_normalizada))>040 ) 41 """42 43 params = (word,1)44 mycursor.execute(sql,params)45 array_de_tuplas = []46 row = mycursor.fetchone()47 while row is not None:48 array_de_tuplas.append(row[0])49 row = mycursor.fetchone()50 # close the communication with the PostgreSQL51 mycursor.close()52 mydb.close() 53 except (Exception, psycopg2.DatabaseError) as error:54 print ("-------------Exception, psycopg2.DatabaseError-------------------")55 print (error)56 print("OFERTA DETALLE UPDATE OFERTA_DETALLE ERROR")57 mydb.close()58 return array_de_tuplas59 60 def select_ofertadetalle_dimension(self,connection,dimension):61 mydb = connection.connect()62 try:63 mycursor = mydb.cursor()64 sql = """SELECT id_ofertadetalle,ofertaperfil_id,descripcion,count(*) 65 FROM oferta_detalle 66 WHERE ofertaperfil_id = 367 GROUP BY id_ofertadetalle,ofertaperfil_id,descripcion 68 ORDER BY 1,2,3 ASC69 LIMIT 10070 """71 #params f (requisito["descripcion_normalizada"], requisito["iddescripcion"])72 mycursor.execute(sql)73 array_de_tuplas = []74 row = mycursor.fetchone()75 while row is not None:76 array_de_tuplas.append(row)77 row = mycursor.fetchone()78 # close the communication with the PostgreSQL79 mycursor.close()80 mydb.close() 81 except (Exception, psycopg2.DatabaseError) as error:82 print ("-------------Exception, psycopg2.DatabaseError-------------------")83 print (error)84 print("OFERTA DETALLE UPDATE OFERTA_DETALLE ERROR")85 mydb.close()86 return array_de_tuplas87 def select_ofertadetalle_dimension2(self,connection,dimension):88 mydb = connection.connect()89 try:90 mycursor = mydb.cursor()91 sql = """SELECT id_ofertadetalle,descripcion92 FROM oferta_detalle 93 WHERE ofertaperfil_id = 3 94 GROUP BY id_ofertadetalle,descripcion 95 ORDER BY 1,2 ASC96 LIMIT 500097 """98 #params f (requisito["descripcion_normalizada"], requisito["iddescripcion"])99 mycursor.execute(sql)100 array_de_tuplas = []101 row = mycursor.fetchone()102 while row is not None:103 array_de_tuplas.append(row)104 row = mycursor.fetchone()105 # close the communication with the PostgreSQL106 mycursor.close()107 mydb.close() 108 except (Exception, psycopg2.DatabaseError) as error:109 print ("-------------Exception, psycopg2.DatabaseError-------------------")110 print (error)111 print("OFERTA DETALLE UPDATE OFERTA_DETALLE ERROR")112 mydb.close()113 return array_de_tuplas114 def update_ofertadetalle_normalized(self,connection,requisito,equipo):115 mydb = connection.connect()116 fecha_modificacion = datetime.today().strftime('%Y-%m-%d')117 try:118 mycursor = mydb.cursor()119 sql = "UPDATE OFERTA_DETALLE SET descripcion_normalizada=%s,f_equipo=%s,equipo=%s where id_ofertadetalle=%s"120 #params = (requisito["descripcion_normalizada"], requisito["iddescripcion"])121 params = (requisito[2],fecha_modificacion,equipo,requisito[0])122 mycursor.execute(sql, params)123 mydb.commit()124 except (Exception, psycopg2.DatabaseError) as error:125 print ("-------------Exception, psycopg2.DatabaseError-------------------")126 print (error)127 print("OFERTA DETALLE UPDATE OFERTA_DETALLE ERROR")128 mydb.close()129 def update_ofertadetalle_normalized2(self,connection,requisito,equipo):130 mydb = connection.connect()131 fecha_modificacion = datetime.today().strftime('%Y-%m-%d')132 try:133 mycursor = mydb.cursor()134 sql = "CREATE TEMP TABLE temp_od(DKEY INTEGER, DVALUE TEXT)"135 mycursor.execute(sql)136 print("CREANDO....")137 mydb.commit()138 sql = "INSERT INTO temp_od (DKEY, DVALUE) VALUES(%s, %s)"139 print("INSERTANDO....")140 mycursor.executemany(sql, requisito)141 mydb.commit()142 sql = """UPDATE oferta_detalle143 SET descripcion_normalizada = temp_od.dvalue144 FROM temp_od145 WHERE oferta_detalle.id_ofertadetalle = temp_od.dkey;"""146 print("ACTUALIZANDO....") 147 mycursor.execute(sql) 148 mydb.commit()149 except (Exception, psycopg2.DatabaseError) as error:150 print ("-------------Exception, psycopg2.DatabaseError-------------------")151 print (error)152 print("OFERTA DETALLE UPDATE OFERTA_DETALLE ERROR")153 mydb.close() 154 def select_ofertadetalle(self,connection):155 mydb = connection.connect()156 try:157 mycursor = mydb.cursor()158 sql = "SELECT * FROM OFERTA_DETALLE LIMIT 1"159 #params = (requisito["descripcion_normalizada"], requisito["iddescripcion"])160 mycursor.execute(sql)161 array_de_tuplas = []162 row = mycursor.fetchone()163 while row is not None:164 array_de_tuplas.append(row)165 row = mycursor.fetchone()166 # close the communication with the PostgreSQL167 mycursor.close()168 mydb.close() 169 except (Exception, psycopg2.DatabaseError) as error:170 print ("-------------Exception, psycopg2.DatabaseError-------------------")171 print (error)172 print("OFERTA DETALLE UPDATE OFERTA_DETALLE ERROR")173 mydb.close()174 return array_de_tuplas175 def update_ofertadetalle(self, connection, requisito):176 mydb = connection.connect()177 fecha_modificacion = datetime.today().strftime('%Y-%m-%d')178 try:179 mycursor = mydb.cursor()180 sql = "UPDATE OFERTA_DETALLE SET descripcion_normalizada=:1,fecha_equipo=:2 where id_ofertadetalle=:3"181 params = (requisito["descripcion_normalizada"],fecha_modificacion, requisito["iddescripcion"])182 mycursor.execute(sql, params)183 mydb.commit()184 except (Exception, psycopg2.DatabaseError) as error:185 print ("-------------Exception, psycopg2.DatabaseError-------------------")186 print (error)187 print("OFERTA DETALLE UPDATE OFERTA_DETALLE ERROR")188 mydb.close()189 def insertOfertaDetalle(self, connection, listaDetalle):190 mydb = connection.connect()191 try:192 #mydb= connection.connect()193 mycursor= mydb.cursor()194 for detalle in listaDetalle:195 sql= "insert into oferta_detalle ( id_ofertadetalle, id_oferta, descripcion, fecha_creacion, fecha_modificacion) values (DEFAULT,%s,%s,current_date,current_date)"196 params= (detalle["id_oferta"],detalle["descripcion"].strip())197 mycursor.execute(sql, params)198 mydb.commit()199 # close the communication with the PostgreSQL200 mycursor.close()201 mydb.close()202 except (Exception, psycopg2.DatabaseError) as error:203 print ("-------------Exception, psycopg2.DatabaseError-------------------")204 print (error)205 print("OFERTA DETALLE INSERTAR OFERTA_DETALLE ERROR")206 mydb.close()207 208 return 1209 def update_prueba(self,connection,requisito):210 mydb = connection.connect()211 try:212 mycursor = mydb.cursor()213 sql = "UPDATE alumno SET nombre=%s where id=%s"214 #params = (requisito["nombre"], requisito["id"])215 params = (requisito[1], requisito[0])216 mycursor.execute(sql, params)217 mydb.commit()218 except (Exception, psycopg2.DatabaseError) as error:219 print ("-------------Exception, psycopg2.DatabaseError-------------------")220 print (error)221 print("OFERTA DETALLE UPDATE OFERTA_DETALLE ERROR")222 mydb.close()223 def select_prueba(self,connection):224 mydb = connection.connect()225 try:226 mycursor = mydb.cursor()227 sql = "SELECT * FROM alumno"228 #params = (requisito["descripcion_normalizada"], requisito["iddescripcion"])229 mycursor.execute(sql)230 array_de_tuplas = []231 row = mycursor.fetchone()232 while row is not None:233 array_de_tuplas.append(row)234 row = mycursor.fetchone()235 # close the communication with the PostgreSQL236 mycursor.close()237 mydb.close() 238 except (Exception, psycopg2.DatabaseError) as error:239 print ("-------------Exception, psycopg2.DatabaseError-------------------")240 print (error)241 print("OFERTA DETALLE UPDATE OFERTA_DETALLE ERROR")242 mydb.close()243 return array_de_tuplas244 245class DatesDB:246 def __init__(self):...
bbdd.py
Source:bbdd.py
1import os2# import sys3import mysql.connector4import json5from datetime import datetime6# from datetime import timedelta7import pytz8def connect(mode):9 if mode == 'dev':10 # Conexión a BBDD11 with open('var_entorno.json','r') as vars_ent:12 vars_ent_json = json.loads(vars_ent.read())13 mydb = mysql.connector.connect(host=vars_ent_json['HOSTNAME_DB'],14 user=vars_ent_json['USERNAME_DB'],15 passwd=vars_ent_json['PASSWORD_DB'],16 db=vars_ent_json['DBNAME_DB'],17 port=vars_ent_json['PORT_DB'])18 return mydb19 elif mode== 'prod':20 # Conexión a BBDD21 mydb = mysql.connector.connect(host=os.getenv('HOSTNAME_DB'),22 user=os.getenv('USERNAME_DB'),23 passwd=os.getenv('PASSWORD_DB'),24 db=os.getenv('DBNAME_DB'),25 port=os.getenv('PORT_DB'))26 return mydb27 else:28 raise 'Mode is not defined'29def insert_new_game(date,time,n_jugadores):30 mydb = connect(os.getenv("MODE"))31 mycursor = mydb.cursor()32 sql = "INSERT INTO games (date, hour, active, players_per_team) VALUES (%s, %s, %s, %s)"33 val = (date, time,True, n_jugadores)34 mycursor.execute(sql, val)35 mydb.commit()36 mydb.close()37def get_games_active():38 mydb = connect(os.getenv("MODE"))39 mycursor = mydb.cursor()40 mycursor.execute("SELECT id, date, hour, players_per_team, active FROM games WHERE active is true")41 myresult = mycursor.fetchall()42 mydb.close()43 return myresult44def insert_new_player(id_player, first_name, last_name):45 mydb = connect(os.getenv("MODE"))46 mycursor = mydb.cursor()47 sql = f"INSERT INTO players (id, first_name, last_name) VALUES (%s, %s, %s)"48 val = (id_player, first_name, last_name)49 try:50 mycursor.execute(sql, val)51 mydb.commit()52 except:53 pass54 mydb.close()55def set_alias(id_player, alias):56 mydb = connect(os.getenv("MODE"))57 mycursor = mydb.cursor()58 sql = f"UPDATE players SET alias = %s WHERE id = %s;"59 val = (alias,id_player)60 mycursor.execute(sql, val)61 mydb.commit()62 mydb.close()63def insert_player_in_game(id_player, id_game, headline, message_id):64 mydb = connect(os.getenv("MODE"))65 mycursor = mydb.cursor()66 sql = f"INSERT INTO players_games (player_id, game_id, headline, message_id) VALUES (%s, %s, %s, %s)"67 val = (id_player, id_game, headline, message_id)68 mycursor.execute(sql, val)69 mydb.commit()70 mydb.close()71def check_exist_ban_player(id_player):72 mydb = connect(os.getenv("MODE"))73 mycursor = mydb.cursor()74 mycursor.execute(f"SELECT id, until_ban_date FROM players WHERE id = {id_player}")75 myresult = mycursor.fetchone()76 if myresult != None:77 exist = True78 if myresult[1] == None:79 ban = False80 date_ban = None81 else:82 date_ban = datetime.strptime(str(myresult[1]), '%Y-%m-%d')83 dif_date = (date_ban.date() - datetime.now(pytz.timezone('America/Argentina/Mendoza')).date()).days84 if dif_date > 0:85 ban = True86 date_ban = date_ban.strftime('%d/%m/%Y')87 else:88 ban = False89 date_ban = None90 down_ban(id_player)91 else:92 exist = False93 ban = None94 date_ban = False95 mydb.close()96 return {'exist': exist, 'ban': ban, 'date_ban': date_ban}97def get_players_game(id_game):98 mydb = connect(os.getenv("MODE"))99 mycursor = mydb.cursor()100 mycursor.execute(f"SELECT pg.player_id, p.first_name, p.last_name, p.alias, pg.headline FROM players_games pg LEFT JOIN players p ON p.id = pg.player_id WHERE pg.game_id = {id_game}")101 myresult = mycursor.fetchall()102 mydb.close()103 return myresult104def get_game_info(id_game):105 mydb = connect(os.getenv("MODE"))106 mycursor = mydb.cursor()107 mycursor.execute(f"SELECT id, date, hour, players_per_team, active FROM games WHERE id = {id_game}")108 myresult = mycursor.fetchone()109 mydb.close()110 return myresult111def ban_player(id_player,date):112 mydb = connect(os.getenv("MODE"))113 mycursor = mydb.cursor()114 sql = f"UPDATE players SET until_ban_date = %s WHERE id = %s;"115 val = (date ,id_player)116 mycursor.execute(sql, val)117 mydb.commit()118 mycursor.execute(f"SELECT id, first_name, last_name, alias, until_ban_date FROM players WHERE id = {id_player}")119 myresult = mycursor.fetchone()120 mydb.close()121 return myresult122def get_ban_players():123 mydb = connect(os.getenv("MODE"))124 mycursor = mydb.cursor()125 mycursor.execute("SELECT id, first_name, last_name, alias, until_ban_date FROM players WHERE until_ban_date is not null")126 myresult = mycursor.fetchall()127 mydb.close()128 return myresult129def get_players():130 mydb = connect(os.getenv("MODE"))131 mycursor = mydb.cursor()132 mycursor.execute("SELECT id, first_name, last_name, alias, until_ban_date FROM players")133 myresult = mycursor.fetchall()134 mydb.close()135 return myresult136def get_player_info(id_player):137 mydb = connect(os.getenv("MODE"))138 mycursor = mydb.cursor()139 mycursor.execute(f"SELECT id, first_name, last_name, alias, until_ban_date FROM players WHERE id = {id_player}")140 myresult = mycursor.fetchone()141 mydb.close()142 return myresult143def down_ban(id_player):144 mydb = connect(os.getenv("MODE"))145 mycursor = mydb.cursor()146 mycursor.execute(f"UPDATE players SET until_ban_date = null WHERE id = {id_player}")147 mydb.commit()148 mycursor.execute(f"SELECT id, first_name, last_name, alias, until_ban_date FROM players WHERE id = {id_player}")149 myresult = mycursor.fetchone()150 mydb.close()151 return myresult152def deannotate_player(id_player, id_game):153 mydb = connect(os.getenv("MODE"))154 mycursor = mydb.cursor()155 mycursor.execute(f"DELETE FROM players_games WHERE player_id = {id_player} and game_id = {id_game}")156 row_count = mycursor.rowcount157 mydb.commit()158 return row_count159def deactivate_game(id_game):160 mydb = connect(os.getenv("MODE"))161 mycursor = mydb.cursor()162 mycursor.execute(f"UPDATE games SET active = false WHERE id = {id_game}")163 row_count = mycursor.rowcount164 mydb.commit()165 return row_count166def get_recent_sup_player(id_game):167 mydb = connect(os.getenv("MODE"))168 mycursor = mydb.cursor()169 mycursor.execute(f"SELECT player_id FROM players_games WHERE game_id = {id_game} AND headline = false ORDER BY message_id asc")170 myresult = mycursor.fetchone()171 mydb.close()172 return myresult173def set_headline_1(id_player, id_game):174 mydb = connect(os.getenv("MODE"))175 mycursor = mydb.cursor()176 mycursor.execute(f"UPDATE players_games SET headline = true WHERE game_id = {id_game} and player_id = {id_player}")177 # row_count = mycursor.rowcount178 mydb.commit()...
Automation Testing Tutorials
Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.
LambdaTest Learning Hubs:
- JUnit Tutorial
- TestNG Tutorial
- Webdriver Tutorial
- WebDriverIO Tutorial
- Protractor Tutorial
- Selenium 4 Tutorial
- Jenkins Tutorial
- NUnit Tutorial
- Jest Tutorial
- Playwright Tutorial
- Cypress Tutorial
- PyTest Tutorial
YouTube
You could also refer to video tutorials over LambdaTest YouTube channel to get step by step demonstration from industry experts.
Run pytest-django automation tests on LambdaTest cloud grid
Perform automation testing on 3000+ real desktop and mobile devices online.
Try LambdaTest Now !!
Get 100 minutes of automation test minutes FREE!!
Was this article helpful?
