My question is an extension to what was originally SOLVED. I have no issue with what is answered here:
Move data from pyodbc to pandas
What I am doing differently:
I have an extremely long query that works but doesn't look very nice in my Jupyter notebook. so rather than type it out, I am trying to use open('query.sql') to read in the file.
sql = open(r'H:\Common_All...\query.sql'\).read()
df = pd.read_sql(sql,cxnn)
Then it returns...
ProgrammingError Traceback (most recent call last)
~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.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)
~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.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:
~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.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
~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.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
~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\compat\__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
~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.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]
