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')
