使用SQL脚本将数据从pyodbc移动到PANAS [英] Move data from pyodbc to pandas using sql script

问题描述

我的问题是最初解决的问题的延伸。我对这里回答的问题没有异议：

Move data from pyodbc to pandas

我做的不同之处：

我有一个非常长的查询，它可以工作，但在我的Jupyter笔记本中看起来不是很好。因此，我尝试使用OPEN(‘query.sql’)来读入文件，而不是将其输入。

sql = open(r'H:Common_All...query.sql').read()

df = pd.read_sql(sql,cxnn)

然后返回...

ProgrammingError                          Traceback (most recent call last)
~AppDataLocalContinuumanaconda3libsite-packagespandasiosql.py in 
execute(self, *args, **kwargs)
   1403             else:
-> 1404                 cur.execute(*args)
   1405             return cur

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

During handling of the above exception, another exception occurred:

DatabaseError                             Traceback (most recent call last)
<ipython-input-12-f1680f29c12d> in <module>()
    158 
    159 # Creating the dataframe from database
--> 160 df = pd.read_sql(sql, conn)

~AppDataLocalContinuumanaconda3libsite-packagespandasiosql.py in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns,     chunksize)
    398             sql, index_col=index_col, params=params,
    399             coerce_float=coerce_float, parse_dates=parse_dates,
--> 400             chunksize=chunksize)
    401 
    402     try:

~AppDataLocalContinuumanaconda3libsite-packagespandasiosql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize)
   1437 
   1438         args = _convert_params(sql, params)
-> 1439         cursor = self.execute(*args)
   1440         columns = [col_desc[0] for col_desc in cursor.description]
   1441 

~AppDataLocalContinuumanaconda3libsite-packagespandasiosql.py in execute(self, *args, **kwargs)
   1414             ex = DatabaseError(
   1415                 "Execution failed on sql '%s': %s" % (args[0], exc))
-> 1416             raise_with_traceback(ex)
   1417 
   1418     @staticmethod

~AppDataLocalContinuumanaconda3libsite-packagespandascompat\__init__.py in raise_with_traceback(exc, traceback)
    342         if traceback == Ellipsis:
    343             _, _, traceback = sys.exc_info()
--> 344         raise exc.with_traceback(traceback)
    345 else:
    346     # this version of raise is a syntax error in Python 3

~AppDataLocalContinuumanaconda3libsite-packagespandasiosql.py in execute(self, *args, **kwargs)
   1402                 cur.execute(*args, **kwargs)
   1403             else:
-> 1404                 cur.execute(*args)
   1405             return cur
   1406         except Exception as exc:

DatabaseError: Execution failed on sql...[insert the query in my sql file]

推荐答案

"Go"附近的语法不正确。

GO不是T-SQL语句。该命令仅适用于以下SQL Server外壳：sqlcmd、SQL Server Management Studio(SSMS)等。若要通过ODBC执行SQL语句，您需要从文本文件中删除GO命令。

有关详细信息，请参阅

SQL Server Utilities Statements - GO

这篇关于使用SQL脚本将数据从pyodbc移动到PANAS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！