Madhu Madhu -4 years ago 150
Python Question

How to check if table exists in DataBase using Robot Framework

I am trying to check whether table-name exists in database and it is throwing me that schema doesn't exist.I have tried to get the values from table and it is successful.Following is the code that I am trying.

***Settings***
Library DatabaseLibrary
Library Collections

***Testcases***
Connect to Vertica and Check if table exist
Connect To Database Using Custom Params vertica_python database='pmdb',user='dbadmin', password='warehouse', host='10.166.12.242', port=5433
Table Must Exist DCA_ITOC_RESOURCE_D
#${tableName} Query select table_name from tables where table_schema='OBR' AND table_name='DCA_ITOC_RESOURCE_D'
#List Should Contain Value ${tableName} DCA_ITOC_RESOURCE_D


Test result

root@hyi01lr0bsaehost92:/var/robot-tests# pybot database-tests.robot
==============================================================================
Database-Tests
==============================================================================
Connect to Vertica and Check if table exist | FAIL |
MissingSchema: Severity: ERROR, Message: Schema "information_schema" does not exist, Sqlstate: 3F000, Routine: RangeVarGetObjid, File: /scratch_a/release/svrtar1291/vbuild/vertica/Catalog/Namespace.cpp, Line: 288, SQL: u"SELECT * FROM information_schema.tables WHERE table_name='DCA_ITOC_RESOURCE_D'"
------------------------------------------------------------------------------
Database-Tests | FAIL |
1 critical test, 0 passed, 1 failed
1 test total, 0 passed, 1 failed
==============================================================================
Output: /var/robot-tests/output.xml
Log: /var/robot-tests/log.html
Report: /var/robot-tests/report.html

Answer Source

This has worked for me after adding vertica query in assertion.py in databaselibrary module

 def table_must_exist(self, tableName, sansTran=False):
        """
        Check if the table given exists in the database. Set optional input `sansTran` to True to run command without an
        explicit transaction commit or rollback.

        For example, given we have a table `person` in a database

        When you do the following:
        | Table Must Exist | person |

        Then you will get the following:
        | Table Must Exist | person | # PASS |
        | Table Must Exist | first_name | # FAIL |

        Using optional `sansTran` to run command without an explicit transaction commit or rollback:
        | Table Must Exist | person | True |
        """
        logger.info('Executing : Table Must Exist  |  %s ' % 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 v_catalog.columns WHERE table_schema='OBR' AND table_name='%s'" % tableName)
        #else:
        #    selectStatement = ("SELECT * FROM information_schema.tables WHERE table_name='%s'" % tableName)
        num_rows = self.row_count(selectStatement, sansTran)
        if num_rows == 0:
            raise AssertionError("Table '%s' does not exist in the db" % tableName)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download