How to install =>
http://franz-see.github.io/Robotframework-Database-Library/
สำหรับ DB2 ให้ลงเพิ่ม
pip install ibm_db
เพิ่ม code ทั้ง 3 File ต่อไปนี้
C:\Python27\Lib\site-packages\DatabaseLibrary\connection_manager.py
สำหรับ connect to database
=============================================================================================
def connect_to_database(self, dbapiModuleName=None, dbName=None, dbUsername=None, dbPassword=None, dbHost='localhost', dbPort="5432", dbConfigFile="./resources/db.cfg"):
config = ConfigParser.ConfigParser()
config.read([dbConfigFile])
dbapiModuleName = dbapiModuleName or config.get('default', 'dbapiModuleName')
dbName = dbName or config.get('default', 'dbName')
dbUsername = dbUsername or config.get('default', 'dbUsername')
dbPassword = dbPassword or config.get('default', 'dbPassword')
dbHost = dbHost or config.get('default', 'dbHost') or 'localhost'
dbPort = int(dbPort or config.get('default', 'dbPort'))
db_api_2 = __import__(dbapiModuleName)
if dbapiModuleName in ["MySQLdb", "pymysql"]:
dbPort = dbPort or 3306
logger.debug ('Connecting using : %s.connect(db=%s, user=%s, passwd=%s, host=%s, port=%s) ' % (dbapiModuleName, dbName, dbUsername, dbPassword, dbHost, dbPort))
self._dbconnection = db_api_2.connect (db=dbName, user=dbUsername, passwd=dbPassword, host=dbHost, port=dbPort)
elif dbapiModuleName in ["psycopg2"]:
dbPort = dbPort or 5432
logger.debug ('Connecting using : %s.connect(database=%s, user=%s, password=%s, host=%s, port=%s) ' % (dbapiModuleName, dbName, dbUsername, dbPassword, dbHost, dbPort))
self._dbconnection = db_api_2.connect (database=dbName, user=dbUsername, password=dbPassword, host=dbHost, port=dbPort)
elif dbapiModuleName in ["ibm_db"]:
dbPort = dbPort or 50000
logger.debug ('Connecting using : %s.connect(DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;UID=%s;PWD=%s;) ' % (dbapiModuleName, dbName, dbHost, dbPort, dbUsername, dbPassword))
self._dbconnection = db_api_2.connect ('DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;UID=%s;PWD=%s;' % (dbName, dbHost, dbPort, dbUsername, dbPassword), '', '')
else:
logger.debug ('Connecting using : %s.connect(database=%s, user=%s, password=%s, host=%s, port=%s) ' % (dbapiModuleName, dbName, dbUsername, dbPassword, dbHost, dbPort))
self._dbconnection = db_api_2.connect (database=dbName, user=dbUsername, password=dbPassword, host=dbHost, port=dbPort)
=============================================================================================
C:\Python27\Lib\site-packages\DatabaseLibrary\assertion.py
สำหรับ initial statement
=============================================================================================
def table_must_exist(self,tableName):
if self.db_api_module_name in ["cx_Oracle"]:
selectStatement = ("SELECT * FROM all_objects WHERE object_type IN ('TABLE','VIEW') AND owner = SYS_CONTEXT('USERENV', 'SESSION_USER') AND object_name = UPPER('%s')" % tableName)
elif self.db_api_module_name in ["sqlite3"]:
selectStatement = ("SELECT name FROM sqlite_master WHERE type='table' AND name='%s' COLLATE NOCASE" % tableName)
elif self.db_api_module_name in ["ibm_db", "ibm_db_dbi"]:
selectStatement = ("SELECT name FROM SYSIBM.SYSTABLES WHERE type='T' AND name=UPPER('%s')" % tableName)
else:
selectStatement = ("SELECT * FROM information_schema.tables WHERE table_name='%s'" % tableName)
num_rows = self.row_count(selectStatement)
if (num_rows == 0):
raise AssertionError("Table '%s' does not exist in the db" % tableName)
=============================================================================================
C:\Python27\Lib\site-packages\DatabaseLibrary\query.py
สำหรับ iqnore comment ที่เป็นแบบ "--"
=============================================================================================
def execute_sql_script(self, sqlScriptFileName):
sqlScriptFile = open(sqlScriptFileName)
cur = None
try:
cur = self._dbconnection.cursor()
sqlStatement = ''
for line in sqlScriptFile:
line = line.strip()
if line.startswith('#'):
continue
elif line.startswith('--'):
continue
sqlFragments = line.split(';')
if len(sqlFragments) == 1:
sqlStatement += line + ' '
else:
for sqlFragment in sqlFragments:
sqlFragment = sqlFragment.strip()
if len(sqlFragment) == 0:
continue
sqlStatement += sqlFragment + ' '
self.__execute_sql(cur, sqlStatement)
sqlStatement = ''
sqlStatement = sqlStatement.strip()
if len(sqlStatement) != 0:
self.__execute_sql(cur, sqlStatement)
self._dbconnection.commit()
finally:
if cur :
self._dbconnection.rollback()
=============================================================================================
ใน code ที่เขียน robot framework
Connect To Database
ibm_db <DB_Name> <USER> <PASS> <HOST_Name or IP> <PORT>
credit:น้องกิบ, พี่บอม
