通过Python pyodbc的MS SQL：为什么一直说这里的语法不正确？

I have a query saved as .sql file using MS Sql Server Management Studio:

我使用MS Sql Server Management Studio将查询保存为.sql文件：

select distinct *
from bom.SalesBOMSampleExplosion b

and I tried to read the file via python using the pyodbc package. my other query runs successfully with pretty much same setup, except this query. Here's the python code I used:

我尝试使用pyodbc包通过python读取文件。我的其他查询使用几乎相同的设置成功运行，但此查询除外。这是我使用的python代码：

import pandas as pd import pyodbc as db

将pandas导入pd导入pyodbc作为db

def sql_reader(qry_file, server_name, database):

    server = db.connect(str('DRIVER={SQL Server};SERVER='+server_name+';DATABASE='+database+';'))
    qry = open(qry_file,'r').read()
    data = pd.read_sql(qry,server)

    return data

but when i use the function and call the sql file:

但是当我使用该函数并调用sql文件时：

server = 'sampleserver'
db = 'sampledb'
Data = sp.sql_reader(os.path.join(qry_path, 'Data.sql'), server_name=server, database=db)

The following error message keeps coming up:

以下错误消息不断出现：

Traceback (most recent call last):
  File "C:/Users/Documents/landlordlady/python codes/test.py", line 8, in <module>
    QPVData = sp.sql_reader(os.path.join(qry_path, '8-28 qpv test.sql'), server_name=server, database=db)
  File "C:\Users\Documents\landlordlady\python codes\sql_processor.py", line 30, in sql_reader
    data = pd.read_sql(qry,server)
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\io\sql.py", line 399, in read_sql
    chunksize=chunksize)
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\io\sql.py", line 1436, in read_query
    cursor = self.execute(*args)
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\io\sql.py", line 1413, in execute
    raise_with_traceback(ex)
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\compat\__init__.py", line 340, in raise_with_traceback
    raise exc.with_traceback(traceback)
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\io\sql.py", line 1401, in execute
    cur.execute(*args)
pandas.io.sql.DatabaseError: Execution failed on sql 'ÿþ

                 s e l e c t   d i s t i n c t   * 

                 f r o m   b o m . S a l e s B O M S a m p l e E x p l o s i o n   b 

 ': ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'e'. (102) (SQLExecDirectW)")

Process finished with exit code 1

I am so confused! What syntax is this error talking about?!

我感到很困惑！这个错误在谈论什么语法？！

1 个解决方案

#1

By default, SQL Server Management Studio writes .sql files as Unicode with UTF-16LE encoding, as indicated by the Byte Order Mark (BOM) ÿþ (corresponding to \xFF\xFE) in the error message.

默认情况下，SQL Server Management Studio使用UTF-16LE编码将.sql文件写为Unicode，如错误消息中的字节顺序标记（BOM）ÿþ（对应于\ xFF \ xFE）所示。

If a file encoding is not specified, the default encoding for open in Python is system-dependent. So, under Python 3.6.2 on Windows ...

如果未指定文件编码，则Python中open的默认编码与系统有关。所以，在Windows上的Python 3.6.2下......

sql_path = r"C:\Users\Gord\Desktop\from_ssms.sql"
with open(sql_path, 'r') as f:
    sql = f.read()
print(sql)

produces

产生

ÿþs e l e c t   n a m e   f r o m   s y s . t a b l e s 

while

而

with open(sql_path, mode='r', encoding='UTF-16LE') as f:

produces

产生

select name from sys.tables