def connect_to_db(data_set):
"""
This action just stores the different database specific configs into shared variables for use by other actions.
NOTE: The actual db connection does not happen here, connection to db is made inside the actions which require it.
db_type input parameter <type of db, ex: postgres, mysql>
db_name input parameter <name of db, ex: zeuz_db>
db_user_id input parameter <user id of the os who have access to the db, ex: postgres>
db_password input parameter <password of db, ex: mydbpass-mY1-t23z>
db_host input parameter <host of db, ex: localhost, 127.0.0.1>
db_port input parameter <port of db, ex: 5432 for postgres by default>
sid optional parameter <sid of db, ex: 15321 for oracle by default>
service_name optional parameter <service_name of db, ex: 'somename' for oracle by default>
odbc_driver optional parameter <specify the odbc driver, optional, can be found from pyodbc.drivers()>
connect to db database action Connect to a database
:param data_set: Action data set
:return: string: "passed" or "zeuz_failed" depending on the outcome
"""
sModuleInfo = inspect.currentframe().f_code.co_name + " : " + MODULE_NAME
try:
for left, _, right in data_set:
if left == DB_TYPE:
sr.Set_Shared_Variables(DB_TYPE, right)
if left == DB_NAME:
sr.Set_Shared_Variables(DB_NAME, right)
if left == DB_USER_ID:
sr.Set_Shared_Variables(DB_USER_ID, right)
if left == DB_PASSWORD:
sr.Set_Shared_Variables(DB_PASSWORD, right)
if left == DB_HOST:
sr.Set_Shared_Variables(DB_HOST, right)
if left == DB_PORT:
sr.Set_Shared_Variables(DB_PORT, right)
if left == DB_SID:
sr.Set_Shared_Variables(DB_SID, right)
if left == DB_SERVICE_NAME:
sr.Set_Shared_Variables(DB_SERVICE_NAME, right)
if left == DB_ODBC_DRIVER:
sr.Set_Shared_Variables(DB_ODBC_DRIVER, right)
CommonUtil.ExecLog(sModuleInfo,
"Trying to establish connection to the database.",
1)
db_get_connection()
return "passed"
except Exception:
traceback.print_exc()
return CommonUtil.Exception_Handler(sys.exc_info())
def db_non_query(data_set):
"""
This action performs a non-query (insert/update/delete) query and stores the "number of rows affected"
in the variable <var_name>
The result will be stored in the format: int
value
query input parameter <query: INSERT INTO table_name(col1, col2, ...) VALUES (val1, val2, ...)>
insert update delete query database action <var_name: name of the variable to store the no of rows affected>
:param data_set: Action data set
:return: string: "passed" or "zeuz_failed" depending on the outcome
"""
sModuleInfo = inspect.currentframe().f_code.co_name + " : " + MODULE_NAME
try:
variable_name = None
query = None
for left, mid, right in data_set:
if left == "query":
# Get the query, and remove any whitespaces
query = right.strip()
if "action" in mid:
variable_name = right.strip()
if variable_name is None:
CommonUtil.ExecLog(sModuleInfo, "Variable name must be provided.",
3)
return "zeuz_failed"
if query is None:
CommonUtil.ExecLog(sModuleInfo, "SQL query must be provided.", 3)
return "zeuz_failed"
CommonUtil.ExecLog(sModuleInfo, "Executing query:\n%s." % query, 1)
# Get db_cursor and execute
db_con = db_get_connection()
with db_con:
with db_con.cursor() as db_cursor:
db_cursor.execute(query)
# Commit the changes
db_con.commit()
# Fetch the number of rows affected
db_rows_affected = db_cursor.rowcount
# Set the rows as a shared variable
sr.Set_Shared_Variables(variable_name, db_rows_affected)
db_con.close()
CommonUtil.ExecLog(sModuleInfo,
"Number of rows affected: %d" % db_rows_affected, 0)
return "passed"
except Exception as e:
return handle_db_exception(sModuleInfo, e)
def update_into_db(data_set):
"""
This action performs a update query and stores the "number of rows affected"
in the variable <var_name>
The result will be stored in the format: int
value
table input parameter <table name: test >
columns input parameter <column name: name>
values input parameter <column name: admin>
where input parameter <where condition: name='admin'>
update into db database action <var_name: name of the variable to store the result of the query>
:param data_set: Action data set
:return: string: "passed" or "zeuz_failed" depending on the outcome
"""
sModuleInfo = inspect.currentframe().f_code.co_name + " : " + MODULE_NAME
try:
table_name = None
query = None
where = None
columns = None
values = None
variable_name = None
for left, mid, right in data_set:
if "table" in left.lower():
# Get the and query, and remove any whitespaces
table_name = right.strip()
if left == "where":
where = right.strip()
if "action" in mid:
variable_name = right.strip()
if "columns" in left.lower():
columns = right.split(',')
if "values" in left.lower():
values = right.split(',')
if variable_name is None:
CommonUtil.ExecLog(sModuleInfo, "Variable name must be provided.",
3)
return "zeuz_failed"
query = "update " + table_name + " set "
for index in range(len(columns)):
query += columns[index] + "= "
query += values[index] + " "
if (index != (len(columns) - 1)):
query += ","
if where is not None:
query += " where " + where
CommonUtil.ExecLog(sModuleInfo, "Executing query:\n%s." % query, 1)
# Get db_cursor and execute
db_con = db_get_connection()
with db_con:
with db_con.cursor() as db_cursor:
db_cursor.execute(query)
db_con.commit()
# Fetch the number of rows affected
db_rows_affected = db_cursor.rowcount
# Set the rows as a shared variable
sr.Set_Shared_Variables(variable_name, db_rows_affected)
db_con.close()
CommonUtil.ExecLog(sModuleInfo,
"Number of rows affected: %d" % db_rows_affected, 0)
return "passed"
except Exception as e:
return handle_db_exception(sModuleInfo, e)
def select_from_db(data_set):
"""
This action performs a select query and stores the result of the query in the variable <var_name>
The result will be stored in the format: list of lists
[ [row1...], [row2...], ... ]
table input parameter <table name: test >
columns input parameter <column name: name,id>
select from db database action <var_name: name of the variable to store the result of the query>
:param data_set: Action data set
:return: string: "passed" or "zeuz_failed" depending on the outcome
"""
sModuleInfo = inspect.currentframe().f_code.co_name + " : " + MODULE_NAME
try:
table_name = None
query = None
where = None
columns = None
variable_name = None
group_by = None
order_by = None
order = " "
for left, mid, right in data_set:
if "table" in left.lower():
# Get the and query, and remove any whitespaces
table_name = right.strip()
if left.lower() == "where":
where = right.strip()
if "action" in mid.lower():
variable_name = right.strip()
if "group" in left.lower():
group_by = right.split(',')
if "order" in left.lower():
order_by = right.split(',')
if "columns" in left.lower():
if right == "" or right == "*":
columns = ["*"]
columns = right.split(',')
if variable_name is None:
CommonUtil.ExecLog(sModuleInfo, "Variable name must be provided.",
3)
return "zeuz_failed"
query = "select "
for index in range(len(columns)):
query += columns[index] + " "
if (index != (len(columns) - 1)):
query += ","
query += "from " + table_name
if where is not None:
query += " where " + where
if group_by is not None:
query += " group by "
for index in range(len(group_by)):
query += group_by[index] + " "
if (index != (len(group_by) - 1)):
query += ","
if order_by is not None:
query += " order by "
for index in range(len(order_by)):
query += order_by[index] + " "
if (index != (len(order_by) - 1)):
query += ","
CommonUtil.ExecLog(sModuleInfo, "Executing query:\n%s." % query, 1)
# Get db_cursor and execute
db_con = db_get_connection()
with db_con:
with db_con.cursor() as db_cursor:
db_cursor.execute(query)
# Commit the changes
db_con.commit()
# Fetch all rows and convert into list
db_rows = []
while True:
db_row = db_cursor.fetchone()
if not db_row:
break
db_rows.append(list(db_row))
# Set the rows as a shared variable
sr.Set_Shared_Variables(variable_name, db_rows)
db_con.close()
CommonUtil.ExecLog(
sModuleInfo,
"Fetched %d rows and stored into variable: %s" %
(len(db_rows), variable_name),
0,
)
return "passed"
except Exception as e:
return handle_db_exception(sModuleInfo, e)
def db_select(data_set):
"""
This action performs a select query and stores the result of the query in the variable <var_name>
The result will be stored in the format: list of lists
[ [row1...], [row2...], ... ]
query input parameter <query: SELECT * FROM test_cases ORDER BY tc_id ASC LIMIT 10>
select query database action <var_name: name of the variable to store the result of the query>
:param data_set: Action data set
:return: string: "passed" or "zeuz_failed" depending on the outcome
"""
sModuleInfo = inspect.currentframe().f_code.co_name + " : " + MODULE_NAME
try:
variable_name = None
query = None
for left, mid, right in data_set:
if left == "query":
# Get the and query, and remove any whitespaces
query = right.strip()
if "action" in mid:
variable_name = right.strip()
if variable_name is None:
CommonUtil.ExecLog(sModuleInfo, "Variable name must be provided.",
3)
return "zeuz_failed"
if query is None:
CommonUtil.ExecLog(sModuleInfo, "SQL query must be provided.", 3)
return "zeuz_failed"
CommonUtil.ExecLog(sModuleInfo, "Executing query:\n%s." % query, 1)
# Get db_cursor and execute
db_con = db_get_connection()
with db_con:
with db_con.cursor() as db_cursor:
db_cursor.execute(query)
# Fetch all rows and convert into list
db_rows = []
while True:
db_row = db_cursor.fetchone()
if not db_row:
break
db_rows.append(list(db_row))
# Set the rows as a shared variable
sr.Set_Shared_Variables(variable_name, db_rows)
db_con.close()
CommonUtil.ExecLog(
sModuleInfo,
"Fetched %d rows and stored into variable: %s" %
(len(db_rows), variable_name),
0,
)
return "passed"
except Exception as e:
return handle_db_exception(sModuleInfo, e)