查询在查询浏览器中工作正常,但在python代码中却无法正常工作 [英] Query is working fine in query browser but not in python code
问题描述
我有一个Teradata sql查询,该查询在Teradata Studio Express的查询浏览器中运行良好,但是当我尝试在python代码中运行相同的查询时,出现以下错误:
I have a teradata sql query which is working fine in Teradata Studio Express's query browser but when I tried to run the same query in my python code I am getting following error:
(3706,"[42000] [Teradata] [ODBC Teradata驱动程序] Teradata DatabaseSyntax错误:预期在'('和之间 '('.)
(3706, "[42000] [Teradata][ODBC Teradata Driver]Teradata DatabaseSyntax error: expected something between '(' and '('.")
我的查询:
td_query = """select
hash_md5((XMLAGG(TRIM(h_age) ORDER BY h_age ASC) (VARCHAR(100))) )hash_age,
hash_md5((XMLAGG(TRIM(h_marks) ORDER BY h_age ASC )(VARCHAR(100)))) hash_marks,
hash_md5((XMLAGG(TRIM(h_id) ORDER BY h_age ASC )(VARCHAR(100)))) hash_id,
hash_md5((XMLAGG(TRIM(h_score) ORDER BY h_age ASC )(VARCHAR(100))))hash_score,
hash_md5((XMLAGG(TRIM(h_name) ORDER BY h_age ASC )(VARCHAR(100))))hash_name,
hash_md5((XMLAGG(TRIM(h_entry) ORDER BY h_entry ASC )(VARCHAR(100))))hash_entry,
SUM(Aggrownum) sum_val,
COUNT(Aggrownum) count_val,
sum_val/count_val row_num
from
(
Select
hash_md5(CAST(age AS VARCHAR(100)))h_age ,
hash_md5(CAST(marks AS VARCHAR(100)))h_marks,
hash_md5(CAST(id AS VARCHAR(100)))h_id,
hash_md5(CAST(score AS VARCHAR(100)))h_score,
hash_md5(CAST(f_name AS VARCHAR(100)))h_name,
hash_md5(CAST(entry AS FORMAT 'yyyy-mm-ddbhh:mi:ss') (char (20)))h_entry,
row_number() over (partition by dob order by age asc) rno,round(rno/500.0) Aggrownum
from tdwm.student_2
) A group by Aggrownum ORDER BY row_num;
"""
并在运行它的python文件中:
and in python file running it as:
df_teradata = pd.read_sql(td_query, connect)
其中connect
是td.UdaExec
对象,并且上面的代码对于其他更简单的查询(例如select * from tdwm.student_2
)也可以正常工作.
Where connect
is the td.UdaExec
object and above code is working fine for other simpler queries such as select * from tdwm.student_2
.
我还尝试了最小化版本的同一查询,但出现了相同的错误.
I also tried minimized version of the same query I am getting the same error.
这是我使用td_query
的代码:
import pandas as pd
import teradata as td
td_host, td_username, td_passwd, td_dbname = '10.20.181.55', 'dbac', 'dbac', 'tdwm'
udaExec = td.UdaExec(appName="test", version="1.0", logConsole=False)
with udaExec.connect(method="odbc",system=td_host, username=td_username, port=1025,
password=td_passwd, driver="Teradata Database ODBC Driver 16.20") as connect:
df_teradata = pd.read_sql(td_query, connect)
推荐答案
我遇到了类似的问题.
查询的问题是它包含teradata python module
未知的hash_md5
函数,这就是错误的原因.
The issue with your query is that it contains hash_md5
function which is unknown to teradata python module
, that's why the error.
解决方案:
在该函数之前添加数据库名称(就像您在表名称tdwm.student_2
之前所做的一样).因此,您的新查询将是:
Add database name before that function (like you have done before table name tdwm.student_2
). So your new query will be:
select
tdwm.hash_md5((XMLAGG(TRIM(h_age) ORDER BY h_age ASC) (VARCHAR(100))) )hash_age,
tdwm.hash_md5((XMLAGG(TRIM(h_marks) ORDER BY h_age ASC )(VARCHAR(100)))) hash_marks,
tdwm.hash_md5((XMLAGG(TRIM(h_id) ORDER BY h_age ASC )(VARCHAR(100)))) hash_id,
tdwm.hash_md5((XMLAGG(TRIM(h_score) ORDER BY h_age ASC )(VARCHAR(100))))hash_score,
tdwm.hash_md5((XMLAGG(TRIM(h_name) ORDER BY h_age ASC )(VARCHAR(100))))hash_name,
tdwm.hash_md5((XMLAGG(TRIM(h_entry) ORDER BY h_entry ASC )(VARCHAR(100))))hash_entry,
SUM(Aggrownum) sum_val,
COUNT(Aggrownum) count_val,
sum_val/count_val row_num
from
(
Select
tdwm.hash_md5(CAST(age AS VARCHAR(100)))h_age ,
tdwm.hash_md5(CAST(marks AS VARCHAR(100)))h_marks,
tdwm.hash_md5(CAST(id AS VARCHAR(100)))h_id,
tdwm.hash_md5(CAST(score AS VARCHAR(100)))h_score,
tdwm.hash_md5(CAST(f_name AS VARCHAR(100)))h_name,
tdwm.hash_md5(CAST(entry AS FORMAT 'yyyy-mm-ddbhh:mi:ss') (char (20)))h_entry,
row_number() over (partition by dob order by age asc) rno,round(rno/500.0) Aggrownum
from tdwm.student_2
) A group by Aggrownum ORDER BY row_num;
这篇关于查询在查询浏览器中工作正常,但在python代码中却无法正常工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!