def test_get_pyodbc_master_connection_str(self):
sqlbase = SQLBase(server_name=server_name, driver_name=driver_name)
# Connects only to database called database_name
conn_str = sqlbase.get_pyodbc_master_connection_str()
test_str = 'DRIVER={SQL Server Native Client 11.0}; SERVER=.\\DT_SQLEXPRESS; DATABASE=master; Trusted_Connection=yes;'
self.assertEqual(conn_str, test_str)
return None
def test_read_table(self):
sqlbase = SQLBase(server_name=server_name, driver_name=driver_name)
# Instantiate a database connection to a specific table
(file_used_bool,
name_used_bool,
existing_database_name) = sqlbase.check_existing_database(path_mdf, database_name)
if not any((file_used_bool, name_used_bool)):
sqlbase.attach_database(path_mdf, path_ldf, database_name)
else:
# The database is already attached
pass
"""Instantiate a connection to the database. This is required before
executing SQL against a table. The SQLBase class does not automatically
connect to a database after attaching"""
sqlbase.init_database_connection(database_name)
# Read a table
sql = """SELECT * FROM [POINTBAS]"""
df = sqlbase.pandas_execute_sql(sql)
netdevid = df['NETDEVID'].iloc[0]
name = df['NAME'].iloc[0]
netdevid_test = 'JHW-R01-AHU01-PXCM71601'
name_test = 'JHW.AHU01A.MINOAD'
# Read sql into a pandas table
sql = """select top(10) *
from [{}].[dbo].[POINTBAS]""".format(database_name)
rows = sqlbase.execute_sql(sql)
name2 = rows[0].NAME
netdevid2 = rows[0].NETDEVID
self.assertTrue(name==name2==name_test)
self.assertTrue(netdevid==netdevid2==netdevid_test)
return None
def test_detach_database(self):
server_name = '.\DT_SQLEXPRESS'
driver_name = 'SQL Server Native Client 11.0'
path_mdf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB.mdf"
path_ldf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB_Log.ldf"
database_name = 'PBJobDB_test'
sqlbase = SQLBase(server_name=server_name, driver_name=driver_name)
# Check to see if a database already exists
(file_used_bool, name_used_bool,
existing_database_name) = sqlbase.check_existing_database(
path_mdf, database_name)
# Attach a database on path_mdf
if not any((file_used_bool, name_used_bool)):
sqlbase.attach_database(path_mdf, path_ldf, database_name)
sqlbase.detach_database(database_name)
# Make sure it is detached
(file_used_bool, name_used_bool,
existing_database_name) = sqlbase.check_existing_database(
path_mdf, database_name)
self.assertTrue(not any((file_used_bool, name_used_bool)))
return None
def test_get_sqlalchemy_connection_str(self):
server_name = '.\DT_SQLEXPRESS'
driver_name = 'SQL Server Native Client 11.0'
path_mdf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB.mdf"
path_ldf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB_Log.ldf"
database_name = 'PBJobDB_test'
sqlbase = SQLBase(server_name=server_name, driver_name=driver_name)
sqlalchemy_str = sqlbase.get_sqlalchemy_connection_str(database_name)
sqlalchemy_str_test = 'mssql+pyodbc://.\\DT_SQLEXPRESS/PBJobDB_test?driver={SQL Server Native Client 11.0}&trusted_connection=yes'
self.assertEqual(sqlalchemy_str, sqlalchemy_str_test)
return None
def test_path_equal(self):
path1 = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB.mdf"
path2 = r"C:/Users/z003vrzk/.spyder-py3/Scripts/Work/PanelBuilder/panel_builder/SQLTest/JHW/JobDB.mdf"
self.assertTrue(SQLBase.path_equal(path1, path2))
return None
def test_get_pyodbc_master_connection_str(self):
server_name = '.\DT_SQLEXPRESS'
driver_name = 'SQL Server Native Client 11.0'
path_mdf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB.mdf"
path_ldf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB_Log.ldf"
database_name = 'PBJobDB_test'
sqlbase = SQLBase(server_name=server_name, driver_name=driver_name)
# Connects only to database called database_name
# Use connection string to execute SQL if needed
conn_str = sqlbase.get_pyodbc_master_connection_str()
test_str = 'DRIVER={SQL Server Native Client 11.0}; SERVER=.\\DT_SQLEXPRESS; DATABASE=master; Trusted_Connection=yes;'
self.assertEqual(conn_str, test_str)
return None
def __init__(self, *args, **kwargs):
super(Test_BOM_Generator, self).__init__(*args, **kwargs)
global sqlbase, BomGenerator, server_name, driver_name, path_mdf
global path_ldf, path_j_vars, database_name, product_db
server_name = '.\DT_SQLEXPRESS'
driver_name = 'SQL Server Native Client 11.0'
path_mdf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\panel_builder\SQLTest\JHW\JobDB.mdf"
path_ldf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\panel_builder\SQLTest\JHW\JobDB_Log.ldf"
path_j_vars = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\panel_builder\SQLTest\JHW\j_vars.ini"
database_name = 'PBJobDB_test'
product_db = 'ProductDB'
sqlbase = SQLBase(server_name, driver_name)
# Check to see if a database already exists
(file_used_bool, name_used_bool,
existing_database_name) = sqlbase.check_existing_database(
path_mdf, database_name)
# Attach a database on path_mdf
if not any((file_used_bool, name_used_bool)):
sqlbase.attach_database(path_mdf, path_ldf, database_name)
else:
# The database is already attached
database_name = existing_database_name
pass
sqlbase.init_database_connection(database_name)
BomGenerator = BOMGenerator(path_mdf, path_ldf, path_j_vars,
database_name, sqlbase)
return None
def __init__(self):
# Initialize connection to SQL database
sql_server = 'MD2E4BFC\DT_SQLEXPRESS'
sql_driver = "SQL Server Native Client 11.0"
database_name = 'JobDB'
sql_base = SQLBase(sql_server, sql_driver)
sql_base.init_database_connection(database_name)
# Other globals
path_mdf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\panel_builder\SQLTest\301295_HCA_Clear_Lake_Chiller_Upfit\JobDB.mdf"
controller_name = 'TEMPLATE_PXC';
point_type = 'LAI'
sensor_type = 'CURRENT'
virtual = 0
point_type_tuple = ('LDI','LPACI')
return None
def init_sql_master_connection(self):
"""Create a database connection for other apps to use"""
# Read configuration options for database connection
if '_DEFAULT_FILE_PATH' not in self.__dict__:
self.init_application_options()
options = OptionsDialog(self)._action_read_json_options(self._OPTIONS_FILE_PATH)
self.sql_server = options['sql_server']
self.sql_driver = options['sql_driver']
self.default_jobs_folder = options['default_jobs_folder']
# Initialize database connection to master database
try:
self.SQLBase = SQLBase(self.sql_server, self.sql_driver)
except Exception as e:
msg=('There was an error connecting to the specified SQL Server' +
' instance. Please navigate to options and enter a correct' +
' SQL Server hostname.\n' +
'SQL Server : {}\n' +
'SQL Driver : {}'.format(self.sql_server, self.sql_driver))
self.action_user_message('Database Connection', msg, detail=str(e))
return None
def test_read_table(self):
server_name = '.\DT_SQLEXPRESS'
driver_name = 'SQL Server Native Client 11.0'
path_mdf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB.mdf"
path_ldf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB_Log.ldf"
database_name = 'PBJobDB_test'
sqlbase = SQLBase(server_name=server_name, driver_name=driver_name)
# Instantiate a database connection to a specific table
(file_used_bool, name_used_bool,
existing_database_name) = sqlbase.check_existing_database(
path_mdf, database_name)
if not any((file_used_bool, name_used_bool)):
sqlbase.attach_database(path_mdf, path_ldf, database_name)
else:
# The database is already attached
pass
"""Instantiate a connection to the database. This is required before
executing SQL against a table. The SQLBase class does not automatically
connect to a database after attaching"""
sqlbase.init_database_connection(database_name)
# Read a table
sql = """SELECT * FROM [POINTBAS]"""
df = sqlbase.pandas_execute_sql(sql)
netdevid = df['NETDEVID'].iloc[0]
name = df['NAME'].iloc[0]
netdevid_test = 'JHW-R01-AHU01-PXCM71601'
name_test = 'JHW.AHU01A.MINOAD'
# Read sql into a pandas table
sql = """select top(10) *
from [{}].[dbo].[POINTBAS]""".format(database_name)
rows = sqlbase.execute_sql(sql)
name2 = rows[0].NAME
netdevid2 = rows[0].NETDEVID
self.assertTrue(name == name2 == name_test)
self.assertTrue(netdevid == netdevid2 == netdevid_test)
return None
def test_detach_database(self):
sqlbase = SQLBase(server_name=server_name, driver_name=driver_name)
# Check to see if a database already exists
(file_used_bool,
name_used_bool,
existing_database_name) = sqlbase.check_existing_database(path_mdf, database_name)
# Attach a database on path_mdf
if not any((file_used_bool, name_used_bool)):
sqlbase.attach_database(path_mdf, path_ldf, database_name)
sqlbase.detach_database(database_name)
# Make sure it is detached
(file_used_bool,
name_used_bool,
existing_database_name) = sqlbase.check_existing_database(path_mdf, database_name)
self.assertTrue(not any((file_used_bool, name_used_bool)))
return None
def test_attach_database(self):
server_name = '.\DT_SQLEXPRESS'
driver_name = 'SQL Server Native Client 11.0'
path_mdf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB.mdf"
path_ldf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB_Log.ldf"
database_name = 'PBJobDB_test'
# Connection
sqlbase = SQLBase(server_name=server_name, driver_name=driver_name)
# Check to see if a database already exists
(file_used_bool, name_used_bool,
existing_database_name) = sqlbase.check_existing_database(
path_mdf, database_name)
"""If the name is already in use then detach that database"""
if name_used_bool:
sqlbase.detach_database(existing_database_name)
"""If the file is already attached then detach the database"""
if file_used_bool:
sqlbase.detach_database(existing_database_name)
# Attach database
sqlbase.attach_database(path_mdf, path_ldf, database_name)
# Execute transactions on the database to make sure its connected
sql = """SELECT * FROM [master].[sys].[databases]"""
with sqlbase.master_connection.cursor() as cursor:
cursor.execute(sql)
row = cursor.fetchone()
while row:
if row.name == database_name:
print('Found Myself')
break
row = cursor.fetchone()
if row.name != database_name:
raise (ValueError(
'Did not fine {} in query'.format(database_name)))
# Finish and detach database
sqlbase.detach_database(database_name)
return None
class AppContext(ApplicationContext):
def __init__(self, *args, **kwargs):
super(AppContext, self).__init__(*args, **kwargs)
self.window = MainWindow(self)
return None
def run(self):
self.window.show()
return self.app.exec_()
def init_sql_master_connection(self):
"""Create a database connection for other apps to use"""
# Read configuration options for database connection
if '_DEFAULT_FILE_PATH' not in self.__dict__:
self.init_application_options()
options = OptionsDialog(self)._action_read_json_options(self._OPTIONS_FILE_PATH)
self.sql_server = options['sql_server']
self.sql_driver = options['sql_driver']
self.default_jobs_folder = options['default_jobs_folder']
# Initialize database connection to master database
try:
self.SQLBase = SQLBase(self.sql_server, self.sql_driver)
except Exception as e:
msg=('There was an error connecting to the specified SQL Server' +
' instance. Please navigate to options and enter a correct' +
' SQL Server hostname.\n' +
'SQL Server : {}\n' +
'SQL Driver : {}'.format(self.sql_server, self.sql_driver))
self.action_user_message('Database Connection', msg, detail=str(e))
return None
def init_sql_database_connection(self, path_mdf, path_ldf, database_name):
"""
If a database is already attached then file_used_bool will be True.
If name_used_bool is True then the logical database name is in use.
existing_database_name is a string if file_used_bool is True"""
(file_used_bool,
name_used_bool,
existing_database_name) = self.SQLBase\
.check_existing_database(path_mdf, database_name)
if file_used_bool:
# The SQL Database file is in use by other program
# Dont try and attach the database
self.database_name = existing_database_name
self.SQLBase.init_database_connection(self.database_name)
elif name_used_bool and not file_used_bool:
# The current database name is in use, and the current .mdf file
# Cannot be attached under that name
# Try to attach the database with a new name
now = datetime.now()
database_name = database_name + now.strftime('%m%d%H%M%S')
self.database_name = database_name
self.SQLBase.attach_database(path_mdf,
database_name=database_name,
path_ldf=path_ldf)
self.SQLBase.init_database_connection(self.database_name)
else:
# The name and database file are not in use - Try to attach
self.SQLBase.attach_database(path_mdf,
path_ldf=path_ldf,
database_name=database_name)
self.SQLBase.init_database_connection(self.database_name)
return None
def init_application_options(self):
self._OPTIONS_FILE_PATH = r'./pb_options.json'
self._DEFAULT_OPTIONS = {'sql_server':'.\DT_SQLEXPRESS',
'products_db':'C:\Program Files\DesignTool\Products',
'sql_driver':'SQL Server Native Client 11.0',
'default_jobs_folder': r'C:\\',
}
self.database_name = 'PBJobDB'
return None
@cached_property
def main_icon(self):
return QIcon(self.get_resource('images/PBIcon.ico'))
@cached_property
def report_icon(self):
return QIcon(self.get_resource('images/chrome_reader_mode-24px.ico'))
@cached_property
def options_icon(self):
return QIcon(self.get_resource('images/settings_applications-24px.svg'))
@cached_property
def help_icon(self):
return QIcon(self.get_resource('images/help-24px.svg'))
@cached_property
def alarm_icon(self):
return QIcon(self.get_resource('images/alarm-24px.svg'))
def test_check_existing_database(self):
sqlbase = SQLBase(server_name=server_name, driver_name=driver_name)
# Check to see if a database already exists
(file_used_bool,
name_used_bool,
existing_database_name) = sqlbase.check_existing_database(path_mdf, database_name)
# Attach a database on path_mdf
if not any((file_used_bool, name_used_bool)):
sqlbase.attach_database(path_mdf, path_ldf, database_name)
else:
sqlbase.detach_database(existing_database_name)
sqlbase.attach_database(path_mdf, path_ldf, database_name)
"""file_used_bool should be True if the database file is already in use
"""
(file_used_bool,
name_used_bool,
existing_database_name) = sqlbase.check_existing_database(path_mdf, database_name)
self.assertTrue(file_used_bool)
self.assertTrue(name_used_bool)
self.assertTrue(existing_database_name == database_name)
sqlbase.detach_database(database_name)
return None
def test_attach_database(self):
# Connection
sqlbase = SQLBase(server_name=server_name, driver_name=driver_name)
# Check to see if a database already exists
(file_used_bool,
name_used_bool,
existing_database_name) = sqlbase.check_existing_database(path_mdf, database_name)
"""If the name is already in use then detach that database"""
if name_used_bool:
sqlbase.detach_database(existing_database_name)
"""If the file is already attached then detach the database"""
if file_used_bool:
sqlbase.detach_database(existing_database_name)
# Attach database
sqlbase.attach_database(path_mdf, path_ldf, database_name)
# Execute transactions on the database to make sure its connected
sql = """SELECT * FROM [master].[sys].[databases]"""
with sqlbase.master_connection.cursor() as cursor:
cursor.execute(sql)
row = cursor.fetchone()
while row:
if row.name == database_name:
print('Found Myself')
break
row = cursor.fetchone()
if row.name != database_name:
raise(ValueError('Did not fine {} in query'.format(database_name)))
# Finish and detach database
sqlbase.detach_database(database_name)
return None
# local imports
from sql_tools import SQLBase
from sql_tools import sqlalchemy_connection_str, pyodbc_connection_str
import sqlalchemy
import pyodbc
# Globals
server_name = '.\SQLEXPRESS'
driver_name = 'SQL Server Native Client 11.0'
database_name = 'Clustering'
path_mdf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB.mdf"
path_ldf = r"C:\Users\z003vrzk\.spyder-py3\Scripts\Work\PanelBuilder\panel_builder\SQLTest\JHW\JobDB_Log.ldf"
sqlbase = SQLBase(server_name=server_name, driver_name=driver_name)
#%%
class SQLTest(unittest.TestCase):
def test_get_pyodbc_database_connection_str(self):
# Connects only to database called database_name
conn_str = sqlbase.get_pyodbc_database_connection_str(database_name)
test_str = 'DRIVER={SQL Server Native Client 11.0}; SERVER=.\\DT_SQLEXPRESS; DATABASE=PBJobDB_test; Trusted_Connection=yes;'
self.assertEqual(conn_str, test_str)
return None
