In python 3.x, I have a class with this definition
def sproc(self, sql, *args):
self.__open()
self.__session.callproc(sql, args)
number_rows = self.__session.rowcount
number_columns = len(self.__session.description)
if number_rows >= 1 and number_columns > 1:
result = [item for item in self.__session.fetchall()]
else:
result = [item[0] for item in self.__session.fetchall()]
self.__close
return result
In my workhorse.py script, I've definition that call sproc.
def tradepl(user_id, play_entry_id, play_exit_id):
try:
db_cnx = db(dbconfig.database_configuration['host']
, dbconfig.database_configuration['user']
, dbconfig.database_configuration['password']
, dbconfig.database_configuration['database'])
args = (user_id, play_entry_id, play_exit_id)
result = db_cnx.sproc('lastpl', args)
return result
except Exception as err:
print(err)
The lastpl is a stored procedure that has the following:
DELIMITER //
CREATE PROCEDURE lastPl (user_id int, play_entry_id int, play_exit_id int, OUT profit_dollar decimal(10,2), OUT profit_percent decimal(10,2), OUT days_in_trade int)
BEGIN
/**** get profit and loss data from the closing trade ****/
select
case p1.entry_transaction_type when 'sto' then (p1.entry_cost - p2.exit_cost)
else (p2.exit_cost - p1.entry_cost)
END profit_dollar
, case p1.entry_transaction_type when 'sto' then (((p1.entry_cost - p2.exit_cost)/p1.entry_cost) * 100)
else (((p2.exit_cost - p1.entry_cost)/p1.entry_cost) * 100)
END profit_percent
, datediff(p1.entry_date, p2.exit_date) days_in_trade
from play_entry p1
join play_exit p2
on p1.play_entry_id = p2.play_entry_id
where p1.user_id = user_id
and p1.play_entry_id = play_entry_id
and p2.play_exit_id = play_exit_id;
END //
DELIMITER ;
When I call the tradepl definition i'm given the error:
output = tradepl(user_id[0], play_entry_id[0], play_exit_id)
for row in output:
profit_dollar = row[0]
profit_percent = row[1]
day_in_trade = row[2]
(1241, 'Operand should contain 1 column(s)')
'NoneType' object is not iterable
I have spent about 5 hours on this whole process and pretty much exhausted. The search results that come up on google/duckduck doesn't match up to what i'm trying to accomplish.
any help would be greatly appreciated. thx
