删除表状态中的配置单元解析异常

2021-02-25 22:05:10 发布

您现在位置:Python中文网/ 问答频道 /正文

我特别使用python和pyodbc模块在Hadoop上执行配置单元查询。代码触发问题的部分如下所示:

import pyodbc
import pandas

oConnexionString = 'Driver={ClouderaHive};[...]'
oConnexion = pyodbc.connect(oConnexionString, autocommit=True)
oConnexion.setencoding(encoding='utf-8')
oQueryParameter = "select * from my_db.my_table;"
oParameterData = pandas.read_sql(oQueryParameter, oConnexion)
oCursor = oConnexion.cursor()

for oRow in oParameterData.index:
    sTableName = oParameterData.loc[oRow,'TableName']
    oQueryDeleteTable = 'drop table if exists my_db.' + sTableName + ';'
    print(oQueryDeleteTable)
    oCursor.execute(oQueryDeleteTable)

打印的结果是:drop table if exists dl_audit_data_quality.hero_context_start_gamemode;

但是cursor.execute触发以下错误消息

pyodbc.Error: ('HY000', "[HY000] [Cloudera][HiveODBC] (80) Syntax or semantic analysis error thrown in server while execurint query. Error message from server: Error while compiling statement: FAILED: ParseException line 1:44 character ' (80) (SQLExecDirectW)")

请注意,当我复制打印并以色调手动执行时,效果很好。我猜这与变量sTableName的编码有关,但我不知道如何修复它。在

谢谢

1条回答
网友
1楼 ·

由于变量sTableName的编码不正确,查询失败。 单独打印变量将正确显示文本。如上图所示:

>>> print(oQueryDeleteTable)
>>> 'drop table if exists dl_audit_data_quality.hero_context_start_gamemode;'

但打印原始数据帧显示它包含以下字符:

^{pr2}$

通过重新编写如下所述的编码来解决问题:Python Dictionary Contains Encoded Values

import pyodbc
import pandas

oConnexionString = 'Driver={ClouderaHive};[...]'
oConnexion = pyodbc.connect(oConnexionString, autocommit=True)
oConnexion.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
oConnexion.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
oConnexion.setencoding(encoding='utf-8')
oQueryParameter = "select * from my_db.my_table;"
oParameterData = pandas.read_sql(oQueryParameter, oConnexion)
oCursor = oConnexion.cursor()

for oRow in oParameterData.index:
    sTableName = oParameterData.loc[oRow,'TableName']
    oQueryDeleteTable = 'drop table if exists my_db.' + sTableName + ';'
    print(oQueryDeleteTable)
    oCursor.execute(oQueryDeleteTable)

相关问题