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

Question

我特别使用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的编码有关，但我不知道如何修复它。在

谢谢

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