def test_can_insert_tuples_all_primitive_datatypes(self):
"""
Ensure tuple subtypes are appropriately handled.
"""
if self.cass_version < (2, 1, 0):
raise unittest.SkipTest("The tuple type was introduced in Cassandra 2.1")
c = Cluster(protocol_version=PROTOCOL_VERSION)
s = c.connect(self.keyspace_name)
s.encoder.mapping[tuple] = s.encoder.cql_encode_tuple
s.execute("CREATE TABLE tuple_primitive ("
"k int PRIMARY KEY, "
"v frozen<tuple<%s>>)" % ','.join(PRIMITIVE_DATATYPES))
values = []
type_count = len(PRIMITIVE_DATATYPES)
for i, data_type in enumerate(PRIMITIVE_DATATYPES):
# create tuples to be written and ensure they match with the expected response
# responses have trailing None values for every element that has not been written
values.append(get_sample(data_type))
expected = tuple(values + [None] * (type_count - len(values)))
s.execute("INSERT INTO tuple_primitive (k, v) VALUES (%s, %s)", (i, tuple(values)))
result = s.execute("SELECT v FROM tuple_primitive WHERE k=%s", (i,))[0]
self.assertEqual(result.v, expected)
c.shutdown()
def test_tuple_primitive_subtypes(self):
"""
Ensure tuple subtypes are appropriately handled.
"""
if self._cass_version < (2, 1, 0):
raise unittest.SkipTest("The tuple type was introduced in Cassandra 2.1")
c = Cluster(protocol_version=PROTOCOL_VERSION)
s = c.connect()
s.encoder.mapping[tuple] = s.encoder.cql_encode_tuple
s.execute("""CREATE KEYSPACE test_tuple_primitive_subtypes
WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1'}""")
s.set_keyspace("test_tuple_primitive_subtypes")
s.execute("CREATE TABLE mytable ("
"k int PRIMARY KEY, "
"v tuple<%s>)" % ','.join(DATA_TYPE_PRIMITIVES))
for i in range(len(DATA_TYPE_PRIMITIVES)):
# create tuples to be written and ensure they match with the expected response
# responses have trailing None values for every element that has not been written
created_tuple = [get_sample(DATA_TYPE_PRIMITIVES[j]) for j in range(i + 1)]
response_tuple = tuple(created_tuple + [None for j in range(len(DATA_TYPE_PRIMITIVES) - i - 1)])
written_tuple = tuple(created_tuple)
s.execute("INSERT INTO mytable (k, v) VALUES (%s, %s)", (i, written_tuple))
result = s.execute("SELECT v FROM mytable WHERE k=%s", (i,))[0]
self.assertEqual(response_tuple, result.v)
def test_tuple_primitive_subtypes(self):
"""
Ensure tuple subtypes are appropriately handled.
"""
if self._cass_version < (2, 1, 0):
raise unittest.SkipTest("The tuple type was introduced in Cassandra 2.1")
s = self._session.cluster.connect()
s.set_keyspace(self._session.keyspace)
s.encoder.mapping[tuple] = s.encoder.cql_encode_tuple
s.execute("CREATE TABLE tuple_primitive ("
"k int PRIMARY KEY, "
"v frozen<tuple<%s>>)" % ','.join(DATA_TYPE_PRIMITIVES))
for i in range(len(DATA_TYPE_PRIMITIVES)):
# create tuples to be written and ensure they match with the expected response
# responses have trailing None values for every element that has not been written
created_tuple = [get_sample(DATA_TYPE_PRIMITIVES[j]) for j in range(i + 1)]
response_tuple = tuple(created_tuple + [None for j in range(len(DATA_TYPE_PRIMITIVES) - i - 1)])
written_tuple = tuple(created_tuple)
s.execute("INSERT INTO tuple_primitive (k, v) VALUES (%s, %s)", (i, written_tuple))
result = s.execute("SELECT v FROM tuple_primitive WHERE k=%s", (i,))[0]
self.assertEqual(response_tuple, result.v)
s.shutdown()
def get_all_primitive_params(key):
"""
Simple utility method used to give back a list of all possible primitive data sample types.
"""
params = [key]
for datatype in PRIMITIVE_DATATYPES:
params.append(get_sample(datatype))
return params
def test_can_insert_primitive_datatypes(self):
"""
Test insertion of all datatype primitives
"""
c = Cluster(protocol_version=PROTOCOL_VERSION)
s = c.connect("typetests")
# create table
alpha_type_list = ["zz int PRIMARY KEY"]
col_names = ["zz"]
start_index = ord('a')
for i, datatype in enumerate(PRIMITIVE_DATATYPES):
alpha_type_list.append("{0} {1}".format(chr(start_index + i), datatype))
col_names.append(chr(start_index + i))
s.execute("CREATE TABLE alltypes ({0})".format(', '.join(alpha_type_list)))
# create the input
params = [0]
for datatype in PRIMITIVE_DATATYPES:
params.append((get_sample(datatype)))
# insert into table as a simple statement
columns_string = ', '.join(col_names)
placeholders = ', '.join(["%s"] * len(col_names))
s.execute("INSERT INTO alltypes ({0}) VALUES ({1})".format(columns_string, placeholders), params)
# verify data
results = s.execute("SELECT {0} FROM alltypes WHERE zz=0".format(columns_string))[0]
for expected, actual in zip(params, results):
self.assertEqual(actual, expected)
# try the same thing with a prepared statement
placeholders = ','.join(["?"] * len(col_names))
insert = s.prepare("INSERT INTO alltypes ({0}) VALUES ({1})".format(columns_string, placeholders))
s.execute(insert.bind(params))
# verify data
results = s.execute("SELECT {0} FROM alltypes WHERE zz=0".format(columns_string))[0]
for expected, actual in zip(params, results):
self.assertEqual(actual, expected)
# verify data with prepared statement query
select = s.prepare("SELECT {0} FROM alltypes WHERE zz=?".format(columns_string))
results = s.execute(select.bind([0]))[0]
for expected, actual in zip(params, results):
self.assertEqual(actual, expected)
# verify data with with prepared statement, use dictionary with no explicit columns
s.row_factory = ordered_dict_factory
select = s.prepare("SELECT * FROM alltypes")
results = s.execute(select)[0]
for expected, actual in zip(params, results.values()):
self.assertEqual(actual, expected)
c.shutdown()
def get_all_primitive_params(key):
"""
Simple utility method used to give back a list of all possible primitive data sample types.
"""
params = [key]
for datatype in PRIMITIVE_DATATYPES:
# Also test for empty strings
if key == 1 and datatype == 'ascii':
params.append('')
else:
params.append(get_sample(datatype))
return params
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_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_tuple_non_primitive_subtypes(self):
"""
Ensure tuple subtypes are appropriately handled for maps, sets, and lists.
"""
if self._cass_version < (2, 1, 0):
raise unittest.SkipTest("The tuple type was introduced in Cassandra 2.1")
c = Cluster(protocol_version=PROTOCOL_VERSION)
s = c.connect()
# set the row_factory to dict_factory for programmatic access
# set the encoder for tuples for the ability to write tuples
s.row_factory = dict_factory
s.encoder.mapping[tuple] = s.encoder.cql_encode_tuple
s.execute("""CREATE KEYSPACE test_tuple_non_primitive_subtypes
WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor': '1'}""")
s.set_keyspace("test_tuple_non_primitive_subtypes")
values = []
# create list values
for datatype in DATA_TYPE_PRIMITIVES:
values.append('v_{} tuple<list<{}>>'.format(len(values), datatype))
# create set values
for datatype in DATA_TYPE_PRIMITIVES:
values.append('v_{} tuple<set<{}>>'.format(len(values), datatype))
# create map values
for datatype in DATA_TYPE_PRIMITIVES:
datatype_1 = datatype_2 = datatype
if datatype == 'blob':
# unhashable type: 'bytearray'
datatype_1 = 'ascii'
values.append('v_{} tuple<map<{}, {}>>'.format(len(values), datatype_1, datatype_2))
# make sure we're testing all non primitive data types in the future
if set(DATA_TYPE_NON_PRIMITIVE_NAMES) != set(['tuple', 'list', 'map', 'set']):
raise NotImplemented('Missing datatype not implemented: {}'.format(
set(DATA_TYPE_NON_PRIMITIVE_NAMES) - set(['tuple', 'list', 'map', 'set'])
))
# create table
s.execute("CREATE TABLE mytable ("
"k int PRIMARY KEY, "
"%s)" % ', '.join(values))
i = 0
# test tuple<list<datatype>>
for datatype in DATA_TYPE_PRIMITIVES:
created_tuple = tuple([[get_sample(datatype)]])
s.execute("INSERT INTO mytable (k, v_%s) VALUES (0, %s)", (i, created_tuple))
result = s.execute("SELECT v_%s FROM mytable WHERE k=0", (i,))[0]
self.assertEqual(created_tuple, result['v_%s' % i])
i += 1
# test tuple<set<datatype>>
for datatype in DATA_TYPE_PRIMITIVES:
created_tuple = tuple([sortedset([get_sample(datatype)])])
s.execute("INSERT INTO mytable (k, v_%s) VALUES (0, %s)", (i, created_tuple))
result = s.execute("SELECT v_%s FROM mytable WHERE k=0", (i,))[0]
self.assertEqual(created_tuple, result['v_%s' % i])
i += 1
# test tuple<map<datatype, datatype>>
for datatype in DATA_TYPE_PRIMITIVES:
if datatype == 'blob':
# unhashable type: 'bytearray'
created_tuple = tuple([{get_sample('ascii'): get_sample(datatype)}])
else:
created_tuple = tuple([{get_sample(datatype): get_sample(datatype)}])
s.execute("INSERT INTO mytable (k, v_%s) VALUES (0, %s)", (i, created_tuple))
result = s.execute("SELECT v_%s FROM mytable WHERE k=0", (i,))[0]
self.assertEqual(created_tuple, result['v_%s' % i])
i += 1
def test_can_insert_empty_strings_and_nulls(self):
"""
Test insertion of empty strings and null values
"""
s = self.session
# create table
alpha_type_list = ["zz int PRIMARY KEY"]
col_names = []
string_types = set(('ascii', 'text', 'varchar'))
string_columns = set((''))
# this is just a list of types to try with empty strings
non_string_types = PRIMITIVE_DATATYPES - string_types - set(('blob', 'date', 'inet', 'time', 'timestamp'))
non_string_columns = set()
start_index = ord('a')
for i, datatype in enumerate(PRIMITIVE_DATATYPES):
col_name = chr(start_index + i)
alpha_type_list.append("{0} {1}".format(col_name, datatype))
col_names.append(col_name)
if datatype in non_string_types:
non_string_columns.add(col_name)
if datatype in string_types:
string_columns.add(col_name)
execute_until_pass(s, "CREATE TABLE all_empty ({0})".format(', '.join(alpha_type_list)))
# verify all types initially null with simple statement
columns_string = ','.join(col_names)
s.execute("INSERT INTO all_empty (zz) VALUES (2)")
results = s.execute("SELECT {0} FROM all_empty WHERE zz=2".format(columns_string))[0]
self.assertTrue(all(x is None for x in results))
# verify all types initially null with prepared statement
select = s.prepare("SELECT {0} FROM all_empty WHERE zz=?".format(columns_string))
results = s.execute(select.bind([2]))[0]
self.assertTrue(all(x is None for x in results))
# insert empty strings for string-like fields
expected_values = dict((col, '') for col in string_columns)
columns_string = ','.join(string_columns)
placeholders = ','.join(["%s"] * len(string_columns))
s.execute("INSERT INTO all_empty (zz, {0}) VALUES (3, {1})".format(columns_string, placeholders), expected_values.values())
# verify string types empty with simple statement
results = s.execute("SELECT {0} FROM all_empty WHERE zz=3".format(columns_string))[0]
for expected, actual in zip(expected_values.values(), results):
self.assertEqual(actual, expected)
# verify string types empty with prepared statement
results = s.execute(s.prepare("SELECT {0} FROM all_empty WHERE zz=?".format(columns_string)), [3])[0]
for expected, actual in zip(expected_values.values(), results):
self.assertEqual(actual, expected)
# non-string types shouldn't accept empty strings
for col in non_string_columns:
query = "INSERT INTO all_empty (zz, {0}) VALUES (4, %s)".format(col)
with self.assertRaises(InvalidRequest):
s.execute(query, [''])
insert = s.prepare("INSERT INTO all_empty (zz, {0}) VALUES (4, ?)".format(col))
with self.assertRaises(TypeError):
s.execute(insert, [''])
# verify that Nones can be inserted and overwrites existing data
# create the input
params = []
for datatype in PRIMITIVE_DATATYPES:
params.append((get_sample(datatype)))
# insert the data
columns_string = ','.join(col_names)
placeholders = ','.join(["%s"] * len(col_names))
simple_insert = "INSERT INTO all_empty (zz, {0}) VALUES (5, {1})".format(columns_string, placeholders)
s.execute(simple_insert, params)
# then insert None, which should null them out
null_values = [None] * len(col_names)
s.execute(simple_insert, null_values)
# check via simple statement
query = "SELECT {0} FROM all_empty WHERE zz=5".format(columns_string)
results = s.execute(query)[0]
for col in results:
self.assertEqual(None, col)
# check via prepared statement
select = s.prepare("SELECT {0} FROM all_empty WHERE zz=?".format(columns_string))
results = s.execute(select.bind([5]))[0]
for col in results:
self.assertEqual(None, col)
# do the same thing again, but use a prepared statement to insert the nulls
s.execute(simple_insert, params)
placeholders = ','.join(["?"] * len(col_names))
insert = s.prepare("INSERT INTO all_empty (zz, {0}) VALUES (5, {1})".format(columns_string, placeholders))
s.execute(insert, null_values)
results = s.execute(query)[0]
for col in results:
self.assertEqual(None, col)
results = s.execute(select.bind([5]))[0]
for col in results:
self.assertEqual(None, col)
def test_can_insert_primitive_datatypes(self):
"""
Test insertion of all datatype primitives
"""
c = Cluster(protocol_version=PROTOCOL_VERSION)
s = c.connect(self.keyspace_name)
# create table
alpha_type_list = ["zz int PRIMARY KEY"]
col_names = ["zz"]
start_index = ord('a')
for i, datatype in enumerate(PRIMITIVE_DATATYPES):
alpha_type_list.append("{0} {1}".format(chr(start_index + i),
datatype))
col_names.append(chr(start_index + i))
s.execute("CREATE TABLE alltypes ({0})".format(
', '.join(alpha_type_list)))
# create the input
params = [0]
for datatype in PRIMITIVE_DATATYPES:
params.append((get_sample(datatype)))
# insert into table as a simple statement
columns_string = ', '.join(col_names)
placeholders = ', '.join(["%s"] * len(col_names))
s.execute(
"INSERT INTO alltypes ({0}) VALUES ({1})".format(
columns_string, placeholders), params)
# verify data
results = s.execute(
"SELECT {0} FROM alltypes WHERE zz=0".format(columns_string))[0]
for expected, actual in zip(params, results):
self.assertEqual(actual, expected)
# try the same thing with a prepared statement
placeholders = ','.join(["?"] * len(col_names))
insert = s.prepare("INSERT INTO alltypes ({0}) VALUES ({1})".format(
columns_string, placeholders))
s.execute(insert.bind(params))
# verify data
results = s.execute(
"SELECT {0} FROM alltypes WHERE zz=0".format(columns_string))[0]
for expected, actual in zip(params, results):
self.assertEqual(actual, expected)
# verify data with prepared statement query
select = s.prepare(
"SELECT {0} FROM alltypes WHERE zz=?".format(columns_string))
results = s.execute(select.bind([0]))[0]
for expected, actual in zip(params, results):
self.assertEqual(actual, expected)
# verify data with with prepared statement, use dictionary with no explicit columns
s.row_factory = ordered_dict_factory
select = s.prepare("SELECT * FROM alltypes")
results = s.execute(select)[0]
for expected, actual in zip(params, results.values()):
self.assertEqual(actual, expected)
c.shutdown()
def test_can_insert_tuples_all_collection_datatypes(self):
"""
Ensure tuple subtypes are appropriately handled for maps, sets, and lists.
"""
if self._cass_version < (2, 1, 0):
raise unittest.SkipTest("The tuple type was introduced in Cassandra 2.1")
c = Cluster(protocol_version=PROTOCOL_VERSION)
s = c.connect("typetests")
# set the row_factory to dict_factory for programmatic access
# set the encoder for tuples for the ability to write tuples
s.row_factory = dict_factory
s.encoder.mapping[tuple] = s.encoder.cql_encode_tuple
values = []
# create list values
for datatype in PRIMITIVE_DATATYPES:
values.append('v_{} frozen<tuple<list<{}>>>'.format(len(values), datatype))
# create set values
for datatype in PRIMITIVE_DATATYPES:
values.append('v_{} frozen<tuple<set<{}>>>'.format(len(values), datatype))
# create map values
for datatype in PRIMITIVE_DATATYPES:
datatype_1 = datatype_2 = datatype
if datatype == 'blob':
# unhashable type: 'bytearray'
datatype_1 = 'ascii'
values.append('v_{} frozen<tuple<map<{}, {}>>>'.format(len(values), datatype_1, datatype_2))
# make sure we're testing all non primitive data types in the future
if set(COLLECTION_TYPES) != set(['tuple', 'list', 'map', 'set']):
raise NotImplemented('Missing datatype not implemented: {}'.format(
set(COLLECTION_TYPES) - set(['tuple', 'list', 'map', 'set'])
))
# create table
s.execute("CREATE TABLE tuple_non_primative ("
"k int PRIMARY KEY, "
"%s)" % ', '.join(values))
i = 0
# test tuple<list<datatype>>
for datatype in PRIMITIVE_DATATYPES:
created_tuple = tuple([[get_sample(datatype)]])
s.execute("INSERT INTO tuple_non_primative (k, v_%s) VALUES (0, %s)", (i, created_tuple))
result = s.execute("SELECT v_%s FROM tuple_non_primative WHERE k=0", (i,))[0]
self.assertEqual(created_tuple, result['v_%s' % i])
i += 1
# test tuple<set<datatype>>
for datatype in PRIMITIVE_DATATYPES:
created_tuple = tuple([sortedset([get_sample(datatype)])])
s.execute("INSERT INTO tuple_non_primative (k, v_%s) VALUES (0, %s)", (i, created_tuple))
result = s.execute("SELECT v_%s FROM tuple_non_primative WHERE k=0", (i,))[0]
self.assertEqual(created_tuple, result['v_%s' % i])
i += 1
# test tuple<map<datatype, datatype>>
for datatype in PRIMITIVE_DATATYPES:
if datatype == 'blob':
# unhashable type: 'bytearray'
created_tuple = tuple([{get_sample('ascii'): get_sample(datatype)}])
else:
created_tuple = tuple([{get_sample(datatype): get_sample(datatype)}])
s.execute("INSERT INTO tuple_non_primative (k, v_%s) VALUES (0, %s)", (i, created_tuple))
result = s.execute("SELECT v_%s FROM tuple_non_primative WHERE k=0", (i,))[0]
self.assertEqual(created_tuple, result['v_%s' % i])
i += 1
c.shutdown()
def test_can_insert_empty_strings_and_nulls(self):
"""
Test insertion of empty strings and null values
"""
s = self.session
# create table
alpha_type_list = ["zz int PRIMARY KEY"]
col_names = []
string_types = set(('ascii', 'text', 'varchar'))
string_columns = set((''))
# this is just a list of types to try with empty strings
non_string_types = PRIMITIVE_DATATYPES - string_types - set(('blob', 'date', 'inet', 'time', 'timestamp'))
non_string_columns = set()
start_index = ord('a')
for i, datatype in enumerate(PRIMITIVE_DATATYPES):
col_name = chr(start_index + i)
alpha_type_list.append("{0} {1}".format(col_name, datatype))
col_names.append(col_name)
if datatype in non_string_types:
non_string_columns.add(col_name)
if datatype in string_types:
string_columns.add(col_name)
execute_until_pass(s, "CREATE TABLE all_empty ({0})".format(', '.join(alpha_type_list)))
# verify all types initially null with simple statement
columns_string = ','.join(col_names)
s.execute("INSERT INTO all_empty (zz) VALUES (2)")
results = s.execute("SELECT {0} FROM all_empty WHERE zz=2".format(columns_string))[0]
self.assertTrue(all(x is None for x in results))
# verify all types initially null with prepared statement
select = s.prepare("SELECT {0} FROM all_empty WHERE zz=?".format(columns_string))
results = s.execute(select.bind([2]))[0]
self.assertTrue(all(x is None for x in results))
# insert empty strings for string-like fields
expected_values = dict((col, '') for col in string_columns)
columns_string = ','.join(string_columns)
placeholders = ','.join(["%s"] * len(string_columns))
s.execute("INSERT INTO all_empty (zz, {0}) VALUES (3, {1})".format(columns_string, placeholders), expected_values.values())
# verify string types empty with simple statement
results = s.execute("SELECT {0} FROM all_empty WHERE zz=3".format(columns_string))[0]
for expected, actual in zip(expected_values.values(), results):
self.assertEqual(actual, expected)
# verify string types empty with prepared statement
results = s.execute(s.prepare("SELECT {0} FROM all_empty WHERE zz=?".format(columns_string)), [3])[0]
for expected, actual in zip(expected_values.values(), results):
self.assertEqual(actual, expected)
# non-string types shouldn't accept empty strings
for col in non_string_columns:
query = "INSERT INTO all_empty (zz, {0}) VALUES (4, %s)".format(col)
with self.assertRaises(InvalidRequest):
s.execute(query, [''])
insert = s.prepare("INSERT INTO all_empty (zz, {0}) VALUES (4, ?)".format(col))
with self.assertRaises(TypeError):
s.execute(insert, [''])
# verify that Nones can be inserted and overwrites existing data
# create the input
params = []
for datatype in PRIMITIVE_DATATYPES:
params.append((get_sample(datatype)))
# insert the data
columns_string = ','.join(col_names)
placeholders = ','.join(["%s"] * len(col_names))
simple_insert = "INSERT INTO all_empty (zz, {0}) VALUES (5, {1})".format(columns_string, placeholders)
s.execute(simple_insert, params)
# then insert None, which should null them out
null_values = [None] * len(col_names)
s.execute(simple_insert, null_values)
# check via simple statement
query = "SELECT {0} FROM all_empty WHERE zz=5".format(columns_string)
results = s.execute(query)[0]
for col in results:
self.assertEqual(None, col)
# check via prepared statement
select = s.prepare("SELECT {0} FROM all_empty WHERE zz=?".format(columns_string))
results = s.execute(select.bind([5]))[0]
for col in results:
self.assertEqual(None, col)
# do the same thing again, but use a prepared statement to insert the nulls
s.execute(simple_insert, params)
placeholders = ','.join(["?"] * len(col_names))
insert = s.prepare("INSERT INTO all_empty (zz, {0}) VALUES (5, {1})".format(columns_string, placeholders))
s.execute(insert, null_values)
results = s.execute(query)[0]
for col in results:
self.assertEqual(None, col)
results = s.execute(select.bind([5]))[0]
for col in results:
self.assertEqual(None, col)
def test_can_insert_empty_strings_and_nulls(self):
"""
Test insertion of empty strings and null values
"""
c = Cluster(protocol_version=PROTOCOL_VERSION)
s = c.connect("typetests")
# create table
alpha_type_list = ["zz int PRIMARY KEY"]
col_names = []
start_index = ord('a')
for i, datatype in enumerate(PRIMITIVE_DATATYPES):
alpha_type_list.append("{0} {1}".format(chr(start_index + i), datatype))
col_names.append(chr(start_index + i))
s.execute("CREATE TABLE alltypes ({0})".format(', '.join(alpha_type_list)))
# verify all types initially null with simple statement
columns_string = ','.join(col_names)
s.execute("INSERT INTO alltypes (zz) VALUES (2)")
results = s.execute("SELECT {0} FROM alltypes WHERE zz=2".format(columns_string))[0]
self.assertTrue(all(x is None for x in results))
# verify all types initially null with prepared statement
select = s.prepare("SELECT {0} FROM alltypes WHERE zz=?".format(columns_string))
results = s.execute(select.bind([2]))[0]
self.assertTrue(all(x is None for x in results))
# insert empty strings for string-like fields
expected_values = {'j': '', 'a': '', 'n': ''}
columns_string = ','.join(expected_values.keys())
placeholders = ','.join(["%s"] * len(expected_values))
s.execute("INSERT INTO alltypes (zz, {0}) VALUES (3, {1})".format(columns_string, placeholders), expected_values.values())
# verify string types empty with simple statement
results = s.execute("SELECT {0} FROM alltypes WHERE zz=3".format(columns_string))[0]
for expected, actual in zip(expected_values.values(), results):
self.assertEqual(actual, expected)
# verify string types empty with prepared statement
results = s.execute(s.prepare("SELECT {0} FROM alltypes WHERE zz=?".format(columns_string)), [3])[0]
for expected, actual in zip(expected_values.values(), results):
self.assertEqual(actual, expected)
# non-string types shouldn't accept empty strings
for col in ('b', 'd', 'e', 'f', 'g', 'i', 'l', 'm', 'o'):
query = "INSERT INTO alltypes (zz, {0}) VALUES (4, %s)".format(col)
with self.assertRaises(InvalidRequest):
s.execute(query, [''])
insert = s.prepare("INSERT INTO alltypes (zz, {0}) VALUES (4, ?)".format(col))
with self.assertRaises(TypeError):
s.execute(insert, [''])
# verify that Nones can be inserted and overwrites existing data
# create the input
params = []
for datatype in PRIMITIVE_DATATYPES:
params.append((get_sample(datatype)))
# insert the data
columns_string = ','.join(col_names)
placeholders = ','.join(["%s"] * len(col_names))
simple_insert = "INSERT INTO alltypes (zz, {0}) VALUES (5, {1})".format(columns_string, placeholders)
s.execute(simple_insert, params)
# then insert None, which should null them out
null_values = [None] * len(col_names)
s.execute(simple_insert, null_values)
# check via simple statement
query = "SELECT {0} FROM alltypes WHERE zz=5".format(columns_string)
results = s.execute(query)[0]
for col in results:
self.assertEqual(None, col)
# check via prepared statement
select = s.prepare("SELECT {0} FROM alltypes WHERE zz=?".format(columns_string))
results = s.execute(select.bind([5]))[0]
for col in results:
self.assertEqual(None, col)
# do the same thing again, but use a prepared statement to insert the nulls
s.execute(simple_insert, params)
placeholders = ','.join(["?"] * len(col_names))
insert = s.prepare("INSERT INTO alltypes (zz, {0}) VALUES (5, {1})".format(columns_string, placeholders))
s.execute(insert, null_values)
results = s.execute(query)[0]
for col in results:
self.assertEqual(None, col)
results = s.execute(select.bind([5]))[0]
for col in results:
self.assertEqual(None, col)
s.shutdown()
def test_can_insert_primitive_datatypes(self):
"""
Test insertion of all datatype primitives
"""
c = Cluster(protocol_version=PROTOCOL_VERSION)
s = c.connect(self.keyspace_name)
# create table
alpha_type_list = ["zz int PRIMARY KEY"]
col_names = ["zz"]
start_index = ord('a')
for i, datatype in enumerate(PRIMITIVE_DATATYPES):
alpha_type_list.append("{0} {1}".format(chr(start_index + i), datatype))
col_names.append(chr(start_index + i))
s.execute("CREATE TABLE alltypes ({0})".format(', '.join(alpha_type_list)))
# create the input
params = [0]
for datatype in PRIMITIVE_DATATYPES:
params.append((get_sample(datatype)))
# insert into table as a simple statement
columns_string = ', '.join(col_names)
placeholders = ', '.join(["%s"] * len(col_names))
s.execute("INSERT INTO alltypes ({0}) VALUES ({1})".format(columns_string, placeholders), params)
# verify data
results = s.execute("SELECT {0} FROM alltypes WHERE zz=0".format(columns_string))[0]
for expected, actual in zip(params, results):
self.assertEqual(actual, expected)
# try the same thing sending one insert at the time
s.execute("TRUNCATE alltypes;")
for i, datatype in enumerate(PRIMITIVE_DATATYPES):
single_col_name = chr(start_index + i)
single_col_names = ["zz", single_col_name]
placeholders = ','.join(["%s"] * len(single_col_names))
single_columns_string = ', '.join(single_col_names)
for j, data_sample in enumerate(get_all_samples(datatype)):
key = i + 1000 * j
single_params = (key, data_sample)
s.execute("INSERT INTO alltypes ({0}) VALUES ({1})".format(single_columns_string, placeholders),
single_params)
# verify data
result = s.execute("SELECT {0} FROM alltypes WHERE zz=%s".format(single_columns_string), (key,))[0][1]
compare_value = data_sample
if six.PY3:
import ipaddress
if isinstance(data_sample, ipaddress.IPv4Address) or isinstance(data_sample, ipaddress.IPv6Address):
compare_value = str(data_sample)
self.assertEqual(result, compare_value)
# try the same thing with a prepared statement
placeholders = ','.join(["?"] * len(col_names))
s.execute("TRUNCATE alltypes;")
insert = s.prepare("INSERT INTO alltypes ({0}) VALUES ({1})".format(columns_string, placeholders))
s.execute(insert.bind(params))
# verify data
results = s.execute("SELECT {0} FROM alltypes WHERE zz=0".format(columns_string))[0]
for expected, actual in zip(params, results):
self.assertEqual(actual, expected)
# verify data with prepared statement query
select = s.prepare("SELECT {0} FROM alltypes WHERE zz=?".format(columns_string))
results = s.execute(select.bind([0]))[0]
for expected, actual in zip(params, results):
self.assertEqual(actual, expected)
# verify data with with prepared statement, use dictionary with no explicit columns
s.row_factory = ordered_dict_factory
select = s.prepare("SELECT * FROM alltypes")
results = s.execute(select)[0]
for expected, actual in zip(params, results.values()):
self.assertEqual(actual, expected)
c.shutdown()
