我试图在python3中运行MS SQL查询,但得到如下错误:
SyntaxError: EOL while scanning string literal
我使用的是SQL server 2014,使用的代码是:
下面的代码是我用来从python连接到SQL server的。
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;")
给我错误的代码是:
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)
我哪里出问题了,有人能帮我吗?
发布于 2018-07-07 02:25:19
你的SQL简直是一团糟。日期文本需要用单引号括起来,否则它只是一个断开的数学语句,并且您的FROM
、GROUP BY
和ORDER BY
子句不应该以逗号开头。
尝试:
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)
https://stackoverflow.com/questions/51215872
复制相似问题