Python flask: mysql query cursor.execute(“SELECT * FROM tasksdb WHERE (id=%s)”, (id,)) returns ()

[Alexander Farr]

I have set up a SQL database in a Docker container and access it with a Flask program. In my test file, I want to return the item from the database depending on its id.

However, even though the task with the specified id exists in the database, I get () as result from any of these queries:

cursor.execute("SELECT * FROM tasksdb WHERE id='%s'", (id,))
cursor.execute("SELECT * FROM tasksdb WHERE id='%s'" % (id,))
cursor.execute('SELECT * FROM tasksdb WHERE id=%s', [id])
This is my full code:

from flask import Flask, request, Response
from collections import OrderedDict
import json
import MySQLdb
import json
app = Flask(__name__)

cursor = None

def get_db_connection():
global cursor
#only executed if cursor has not been initialized so far
if not cursor:
#db = MySQLdb.connect("some-mysql", "root", "DockerPasswort!", "demo")
#db = MySQLdb.connect("localhost:3306", "root", "DockerPasswort!", "demo")
db = MySQLdb.connect("127.0.0.1", "root", "DockerPasswort!", "demo", port=3306)
cursor = db.cursor()

return cursor


# Create a new task
@app.route('/v1/tasks', methods=['POST'])
def post():
cursor = get_db_connection()
data = request.get_json()
if "title" not in data:
return bulkadd(data)
is_completed=False
if "is_completed" in data:
is_completed=data["is_completed"]
notify=""
if "notify" in data:
notify=data["notify"]
task = data["title"]
sql='INSERT INTO tasksdb (task, is_completed, notify) VALUES (%s, %s, %s)'
cursor.execute(sql, [task, is_completed, notify])
cursor.execute('SELECT MAX(id) as maxid FROM tasksdb')
id=int(cursor.fetchall()[0][0])
return json.dumps({"id": id}), 201

#List all tasks created
@app.route('/v1/tasks', methods=['GET'])
def getall():
cursor = get_db_connection()
cursor.execute("SELECT * from tasksdb")
data = cursor.fetchall()
response_msg = list()

for row in data:
response_msg_link = OrderedDict()
response_msg_link["id"] = row[0]
response_msg_link["title"] = row[1]
is_completed=row[2]
if(is_completed==0):
is_completed=False
if(is_completed==1):
is_completed=True
response_msg_link["is_completed"] = is_completed
response_msg_link["notify"] = row[3]
response_msg.append(response_msg_link)

return json.dumps({"tasks": response_msg}), 200

#Get a specific task
@app.route('/v1/tasks/<id>', methods=['GET'])
def getone(id):
# I set up id to 22, because I am certain that this exists in the database:
id=int(22)
#cursor.execute("SELECT * FROM tasksdb WHERE id='%s'", (id,))
#cursor.execute("SELECT * FROM tasksdb WHERE id='%s'" % (id,))
cursor.execute('SELECT * FROM tasksdb WHERE id=%s', [id])
row = cursor.fetchall()
print(cursor.fetchall())
try:
response_msg_link = OrderedDict()
response_msg_link["id"] = row[0]
response_msg_link["title"] = row[1]
is_completed=row[2]
if(is_completed==0):
is_completed=False
if(is_completed==1):
is_completed=True
response_msg_link["is_completed"] = is_completed
response_msg_link["notify"] = row[3]
return json.dumps({response_msg}), 200
except:
return json.dumps({"error": "There is no task at that id"}), 404
Even though I have added many more entries in the database with the Flask app, this is the only result from the database (I created this directly in the sql bash shell):

SELECT * FROM tasksdb;
+----+---------------+--------------+-----------------------------+
| id | task | is_completed | notify |
+----+---------------+--------------+-----------------------------+
| 22 | My First Task | 0 | [e-mail] |
+----+---------------+--------------+-----------------------------+
1 row in set (0.00 sec)
Thanks for your help!