python3中的MS SQL Server查询

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (105)

我试图在python3中运行MS SQL查询,我得到一个错误如下:

       SyntaxError: EOL while scanning string literal 

我使用的是SQL Server 2014,使用的代码是:

我用来从python工作连接到SQL服务器的下面的代码。

 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) 

谁能帮到我出错的地方,拜托?

提问于
用户回答回答于

你的SQL很乱。您的日期文字需要周围有单引号,否则它只是一个破碎的数学语句,你FROMGROUP BYORDER 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) 

扫码关注云+社区

领取腾讯云代金券