MS SQL Server query in python3

Question

I was trying to run MS SQL query in python3 and I got an error as follows:

 SyntaxError: EOL while scanning string literal 

I am using SQL server 2014 and the code used are:

the below code that I used to connect to SQL server from python works.

 import pyodbc #to import data from SQL
 cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
 "Server=CURRYS-PC\SQLEXPRESS;"
 "Database=AdventureWorksDW2012;"
 "Trusted_Connection=yes;")

The code that gave me error was:

import pandas as pd
Data = pd.read_sql_query('SELECT [CustomerKey]\
 ,MIN([OrderQuantity]) AS MIN_TOTAL_ITEMS\
 ,MAX([OrderQuantity]) AS MAX_TOTAL_ITEMS\
 ,SUM([OrderQuantity]) AS TOTAL_ITEMS\
 ,MIN([SalesAmount]) AS MIN_TXN_VALUE\
 ,MAX([SalesAmount]) AS MAX_TXN_VALUE \
 ,AVG([SalesAmount]) AS AVG_TXN_VALUE\
 ,SUM([SalesAmount]) AS TOTAL_REVENUE \
 ,SUM([TotalProductCost]) AS TOTAL_COST\
 ,SUM([SalesAmount] - [TotalProductCost]) AS TOTAL_PROFIT\
 ,MAX([OrderDate]) AS Last_txn_date\
 ,CASE WHEN MAX([OrderDate]) > 2007年07月31日 00:00:00.000 THEN 0 ELSE 1 END AS CHURN_FLAG\ 
 ,FROM [dbo].[FactInternetSales]\
 ,GROUP BY [CustomerKey]\
 ,ORDER BY [CustomerKey]', cnxn) 

can anyone help where I went wrong,please?

• Sean Lange: Date literals in sql server need to be wrapped in single quotes. Your code has nothing around your date literal. Voting to close as a typographical error.
• JNevill: You have comma before your FROM, GROUP BY, and ORDER BY which is not valid SQL. Try running this statement in a sql client and get it to work before dropping it in your code.
• L.G.: Sean Lange, it is still giving me the same error even though I rewrite the code that was suggested by JNevill!!
• L.G.: @Sean Lange, I worked out the code. Used triple quote around SQL code (suggested by JNevill) and remove all '\' sign including from the date .

Answer 1

Your SQL is a hot mess. Your date literal needs to have single quotes around it, otherwise it's just a broken math statement, and your FROM, GROUP BY and ORDER BY clauses should not start with a comma.

Try:

Data = pd.read_sql_query('SELECT [CustomerKey]\
 ,MIN([OrderQuantity]) AS MIN_TOTAL_ITEMS\
 ,MAX([OrderQuantity]) AS MAX_TOTAL_ITEMS\
 ,SUM([OrderQuantity]) AS TOTAL_ITEMS\
 ,MIN([SalesAmount]) AS MIN_TXN_VALUE\
 ,MAX([SalesAmount]) AS MAX_TXN_VALUE \
 ,AVG([SalesAmount]) AS AVG_TXN_VALUE\
 ,SUM([SalesAmount]) AS TOTAL_REVENUE \
 ,SUM([TotalProductCost]) AS TOTAL_COST\
 ,SUM([SalesAmount] - [TotalProductCost]) AS TOTAL_PROFIT\
 ,MAX([OrderDate]) AS Last_txn_date\
 ,CASE WHEN MAX([OrderDate]) > \'2007-07-31 00:00:00.000\' THEN 0 ELSE 1 END AS CHURN_FLAG\ 
 FROM [dbo].[FactInternetSales]\
 GROUP BY [CustomerKey]\
 ORDER BY [CustomerKey]', cnxn) 

• L.G.: Thanks for rewriting the code. It is still giving the same error: SyntaxError: EOL while scanning string literal
• L.G.: I worked out the code. Used triple quote around SQL code and remove all '\' sign including from the date.