0

I am trying to limit my query to a table in teradata database using python

#1.params=dfm['cust'].astype(str).to_list()
params=tuple(list(dfm['cust'].astype(str)))
import teradata
import pandas as pd


#Make a connection
udaExec = teradata.UdaExec (appName="test", version="1.0", logConsole=False)


with udaExec.connect(method="ODBC",system=host, username=uid,
                             password=pwd, driver="Teradata Database ODBC Driver 16.20",authentication="LDAP") as connect:

    
    query = "SELECT Cust, Flag FROM DBName.Tablename where Cust in %(params)s"

    #Reading query to df
    df = pd.read_sql(query,connect,params=params)

I tried to supply params as a list and it did not work. I tried to supply params as a tuple and it still did not work. I got the following error.

Execution failed on sql 'SELECT Cust,Flag FROM DBName.TableName where Cust in %(params)s': (3704, "[42000] [Teradata][ODBC Teradata Driver][Teradata Database](-3704)'%' ('25'X) is not a valid Teradata SQL token.")

What is wrong with the code?

user2162611
  • 146
  • 3
  • 13
  • you need to join the values like `params=','.join(dfm['cust'].astype(str))` or something like this should work. see [this post](https://stackoverflow.com/questions/283645/python-list-in-sql-query-as-parameter) for more options – Ben.T Oct 12 '21 at 15:00
  • @Ben.T, I tried that most of the solutions laid out. It gives me an execution error DatabaseError: Execution failed on sql 'SELECT Cust, Flag FROM DBName.TableName where Cust in ('555', '666',777') – user2162611 Oct 12 '21 at 15:22
  • I don't know if teradata has specific ways to query, but first are you sure that you can query on the DB, did you try without the where clause, like `pd.read_sql('select top 5 * from DBName.TableName',connect)`? also are the real names of the DB `DBName` and of the table `Tablename`? – Ben.T Oct 12 '21 at 15:36
  • 1
    The result of the following query came out fine. query = "SELECT top 5 Cust,Flag FROM DBName.TableName where Cust in ('888')". Of course, I am masking the dbname and tablename – user2162611 Oct 12 '21 at 15:54

0 Answers0