def test_can_insert_udts_with_nulls(self): """ Test the insertion of UDTs with null and empty string fields """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect(self.keyspace_name, wait_for_all_pools=True) s.execute("CREATE TYPE user (a text, b int, c uuid, d blob)") User = namedtuple('user', ('a', 'b', 'c', 'd')) c.register_user_type(self.keyspace_name, "user", User) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") insert = s.prepare("INSERT INTO mytable (a, b) VALUES (0, ?)") s.execute(insert, [User(None, None, None, None)]) results = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual((None, None, None, None), results[0].b) select = s.prepare("SELECT b FROM mytable WHERE a=0") self.assertEqual((None, None, None, None), s.execute(select)[0].b) # also test empty strings s.execute(insert, [User('', None, None, six.binary_type())]) results = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual(('', None, None, six.binary_type()), results[0].b) c.shutdown()
def test_can_register_udt_before_connecting(self): """ Test the registration of UDTs before session creation """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() s.execute( """ CREATE KEYSPACE udt_test_register_before_connecting WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """ ) s.set_keyspace("udt_test_register_before_connecting") s.execute("CREATE TYPE user (age int, name text)") s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") s.execute( """ CREATE KEYSPACE udt_test_register_before_connecting2 WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """ ) s.set_keyspace("udt_test_register_before_connecting2") s.execute("CREATE TYPE user (state text, is_cool boolean)") s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") # now that types are defined, shutdown and re-create Cluster c.shutdown() c = Cluster(protocol_version=PROTOCOL_VERSION) User1 = namedtuple("user", ("age", "name")) User2 = namedtuple("user", ("state", "is_cool")) c.register_user_type("udt_test_register_before_connecting", "user", User1) c.register_user_type("udt_test_register_before_connecting2", "user", User2) s = c.connect() s.set_keyspace("udt_test_register_before_connecting") s.execute("INSERT INTO mytable (a, b) VALUES (%s, %s)", (0, User1(42, "bob"))) result = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual(1, len(result)) row = result[0] self.assertEqual(42, row.b.age) self.assertEqual("bob", row.b.name) self.assertTrue(type(row.b) is User1) # use the same UDT name in a different keyspace s.set_keyspace("udt_test_register_before_connecting2") s.execute("INSERT INTO mytable (a, b) VALUES (%s, %s)", (0, User2("Texas", True))) result = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual(1, len(result)) row = result[0] self.assertEqual("Texas", row.b.state) self.assertEqual(True, row.b.is_cool) self.assertTrue(type(row.b) is User2) c.shutdown()
def main(): print("--- %s seconds --- START" % (time.time() - start_time)) cluster = Cluster(['192.168.1.141'], port=30054) session = cluster.connect() print("--- %s seconds ---CONNECTED" % (time.time() - start_time)) try: session.set_keyspace('mykeyspace') except: session.execute(""" CREATE KEYSPACE mykeyspace WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };""") session.set_keyspace('mykeyspace') # session.execute("CREATE TYPE address (street text, zipcode int)") # session.execute("CREATE TABLE users (id int PRIMARY KEY, location address)") cluster.register_user_type('mykeyspace', 'address', Address) # insert a row using an instance of Address for item in range(10): ADDRR = "".join([random.choice(string.letters) for i in xrange(10)]) session.execute( "INSERT INTO users (id, location) VALUES (%s, %s)", (item, Address("123 Main St." + ADDRR, random.randint(1, 99999)))) # results will include Address instances print("--- %s seconds ---INSERTED" % (time.time() - start_time)) results = session.execute("SELECT * FROM users") for row in sorted(results, key=lambda person: person[0]): print row.id, row.location.street, row.location.zipcode
def test_can_insert_nested_registered_udts(self): """ Test for ensuring nested registered udts are properly inserted """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect("udttests") s.row_factory = dict_factory MAX_NESTING_DEPTH = 16 # create the schema self.nested_udt_schema_helper(s, MAX_NESTING_DEPTH) # create and register the seed udt type udts = [] udt = namedtuple('depth_0', ('age', 'name')) udts.append(udt) c.register_user_type("udttests", "depth_0", udts[0]) # create and register the nested udt types for i in range(MAX_NESTING_DEPTH): udt = namedtuple('depth_{0}'.format(i + 1), ('value')) udts.append(udt) c.register_user_type("udttests", "depth_{0}".format(i + 1), udts[i + 1]) # insert udts and verify inserts with reads self.nested_udt_verification_helper(s, MAX_NESTING_DEPTH, udts) c.shutdown()
def test_can_insert_udts_with_nulls(self): """ Test the insertion of UDTs with null and empty string fields """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect(self.keyspace_name, wait_for_all_pools=True) s.execute("CREATE TYPE user (a text, b int, c uuid, d blob)") User = namedtuple('user', ('a', 'b', 'c', 'd')) c.register_user_type(self.keyspace_name, "user", User) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") insert = s.prepare("INSERT INTO mytable (a, b) VALUES (0, ?)") s.execute(insert, [User(None, None, None, None)]) results = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual((None, None, None, None), results[0].b) select = s.prepare("SELECT b FROM mytable WHERE a=0") self.assertEqual((None, None, None, None), s.execute(select)[0].b) # also test empty strings s.execute(insert, [User('', None, None, six.binary_type())]) results = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual(('', None, None, six.binary_type()), results[0].b) c.shutdown()
def test_can_insert_udts_with_nulls(self): """ Test the insertion of UDTs with null and empty string fields """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect("udttests") s.execute("CREATE TYPE user (a text, b int, c uuid, d blob)") User = namedtuple("user", ("a", "b", "c", "d")) c.register_user_type("udttests", "user", User) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") insert = s.prepare("INSERT INTO mytable (a, b) VALUES (0, ?)") s.execute(insert, [User(None, None, None, None)]) results = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual((None, None, None, None), results[0].b) select = s.prepare("SELECT b FROM mytable WHERE a=0") self.assertEqual((None, None, None, None), s.execute(select)[0].b) # also test empty strings s.execute(insert, [User("", None, None, "")]) results = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual(("", None, None, ""), results[0].b) self.assertEqual(("", None, None, ""), s.execute(select)[0].b) c.shutdown()
def test_can_insert_nested_registered_udts(self): """ Test for ensuring nested registered udts are properly inserted """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect("udttests") s.row_factory = dict_factory MAX_NESTING_DEPTH = 16 # create the schema self.nested_udt_schema_helper(s, MAX_NESTING_DEPTH) # create and register the seed udt type udts = [] udt = namedtuple("depth_0", ("age", "name")) udts.append(udt) c.register_user_type("udttests", "depth_0", udts[0]) # create and register the nested udt types for i in range(MAX_NESTING_DEPTH): udt = namedtuple("depth_{}".format(i + 1), ("value")) udts.append(udt) c.register_user_type("udttests", "depth_{}".format(i + 1), udts[i + 1]) # insert udts and verify inserts with reads self.nested_udt_verification_helper(s, MAX_NESTING_DEPTH, udts) c.shutdown()
def connect(self): if self.initialized is False: raise Exception( 'Please initialize the connection parameters first with SessionManager.save_credentials' ) if self._session is None: # This is how you use the Astra secure connect bundle to connect to an Astra database # note that the database username and password required. # note that no contact points or any other driver customization is required. astra_config = { 'secure_connect_bundle': self.secure_connect_bundle_path } cluster = Cluster(cloud=astra_config, auth_provider=PlainTextAuthProvider( self.username, self.password)) self._session = cluster.connect(keyspace=self.keyspace) # have the driver return results as dict self._session.row_factory = dict_factory # have the driver return LocationUDT as a dict cluster.register_user_type(self.keyspace, 'location_udt', dict) return self._session
def test_udts_with_nulls(self): """ Test UDTs with null and empty string fields. """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() s.execute(""" CREATE KEYSPACE test_udts_with_nulls WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """) s.set_keyspace("test_udts_with_nulls") s.execute("CREATE TYPE user (a text, b int, c uuid, d blob)") User = namedtuple('user', ('a', 'b', 'c', 'd')) c.register_user_type("test_udts_with_nulls", "user", User) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") insert = s.prepare("INSERT INTO mytable (a, b) VALUES (0, ?)") s.execute(insert, [User(None, None, None, None)]) results = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual((None, None, None, None), results[0].b) select = s.prepare("SELECT b FROM mytable WHERE a=0") self.assertEqual((None, None, None, None), s.execute(select)[0].b) # also test empty strings s.execute(insert, [User('', None, None, '')]) results = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual(('', None, None, ''), results[0].b) self.assertEqual(('', None, None, ''), s.execute(select)[0].b) c.shutdown()
def test_can_insert_nested_registered_udts_with_different_namedtuples(self): """ Test for ensuring nested udts are inserted correctly when the created namedtuples are use names that are different the cql type. """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect(self.keyspace_name, wait_for_all_pools=True) s.row_factory = dict_factory MAX_NESTING_DEPTH = 16 # create the schema self.nested_udt_schema_helper(s, MAX_NESTING_DEPTH) # create and register the seed udt type udts = [] udt = namedtuple('level_0', ('age', 'name')) udts.append(udt) c.register_user_type(self.keyspace_name, "depth_0", udts[0]) # create and register the nested udt types for i in range(MAX_NESTING_DEPTH): udt = namedtuple('level_{0}'.format(i + 1), ('value')) udts.append(udt) c.register_user_type(self.keyspace_name, "depth_{0}".format(i + 1), udts[i + 1]) # insert udts and verify inserts with reads self.nested_udt_verification_helper(s, MAX_NESTING_DEPTH, udts) c.shutdown()
def test_can_insert_nested_registered_udts_with_different_namedtuples( self): """ Test for ensuring nested udts are inserted correctly when the created namedtuples are use names that are different the cql type. """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect(self.keyspace_name, wait_for_all_pools=True) s.row_factory = dict_factory MAX_NESTING_DEPTH = 16 # create the schema self.nested_udt_schema_helper(s, MAX_NESTING_DEPTH) # create and register the seed udt type udts = [] udt = namedtuple('level_0', ('age', 'name')) udts.append(udt) c.register_user_type(self.keyspace_name, "depth_0", udts[0]) # create and register the nested udt types for i in range(MAX_NESTING_DEPTH): udt = namedtuple('level_{0}'.format(i + 1), ('value')) udts.append(udt) c.register_user_type(self.keyspace_name, "depth_{0}".format(i + 1), udts[i + 1]) # insert udts and verify inserts with reads self.nested_udt_verification_helper(s, MAX_NESTING_DEPTH, udts) c.shutdown()
def test_can_register_udt_before_connecting(self): """ Test the registration of UDTs before session creation """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() s.execute(""" CREATE KEYSPACE udt_test_register_before_connecting WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """) s.set_keyspace("udt_test_register_before_connecting") s.execute("CREATE TYPE user (age int, name text)") s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") s.execute(""" CREATE KEYSPACE udt_test_register_before_connecting2 WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """) s.set_keyspace("udt_test_register_before_connecting2") s.execute("CREATE TYPE user (state text, is_cool boolean)") s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") # now that types are defined, shutdown and re-create Cluster c.shutdown() c = Cluster(protocol_version=PROTOCOL_VERSION) User1 = namedtuple('user', ('age', 'name')) User2 = namedtuple('user', ('state', 'is_cool')) c.register_user_type("udt_test_register_before_connecting", "user", User1) c.register_user_type("udt_test_register_before_connecting2", "user", User2) s = c.connect() s.set_keyspace("udt_test_register_before_connecting") s.execute("INSERT INTO mytable (a, b) VALUES (%s, %s)", (0, User1(42, 'bob'))) result = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual(1, len(result)) row = result[0] self.assertEqual(42, row.b.age) self.assertEqual('bob', row.b.name) self.assertTrue(type(row.b) is User1) # use the same UDT name in a different keyspace s.set_keyspace("udt_test_register_before_connecting2") s.execute("INSERT INTO mytable (a, b) VALUES (%s, %s)", (0, User2('Texas', True))) result = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual(1, len(result)) row = result[0] self.assertEqual('Texas', row.b.state) self.assertEqual(True, row.b.is_cool) self.assertTrue(type(row.b) is User2) c.shutdown()
def connect(self): if self.initialized is False: raise Exception('Please initialize the connection parameters first with SessionManager.save_credentials') if self._session is None: # This is how you use the Astra secure connect bundle to connect to an Astra database # note that the database username and password required. # note that no contact points or any other driver customization is required. if os.getenv('USE_ASTRA') == 'true': astra_config = { 'secure_connect_bundle': self.secure_connect_bundle_path } cluster = Cluster(cloud=astra_config, auth_provider=PlainTextAuthProvider(self.username, self.password)) elif os.getenv('USE_ASTRA') == false: cluster = Cluster([os.getenv('CONNECTION_POINTS')],auth_provider=PlainTextAuthProvider(os.getenv('USERNAME'), os.getenv('PASSWORD'))) self.keyspace = os.getenv('KEYSPACE') self.initialized = True else: logger.info("Missing env value for USE_ASTRA") self._session = cluster.connect(keyspace=self.keyspace) # have the driver return results as dict self._session.row_factory = dict_factory # have the driver return LocationUDT as a dict cluster.register_user_type(self.keyspace, 'location_udt', dict) return self._session
def test_udts_with_nulls(self): """ Test UDTs with null and empty string fields. """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() s.execute(""" CREATE KEYSPACE test_udts_with_nulls WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """) s.set_keyspace("test_udts_with_nulls") s.execute("CREATE TYPE user (a text, b int, c uuid, d blob)") User = namedtuple('user', ('a', 'b', 'c', 'd')) c.register_user_type("test_udts_with_nulls", "user", User) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b user)") insert = s.prepare("INSERT INTO mytable (a, b) VALUES (0, ?)") s.execute(insert, [User(None, None, None, None)]) results = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual((None, None, None, None), results[0].b) select = s.prepare("SELECT b FROM mytable WHERE a=0") self.assertEqual((None, None, None, None), s.execute(select)[0].b) # also test empty strings s.execute(insert, [User('', None, None, '')]) results = s.execute("SELECT b FROM mytable WHERE a=0") self.assertEqual(('', None, None, ''), results[0].b) self.assertEqual(('', None, None, ''), s.execute(select)[0].b) c.shutdown()
def test_can_insert_udt_all_collection_datatypes(self): """ Test for inserting various types of COLLECTION_TYPES into UDT's """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect("udttests") # create UDT alpha_type_list = [] start_index = ord('a') for i, collection_type in enumerate(COLLECTION_TYPES): for j, datatype in enumerate(PRIMITIVE_DATATYPES): if collection_type == "map": type_string = "{0}_{1} {2}<{3}, {3}>".format(chr(start_index + i), chr(start_index + j), collection_type, datatype) elif collection_type == "tuple": type_string = "{0}_{1} frozen<{2}<{3}>>".format(chr(start_index + i), chr(start_index + j), collection_type, datatype) else: type_string = "{0}_{1} {2}<{3}>".format(chr(start_index + i), chr(start_index + j), collection_type, datatype) alpha_type_list.append(type_string) s.execute(""" CREATE TYPE alldatatypes ({0}) """.format(', '.join(alpha_type_list)) ) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<alldatatypes>)") # register UDT alphabet_list = [] for i in range(ord('a'), ord('a') + len(COLLECTION_TYPES)): for j in range(ord('a'), ord('a') + len(PRIMITIVE_DATATYPES)): alphabet_list.append('{0}_{1}'.format(chr(i), chr(j))) Alldatatypes = namedtuple("alldatatypes", alphabet_list) c.register_user_type("udttests", "alldatatypes", Alldatatypes) # insert UDT data params = [] for collection_type in COLLECTION_TYPES: for datatype in PRIMITIVE_DATATYPES: params.append((get_collection_sample(collection_type, datatype))) insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, Alldatatypes(*params))) # retrieve and verify data results = s.execute("SELECT * FROM mytable") self.assertEqual(1, len(results)) row = results[0].b for expected, actual in zip(params, row): self.assertEqual(expected, actual) c.shutdown()
def test_udt_sizes(self): """ Test for ensuring extra-lengthy udts are handled correctly. """ if self._cass_version < (2, 1, 0): raise unittest.SkipTest( "The tuple type was introduced in Cassandra 2.1") MAX_TEST_LENGTH = 16384 EXTENDED_QUERY_TIMEOUT = 60 c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() s.execute("""CREATE KEYSPACE test_udt_sizes WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1'}""" ) s.set_keyspace("test_udt_sizes") # create the seed udt, increase timeout to avoid the query failure on slow systems s.execute("CREATE TYPE lengthy_udt ({})".format(', '.join( ['v_{} int'.format(i) for i in range(MAX_TEST_LENGTH)])), timeout=EXTENDED_QUERY_TIMEOUT) # create a table with multiple sizes of nested udts # no need for all nested types, only a spot checked few and the largest one s.execute( "CREATE TABLE mytable (" "k int PRIMARY KEY, " "v frozen<lengthy_udt>)", timeout=EXTENDED_QUERY_TIMEOUT) # create and register the seed udt type udt = namedtuple( 'lengthy_udt', tuple(['v_{}'.format(i) for i in range(MAX_TEST_LENGTH)])) c.register_user_type("test_udt_sizes", "lengthy_udt", udt) # verify inserts and reads for i in (0, 1, 2, 3, MAX_TEST_LENGTH): # create udt params = [j for j in range(i) ] + [None for j in range(MAX_TEST_LENGTH - i)] created_udt = udt(*params) # write udt s.execute("INSERT INTO mytable (k, v) VALUES (0, %s)", (created_udt, )) # verify udt was written and read correctly, increase timeout to avoid the query failure on slow systems result = s.execute("SELECT v FROM mytable WHERE k=0", timeout=EXTENDED_QUERY_TIMEOUT)[0] self.assertEqual(created_udt, result.v) c.shutdown()
def test_prepared_registered_udts(self): c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() s.execute( """ CREATE KEYSPACE udt_test_prepared_registered WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """ ) s.set_keyspace("udt_test_prepared_registered") s.execute("CREATE TYPE user (age int, name text)") User = namedtuple("user", ("age", "name")) c.register_user_type("udt_test_prepared_registered", "user", User) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b user)") insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, User(42, "bob"))) select = s.prepare("SELECT b FROM mytable WHERE a=?") result = s.execute(select, (0,)) self.assertEqual(1, len(result)) row = result[0] self.assertEqual(42, row.b.age) self.assertEqual("bob", row.b.name) self.assertTrue(type(row.b) is User) # use the same UDT name in a different keyspace s.execute( """ CREATE KEYSPACE udt_test_prepared_registered2 WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """ ) s.set_keyspace("udt_test_prepared_registered2") s.execute("CREATE TYPE user (state text, is_cool boolean)") User = namedtuple("user", ("state", "is_cool")) c.register_user_type("udt_test_prepared_registered2", "user", User) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b user)") insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, User("Texas", True))) select = s.prepare("SELECT b FROM mytable WHERE a=?") result = s.execute(select, (0,)) self.assertEqual(1, len(result)) row = result[0] self.assertEqual("Texas", row.b.state) self.assertEqual(True, row.b.is_cool) self.assertTrue(type(row.b) is User) c.shutdown()
def test_primitive_datatypes(self): """ Test for inserting various types of DATA_TYPE_PRIMITIVES into UDT's """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() # create keyspace s.execute(""" CREATE KEYSPACE test_primitive_datatypes WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """) s.set_keyspace("test_primitive_datatypes") # create UDT alpha_type_list = [] start_index = ord('a') for i, datatype in enumerate(DATA_TYPE_PRIMITIVES): alpha_type_list.append("{0} {1}".format(chr(start_index + i), datatype)) s.execute(""" CREATE TYPE alldatatypes ({0}) """.format(', '.join(alpha_type_list))) s.execute( "CREATE TABLE mytable (a int PRIMARY KEY, b frozen<alldatatypes>)") # register UDT alphabet_list = [] for i in range(ord('a'), ord('a') + len(DATA_TYPE_PRIMITIVES)): alphabet_list.append('{}'.format(chr(i))) Alldatatypes = namedtuple("alldatatypes", alphabet_list) c.register_user_type("test_primitive_datatypes", "alldatatypes", Alldatatypes) # insert UDT data params = [] for datatype in DATA_TYPE_PRIMITIVES: params.append((get_sample(datatype))) insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, Alldatatypes(*params))) # retrieve and verify data results = s.execute("SELECT * FROM mytable") self.assertEqual(1, len(results)) row = results[0].b for expected, actual in zip(params, row): self.assertEqual(expected, actual) c.shutdown()
def test_can_insert_prepared_registered_udts(self): """ Test the insertion of prepared, registered UDTs """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect("udttests") s.execute("CREATE TYPE user (age int, name text)") User = namedtuple('user', ('age', 'name')) c.register_user_type("udttests", "user", User) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, User(42, 'bob'))) select = s.prepare("SELECT b FROM mytable WHERE a=?") result = s.execute(select, (0,)) self.assertEqual(1, len(result)) row = result[0] self.assertEqual(42, row.b.age) self.assertEqual('bob', row.b.name) self.assertTrue(type(row.b) is User) # use the same UDT name in a different keyspace s.execute(""" CREATE KEYSPACE udt_test_prepared_registered2 WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """) s.set_keyspace("udt_test_prepared_registered2") s.execute("CREATE TYPE user (state text, is_cool boolean)") User = namedtuple('user', ('state', 'is_cool')) c.register_user_type("udt_test_prepared_registered2", "user", User) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, User('Texas', True))) select = s.prepare("SELECT b FROM mytable WHERE a=?") result = s.execute(select, (0,)) self.assertEqual(1, len(result)) row = result[0] self.assertEqual('Texas', row.b.state) self.assertEqual(True, row.b.is_cool) self.assertTrue(type(row.b) is User) s.execute("DROP KEYSPACE udt_test_prepared_registered2") c.shutdown()
def test_primitive_datatypes(self): """ Test for inserting various types of DATA_TYPE_PRIMITIVES into UDT's """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() # create keyspace s.execute(""" CREATE KEYSPACE test_primitive_datatypes WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """) s.set_keyspace("test_primitive_datatypes") # create UDT alpha_type_list = [] start_index = ord('a') for i, datatype in enumerate(DATA_TYPE_PRIMITIVES): alpha_type_list.append("{0} {1}".format(chr(start_index + i), datatype)) s.execute(""" CREATE TYPE alldatatypes ({0}) """.format(', '.join(alpha_type_list)) ) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<alldatatypes>)") # register UDT alphabet_list = [] for i in range(ord('a'), ord('a') + len(DATA_TYPE_PRIMITIVES)): alphabet_list.append('{}'.format(chr(i))) Alldatatypes = namedtuple("alldatatypes", alphabet_list) c.register_user_type("test_primitive_datatypes", "alldatatypes", Alldatatypes) # insert UDT data params = [] for datatype in DATA_TYPE_PRIMITIVES: params.append((get_sample(datatype))) insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, Alldatatypes(*params))) # retrieve and verify data results = s.execute("SELECT * FROM mytable") self.assertEqual(1, len(results)) row = results[0].b for expected, actual in zip(params, row): self.assertEqual(expected, actual) c.shutdown()
def test_prepared_registered_udts(self): c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() s.execute(""" CREATE KEYSPACE udt_test_prepared_registered WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """) s.set_keyspace("udt_test_prepared_registered") s.execute("CREATE TYPE user (age int, name text)") User = namedtuple('user', ('age', 'name')) c.register_user_type("udt_test_prepared_registered", "user", User) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b user)") insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, User(42, 'bob'))) select = s.prepare("SELECT b FROM mytable WHERE a=?") result = s.execute(select, (0, )) self.assertEqual(1, len(result)) row = result[0] self.assertEqual(42, row.b.age) self.assertEqual('bob', row.b.name) self.assertTrue(type(row.b) is User) # use the same UDT name in a different keyspace s.execute(""" CREATE KEYSPACE udt_test_prepared_registered2 WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """) s.set_keyspace("udt_test_prepared_registered2") s.execute("CREATE TYPE user (state text, is_cool boolean)") User = namedtuple('user', ('state', 'is_cool')) c.register_user_type("udt_test_prepared_registered2", "user", User) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b user)") insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, User('Texas', True))) select = s.prepare("SELECT b FROM mytable WHERE a=?") result = s.execute(select, (0, )) self.assertEqual(1, len(result)) row = result[0] self.assertEqual('Texas', row.b.state) self.assertEqual(True, row.b.is_cool) self.assertTrue(type(row.b) is User) c.shutdown()
def test_can_insert_udt_all_datatypes(self): """ Test for inserting various types of PRIMITIVE_DATATYPES into UDT's """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect("udttests") # create UDT alpha_type_list = [] start_index = ord("a") for i, datatype in enumerate(PRIMITIVE_DATATYPES): alpha_type_list.append("{0} {1}".format(chr(start_index + i), datatype)) s.execute( """ CREATE TYPE alldatatypes ({0}) """.format( ", ".join(alpha_type_list) ) ) s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<alldatatypes>)") # register UDT alphabet_list = [] for i in range(ord("a"), ord("a") + len(PRIMITIVE_DATATYPES)): alphabet_list.append("{}".format(chr(i))) Alldatatypes = namedtuple("alldatatypes", alphabet_list) c.register_user_type("udttests", "alldatatypes", Alldatatypes) # insert UDT data params = [] for datatype in PRIMITIVE_DATATYPES: params.append((get_sample(datatype))) insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, Alldatatypes(*params))) # retrieve and verify data results = s.execute("SELECT * FROM mytable") self.assertEqual(1, len(results)) row = results[0].b for expected, actual in zip(params, row): self.assertEqual(expected, actual) c.shutdown()
def test_udt_sizes(self): """ Test for ensuring extra-lengthy udts are handled correctly. """ if self._cass_version < (2, 1, 0): raise unittest.SkipTest("The tuple type was introduced in Cassandra 2.1") MAX_TEST_LENGTH = 16384 EXTENDED_QUERY_TIMEOUT = 60 c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() s.execute("""CREATE KEYSPACE test_udt_sizes WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1'}""") s.set_keyspace("test_udt_sizes") # create the seed udt, increase timeout to avoid the query failure on slow systems s.execute("CREATE TYPE lengthy_udt ({})" .format(', '.join(['v_{} int'.format(i) for i in range(MAX_TEST_LENGTH)])), timeout=EXTENDED_QUERY_TIMEOUT) # create a table with multiple sizes of nested udts # no need for all nested types, only a spot checked few and the largest one s.execute("CREATE TABLE mytable (" "k int PRIMARY KEY, " "v frozen<lengthy_udt>)", timeout=EXTENDED_QUERY_TIMEOUT) # create and register the seed udt type udt = namedtuple('lengthy_udt', tuple(['v_{}'.format(i) for i in range(MAX_TEST_LENGTH)])) c.register_user_type("test_udt_sizes", "lengthy_udt", udt) # verify inserts and reads for i in (0, 1, 2, 3, MAX_TEST_LENGTH): # create udt params = [j for j in range(i)] + [None for j in range(MAX_TEST_LENGTH - i)] created_udt = udt(*params) # write udt s.execute("INSERT INTO mytable (k, v) VALUES (0, %s)", (created_udt,)) # verify udt was written and read correctly, increase timeout to avoid the query failure on slow systems result = s.execute("SELECT v FROM mytable WHERE k=0", timeout=EXTENDED_QUERY_TIMEOUT)[0] self.assertEqual(created_udt, result.v) c.shutdown()
def test_can_insert_unprepared_registered_udts(self): """ Test the insertion of unprepared, registered UDTs """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect(self.keyspace_name, wait_for_all_pools=True) s.execute("CREATE TYPE user (age int, name text)") s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") User = namedtuple('user', ('age', 'name')) c.register_user_type(self.keyspace_name, "user", User) s.execute("INSERT INTO mytable (a, b) VALUES (%s, %s)", (0, User(42, 'bob'))) result = s.execute("SELECT b FROM mytable WHERE a=0") row = result[0] self.assertEqual(42, row.b.age) self.assertEqual('bob', row.b.name) self.assertTrue(type(row.b) is User) # use the same UDT name in a different keyspace s.execute(""" CREATE KEYSPACE udt_test_unprepared_registered2 WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1' } """) s.set_keyspace("udt_test_unprepared_registered2") s.execute("CREATE TYPE user (state text, is_cool boolean)") s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") User = namedtuple('user', ('state', 'is_cool')) c.register_user_type("udt_test_unprepared_registered2", "user", User) s.execute("INSERT INTO mytable (a, b) VALUES (%s, %s)", (0, User('Texas', True))) result = s.execute("SELECT b FROM mytable WHERE a=0") row = result[0] self.assertEqual('Texas', row.b.state) self.assertEqual(True, row.b.is_cool) self.assertTrue(type(row.b) is User) s.execute("DROP KEYSPACE udt_test_unprepared_registered2") c.shutdown()
def test_can_insert_udt_all_datatypes(self): """ Test for inserting various types of PRIMITIVE_DATATYPES into UDT's """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect(self.keyspace_name, wait_for_all_pools=True) # create UDT alpha_type_list = [] start_index = ord('a') for i, datatype in enumerate(PRIMITIVE_DATATYPES): alpha_type_list.append("{0} {1}".format(chr(start_index + i), datatype)) s.execute(""" CREATE TYPE alldatatypes ({0}) """.format(', '.join(alpha_type_list))) s.execute( "CREATE TABLE mytable (a int PRIMARY KEY, b frozen<alldatatypes>)") # register UDT alphabet_list = [] for i in range(ord('a'), ord('a') + len(PRIMITIVE_DATATYPES)): alphabet_list.append('{0}'.format(chr(i))) Alldatatypes = namedtuple("alldatatypes", alphabet_list) c.register_user_type(self.keyspace_name, "alldatatypes", Alldatatypes) # insert UDT data params = [] for datatype in PRIMITIVE_DATATYPES: params.append((get_sample(datatype))) insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, Alldatatypes(*params))) # retrieve and verify data results = s.execute("SELECT * FROM mytable") row = results[0].b for expected, actual in zip(params, row): self.assertEqual(expected, actual) c.shutdown()
def cassandra_connect(): cluster = Cluster(['127.0.0.1'], protocol_version=3) cluster.register_user_type('ensembl', 'xref_record', xref_record) cluster.register_user_type('ensembl', 'exon', base_udt) cluster.register_user_type('ensembl', 'translation', base_udt) cluster.register_user_type('ensembl', 'transcript', base_udt) session = cluster.connect('ensembl') session.row_factory = cassandra.query.ordered_dict_factory return session
class Connect(object): global session global cluster def __init__(self, keyspace, ipaddress): self.keyspace = keyspace addressList = [] addressList.append(ipaddress) self.cluster = Cluster(addressList) self.session = self.cluster.connect(keyspace) self.cluster.register_user_type(keyspace, 'address', Address) def getSession(self): return self.session def close(self): self.session.shutdown() self.cluster.shutdown()
def test_raise_error_on_nonexisting_udts(self): """ Test for ensuring that an error is raised for operating on a nonexisting udt or an invalid keyspace """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect(self.keyspace_name, wait_for_all_pools=True) User = namedtuple('user', ('age', 'name')) with self.assertRaises(UserTypeDoesNotExist): c.register_user_type("some_bad_keyspace", "user", User) with self.assertRaises(UserTypeDoesNotExist): c.register_user_type("system", "user", User) with self.assertRaises(InvalidRequest): s.execute("CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") c.shutdown()
def test_can_insert_udts_with_varying_lengths(self): """ Test for ensuring extra-lengthy udts are properly inserted """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect(self.keyspace_name, wait_for_all_pools=True) MAX_TEST_LENGTH = 254 # create the seed udt, increase timeout to avoid the query failure on slow systems s.execute("CREATE TYPE lengthy_udt ({0})".format(', '.join( ['v_{0} int'.format(i) for i in range(MAX_TEST_LENGTH)]))) # create a table with multiple sizes of nested udts # no need for all nested types, only a spot checked few and the largest one s.execute("CREATE TABLE mytable (" "k int PRIMARY KEY, " "v frozen<lengthy_udt>)") # create and register the seed udt type udt = namedtuple( 'lengthy_udt', tuple(['v_{0}'.format(i) for i in range(MAX_TEST_LENGTH)])) c.register_user_type(self.keyspace_name, "lengthy_udt", udt) # verify inserts and reads for i in (0, 1, 2, 3, MAX_TEST_LENGTH): # create udt params = [j for j in range(i) ] + [None for j in range(MAX_TEST_LENGTH - i)] created_udt = udt(*params) # write udt s.execute("INSERT INTO mytable (k, v) VALUES (0, %s)", (created_udt, )) # verify udt was written and read correctly, increase timeout to avoid the query failure on slow systems result = s.execute("SELECT v FROM mytable WHERE k=0")[0] self.assertEqual(created_udt, result.v) c.shutdown()
def cassandra_connect(): global cluster, session log.info("Initializing Cassandra cluster") cluster = Cluster(app.config['HOSTS'], app.config['PORT']) session = cluster.connect(app.config['KEYSPACE']) session.row_factory = dict_factory session.default_consistency_level = 4 log.debug(session.default_consistency_level) cluster.register_user_type(app.config['KEYSPACE'], 'averages', Averages) cluster.register_user_type(app.config['KEYSPACE'], 'description', Description) cluster.register_user_type(app.config['KEYSPACE'], 'name', Name) cluster.register_user_type(app.config['KEYSPACE'], 'position', Position) cluster.register_user_type(app.config['KEYSPACE'], 'thumbnails', Thumbnails) return "Done"
def test_raise_error_on_nonexisting_udts(self): """ Test for ensuring that an error is raised for operating on a nonexisting udt or an invalid keyspace """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect(self.keyspace_name, wait_for_all_pools=True) User = namedtuple('user', ('age', 'name')) with self.assertRaises(UserTypeDoesNotExist): c.register_user_type("some_bad_keyspace", "user", User) with self.assertRaises(UserTypeDoesNotExist): c.register_user_type("system", "user", User) with self.assertRaises(InvalidRequest): s.execute( "CREATE TABLE mytable (a int PRIMARY KEY, b frozen<user>)") c.shutdown()
def test_can_insert_udts_with_varying_lengths(self): """ Test for ensuring extra-lengthy udts are properly inserted """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect(self.keyspace_name, wait_for_all_pools=True) MAX_TEST_LENGTH = 254 # create the seed udt, increase timeout to avoid the query failure on slow systems s.execute("CREATE TYPE lengthy_udt ({0})" .format(', '.join(['v_{0} int'.format(i) for i in range(MAX_TEST_LENGTH)]))) # create a table with multiple sizes of nested udts # no need for all nested types, only a spot checked few and the largest one s.execute("CREATE TABLE mytable (" "k int PRIMARY KEY, " "v frozen<lengthy_udt>)") # create and register the seed udt type udt = namedtuple('lengthy_udt', tuple(['v_{0}'.format(i) for i in range(MAX_TEST_LENGTH)])) c.register_user_type(self.keyspace_name, "lengthy_udt", udt) # verify inserts and reads for i in (0, 1, 2, 3, MAX_TEST_LENGTH): # create udt params = [j for j in range(i)] + [None for j in range(MAX_TEST_LENGTH - i)] created_udt = udt(*params) # write udt s.execute("INSERT INTO mytable (k, v) VALUES (0, %s)", (created_udt,)) # verify udt was written and read correctly, increase timeout to avoid the query failure on slow systems result = s.execute("SELECT v FROM mytable WHERE k=0")[0] self.assertEqual(created_udt, result.v) c.shutdown()
def connect(self): if self.initialized is False: raise Exception( 'Please initialize the connection parameters first with SessionManager.save_credentials' ) if self._session is None: if os.getenv('USE_ASTRA') == 'false': graph_name = os.getenv('KEYSPACE') ep_graphson3 = GraphExecutionProfile( row_factory=graph_graphson3_row_factory, graph_options=GraphOptions( graph_protocol=GraphProtocol.GRAPHSON_3_0, graph_name=graph_name)) connectionPoint = [os.getenv('CONNECTION_POINTS')] cluster = Cluster(contact_points=connectionPoint, execution_profiles={'core': ep_graphson3}) self._session = cluster.connect() else: # This is how you use the Astra secure connect bundle to connect to an Astra database # note that the database username and password required. # note that no contact points or any other driver customization is required. astra_config = { 'secure_connect_bundle': self.secure_connect_bundle_path } cluster = Cluster(cloud=astra_config, auth_provider=PlainTextAuthProvider( self.username, self.password)) self._session = cluster.connect(keyspace=self.keyspace) # have the driver return results as dict self._session.row_factory = dict_factory # have the driver return LocationUDT as a dict cluster.register_user_type(self.keyspace, 'location_udt', dict) return self._session
def _execute(input_file): if not os.path.exists(input_file): print '%s not exists...' % input_file return keys = [] with open(input_file, 'rb') as f: contents = f.read() keys_str = contents.split('\n') keys.extend([key.split(' ') for key in keys_str]) if len(keys) == 0: print 'No keys to execute...' return cluster = Cluster(seeds) session = cluster.connect() session.row_factory = dict_factory session.default_fetch_size = 100 session.default_timeout = 120 cluster.register_user_type(keyspace, 'object_md', object_md) cluster.register_user_type(keyspace, 'location', location) cluster.register_user_type(keyspace, 'object_md_extra', object_md_extra) keys_bk = keys[0:-1] for key in keys: if len(key) <= 1: continue _check_and_fix(session, key[0], key[1]) print '$' * 30 for key in keys_bk: if len(key) <= 1: continue stat = "select version from %s.object_versions where bucket = '%s' and key = '%s';" % ( keyspace, key[0], key[1]) print stat print '$' * 30
def test_nested_registered_udts_with_different_namedtuples(self): """ Test for ensuring nested udts are handled correctly when the created namedtuples are use names that are different the cql type. Future improvement: optimize these three related tests using a single helper method to cut down on code reuse. """ if self._cass_version < (2, 1, 0): raise unittest.SkipTest( "The tuple type was introduced in Cassandra 2.1") MAX_NESTING_DEPTH = 128 c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() # set the row_factory to dict_factory for programmatically accessing values s.row_factory = dict_factory s.execute("""CREATE KEYSPACE different_namedtuples WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1'}""" ) s.set_keyspace("different_namedtuples") # create the seed udt s.execute("CREATE TYPE depth_0 (age int, name text)") # create the nested udts for i in range(MAX_NESTING_DEPTH): s.execute("CREATE TYPE depth_{} (value depth_{})".format(i + 1, i)) # create a table with multiple sizes of nested udts # no need for all nested types, only a spot checked few and the largest one s.execute("CREATE TABLE mytable (" "k int PRIMARY KEY, " "v_0 depth_0, " "v_1 depth_1, " "v_2 depth_2, " "v_3 depth_3, " "v_{0} depth_{0})".format(MAX_NESTING_DEPTH)) # create the udt container udts = [] # create and register the seed udt type udt = namedtuple('level_0', ('age', 'name')) udts.append(udt) c.register_user_type("different_namedtuples", "depth_0", udts[0]) # create and register the nested udt types for i in range(MAX_NESTING_DEPTH): udt = namedtuple('level_{}'.format(i + 1), ('value')) udts.append(udt) c.register_user_type("different_namedtuples", "depth_{}".format(i + 1), udts[i + 1]) # verify inserts and reads for i in (0, 1, 2, 3, MAX_NESTING_DEPTH): # create udt udt = self.nested_udt_helper(udts, i) # write udt s.execute("INSERT INTO mytable (k, v_%s) VALUES (0, %s)", (i, udt)) # verify udt was written and read correctly result = s.execute("SELECT v_%s FROM mytable WHERE k=0", (i, ))[0] self.assertEqual(udt, result['v_%s' % i])
def test_can_insert_udt_all_collection_datatypes(self): """ Test for inserting various types of COLLECTION_TYPES into UDT's """ c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect("udttests") # create UDT alpha_type_list = [] start_index = ord('a') for i, collection_type in enumerate(COLLECTION_TYPES): for j, datatype in enumerate(PRIMITIVE_DATATYPES): if collection_type == "map": type_string = "{0}_{1} {2}<{3}, {3}>".format( chr(start_index + i), chr(start_index + j), collection_type, datatype) elif collection_type == "tuple": type_string = "{0}_{1} frozen<{2}<{3}>>".format( chr(start_index + i), chr(start_index + j), collection_type, datatype) else: type_string = "{0}_{1} {2}<{3}>".format( chr(start_index + i), chr(start_index + j), collection_type, datatype) alpha_type_list.append(type_string) s.execute(""" CREATE TYPE alldatatypes ({0}) """.format(', '.join(alpha_type_list))) s.execute( "CREATE TABLE mytable (a int PRIMARY KEY, b frozen<alldatatypes>)") # register UDT alphabet_list = [] for i in range(ord('a'), ord('a') + len(COLLECTION_TYPES)): for j in range(ord('a'), ord('a') + len(PRIMITIVE_DATATYPES)): alphabet_list.append('{0}_{1}'.format(chr(i), chr(j))) Alldatatypes = namedtuple("alldatatypes", alphabet_list) c.register_user_type("udttests", "alldatatypes", Alldatatypes) # insert UDT data params = [] for collection_type in COLLECTION_TYPES: for datatype in PRIMITIVE_DATATYPES: params.append((get_collection_sample(collection_type, datatype))) insert = s.prepare("INSERT INTO mytable (a, b) VALUES (?, ?)") s.execute(insert, (0, Alldatatypes(*params))) # retrieve and verify data results = s.execute("SELECT * FROM mytable") self.assertEqual(1, len(results)) row = results[0].b for expected, actual in zip(params, row): self.assertEqual(expected, actual) c.shutdown()
import logging import typing from cassandra.cluster import Cluster cluster = Cluster(protocol_version=3) session = cluster.connect() session.set_keyspace('mykeyspace') session.execute("CREATE TYPE address (street text, zipcode int)") session.execute( "CREATE TABLE users (id int PRIMARY KEY, location frozen<address>)") # create a class to map to the "address" UDT class Address(object): def __init__(self, street, zipcode): self.street = street self.zipcode = zipcode cluster.register_user_type('mykeyspace', 'address', Address) # insert a row using an instance of Address session.execute("INSERT INTO users (id, location) VALUES (%s, %s)", (0, Address("123 Main St.", 78723))) # results will include Address instances results = session.execute("SELECT * FROM users") row = results[0] print(row.id, row.location.street, row.location.zipcode)
class query: cluster = None session = None def __init__(self): self.cluster = Cluster() self.session = self.cluster.connect() self.session.execute("USE infoscheme") def select_all(self): d_quer = "select scheme_id, scheme_name, dept_name, s_constraints,s_details from schemes" schemes = self.session.execute(d_quer) return schemes def get_departments(self): d_query = self.session.execute("select * from departments") return d_query def get_constraints(self): c_query = self.session.execute("select * from constraints") return c_query def get_scheme_size(self): s_query = self.session.execute("select scheme_id from schemes") mx = 0 for x in s_query: if x[0] > mx: mx = x[0] return mx def add_into_schemes(self, s_data, master_data): c_list = [] self.cluster.register_user_type('infoscheme', 'construct', Construct) s_query = "insert into schemes (scheme_id,scheme_name,dept_name,start_date,end_date,s_details,s_constraints)values(?,?,?,?,?,?,[" if 'constraints' in master_data.keys(): tlen = len(master_data['constraints']) i = 0 for c_tuple in master_data['constraints']: temp = [] i = i + 1 s_query += "{ c_data : ['" s_query += c_tuple['constraint_name'] s_query += "','" s_query += c_tuple['answer_type'] s_query += "'" temp.append(c_tuple['constraint_name']) temp.append(c_tuple['answer_type']) temp_list = c_tuple['fields'].split(';') for foo in temp_list: temp.append(foo) s_query += ",'" s_query += foo s_query += "'" s_query += ']}' if i == tlen: pass else: s_query += ',' print "hello", print s_query s_query += "]" s_query += ")" print s_query print s_data try: s_query = self.session.prepare(s_query) self.session.execute(s_query, s_data) except: return 0 return 1 # def add_scheme(self, scheme): # if(scheme.dept_flag): # add_dept(scheme.dept_name) # scheme.dept_id=get_deptid(dept_name) # def add_dept(self, dept_name): #Add department in the db if it doesnt exist # siz=get_size("departments") # d_quer="insert into departments (dept_id, dept_name) values (?,?)" # session.execute(d_quer,(siz,dept_name)) # def is_num(self, num): # for i in range(len(num)): # if num[i]>'9' || num[i]<'0': # return False # return True # def get_size(self, table_name): # t_quer=[] # t_quer=session.execute("select * from %s",table_name) # return len(t_quer) # def is_alpha(self, var): # for i in range(len(var)): # temp=var[i] # if var[i]>'a': # temp=var[i]-'a' # else if var[i]>'A': # temp=var[i]-'A' # if (temp<0 || temp>26): # return False # return True
def test_nested_registered_udts_with_different_namedtuples(self): """ Test for ensuring nested udts are handled correctly when the created namedtuples are use names that are different the cql type. Future improvement: optimize these three related tests using a single helper method to cut down on code repetition. """ if self._cass_version < (2, 1, 0): raise unittest.SkipTest("The tuple type was introduced in Cassandra 2.1") MAX_NESTING_DEPTH = 16 c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() # set the row_factory to dict_factory for programmatically accessing values s.row_factory = dict_factory s.execute("""CREATE KEYSPACE different_namedtuples WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1'}""") s.set_keyspace("different_namedtuples") # create the seed udt s.execute("CREATE TYPE depth_0 (age int, name text)") # create the nested udts for i in range(MAX_NESTING_DEPTH): s.execute("CREATE TYPE depth_{} (value frozen<depth_{}>)".format(i + 1, i)) # create a table with multiple sizes of nested udts # no need for all nested types, only a spot checked few and the largest one s.execute("CREATE TABLE mytable (" "k int PRIMARY KEY, " "v_0 frozen<depth_0>, " "v_1 frozen<depth_1>, " "v_2 frozen<depth_2>, " "v_3 frozen<depth_3>, " "v_{0} frozen<depth_{0}>)".format(MAX_NESTING_DEPTH)) # create the udt container udts = [] # create and register the seed udt type udt = namedtuple('level_0', ('age', 'name')) udts.append(udt) c.register_user_type("different_namedtuples", "depth_0", udts[0]) # create and register the nested udt types for i in range(MAX_NESTING_DEPTH): udt = namedtuple('level_{}'.format(i + 1), ('value')) udts.append(udt) c.register_user_type("different_namedtuples", "depth_{}".format(i + 1), udts[i + 1]) # verify inserts and reads for i in (0, 1, 2, 3, MAX_NESTING_DEPTH): # create udt udt = self.nested_udt_helper(udts, i) # write udt s.execute("INSERT INTO mytable (k, v_%s) VALUES (0, %s)", (i, udt)) # verify udt was written and read correctly result = s.execute("SELECT v_%s FROM mytable WHERE k=0", (i,))[0] self.assertEqual(udt, result['v_%s' % i]) c.shutdown()
#cassandra-udt.py #example 12.11 from cassandra.cluster import Cluster cluster = Cluster(protocol_version=3) session = cluster.connect() session.set_keyspace('mykeyspace') session.execute("CREATE TYPE contact (email text, phone text)") session.execute("CREATE TABLE users (userid int PRIMARY KEY, name text, contact frozen<contact>)") class ContactInfo: def __init__(self, email, phone): self.email = email self.phone = phone cluster.register_user_type('mykeyspace', 'contact', ContactInfo) # insert a row using an instance of ContctInfo session.execute("INSERT INTO users (userid, name, contact) VALUES (%s, %s, %s)", (1, 'Admin', ContactInfo("*****@*****.**", '9988776655')))
from cassandra.cluster import Cluster cluster = Cluster(protocol_version=3) session = cluster.connect() session.set_keyspace('office') session.execute("CREATE TYPE address (street text, zipcode int)") session.execute( "CREATE TABLE users (id int PRIMARY KEY, location frozen<address>)") # create a class to map to the "address" UDT class Address(object): def __init__(self, street, zipcode): self.street = street self.zipcode = zipcode cluster.register_user_type('office', 'address', Address) # insert a row using an instance of Address session.execute("INSERT INTO users (id, location) VALUES (%s, %s)", (0, Address("123 Main St.", 78723))) # results will include Address instances results = session.execute("SELECT * FROM users") row = results[0] print(row.id, row.location.street, row.location.zipcode)
def test_nested_registered_udts(self): """ Test for ensuring nested udts are handled correctly. """ if self._cass_version < (2, 1, 0): raise unittest.SkipTest("The tuple type was introduced in Cassandra 2.1") MAX_NESTING_DEPTH = 4 # TODO: Move to 128, or similar c = Cluster(protocol_version=PROTOCOL_VERSION) s = c.connect() # set the row_factory to dict_factory for programmatically accessing values s.row_factory = dict_factory s.execute("""CREATE KEYSPACE test_nested_unregistered_udts WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1'}""") s.set_keyspace("test_nested_unregistered_udts") # create the seed udt s.execute("CREATE TYPE depth_0 (age int, name text)") # create the nested udts for i in range(MAX_NESTING_DEPTH): s.execute("CREATE TYPE depth_{} (value depth_{})".format(i + 1, i)) # create a table with multiple sizes of nested udts # no need for all nested types, only a spot checked few and the largest one s.execute("CREATE TABLE mytable (" "k int PRIMARY KEY, " "v_0 depth_0, " "v_1 depth_1, " "v_2 depth_2, " "v_3 depth_3, " "v_{0} depth_{0})".format(MAX_NESTING_DEPTH)) # create the udt container udts = [] # create and register the seed udt type udt = namedtuple('depth_0', ('age', 'name')) udts.append(udt) c.register_user_type("test_nested_unregistered_udts", "depth_0", udts[0]) # create and register the nested udt types for i in range(MAX_NESTING_DEPTH): udt = namedtuple('depth_{}'.format(i + 1), ('value')) udts.append(udt) c.register_user_type("test_nested_unregistered_udts", "depth_{}".format(i + 1), udts[i + 1]) # verify inserts and reads for i in (0, 1, 2, 3, MAX_NESTING_DEPTH): # create udt udt = self.nested_udt_helper(udts, i) # write udt s.execute("INSERT INTO mytable (k, v_%s) VALUES (0, %s)", (i, udt)) # verify udt was written and read correctly result = s.execute("SELECT v_%s FROM mytable WHERE k=0", (i,))[0] self.assertEqual(udt, result['v_%s' % i])
self._session = cluster.connect(keyspace=os.getenv('KEYSPACE')) else: logger.info("Missing env value for USE_ASTRA") ======= else: cluster = Cluster([os.getenv('CONNECTION_POINTS')]) self._session = cluster.connect(keyspace='killrvideo') self.initialized = True self.keyspace = 'killrvideo' >>>>>>> 3ce7c423dd19e11ba6e2daa1b2f1a5df1f38d4a2 # have the driver return results as dict self._session.row_factory = dict_factory # have the driver return LocationUDT as a dict cluster.register_user_type(self.keyspace, 'location_udt', dict) return self._session def check_connection(self): try: result = self.connect().execute(self.ping_query) return True except (Unauthorized, Unavailable, AuthenticationFailed, OperationTimedOut, ReadTimeout) as e: return False def close(self): if self.initialized and self._session is not None: self._session.shutdown()
self.name = name self.type = type self.is_null = is_null, self.primary_key = primary_key class EntityModel: def __init__(self, id, name): self.id = id self.name = name cluster = Cluster(['127.0.0.1']) session = cluster.connect('dbbuilder') cluster.register_user_type('dbbuilder', 'entity_column', Column) cluster.register_user_type('dbbuilder', 'file_column', Column) cluster.register_user_type('dbbuilder', 'entity_model', EntityModel) """ DATA INSERT """ insert_user_command = SimpleStatement( "INSERT INTO users (username, password) VALUES (%s, %s)") session.execute( insert_user_command, ('artemkovtun', '5994471abb01112afcc18159f6cc74b4f511b99806da59b3caf5a9c173cacfc5'), ConsistencyLevel.EACH_QUORUM) session.execute( insert_user_command, ('supermario',
class DBWorker(object): def __init__(self, address, db): self.cluster = Cluster([address]) self.session = self.cluster.connect(db) self.cluster.register_user_type('market', 'market_order', Order) def insert_trade(self, trade, timestamp): query = '''INSERT INTO market.trades (id, ticker, price, size, bid, ask, time) VALUES (%(id)s,%(ticker)s, %(price)s, %(size)s, %(bid)s, %(ask)s, %(time)s);''' params = { 'id': trade.id, 'ticker': trade.ticker, 'price': trade.price, 'size': trade.size, 'bid': trade.bid, 'ask': trade.ask, 'time': timestamp } self.session.execute(query, params) return def insert_order_book_snapshot(self, ticker, bids, asks, time): query = '''INSERT INTO market.order_book (ticker, bids, asks, time) VALUES (%(ticker)s, %(bids)s, %(asks)s, %(time)s);''' params = {'ticker': ticker, 'bids': bids, 'asks': asks, 'time': time} self.session.execute(query, params) return def get_trades(self, ticker, limit): query = '''SELECT * FROM trades WHERE ticker = %(ticker)s order by time desc limit %(limit)s;''' params = {'ticker': ticker, 'limit': limit} rows = self.session.execute(query, params) df = pd.DataFrame(columns=['Price']) for row in rows: df.loc[row.time] = row.price df = df.sort_index() return df def get_trades_for_fake_trading(self, ticker, start_time, limit_num): query = '''SELECT * FROM trades WHERE ticker = %(ticker)s AND time>= %(start_date)s order by time asc limit %(limit_num)s;''' params = { 'ticker': ticker, 'start_date': start_time, 'limit_num': limit_num } rows = self.session.execute(query, params) df = pd.DataFrame(columns=['Price', 'Volume']) for row in rows: df.loc[row.time] = [row.price, row.size] # df.loc[row.time]['Volume'] = row.size df = df.sort_index() return df.ix[-1] def get_trades_for_period(self, ticker, start_date, end_date=None): if end_date == None: end_date = dt.today() query = '''SELECT * FROM trades WHERE ticker = %(ticker)s AND time>= %(start_date)s and time <= %(end_date)s;''' params = { 'ticker': ticker, 'start_date': start_date, 'end_date': end_date } rows = self.session.execute(query, params) df = pd.DataFrame(columns=['Price', 'Volume']) for row in rows: df.loc[row.time] = [row.price, row.size] # df.loc[row.time]['Volume'] = row.size df = df.sort_index() return df def get_latest_order_book(self, ticker): query = '''SELECT bids, asks FROM order_book WHERE ticker = %(ticker)s ORDER BY time DESC LIMIT 1;''' params = { 'ticker': ticker, } rows = self.session.execute(query, params) [(bids, asks)] = [(row.bids, row.asks) for row in rows] return bids, asks
class query: cluster=None session=None def __init__(self): self.cluster=Cluster() self.session=self.cluster.connect() self.session.execute("USE infoscheme") def select_all(self): d_quer="select scheme_id, scheme_name, dept_name, s_constraints,s_details from schemes" schemes = self.session.execute(d_quer) return schemes def get_departments(self): d_query=self.session.execute("select * from departments") return d_query def get_constraints(self): c_query=self.session.execute("select * from constraints") return c_query def get_scheme_size(self): s_query=self.session.execute("select scheme_id from schemes") mx=0 for x in s_query: if x[0]>mx: mx=x[0] return mx def add_into_schemes(self,s_data,master_data): c_list=[] self.cluster.register_user_type('infoscheme', 'construct',Construct) s_query="insert into schemes (scheme_id,scheme_name,dept_name,start_date,end_date,s_details,s_constraints)values(?,?,?,?,?,?,[" if 'constraints' in master_data.keys(): tlen=len(master_data['constraints']) i=0 for c_tuple in master_data['constraints']: temp=[] i=i+1 s_query+="{ c_data : ['" s_query+=c_tuple['constraint_name'] s_query+="','" s_query+=c_tuple['answer_type'] s_query+="'" temp.append(c_tuple['constraint_name']) temp.append(c_tuple['answer_type']) temp_list=c_tuple['fields'].split(';') for foo in temp_list: temp.append(foo) s_query+=",'" s_query+=foo s_query+="'" s_query+=']}' if i==tlen: pass else: s_query+=',' print "hello", print s_query s_query+="]" s_query+=")" print s_query print s_data try: s_query=self.session.prepare(s_query) self.session.execute(s_query,s_data) except: return 0 return 1 # def add_scheme(self, scheme): # if(scheme.dept_flag): # add_dept(scheme.dept_name) # scheme.dept_id=get_deptid(dept_name) # def add_dept(self, dept_name): #Add department in the db if it doesnt exist # siz=get_size("departments") # d_quer="insert into departments (dept_id, dept_name) values (?,?)" # session.execute(d_quer,(siz,dept_name)) # def is_num(self, num): # for i in range(len(num)): # if num[i]>'9' || num[i]<'0': # return False # return True # def get_size(self, table_name): # t_quer=[] # t_quer=session.execute("select * from %s",table_name) # return len(t_quer) # def is_alpha(self, var): # for i in range(len(var)): # temp=var[i] # if var[i]>'a': # temp=var[i]-'a' # else if var[i]>'A': # temp=var[i]-'A' # if (temp<0 || temp>26): # return False # return True
self.state = state self.zip = zip def getdata(filename): with open(filename, "rb") as csvfile: datareader = csv.reader(csvfile) for row in datareader: yield row # Start of program ipAddr = [] ipAddr.append(sys.argv[1]) cluster = Cluster(ipAddr); session = cluster.connect('team10') cluster.register_user_type('team10', 'address', Address) insert_statement = session.prepare("INSERT INTO district (d_w_id, d_id, d_name, d_address, d_tax, d_ytd, d_next_o_id) VALUES (?, ?, ?, ?, ?, ?, ?)") print("Inserting District data ... ") for row in getdata('../data-files/district.csv'): d_w_id = int(row[0]) d_id = int(row[1]) d_tax = Decimal(row[8]) d_ytd = Decimal(row[9]) d_next_o_id = int(row[10]) session.execute(insert_statement, [d_w_id, d_id, row[2], Address(row[3], row[4], row[5], row[6], row[7]), d_tax, d_ytd, d_next_o_id]) print("Inserting Done ... ")
# result = session.execute("select * from users where lastname='Jones' ")[0] # print result.firstname, result.age # create a class to map to the "address" UDT # class Address: # def __init__(self, lat, lon): # self.lon = lon # self.lat = lat class Address(object): def __init__(self, lat, lon): self.lat = lat self.lon = lon cluster.register_user_type('demo', 'lat_lon', Address) geohash = Geocode.encode(19.128634, 72.928142) # print Geocode.encode(19.112407, 72.928255) # # print Geocode.encode(19.142371, 72.937739) # insert a row using an instance of Address # cql = "INSERT INTO station_details (station_code, lat, lon, station_name, connecting_stations, number_of_tracks," \ # " station_radius, track1, track2) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)" # # session.execute(cql, ( # geohash, 19.128634, 72.928142, 'Kanjurmarg', {'te7uex29zdbk9knh': 1, 'te7ugsz8ks84f7pz': 2}, 2, 0.2, # [Address(19.126593, 72.927815), Address(19.126249, 72.927836), Address(19.125924, 72.927836)], # [Address(19.131175, 72.929360), Address(19.132006, 72.929832), Address(19.132959, 72.930368)])) # cql = "insert into fare_charts (station_from_name, station_from_code, station_from_lat_lon, station_to_name," \