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," \
