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?

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. — Sean Lange
– Sean Lange, Commented Jul 6, 2018 at 18:21
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. — JNevill
– JNevill, Commented Jul 6, 2018 at 18:23
Sean Lange, it is still giving me the same error even though I rewrite the code that was suggested by JNevill!! — L.G.
– L.G., Commented Jul 7, 2018 at 14:55
@Sean Lange, I worked out the code. Used triple quote around SQL code (suggested by JNevill) and remove all '\' sign including from the date . — L.G.
– L.G., Commented Jul 9, 2018 at 10:05

JNevill · Accepted Answer · 2018-07-06 18:25:19Z

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)

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

Collectives™ on Stack Overflow

MS SQL Server query in python3

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related