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')