Python Pyodbc - Invalid Syntax with SQL

Question

I am using Pyodbc to pull some data off our internal database, and am using the following SQL which works perfectly in SQL Server Management Studio.

'SELECT GrantInformation.GrantRefNumber, Count(GrantResearchTopic.PrimaryInd) AS CountOfPrimaryInd 
FROM GrantInformation 
LEFT JOIN GrantResearchTopic ON GrantInformation.GrantRefNumber = GrantResearchTopic.GrantRefNumber 
WHERE ((GrantResearchTopic.PrimaryInd)='True')  
GROUP BY GrantInformation.GrantRefNumber;'

For some reason if I remove the WHERE statement the SQL pull works, but if I leave it in, I get 'INVALID SYNTAX' despite as I said the SQL working fine in SQLSMS.

I have tried using double quotes around true, but get the following error:

ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'True'. (207) (SQLExecDirectW)")

Any ideas?

Edit: Problem solved. I didn't use double quotes on my SQL Statement so it wouldn't recognise the single quotes around the True statement. Using 1 worked as wasnt using quote marks, and using double quotes around the statement also worked as it then recognised the single quotes around 'True'. Thank you all.

Matt · Accepted Answer · 2017-09-07 08:47:16Z

4

Your whole SQL statement should be in double quotes not single quotes and then the true can remain in the single quotes.

answered Sep 7, 2017 at 8:47

Matt

8332 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicholas Over a year ago

Yep, that also worked. That was a simple mistake! Thank you Matt!

Paolo Stefan · Accepted Answer · 2017-09-07 08:44:01Z

2

I solved a similar problem replacing 'True' with 1 (without quotes).

answered Sep 7, 2017 at 8:44

Paolo Stefan

10.3k5 gold badges47 silver badges65 bronze badges

1 Comment

Nicholas Over a year ago

Perfect, yep, changing it to 1 fixed the problem. It is strange as I have done many SQL statements before with Pyodbc and never had this issue before, must be something about the database!

naimul64 · Accepted Answer · 2017-09-07 08:57:30Z

2

'True' (True enclosed by single or double quote) is a string and cannot be compared with a boolean. In sql using 1 or TRUE (both without quotes and TRUE is not case sensitive) for boolean true will work.

answered Sep 7, 2017 at 8:57

naimul64

1433 silver badges17 bronze badges

2 Comments

Nicholas Over a year ago

The 1 worked for me, the TRUE didn't, but either way, it was the fact I didnt double quote the SQL string which was causing the issue. thank you for your help! :)

naimul64 Over a year ago

Most probably your 'PrimaryInd' field is a bit, not a boolean.

Shurov · Accepted Answer · 2017-09-07 08:43:07Z

-1

Try removing semicolon at the end of your statement

answered Sep 7, 2017 at 8:43

Shurov

4011 gold badge9 silver badges25 bronze badges

2 Comments

cs95 Over a year ago

SQL statements can be semicolon terminated.

Nicholas Over a year ago

I tried removing it and still get the same syntax error pointing at the true statement :/

Collectives™ on Stack Overflow

Python Pyodbc - Invalid Syntax with SQL

4 Answers 4

1 Comment

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related