MySQL Forums
Forum List  »  Connector/Python

Python flask: mysql query cursor.execute(“SELECT * FROM tasksdb WHERE (id=%s)”, (id,)) returns ()
Posted by: Alexander Farr
Date: April 06, 2020 02:39AM

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 | test@berkeley.edu |
+----+---------------+--------------+-----------------------------+
1 row in set (0.00 sec)
Thanks for your help!

Options: ReplyQuote


Subject
Written By
Posted
Python flask: mysql query cursor.execute(“SELECT * FROM tasksdb WHERE (id=%s)”, (id,)) returns ()
April 06, 2020 02:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.