I am trying to use PyPyODBC, specifically a take on this example. However, I am not interested in creating a new table, I simply want to read the data (which resides in a database/table in SQL Server) and work with it inside of Python.
The below code works fine with the exception of the following section (
for row in cur.fetchall():
).
from __future__ import print_function
import pypyodbc
from pandas import *
import pandas as pd
con = pypyodbc.connect(driver = '{SQL Server}', server = 'WIN-SAV35R5AKCQ',database ='V2.0.6_X12_837I')
cur = con.cursor()
cur.execute('''SELECT * FROM TS837_2400;''')
cur.commit()
for d in cur.description:
print (d[0], end=" ")
print('')
for row in cur.fetchall():
for field in row:
print (field, end=" ")
print ('')
cur.close()
con.close()
However, I get this error.
---------------------------------------------------------------------------
Error Traceback (most recent call last)
<ipython-input-70-c08d5a03056a> in <module>()
14 print('')
15
---> 16 for row in cur.fetchall():
17 for field in row:
18 print (field, end=" ")
C:\Users\Administrator\Anaconda\lib\site-packages\pypyodbc.pyc in fetchall(self)
1805 rows = []
1806 while True:
-> 1807 row = self.fetchone()
1808 if row is None:
1809 break
C:\Users\Administrator\Anaconda\lib\site-packages\pypyodbc.pyc in fetchone(self)
1900 return None
1901 else:
-> 1902 check_success(self, ret)
1903
1904 def __next__(self):
C:\Users\Administrator\Anaconda\lib\site-packages\pypyodbc.pyc in check_success(ODBC_obj, ret)
981 if ret not in (SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA):
982 if isinstance(ODBC_obj, Cursor):
--> 983 ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
984 elif isinstance(ODBC_obj, Connection):
985 ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
C:\Users\Administrator\Anaconda\lib\site-packages\pypyodbc.pyc in ctrl_err(ht, h, val_ret, ansi)
959 raise OperationalError(state,err_text)
960 elif state[:2] in (raw_s('IM'),raw_s('HY')):
--> 961 raise Error(state,err_text)
962 else:
963 raise DatabaseError(state,err_text)
Error: (u'HY010', u'[HY010] [Microsoft][ODBC Driver Manager] Function sequence error')
Question: Ultimately, I want to be able to investigate the data in Python.How can this be done?
Thank you in advance.
Remove the cur.commit()
line, it isn't needed for a SELECT.
I was able to reproduce the error with pyodbc, so I'm assuming the same will work for pypyodbc. You should be able to apply the pyodbc examples to your code, I think they are more complete than pypyodbc's current offering.