0

I'm experimenting with Tabpy in Tableau and writing data back to a database via a users selection in a dashboard.

My data connections work fine. However, I'm having trouble passing a variable back into the SQL query and getting an error saying the "variable name" doesn't exist in the table that I'm trying to update. Here is my code.

The error states that "dlr_status" does not exist on the table. This is the variable I'm trying to pass back to the query. The database is a Postgres database. Any help is greatly appreciated. I've been researching this for several days and can't find anything.

SCRIPT_STR("
    import psycopg2
    import numpy as np
    from datetime import date
    
    con = psycopg2.connect(
     dbname='edw',
     host='serverinfo',
     port='5439',
     user='username',
     password='userpassword')
    dlr_no_change = _arg1[0]
    dlr_status = _arg2[0]
    update_trigger = _arg3[0]
    sql = '''update schema.table set status = dlr_status where dlr_no = dlr_no_change'''
    
    if update_trigger == True:
        cur = con.cursor()
        cur.execute(sql)
        cur.commit",
ATTR([Dlr No]), ATTR([dlr_status]), ATTR([Update_Now]))
x1PatientZero1x
  • 5
  • 5
  • Not sure if this is exactly the issue but only Tableau's Initial SQL connections allow for DDL (CREATE, UPDATE, etc). The regular SQL connection only allows SELECT statements an CTEs, (WITH statement). In notebook calculated fields will use the regular connection since it runs each time the user does something. Initial SQL only runs once per session. – Bernardo Mar 02 '22 at 20:08
  • You are correct. However, this is using Python via TabPy which is installed on the Tableau server. Basically, the idea is there is a parameter that defaults to False. The user makes their selection of a couple of fields and then changes the parameter to True. Once True is selected, that triggers the Python script. Since posting the question. I've now got it reading the variables and have confirmed the variables are reading perfectly. Now I'm working on writing back to the database and no errors are happening finally. However, the update in the database doesn't occur. – x1PatientZero1x Mar 02 '22 at 21:34

1 Answers1

0

Your commit is missing "()". Or add a con.autocommit = True after creating the connection if you don't want to commit each step.

Bernardo
  • 3,212
  • 1
  • 10
  • 17