Error running sqlite data retrieve statement in Datalore with Python

Question

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

`AND` is operator in SQL syntax, not a function – buran Jun 28 '22 at 18:52 — buran, Jun 28 '22 at 18:52

forpas · Accepted Answer · 2022-06-29T05:44:36.223

1

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;

edited Jun 29 '22 at 05:44

answered Jun 28 '22 at 18:53

forpas

160,666
10
38
76

this helps but putting the AND operator appropriately and using the JOIN, ON alternatives do not seem to resolve the issue. The same error pops out as per my edited post above that I have added a screenshot. The IDE points to the line 'gdpdata = pd.read_sql_query(gdpquery, conn)' as the one containing the error but I understand it could be any other. – user3087182 Jun 29 '22 at 02:19
@user3087182 I missed a `,` between I.Indicator and G.Value in my query. Check now. – forpas Jun 29 '22 at 05:43

Error running sqlite data retrieve statement in Datalore with Python

1 Answers1