CODEWITHSUNDEEP
pythonsqlsql-serverpyodbc
theRealJuicyJ
theRealJuicyJ

Reputation: 3

Move data from pyodbc to pandas using sql script

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]

Upvotes: 0

Views: 589

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

Incorrect syntax near 'GO'.

GO is not a T-SQL statement. It is a command that only applies to the SQL Server shells: sqlcmd, SQL Server Management Studio (SSMS), etc.. To execute your SQL statement via ODBC you will need to remove the GO command from your text file.

For more information see

SQL Server Utilities Statements - GO

Upvotes: 1

Related Questions