A few months ago I developed an application that is using a MS SQL Server 2019 for data storage, and a Flask API to query data from a web application. This ran fine on an Ubuntu server for months without an error.
Since then the server was shut down and I'm setting the project up locally on my Windows 10 machine for demo purposes. I am now experiencing unspecified erros when querying the Flask API from my web application randomly. Sometimes the requests go through fine, sometimes the Flask app throws an error. It is worth noting that I don't experience errors when testing it with Postman or manually execute the query on the SQL Server. It only happens when doing this from the web app.
ERROR:app:Exception on /my-endpoint [GET]
Traceback (most recent call last):
File "D:\Project\ProjectRestApi\venv\lib\site-packages\flask\app.py", line 2447, in wsgi_app
response = self.full_dispatch_request()
File "D:\Project\ProjectRestApi\venv\lib\site-packages\flask\app.py", line 1952, in full_dispatch_request
rv = self.handle_user_exception(e)
File "D:\Project\ProjectRestApi\venv\lib\site-packages\flask_cors\extension.py", line 165, in wrapped_function
return cors_after_request(app.make_response(f(*args, **kwargs)))
File "D:\Project\ProjectRestApi\venv\lib\site-packages\flask\app.py", line 1821, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "D:\Project\ProjectRestApi\venv\lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "D:\Project\ProjectRestApi\venv\lib\site-packages\flask\app.py", line 1950, in full_dispatch_request
rv = self.dispatch_request()
File "D:\Project\ProjectRestApi\venv\lib\site-packages\flask\app.py", line 1936, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "D:\Project\ProjectRestApi\venv\lib\site-packages\flask_cors\decorator.py", line 128, in wrapped_function
resp = make_response(f(*args, **kwargs))
File "d:/Project/ProjectRestApi/app.py", line 186, in get_my_endpoint
cursor.execute(query)
pyodbc.Error: ('HY000', 'The driver did not supply an error!')
Sometimes I also get the following error message:
pyodbc.Error: ('HY010', '[HY010] [Microsoft][ODBC Driver Manager] Function sequence error (0) (SQLRowCount)')
The Python code should not matter as it is a simple SQL SELECT query and has been working for months, and only throws this error when querying from the web app. This also happens with several endpoints, all throwing the same error. For completeness, this is one of the endpoints throwing the error:
@app.route('/my-endpoint', methods=['GET'])
@cross_origin()
def get_my_endpoint():
min_date = request.args.get('min_date')
max_date = request.args.get('max_date')
query = "SELECT * FROM mytable"
if min_date is None and max_date is None:
query += ";"
elif min_date is None:
query += " WHERE dataday <= '{0}';".format(max_date)
elif max_date is None:
query += " WHERE dataday >= '{0}';".format(min_date)
else:
query += " WHERE dataday >= '{0}' AND dataday <= '{1}';".format(min_date, max_date)
cursor.execute(query)
rows = cursor.fetchall()
data = []
for row in rows:
data.append(MyTable(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7]))
return jsonify(data)
On the original Ubuntu server, I was using Gunicorn to communicate between the API and the Nginx server. Now on Windows, I am simply running the Python script. I also received a CORS error when trying to query the API from the local web app, which I solved by using flask_cors.
app = Flask(__name__)
app.json_encoder = MyJSONEncoder
cors = CORS(app)
app.config['CORS_HEADERS'] = 'Content-Type'
database = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + cfg.db['server'] + ';DATABASE=' +
cfg.db['database'] + ';UID=' + cfg.db['username'] + ';PWD=' + cfg.db['password'] +
";MultipleActiveResultSets=True" +
(";Trusted_Connection=Yes" if os.name == 'nt' else ""), autocommit=True)
database.setencoding(encoding='utf-8')
cursor = database.cursor()
The request from the web app is made via axios:
let response = await axios.get("http://localhost:5000/my-endpoint");
I'm thinking this has to be an issue with me running the setup locally on Windows instead of a remote Linux server, as it has worked perfectly fine there. Unfortunately the error is not giving me any information and is only happening in 50% of cases which makes no sense to me.
