MS-SQL setup for Robocorp

Build with Robocorp
#1

The Working with databases page states we “need to get the appropriate module for the database system you are interacting with and add it to your robot’s conda.yaml file.” However, it doesn’t actually show us how to do that.

I get ModuleNotFoundError: No module named ’ pymssql’ when running the below.

I am trying to get a connection to a Microsoft 2017 SQL Server running on Linux. I see that pymssql has the library supporting it.

#conda.yaml file
    channels:
      # Define conda channels here. 
      - defaults
      - conda-forge

        dependencies:
          # Define conda packages here. 
          # If available, always prefer the conda version of a package, installation will be faster and more efficient.
          # https://anaconda.org/search  
          - python=3.7.5
          
          - pip=20.1
          - pip:
            # Define pip packages here. 
            # https://pypi.org/
            - rpaframework==7.1.1 # https://rpaframework.org/releasenotes.html

*** Settings ***
Documentation   Template robot main suite.
Library                RPA.Database

*** Tasks ***
Minimal task
    Connect To Database    pymssql     MyDatabaseName ....
#2

Hi, I couldn’t yet test this, but it may work if you add pymssql to dependencies in conda.yaml

#conda.yaml file
channels:
  # Define conda channels here. 
  - defaults
  - conda-forge

dependencies:
  # Define conda packages here. 
  # If available, always prefer the conda version of a package, installation will be faster and more efficient.
  # https://anaconda.org/search  
  - python=3.7.5
  - pymssql

  - pip=20.1
  - pip:
    # Define pip packages here. 
    # https://pypi.org/
    - rpaframework==7.1.1 # https://rpaframework.org/releasenotes.html
#3

…and with my limited mssql experience I found out that there is another driver option pyodbc that also states being DB API 2.0 compatible. You may want to try that also - just replace pymssql with pyodbc in conda.yaml and Connect To Database.

#4

Fantastic Teppo! I was putting the “- pymssql” in the “pip:” section. When I moved it to the dependencies all worked great. I recommend copying your example above into that Working with Database document. It was very helpful.

Have a wonderful evening.

Scott

1 Like
#5

Does anyone know if there is a tutorial video on working with database’s? Here are a couple of questions I have:

  • How do we return columns into separate variables in a select statement? Do we need to parse the row?
  • For an insert statement, can we get the inserted primary key back? Using Guid and Int.
  • I see the Get Rows can return a table, how would we get the data by column out?

Thanks for the help

#6

For first question: check if you really need separate variables, as there is an easy syntax to access columns from a row e.g.

*** Tasks ***
Get Orders From Database
    Connect To Database  sqlite3  test.db
    @{persons}            Query    Select * FROM person
    FOR   ${person}  IN  @{persons}
        Notebook Print  ${person}[id]
        Notebook Print  ${person}[name]
    END
1 Like
#7

I think that the answer to the second question is db specific.

postgresql allows INSERT with RETURNING:

${row}=  QUERY  INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

While SQLite probably requires some python to get it done:

def insert_user(firstname, lastname):
    with sqlite3.connect("example.db") as conn:
        sql = "INSERT INTO person (id, name) VALUES (?, ?)"
        cur = conn.cursor()
        cur.execute(sql, [firstname, lastname])
        conn.commit()

        sql = "SELECT id FROM users WHERE rowid=%s" % (cur.lastrowid)
        cur.execute(sql)
        return cur.fetchall()[0][0]

Hopefully someone can provide MSSql example?

EDIT: I found this https://stackoverflow.com/a/7917874
MSSQL may work with something like this query, but I can’t test it:

${row}=  Query  INSERT INTO table (name) OUTPUT Inserted.ID VALUES('bob');
1 Like