robot framework學習筆記之七—連接配接mysql資料庫

     1.安裝Database-Library

     輸入指令：pip install robotframework_databaselibrary

     2.添加Database的Library

    3.執行個體

*** Test Cases ***
查詢單條資料 
    ${data}    create dictionary    shopCode=xxx    coordinate=
    ${response}    Rest.Get    /api/shop/current.json    ${data}    form    ${hosts["weidian"]}
    Connect To Database Using Custom Params    pymysql    host='115.29.xx.xxx', port=3306, user='xx', passwd='xxx', db='test', charset= 'utf8'
    ${name}    query    select nickname from authshop where code='xxx'
    log    ${response["data"]["shop"]["shopName"]}
    should be equal    ${response['data']['shop']['shopName']}    ${name[0][0]}
    log    ${name[0][0]}

查詢多條資料


資料庫中存在
    #Check If Exists In Database
    ${data}    create dictionary    shopCode=xxx    coordinate=
    ${response}    Rest.Get    /api/shop/current.json    ${data}    form    ${hosts["weidian"]}
    Connect To Database Using Custom Params    pymysql    host='115.29.xx.xx', port=3306, user='test', passwd='xxx', db='test', charset= 'utf8'
    Check If Exists In Database    select nickname from authshop where code='xxx'

資料庫中不存在
    #Check If Not Exists In Database
    ${data}    create dictionary    shopCode=xxx    coordinate=
    ${response}    Rest.Get    /api/shop/current.json    ${data}    form    ${hosts["weidian"]}
    Connect To Database Using Custom Params    pymysql    host='115.29.xx.xxx', port=3306, user='xx', passwd='xx', db='test', charset= 'utf8'
    Check If Exists In Database    select nickname from authshop where code='xxx'

查詢
    #Description
    ${data}    create dictionary    shopCode=xxx    coordinate=
    ${response}    Rest.Get    /api/shop/current.json    ${data}    form    ${hosts["weidian"]}
    Connect To Database Using Custom Params    pymysql    host='115.29.xx.xxx', port=3306, user='xxx', passwd='xxx', db='test', charset= 'utf8'
    ${qeeryResults}    query    select nickname from authshop where code='xxx'
    log    ${qeeryResults}

傳回查詢結果總行數
#Row Count
    ${data}    create dictionary    shopCode=xxx    coordinate=
    ${response}    Rest.Get    /api/shop/current.json    ${data}    form    ${hosts["weidian"]}
    Connect To Database Using Custom Params    pymysql    host='115.29.xx.xxx', port=3306, user='xxx', passwd='xxx', db='xxx', charset= 'utf8'
    ${qeeryResults}    row count    select shop_code from authshop_relation where site_id='1'
    log    ${qeeryResults}      

  4.api詳情

API	參數	說明
Check If Exists In Database	查詢語句	資料庫中存在：存在，則為PASS，不存在，則為FAIL
Check If Not Exists In Database	查詢語句	資料庫中不存在：不存在，則為PASS，存在，則為FAIL
Connect To Database	dbapiModuleName=None, dbName=None, dbUsername=None, dbPassword=None, dbHost=localhost, dbPort=5432, dbConfigFile=./resources/db.cfg	連接配接資料庫配制資訊，通過配制各項屬性完成
Connect To Database Using Custom Params	dbapiModuleName=None, db_connect_string=	連接配接資料庫配制資訊，通過db_connect_string來傳遞。如database='my_db_test', user='postgres', password='s3cr3t', host='tiger.foobar.com', port=5432
Delete All Rows From Table	表名	删除資料庫中表的所有行：删除成功，則PASS；失敗，則Fail
Description	查詢語句	傳回查詢語句的結果，内容格式為： [Column(name='id', type_code=1043, display_size=None, internal_size=255, precision=None, scale=None, null_ok=None)] [Column(name='first_name', type_code=1043, display_size=None, internal_size=255, precision=None, scale=None, null_ok=None)] [Column(name='last_name', type_code=1043, display_size=None, internal_size=255, precision=None, scale=None, null_ok=None)]
Disconnect From Database	無	斷開資料庫連結
Execute Sql Script	SqlScriptFileName	執行腳本檔案。SqlScriptFileName實體路徑＋FileName。
Execute Sql String	SqlString	執行Sql語句
Query	查詢語句	傳回查詢語句的查詢結果
Row Count	查詢語句	傳回查詢語句的查詢結果行總數
Row Count Is 0	查詢語句	查詢語句的查詢結果行總數：為0,則PASS;非0,則FAIL
Row Count Is Equal To X	查詢語句，X	查詢語句的查詢結果行總數：為X,則PASS;非X，則FAIL X，為預期的查詢結果行數，第二個需要傳遞的參數
Row Count Is Greater Than X	查詢語句，X	查詢語句的查詢結果行總數：大于X,則PASS;小于或等于X，則FAIL X，為預期的查詢結果行數，第二個需要傳遞的參數
Row Count Is Less Than X	查詢語句，X	查詢語句的查詢結果行總數：大于X,則PASS;小于或等于X，則FAIL X，為預期的查詢結果行數，第二個需要傳遞的參數
Table Must Exist	表名	表名必須存在：存在，則PASS；不存在，則FAIL

   5.執行個體二

#Connect To Database Using Custom Params:使用參數來連接配接資料庫    pymysql：連接配接的資料庫     charset=‘utf8’解決中文顯示成？？？的問題
    Connect To Database Using Custom Params    pymysql    host=‘115.29.10.xxx’, port=3306, user=‘xxx’, passwd=‘xxx’, db=‘xxx’,charset=‘utf8’
    #使用Query方法來執行"select name from wx_account_detail where type=0”，并将查詢的結果傳回對變量@{name}
    @{name}    query    select name from wx_account_detail where type=0
    #循環擷取多條記錄的值，并列印
    : FOR    ${i}    IN RANGE    0    len(@{name})
        log    @{name}[${i}]