def tbl_col_update(database, tbl_name, old_col_name, new_col_name):
''' sqlite does not support change column name so have implemented a simple
version; does not handle complex db features which we will not be needing currently'''
col_defn = tbl_cols_get(database, tbl_name)
new_col_defn = []
for colname, coltype in col_defn:
if colname == old_col_name:
new_col_defn.append((new_col_name, coltype))
else:
new_col_defn.append((colname, coltype))
# try to drop tmp table in case left behind from prev run
try:
tbl_remove(database, tbl_name + "_new")
except:
pass
tbl_create(database, tbl_name + "_new", new_col_defn)
colnames, rows, exec_str = tbl_rows_get(database, tbl_name)
colindex = colnames.index(old_col_name)
colnames.remove(old_col_name)
colnames.insert(colindex, new_col_name)
rows = _quotestrs(rows)
tbl_rows_insert(database, tbl_name + "_new", colnames, rows)
tbl_remove(database, tbl_name)
tbl_rename(database, tbl_name + "_new", tbl_name)
def test_tbl_rows_insert_dupe_key(self):
database = Database(test_db.name, remove_flag=True)
with database:
tbl_create(database, test_db.tbl_name, test_db.col_defn,
test_db.tbl_pk_defn)
with self.assertRaises(S3IntegrityError):
tbl_rows_insert(database, test_db.tbl_name, test_db.col_name,
test_db.tbl_rows_dupe_key)
def test_db_create(self):
database = Database(test_db.name)
with database:
tbl_create(database, test_db.tbl_name, test_db.col_defn)
database = Database(test_db.name, True)
with database:
self.assertTrue(database.tbl_exists(test_db.tbl_name))
def persist(self, createtable=True):
self._metadata_set()
if not tbl_exists(self.database, self.tbl_name) == True:
tbl_create(self.database, self.tbl_name, self.tbl_col_defn)
result, exec_str = tbl_rows_insert(self.database, self.tbl_name,
self.tbl_col_names,
self.tbl_row_values)
return (result, exec_str)
def setUp(self):
self.database_name = 'foobar'
self.table_name = 'foobar'
columns = ['col1', 'col2', 'col3']
column_defn = [('col1', 'text'), ('col2', 'text'), ('col3', 'integer')]
self.row = [['x', 'y', 6]]
self.qrow = _quotestrs(self.row)
self.filename = "b64pyshell.txt"
self.b64row = _quotestrs(DatabaseBase._encode_2darray(self.row))
database = Database('foobar')
with database:
tbl_create(database, self.database_name, column_defn)
tbl_rows_insert(database, self.table_name, columns, self.b64row)
def test_tbl_rows_get_all(self):
database = Database(test_db.name)
with database:
tbl_create(database, test_db.tbl_name, test_db.col_defn)
tbl_rows_insert(database, test_db.tbl_name, test_db.col_name,
test_db.tbl_rows)
database = Database(test_db.name, remove_flag=True)
with database:
col_name, tbl_rows, _ = tbl_rows_get(database, test_db.tbl_name)
self.assertListEqual(col_name, test_db.col_name)
self.assertListEqual(tbl_rows, test_db.tbl_rows)
def test_tbl_rows_insert_str(self):
database = Database(test_db_str.name)
with database:
tbl_create(database, test_db_str.tbl_name, test_db_str.col_defn)
tbl_rows_insert(database, test_db_str.tbl_name,
test_db_str.col_name, test_db_str.tbl_rows)
database = Database(test_db_str.name, remove_flag=True)
with database:
self.assertEquals(
[['foobar', 'barfoo']],
database.execute(
"select col_name1,col_name2 from tbl_name_test"))
def test_tbl_rows_get_spoecific_field(self):
database = Database(test_db.name)
with database:
tbl_create(database, test_db.tbl_name, test_db.col_defn)
tbl_rows_insert(database, test_db.tbl_name, test_db.col_name,
test_db.tbl_rows)
database = Database(test_db.name, remove_flag=True)
with database:
col_name, tbl_rows, _ = tbl_rows_get(
database, test_db.tbl_name,
['col_name1', 'col_name2', 'col_name3', 'col_name4'])
self.assertListEqual(col_name, test_db.col_name)
self.assertListEqual(tbl_rows, test_db.tbl_rows)
def test_tbl_rows_insert_str_1col(self):
database = Database(test_db_str_1col.name)
with database:
tbl_create(database, test_db_str_1col.tbl_name,
test_db_str_1col.col_defn)
tbl_rows_insert(database, test_db_str_1col.tbl_name,
test_db_str_1col.col_name,
test_db_str_1col.tbl_rows)
database = Database(test_db_str_1col.name, remove_flag=True)
with database:
self.assertEquals(
'foobar',
database.execute("select col_name1 from tbl_name_test", True))
def test_tbl_create_pk(self):
database = Database(test_db.name)
with database:
tbl_create(database, test_db.tbl_name, test_db.col_defn,
test_db.tbl_pk_defn)
database = Database(test_db.name, True)
with database:
self.assertEquals(1, tbl_index_count(database, test_db.tbl_name))
self.assertListEqual(
test_db.tbl_pk_defn,
tbl_index_defn_get(database, test_db.tbl_name))
def tbl_move(database, database_new, tbl_name, overwrite=False):
# get old table details
with database:
col_defn = tbl_cols_get(database, tbl_name)
colnames, rows, exec_str = tbl_rows_get(database, tbl_name)
with database_new:
# create new table details
if overwrite == True:
try:
tbl_remove(database_new, tbl_name)
except:
pass
tbl_create(database_new, tbl_name, col_defn)
# insert new data
rows = _quotestrs(rows)
tbl_rows_insert(database_new, tbl_name, colnames, rows)
def test_(self):
expected_results = "<root><tbl_name_test><row><col_name1>1</col_name1><col_name2>2</col_name2><col_name3>3</col_name3><col_name4>4</col_name4></row></tbl_name_test></root>"
database = Database(test_db.name)
with database:
tbl_create(database, test_db.tbl_name, test_db.col_defn)
tbl_rows_insert(database, test_db.tbl_name, test_db.col_name,
test_db.tbl_rows)
database = Database(test_db.name, remove_flag=True)
with database:
xmlroot = xmltree.Element('root')
whereclause = [["col_name1", "=", 1]]
tbl_to_xml(database,
test_db.tbl_name,
['col_name1', 'col_name2', 'col_name3', 'col_name4'],
whereclause,
xmlroot=xmlroot)
self.assertEqual(expected_results, xmltree.tostring(xmlroot))
def _create_table(self, tbl_name, col_defn, tbl_pk_defn=[]):
with self.database:
tbl_create(self.database, tbl_name, col_defn, tbl_pk_defn)
def setUp(self):
database = Database(test_db.name)
with database:
tbl_create(database, test_db.tbl_name, test_db.col_defn,
test_db.tbl_pk_defn)
