Insert Data to Database( SQL Server) with Robort Framework.

Another way to Insert Data into Database without using script, it is inserting data into database with Robort Framework. Robot Framework is not only use for automation test web and mobile, but you can use it to manage database too.

For this example, I want to create 100 users for doing load test performance which is need many account. So I use Robot to loop insert user.

Settings part, this library is the lib that you have to install for using database.

Library           DatabaseLibrary

Variables part, create variable for DBHost, DBpassword, DBPort, and DBUser

${DBHost}         192.xxx.x.xx
${DBPass}         Passw0rd
${DBPort}         1433
${DBUser}         administrator

Keywords part.

1. Create keyword to connect your database, we use “pymssql” for SQL Server. If you want to connect to others database, you cannot use this.

pymssql : A simple database interface for Python that builds on top of FreeTDS to provide a Python DB-API (PEP-249) interface to Microsoft SQL Server.

Connect to Database
    Connect To Database    pymssql    tableName   ${DBUser}           \   ${DBPass}    ${DBHost}    ${DBPort}

2. Create keyword to insert data. For this example, I use loop to insert user into dbo.Users. You do not need to assing Id in VALUES it will automatically generate. And the value type should concurrences.

Loop Insert User to dbo.Users
    [Arguments]     ${Start}    ${End}
    : FOR    ${INDEX}    IN RANGE    ${Start}    ${End}
    \    Connect to Database
    \    ${output}=    Excute SQL String    INSERT INTO dbo.Users
    \   (Username,Password,Name)
    \   VALUES('usname${INDEX}','1234','FullName${INDEX}');

Test Case part, just call keyword to be executed.

Test Insert User in DB
    Loop Insert User to dbo.Users     1    100

After execute this script, go to check in database. The data should be like this.

--------------------------------------------------------------------
  ID  |    Username    |    Password    |    Name
--------------------------------------------------------------------
1     |    usname1     |    1234        |    Fullname1
2     |    usname2     |    1234        |    Fullname2
3     |    usname3     |    1234        |    Fullname3
...   |    ...         |   .....        |   ......
99    |    usname99    |    1234        |    Fullname99