用Python在Datalore中运行sqlite数据检索语句时出错

Question

sqlite的Python代码在Datalore中失败，我不知道原因。数据库已正确连接，其他脚本正在运行。

gdpquery= '''SELECT  C.Country_Area, G.Year, I.Indicator G.Value FROM Countries C, GDP G, Indicators I
WHERE AND(C.CA_M49_Code = G.CA_M49_Code, G.Ind_No = I.Ind_No, G.Ind_Type_No = I.Ind_Type_No)'''

gdpdata = pd.read_sql_query(gdpquery, conn)

gdpdata.head(4)

错误：

Traceback (most recent call last):

  at block 10, line 3

  at /opt/python/envs/default/lib/python3.8/site-packages/pandas/io/sql.pyline 436, in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype)

  at /opt/python/envs/default/lib/python3.8/site-packages/pandas/io/sql.pyline 2116, in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize, dtype)

  at /opt/python/envs/default/lib/python3.8/site-packages/pandas/io/sql.pyline 2068, in execute(self, *args, **kwargs)

DatabaseError: Execution failed on sql 'SELECT C.Country_Area, G.Year, I.Indicator G.Value FROM Countries C, GDP G, Indicators I WHERE AND(C.CA_M49_Code = G.CA_M49_Code, G.Ind_No = I.Ind_No, G.Ind_Type_No = I.Ind_Type_No)': near ".": syntax error

​

​

Answer 1

这是：

WHERE AND(C.CA_M49_Code = G.CA_M49_Code, G.Ind_No = I.Ind_No, G.Ind_Type_No = I.Ind_Type_No)

不是有效的SQLite (或一般情况下是SQL )语法。

它应该是：

WHERE C.CA_M49_Code = G.CA_M49_Code AND G.Ind_No = I.Ind_No AND G.Ind_Type_No = I.Ind_Type_No

但是，正确的表达逻辑的方法是用适当的联接ON子句：

SELECT C.Country_Area, G.Year, I.Indicator, G.Value 
FROM Countries C 
INNER JOIN GDP G ON C.CA_M49_Code = G.CA_M49_Code
INNER JOIN Indicators I ON G.Ind_No = I.Ind_No AND G.Ind_Type_No = I.Ind_Type_No;