def DataTableList(cls, db=_DEFAULT_DB): # Output: List of data tables dba = DBAccess(db) sql_str = "SELECT table_name FROM %s" % settings.UPLOAD_FILE_TABLE rows, _ = dba.ExecuteQuery(sql_str) table_list = [r[0] for r in rows] return table_list
def RemoveUploadedFile(cls, filename, db=_DEFAULT_DB): # Removes an uploaded file from UPLOADED_FILE_TABLE # Input: filename - name of file to remove # db - database to use dba = DBAccess(db_path=db) sql_str = "DELETE FROM %s WHERE table_name='%s'" % ( settings.UPLOAD_FILE_TABLE, filename) dba.ExecuteQuery(sql_str, commit=True)
def test_AddFileToTable(self): return ft = FileTable(th.FILE_PATH, db=th.TEST_DB_PATH) ft._AddFileToTable() dba = DBAccess(db_path=th.TEST_DB_PATH) sql_str = "SELECT * FROM %s WHERE file_name='%s'" % ( settings.UPLOAD_FILE_TABLE, th.FILE_NAME) rows, _ = dba.ExecuteQuery(sql_str) file_name = rows[0][0] self.assertEqual(file_name, th.FILE_NAME)
def _ConstructSQLTableCreate(self, values): dba = DBAccess() if dba.IsTablePresent(self._table_name): return '' sql_str = "CREATE TABLE %s (" % self._table_name for n in range(len(self._colnames)): sql_str += "%s %s" % (self._colnames[n], SQLType(values[n])) if n+1 >= len(self._colnames): sql_str += ")" else: sql_str += "," return sql_str
def _AddFileToTable(self): # Records the file in the Django UploadedFiles table dba = DBAccess(db_path=self._db_path) if not dba.IsTablePresent(settings.UPLOAD_FILE_TABLE): raise DBError('%s does not exist' % settings.UPLOAD_FILE_TABLE) sql_str = "SELECT * from %s where table_name='%s'" % ( settings.UPLOAD_FILE_TABLE, self._filename) rows, _ = dba.ExecuteQuery(sql_str) if len(rows) == 0: sql_str = ''' INSERT into %s ('file_name', 'file_path', 'table_name') VALUES ('%s', '%s', '%s') ''' % (settings.UPLOAD_FILE_TABLE, self._filename, self._file_path, self._table_name) dba.ExecuteQuery(sql_str, commit=True)
def testDataTableList(self): return dba = DBAccess(db_path=th.TEST_DB_PATH) ft = FileTable(th.FILE_PATH, db=th.TEST_DB_PATH) sql_str = "DELETE FROM %s WHERE table_name='%s'" % ( settings.UPLOAD_FILE_TABLE, ft._table_name) try: dba.ExecuteQuery(sql_str, commit=True) except: pass old_table_set = set(FileTable.DataTableList(db=th.TEST_DB_PATH)) ft.CreateAndPopulateTable() new_table_set = set(FileTable.DataTableList(db=th.TEST_DB_PATH)) new_row_set = set([ft._table_name]) self.assertTrue(new_table_set.issuperset(new_row_set)) self.assertFalse(old_table_set.issuperset(new_row_set)) ft._ExecuteSQL('', CloseDB=True)
def maketable(request): table_list = FileTable.DataTableList(CUR_DB) if request.method == 'POST': tablename = request.POST['tablename'] table_list.remove(tablename) table_list.insert(0, tablename) # Make sure it's the default # Create a form instance and populate it with data from the request: display_form = TableForm(request.POST) # check whether it's valid: if display_form.is_valid(): numrows = GetFieldFromForm(display_form, 'numrows') lastrow = GetFieldFromForm(display_form, 'lastrow') dba = DBAccess() if not dba.IsTablePresent(tablename): raise TableviewUserError("Cannot find table %s" % tablename) headers = dba.GetSchema(tablename) sql_str = 'SELECT * FROM %s' % tablename all_rows, _ = dba.ExecuteQuery(sql_str) if 'Next' in request.POST: firstrow = lastrow + 1 else: firstrow = lastrow - 2*numrows lastrow = firstrow + numrows - 1 rows = all_rows[firstrow-1:lastrow-1] new_display_form = TableForm(initial={ 'numrows': numrows, 'lastrow': lastrow}) ctx_dict = {'rows':rows, 'display_form': new_display_form, 'tablename': tablename, 'headers': headers, 'table_list': table_list, } return render_to_response('table_view.html', ctx_dict, context_instance=RequestContext(request)) else: pass # if a GET (or any other method) we'll create a blank form else: display_form = TableForm() return render(request, 'table_request.html', {'display_form': display_form, 'table_list': table_list})
def testRemoveUploadedFile(self): return dba = DBAccess(db_path=th.TEST_DB_PATH) ft = FileTable(th.FILE_PATH, db=th.TEST_DB_PATH) ft.CreateAndPopulateTable() table_set = set(FileTable.DataTableList(db=th.TEST_DB_PATH)) self.assertTrue(table_set.issuperset(set([ft._table_name]))) FileTable.RemoveUploadedFile(ft._filename, db=th.TEST_DB_PATH) remove_table_set = set(FileTable.DataTableList(db=th.TEST_DB_PATH)) self.assertFalse(remove_table_set.issuperset(set([ft._table_name])))
def query(request): dba = DBAccess() if request.method == 'POST': # Create a form instance and populate it with data from the request: query_form = QueryForm(request.POST) # check whether it's valid: if query_form.is_valid(): query_string = GetFieldFromForm(query_form, 'query_string') if len(query_string) > 0: rows,_ = dba.ExecuteQuery(query_string) headers = dba.GetSchemaFromSelect(query_string) if len(rows[0]) != len(headers): raise TableviewInternalError("header length doesn't match row length") else: # Used in test rows = [] headers = [] ctx_dict = {'rows':rows, 'query_string': query_string, 'headers': headers, } return render_to_response('query_result.html', ctx_dict, context_instance=RequestContext(request)) else: pass # if a GET (or any other method) we'll create a blank form # Extend by including the schemas with types else: query_form = QueryForm() table_list = FileTable.DataTableList(CUR_DB) rows = [] dba = DBAccess() for table in table_list: row = [table, ", ".join(dba.GetSchema(table))] rows.append(row) return render(request, 'query_request.html', {'rows': rows, 'query_form': query_form, })