[FIXED] Error running sqlite data retrieve statement in Datalore with Python

Issue

This Python code for sqlite db fails in Datalore and I cant figure out why. The db is properly connected and other scripts are running.

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)

Error:

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

Solution

This:

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

is not valid SQLite (or SQL in general) syntax.
It should be:

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

But, the correct way to express your logic is with proper joins with ON clauses:

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; 

Answered By - forpas