def get_validity(project_id): db_handler = SQLServerHandler() result = db_handler.exec_query("select DataGuid, isvalid from {table} where projectid = {project_id}".format( table=mssql_database+mssql_tables['data_result'], project_id=project_id) ) result = {str(r[0]):r[1] for r in result} db_handler.close() return result
def find_project_id(name): db_handler = SQLServerHandler() result = db_handler.exec_query("select * from {table} where name like '%{name}%'".format( table=mssql_database+mssql_tables['project'], name=name) ) if result: if len(result) > 1: print("{0} results found, confirms which one was required: \n") for i, line in enumerate(result): print("{0}. {1} - {2}".format(i, line[0], line[2].encode('utf-8'))) project_id = raw_input("enter the project id you need: ") #TODO: check the value to see if it was valid elif len(result) == 1: project_id = result[0][0] else: print("no result has found") project_id = None db_handler.close() return project_id
def setUp(self): self.db_handler = SQLServerHandler() if self.db_handler.exec_query(r"select * from sys.tables where name='TestData'"): self.db_handler.exec_commit("drop table dbo.TestData") self.db_handler.exec_commit("create table dbo.TestData (test_id int PRIMARY KEY NOT NULL, test_content varchar(10))")
class SQLServerHandlerTestCase(unittest.TestCase): def setUp(self): self.db_handler = SQLServerHandler() if self.db_handler.exec_query(r"select * from sys.tables where name='TestData'"): self.db_handler.exec_commit("drop table dbo.TestData") self.db_handler.exec_commit("create table dbo.TestData (test_id int PRIMARY KEY NOT NULL, test_content varchar(10))") def tearDown(self): self.db_handler.exec_commit("drop table dbo.TestData") # pass def test_connect(self): # close the connection before testing if self.db_handler.conn: self.db_handler.close() self.assertIsNone(self.db_handler.conn, 'did not close the connection') self.assertIsNotNone(self.db_handler.connect(), 'unable to provide a reliable conncetion') def test_query(self): self.assertEqual(len(self.db_handler.exec_query("select top 5 * from DataAcquisition")), 5) # self.assertEqual(len(self.db_handler.exec_query("select top 5 * from sys.tables")), 5) self.assertEqual(self.db_handler.exec_query("select ProjectId from DataAcquisition where ProjectId = 1774")[0][0], 1774) # self.assertEqual(self.db_handler.exec_query("select id from sys.tables where id = 1")[0][0], 1) def test_commit(self): self.assertIsNotNone(self.db_handler.exec_query(r"select * from sys.tables where name='TestData'"), 'create tabel failed') self.db_handler.exec_commit(r"insert into TestData values (1, 'test1')") self.assertIsNotNone(self.db_handler.exec_query("select * from TestData where test_id=1")) self.db_handler.exec_commit(r"update TestData set test_content='test2' where test_id=1") self.assertEqual(self.db_handler.exec_query(r"select test_content from TestData")[0][0], 'test2', 'update faield')