1

I am trying to send a query to a Teradata Database through a python script using the jaydebapi package. I have had no issues pulling data in the past until I tried to use a wild card in a like statement

SELECT .... FROM TABLE WHERE column LIKE 'value%'

JayDeBeApi says that this package provides a Python DB-API v2.0 connection to a database. Looking at the parameter usage (and trying many many different combinations of formatting the parameter strings) I am not pulling back any records.

When I run this query in Teradata SQL Assistant, however, I do find records.

How do I properly send a query with a parameter that has a wildcard character?

Here is my code:

import jaydebeapi
import pandas as pd
from tabulate import tabulate

jars = ['C:\\Users\\<path to TD jars>\\tdgssconfig.jar', 'C:\\Users\\<path to TD jars>\\terajdbc4.jar']
user = 'USER_NAME'
password = "PASSWORD"
jclassname = 'com.teradata.jdbc.TeraDriver'
url = "jdbc:teradata://<URL_TO_DB>/LOGMECH=LDAP,TMODE=ANSI,CHARSET=UTF8"
driver_args = [url, user, password]

class Teradata(object):
    def __init__(self):
        self.conn = jaydebeapi.connect(jclassname, driver_args, jars)
        self.cursor = self.conn.cursor()

def search_by_base_address(account):
    td = Teradata()
    address1 = account.base_address
    city = account.city
    state = account.state

    q = ''' 
        SELECT 
        BUSINESS_NAME, 
        SECONDARY_NAME, 
        STREET_ADDR, 
        STREET_ADDR2, 
        CITY, 
        STATE, 
        POSTAL_CD, 
        FROM DB.TABLE
        WHERE STREET_ADDR LIKE ?
        AND CITY = '{}'
        AND STATE = '{}';
            '''.format(city, state)
    if len(address1) > 1:
        result = pd.read_sql(q, td.conn, params=(address1+"%",))
        print(tabulate(result, headers='keys', tablefmt='psql'))
    else:
        print('No base address to search')
Ian
  • 933
  • 12
  • 17

0 Answers0