We use pyODBC for connection to a MSSQL DB. Full robot line would be: Connect To Database Using Custom Params pyodbc "DRIVER={SQL Server Native Client 10.0}; SERVER=${DATABASE_SERVER}; DATABASE=${DATABASE_NAME}; Trusted_Connection=yes;" This returns a -1 with the rowCount = cur.rowcount in the Query class. If I add pyodbc to the list of module APIs(if self.db_api_module_name in ["sqlite3", "ibm_db", "ibm_db_dbi"]:), then it returns the correct row count.

Could you maybe add pyodbc to the list that hits the rowCount = len(data)? I can't say with any certainty if this will/won't cause issues with other drivers using pyodbc though. Thought I could at least open a discussion on that.

DatabaseLibrary does not work with jaydebeapi, or at least so it seems.
The jaydebeapi uses different connect args:
Driver
Jdbc Url
User
Passwd
Can we add the capability to connect passing any number of arguments?

It would be nice to have keywords for Row Count validations like:
Row Count is Equal to 0
Row Count is Equal to X (where "X" is a defined number)
Row Count is Greater Than X
Row Count is Less Than X

I have checked these keywords into my fork already.

MySQLdb uses different arguments. When I added the following to connection_manager.py I was able to connect and use all keywords.

    if dbapiModuleName == "MySQLdb":
        self._dbconnection = db_api_2.connect (db=dbName, user=dbUsername, passwd=dbPassword, host=dbHost, port=dbPort)
    elif dbapiModuleName == "psycopg2":
        self._dbconnection = db_api_2.connect (database=dbName, user=dbUsername, password=dbPassword, host=dbHost, port=dbPort)
    else:
        print "Unknown dbapi passed in, trying defaults"
        self._dbconnection = db_api_2.connect (database=dbName, user=dbUsername, password=dbPassword, host=dbHost, port=dbPort)

This is definitely not an ideal way to add modules, but is working for me now.

https://pypi.python.org/pypi/robotframework-databaselibrary
this site package is old version 0.6.
People who using "pip install robotframework-databaselibrary" to install this library will install the old one. The new version in github is 0.7.
You can add me to Maintainer on pypi. my user is qitaos. Thanks.

One of the problems reported usually is the connection to the database, because although DB API 2.0 specifies the connect() for connecting to a database, the parameters used varies depending on the provider.

Thus, to remedy this, an alternative way should be provided so that users can provide their connection string to connect to a DB API 2.0 provider.

DatabaseLibrary version = 0.6

connection_manager.py doesn't seem to read the config file's dbHost and dbPort key values properly since it's been defined by default in the function definition for (connect_to_database(...)). It seems like the logical 'or' operator is preventing this because it's stopping to override from config file as soon as it detects a value exists, which it does in the definition.

def connect_to_database(self, dbapiModuleName=None, dbName=None, dbUsername=None, dbPassword=None, dbHost='localhost', dbPort="5432", dbConfigFile="./resources/db.cfg"):
...
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'))

a quick fix to this can be made by modifying the subject values to 'None' in the definition so...

def connect_to_database(self, dbapiModuleName=None, dbName=None, dbUsername=None, dbPassword=None, dbHost=None, dbPort=None, dbConfigFile="./resources/db.cfg"):

with that done, ofcourse the user will have to provide these values at the Keyword and Config File.

I got this error when executing keyword to connect to a sqlite.dll (Version 3.8.7.1) database.
Connect To Database Using Custom Params sqlite3 database='path_to_dbfile\dbname.db'

DatabaseLibrary version: 0.6

Reading DatabaseLibrary's setup.py fails if robotframework hasn't been installed yet, so it can't be installed from the same pip invocation that first installs robotframework.

It would be nice if:

1. This didn't cause the installation to fail
2. setup.py listed robotframework as a dependency

Steps to reproduce:

me@sandbox:~/tmp/robot-dblib-install-bug$ (
> echo robotframework
> echo robotframework-databaselibrary
> ) > requirements.txt
me@sandbox:~/tmp/robot-dblib-install-bug$ cat requirements.txt
robotframework
robotframework-databaselibrary
me@sandbox:~/tmp/robot-dblib-install-bug$ pip insta^C
me@sandbox:~/tmp/robot-dblib-install-bug$ virtualenv env
New python executable in env/bin/python
Installing setuptools, pip...done.
me@sandbox:~/tmp/robot-dblib-install-bug$ . env/bin/activate
(env)me@sandbox:~/tmp/robot-dblib-install-bug$ pip install -r requirements.txt

Error output:

Downloading/unpacking robotframework (from -r requirements.txt (line 1))
  Downloading robotframework-2.8.6.tar.gz (378kB): 378kB downloaded
  Running setup.py (path:/home/me/tmp/robot-dblib-install-bug/env/build/robotframework/setup.py) egg_info for package robotframework

    no previously-included directories found matching 'src/robot/htmldata/testdata'
Downloading/unpacking robotframework-databaselibrary (from -r requirements.txt (line 2))
  Downloading robotframework-databaselibrary-0.6.tar.gz
  Running setup.py (path:/home/me/tmp/robot-dblib-install-bug/env/build/robotframework-databaselibrary/setup.py) egg_info for package robotframework-databaselibrary
    Traceback (most recent call last):
      File "<string>", line 17, in <module>
      File "/home/me/tmp/robot-dblib-install-bug/env/build/robotframework-databaselibrary/setup.py", line 25, in <module>
        from DatabaseLibrary import __version__
      File "/home/me/tmp/robot-dblib-install-bug/env/build/robotframework-databaselibrary/src/DatabaseLibrary/__init__.py", line 15, in <module>
        from connection_manager import ConnectionManager
      File "/home/me/tmp/robot-dblib-install-bug/env/build/robotframework-databaselibrary/src/DatabaseLibrary/connection_manager.py", line 16, in <module>
        from robot.api import logger
    ImportError: No module named robot.api
    Complete output from command python setup.py egg_info:
    Traceback (most recent call last):

  File "<string>", line 17, in <module>

  File "/home/me/tmp/robot-dblib-install-bug/env/build/robotframework-databaselibrary/setup.py", line 25, in <module>

    from DatabaseLibrary import __version__

  File "/home/me/tmp/robot-dblib-install-bug/env/build/robotframework-databaselibrary/src/DatabaseLibrary/__init__.py", line 15, in <module>

    from connection_manager import ConnectionManager

  File "/home/me/tmp/robot-dblib-install-bug/env/build/robotframework-databaselibrary/src/DatabaseLibrary/connection_manager.py", line 16, in <module>

    from robot.api import logger

ImportError: No module named robot.api

----------------------------------------
Cleaning up...
Command python setup.py egg_info failed with error code 1 in /home/me/tmp/robot-dblib-install-bug/env/build/robotframework-databaselibrary
Storing debug log for failure in /home/me/.pip/pip.log
(env)me@sandbox:~/tmp/robot-dblib-install-bug$

Hello,

I was trying to use the keyword "delete_all_rows_from_table" and got the following error:

21:38:48.903 TRACE Arguments: [ u'client' ]
21:38:48.903 DEBUG Executing : delete from client;
21:38:48.911 FAIL AttributeError: 'int' object has no attribute 'fetchall'
21:38:48.911 DEBUG Traceback (most recent call last):
File "D:\Development\Python27\lib\site-packages\DatabaseLibrary\query.py", line 145, in delete_all_rows_from_table
return result.fetchall()

I dig into the code and I've found the follwoing:

def delete_all_rows_from_table(self, tableName):
(...)
result = self.__execute_sql(cur, selectStatement)
if result is not None:
return result.fetchall()
self._dbconnection.commit()
(...)

Which I think it not ok (the method returns before commit the changes and "fetchall" is not member of int value from 'result'). I've made a small change and work perfectly

(...)
result = self.__execute_sql(cur, selectStatement).
if result is not None:
self._dbconnection.commit()
return result #.fetchall()
(...)

Kind Regards
cleonf

pip install https://github.com/franz-see/Robotframework-Database-Library/archive/master.zip

results in missing VERSION file:

[ ERROR ] Error in file '~/Projects/QAAutomation/services/DB/DatabaseBaseService.robot': 
    Importing test library 'DatabaseLibrary' failed: IOError: [Errno 2] No such file or directory: '~/virts  /robot/local/lib/python2.7/site-packages/robotframework_databaselibrary-0.7-py2.7.egg/DatabaseLibrary/VERSION'
Traceback (most recent call last):
  File "~/virts/robot/local/lib/python2.7/site-packages/robotframework_databaselibrary-0.7-py2.7.egg/DatabaseLibrary/__init__.py", line 22, in <module>
__version__ = open(__version_file_path__, 'r').read().strip()
PYTHONPATH:
  ~/virts/robot/bin
  ~/Projects/QAAutomation/libraries/robotframework-cmp
  ~/virts/robot/local/lib/python2.7/site-packages/robotframework_databaselibrary-0.7-py2.7.egg
  ~/virts/robot/lib/python2.7/site-packages/robotframework_databaselibrary-0.7-py2.7.egg
  ~/virts/robot/lib/python2.7
  ~/virts/robot/lib/python2.7/plat-x86_64-linux-gnu
  ~/virts/robot/lib/python2.7/lib-tk
  ~/virts/robot/lib/python2.7/lib-old
  ~/virts/robot/lib/python2.7/lib-dynload
  /usr/lib/python2.7
  /usr/lib/python2.7/plat-x86_64-linux-gnu
  /usr/lib/python2.7/lib-tk
 ~/virts/robot/local/lib/python2.7/site-packages
  ~/virts/robot/lib/python2.7/site-packages

I have tests that verify rowcount for tables in our database. I mistyped one of the table names and noticed that all testcases after that failed because of the ProgrammingError on this one testcase

Contract_property table | PASS |

Contrast_job table | FAIL |
ProgrammingError: relation "contract_job" does not exist
LINE 1: select * from contract_job;

^

Defaultclassification table | FAIL |

InternalError: current transaction is aborted, commands ignored until end of transaction block

Job_Information_new table | FAIL |

InternalError: current transaction is aborted, commands ignored until end of transaction block

I open the db connection as part of the SuiteStartup and all testcases expect that the db connection is available.

Setting empty string as passwords fails when invoke connect_to_database. ConnectionManager attempts get it from config file. This leads to connect_to_database failure if config files are not used.

Noticed that file contents with list of multiple sql queries is handled incorretly, and this causes sql exption like ORA-01756: quoted string not properly terminated (in case of working with oracle) if statements contain semicolon(;) character in unexpected place.

Steps to reproduce:

• create sql script file quiery.sql containing something like:
  INSERT INTO FILES('ID', 'NAME') VALUES ('1', 'Any; name');
• execute command Execute Sql Script quiery.sql

So semilcolon after word "Any" confuses function execute_sql_script (line 205 of query.py)

this line has two dbPort. When use the pyodbc module will be failed.
logger.debug ('Connecting using : %s.connect(DRIVER={SQL Server};SERVER=%s,%s;DATABASE=%s;UID=%s;PWD=%s)' % (dbapiModuleName,dbHost,dbPort,dbName,dbPort,dbUsername, dbPassword))

It would be nice if Query.query() could accept positional and/or keyword arguments[1] and pass them to the DB API's execute() method.

This way, we could write queries like this (depending on the DB Driver):

[1]: The keyword could complain if it gets both sequence & mapped parameters at once, or the wrong kind for the current DB Driver's paramstyle

The Keyword "connect to database" doesn't work with "DB Api"s adodpapi, mssql and pyodbc. As a consequence, we can't use the new version of database Library with MS-SQL Server

I tried to use ibm_db module to connect DB2, but an error occured.

db_api_2 = import('ibm_db')
db_api_2.connect (database='SDMRU',user='xxxx', password='xxxx')
Traceback (most recent call last):
File "", line 1, in
TypeError: function takes at least 3 arguments (0 given)

Hi,

I'm trying to connect to an oracle database using

Connect To Database Using Custom Params cx_Oracle user/password@host/db

while this connection string works fine in a normal python program, it fails when used in Robot Framework. I get this in debug.txt when I call pybot with -b:

SyntaxError: invalid syntax (, line 1)
Traceback (most recent call last):
File "/home/dheinric/python/lib/DatabaseLibrary/connection_manager.py", line 92, in connect_to_database_using_custom_params
self._dbconnection = eval(db_connect_string)

Note that connecting to PostgreSQL using psycopg2 works fine (with a different connection string, of course).

DatabaseLibrary is version 0.5.

Is there a way to use a Dictionary cursor with the keyword:

Connect To Database using Custom Params pymysql database='mydb', user='myuser', password='pass', host='192.168.10.10', port=3306, cursorclass=pymysql.cursors.DictCursor

I get the error:

NameError: name 'pymysql' is not defined

Even If I try to do:

Library pymysql (on the settings section, still doesn't work due to collisions of func names)

Any tips?

Hi,
It happen randomly that when I call:
DatabaseLibrary.Execute Sql String delete t_Skill_Group_Member where AgentSkillTargetID = ${agent_id}, where ${agent_id} is a number

I get:
(u'HY010', u'[HY010] [unixODBC][Driver Manager]Function sequence error')

Traceback:
Traceback (most recent call last): File "/usr/lib/python2.7/site-packages/DatabaseLibrary/query.py", line 252, in execute_sql_string self.__execute_sql(cur, sqlString) File "/usr/lib/python2.7/site-packages/DatabaseLibrary/query.py", line 260, in __execute_sql return cur.execute(sqlStatement) File "/usr/lib/python2.7/site-packages/pypyodbc.py", line 1605, in execute self.execdirect(query_string) File "/usr/lib/python2.7/site-packages/pypyodbc.py", line 1632, in execdirect self._NumOfRows() File "/usr/lib/python2.7/site-packages/pypyodbc.py", line 1796, in _NumOfRows check_success(self, ret) File "/usr/lib/python2.7/site-packages/pypyodbc.py", line 986, in check_success ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi) File "/usr/lib/python2.7/site-packages/pypyodbc.py", line 964, in ctrl_err raise Error(state,err_text)

My setup is following:
robotframework-databaselibrary (0.6)
pypyodbc (1.3.3)
and odbc driver 11 for SQL Server 2011.110.2270.00

I have read some article and it says that fetchall() prior getting num of rows can help. (we were solving similar issue here)
What I don't know is whether it should be fixed in database-library or in pypyodbc project.

New JayDeBeApi now honors CLASSPATH, but in doing so it modified the connect() method breaking 'Connect To Database Using Custom Params'

I have a database with charset UTF-8.
I execute an sql file that is an UTF-8 file and contains some Chinese word .
Atfer executing ,the Chinese word in database is wrong.
Because my database is used in UTF-8,and Database-library connect it with Latin charset.

It would be nice to have a keyword that would delete all the data in a given table.

I have added this code in my branch, see commit jerry57@f81ee48

The http://franz-see.github.io/Robotframework-Database-Library/ only shows version 0.6, could the page be updated to contain the latest version too?

Hello,

The row count of any query is getting returned as -1 when using 'Row Count' keyword with pyodbc. I have installed the latest code from master branch, still this issue not resolved?

Hi,

Is it time to push the latest release to https://pypi.python.org/pypi?

If we want to execute specific script actually, we have to create an external file and use the 'Execute Sql Script' keyword.

But its hard to use arguments with a previously hardcoded sql file.

It would be much easier to have an 'Execute Sql String' keyword that allow to execute the string we pass at argument.

definitely a feature -- when I create a SQL Script in PGAdmin3 on Windows, it puts three extra control characters at the beginning of the file. "od" tells me that they are 357 273 277. When I run the script through the robot framework, I get a syntax error near "insert".

The workaround is to create my scripts in vim for Windows, but it would sure be nice if I could refine my scripts in a database tool.

P.S. vim seems to recognize these characters for whatever they are, and does not display them.

i'm using DatabaseLibrary to connect to oracle thru cx_Oracle

${results} Row Count select * from dual
Row Count Is Equal To X select * from dual 1

and it's always returning 0 records

i've fixed query.py and added cur.fetchall() and it's working

    cur = None
    try:
        cur = self._dbconnection.cursor()
        self.__execute_sql(cur, selectStatement)
        cur.fetchall()
        rowCount = cur.rowcount
        return rowCount
    finally :
        if cur :
            self._dbconnection.rollback() 

Error occurs when I try to run the previous working code in using the latest JayDeBeApi 1.1.1. It causes python to crash. It works when I downgraded JayDeBeApi back to 0.2.0.

Based on the change log for JayDeBeApi, there is a change in v1.0.0 which make major changes to the connect method.
https://pypi.python.org/pypi/JayDeBeApi/#changelog

adding new keywords to retreive the rowcount and table description would greatly enhance the DatabaseLibrary functionality.

I have patch files and will e-mail them to you.

I need to call stored procedure from oracle database I wrote some code to do this it may be it will be useful to include to project though calling procedures are not related to all databases. Code is not ideal but it's working. If you have any ideas how to make it ideal :) you are welcome

def call_procedure(self, name, parameters):
    '''
    call stored procedure with in and out parameters 
    @param name: name of procedure
    @param parameters: parameters as tuple as example of
    ('in_value',('STRING','out_var_to_init'),('NUMBER', 'out_var_to_init'),'in_value')
    '''
    cur = None

    try:
        cur = self._dbconnection.cursor()
        #process parameters
        parsed_parameters = []
        for i, item in enumerate(parameters):
            if item.lower() in ("string", "number"): 
                exec "%s = cur.var(self.db_api_2.%s);parsed_parameters.append(%s);" % (item+str(i),item.upper(),item+str(i))
            else: 
                parsed_parameters.append(item)
        input_output = tuple(parsed_parameters)
        cur.callproc(name,input_output)
        #backward processing to cx_Oracle.* type
        parsed_parameters = []
        for i, item in enumerate(list(input_output)):
            if isinstance(item,self.db_api_2.NUMBER) or isinstance(item,self.db_api_2.STRING): 
                parsed_parameters.append(item.getvalue());
            else: 
                parsed_parameters.append(item)
        self._dbconnection.commit()
        return parsed_parameters
    finally :
        if cur :
            self._dbconnection.rollback()

NonSQL databases don't have db api 2 support right now, but they do have api support. Unfortunately this means they don't share the same connect or keywords as SQL databases. I have working connection/disconnection and keywords in my fork right now. I have also added a simple testsuite for MongoDB running locally.

I have a very complex SQL join which is outputting a single value. How do I return that value using "Execute SQL Script" keyword ? I am using keyword below

Test Case
Connect to Database
${result} = Execute Sql Script ${EXECDIR}${/}resources${/}cg.sql

Its returning "None"

when storing the db-configuration in dbConfigFile, the variables dbHost and dbPort are allways 'localhost' and '5432', because of line 68 and 69 in connection_manager.py .

when use kw "execute sql string " to do update operation, meet this issue : TypeError: expecting None or a string.

I do not know the reason why happen

${output} = Execute SQL String CREATE TABLE transaction (trx_id varchar,address_ship varchar,date_order varchar,seller_name varchar,delivery_service varchar);
Log ${output}
Should Be Equal As Strings ${output} None

error message: OperationalError: near "transaction": syntax error

elif dbapiModuleName in ["ibm_db"]:
logger.debug ('Connecting using : %s.connect(database=%s, user=%s, password=%s) ' % (dbapiModuleName, dbName, dbUsername, dbPassword))
self._dbconnection = db_api_2.connect(dbName,dbUsername,dbPassword)

I add some codes in connection_manager.py to connect DB2. otherwise there will be an error.

I have the Database Library in Place.
I am using the following Keyword
${RowCount} Row Count SELECT * FROM DIRGRP.ALIANC

It is always returning -1 irrespective of the query used

I am using Seleniumversion 2.24
Not sure if this was fixed in a later version. If yes, then please let me know the version number.

Hi,
I am using robot framework, and I have noticed that if I try to execute something like:

INSERT INTO table(mystring) values('test;test;test');

I get the error

DatabaseError: ORA-01756: quoted string not properly terminated

i.e. it seems that the library breaks the file at each semicolon, without taking care of quotes.
Could you help with this?

Thanks!
Luigi

Adding print statements to the output of keywords will make log reading more useful. I have added these to my fork of the code.

In a clean virtualenv do
pip install robotframework-databaselibrary
and watch it fail

It seems that one or two things are broken:
1 when importing version a chain of import is started that goes too far
2 the robotframework dependency is not listed

I use the keyword(Delete All Rows From Table ) to delete a table,but something wrong has happen.Because the database return a Long type object.

DatabaseLibrary version :0.5
DB:Mysql 5.5.27 &MySQL-python 1.2.3

traceback:

11:14:16.137 DEBUG Executing : delete from gh_user_info;
11:14:16.226 FAIL AttributeError: 'long' object has no attribute 'fetchall'
11:14:16.227 DEBUG Traceback (most recent call last):
File "C:\Python27\lib\site-packages\DatabaseLibrary\query.py", line 145, in delete_all_rows_from_table
return result.fetchall()

I've been considering making a Robot Framework SQLAlchemy library, and it seems good to ask first if there are any plans to delegate to sqlalchemy as suggested here (in which case I'd work on a pull request instead of a new project), or if you'd rather keep this library using straight DBAPI.

Can we have a new feature : Execute Sql Script With Parameters ?

For example :
I have sql file InsertUser.sql as following

Declare @UserName NVarChar = '{UserName}'
Insert into User (UserName) values (@UserName)

So that I can do :

*** Test Cases ***
Test Case Sample
       ${Replacements}     Create Dictionary     UserName=John
       Execute Sql Script With Parameters     ${EXECDIR}${/}resources${/}InsertUser.sql     ${Replacements}

Lines starting with '--' are also comments in mysql. Consider adding check for those too...

try:
    cur = self._dbconnection.cursor()        
    sqlStatement = ''
    for line in sqlScriptFile:
        line = line.strip()
        if line.startswith('#'):
            continue
        if line.startswith('--'):
            continue
        ...

Thank you so much for this library. Can you create a keyword to query a column of datatype CLOB or such variant?

This library would be even better if it could connect to a remote host and allow for different ports. I believe that I have added this with the following patch.

diff --git src/DatabaseLibrary/connection_manager.py src/DatabaseLibrary/connection_manager.py
index 1689001..3984503 100644
--- src/DatabaseLibrary/connection_manager.py
+++ src/DatabaseLibrary/connection_manager.py
@@ -25,7 +25,7 @@ class ConnectionManager(object):
"""
self._dbconnection = None

• def connect_to_database(self, dbapiModuleName=None, dbName=None, dbUsername=None, dbPassword=None, dbConfigFile="./resources/db.cfg"):

• def connect_to_database(self, dbapiModuleName=None, dbName=None, dbUsername=None, dbPassword=None, dbHostname=None, dbPort=None, dbConfigFile="./resources/db.cfg"):
  """
  Loads the DB API 2.0 module given dbapiModuleName then uses it to
  connect to the database using dbName, dbUsername, and dbPassword.
  @@ -64,9 +64,11 @@ class ConnectionManager(object):
  dbName = dbName or config.get('default', 'dbName')
  dbUsername = dbUsername or config.get('default', 'dbUsername')
  dbPassword = dbPassword or config.get('default', 'dbPassword')

•    dbHostname = dbHostname or config.get('default', 'dbHostname')
  

•    dbPort = dbPort or config.get('default', 'dbPort')
  
   db_api_2 = __import__(dbapiModuleName);
  

•    self._dbconnection = db_api_2.connect (database=dbName, user=dbUsername, password=dbPassword)
  

•    self._dbconnection = db_api_2.connect (database=dbName, user=dbUsername, password=dbPassword, host=dbHostname, port=dbPort)
  

  def disconnect_from_database(self):

I couldn't find a way to attach the file so pasted it into this issue. I can send a diff/patch file if that would help.

     """