[SOLVED] TabPy TypeError %d format a number is required, not str, followed by no return value

TabPy TypeError %d format a number is required, not str, followed by no return value

I have just started to work with TabPy on 10.13.2. I used the conda pymysql packages (pymysql and PyMySQL-0.8.0-py2.7.egg-info) and placed them in the site-packages for anaconda, so that Tableau would be able to connect to the db, retrieve the dataset and hold in a calculated field.

I originally tried mysql.connector, as I do within PyCharm and the CLI, but TabPy uses anaconda, which does not have a site-package for mysql.

Anyways, it initially connects to the TabPy server, which returns:

An error occurred while communicating with the Predictive Service.

However, this is soon followed by the next two lines:

Error processing script
TypeError : %d format: a number is required, not str

I have done some digging for the aforementioned error and everything that I have tried has produced the same error. I found the solution, but I have another error at the end.

SCRIPT_REAL("
import pymysql

db = pymysql.connect(
                     host='localhost',
                     port='9999',
                     user='usern',
                     passwd='passw',
                     db='someDb'
                     )

cur = db.cursor()

t1 = int(0)

t2 = datetime.datetime(2018, 2, 1)

sqlStr = 'select distinct APPL_ID, APPL_SUBMIT_DT from APPL_APP where APPL_ACTIVE_FLAG > %d and APPL_SUBMIT_DT >= %d' % (t1, t2)

cur.execute()

for row in cur.fetchall():
    print row

db.close()
",
COUNT([Appl Id])
)

I do not understand why the script would be returning such an error, until I ran it in PyCharm. It was my port number that needed to be a number instead of a string.

import pymysql

db = pymysql.connect(
                     host='localhost',
                     port=9999,
                     user='usern',
                     passwd='passw',
                     db='someDb'
                     )

cur = db.cursor()

t1 = int(0)

t2 = (2018-02-01)

sqlStr = 'select distinct APPL_ID, APPL_SUBMIT_DT from APPL_APP where APPL_ACTIVE_FLAG > %d and APPL_SUBMIT_DT >= %d' % (t1, t2)

cur.execute(sqlStr)

for row in cur.fetchall():
    print row

db.close()

Of course, while I could see all the data being returned in my terminal via the TabPy server, but it finished with the following:

(3957423, datetime.datetime(2018, 2, 27, 15, 30, 16))
(3957424, datetime.datetime(2018, 2, 27, 15, 31))
(3957425, datetime.datetime(2018, 2, 27, 15, 31, 4))
(3957426, datetime.datetime(2018, 2, 27, 15, 31, 55))
(3957428, datetime.datetime(2018, 2, 27, 15, 32, 17))
(3957429, datetime.datetime(2018, 2, 27, 15, 32, 18))
None
ERROR:__main__:{"info": null, "ERROR": "Error running script. No return value"}

How is that possible? There is clearly data there.

Solution

For your script to work in Tableau, you need to use the python command return something where something is a list containing the appropriate return type. Otherwise the values may exist in python but Tableau can't see them.

In you case you would need to build a list using code something like this:

ReturnValues=[]
for row in cur.fetchall():
   ReturnValues.append(row)
return ReturnValues

Then the complete list of rows will be sent back to Tableau. You might still have problems, though, as Tableau will be expecting a list of a particular size that matches the input list sent to python. You don't have such an input which may cause problems.