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)