Why pymysql cursor.execute() gets different results?

Question

In my flask app (Flask 0.12.2, Python 3.6.2), i want to select row from the MySQL database. This is my import:

from flask import Flask, jsonify
from flaskext.mysql import MySQL

and some initialization:

app = Flask(__name__)
app.config['MYSQL_DATABASE_PORT'] = 3306 
app.config['MYSQL_DATABASE_USER'] = '...'
app.config['MYSQL_DATABASE_PASSWORD'] = '...'
app.config['MYSQL_DATABASE_DB'] = '...'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql = MySQL()
mysql.init_app(app)
con = mysql.connect()

By the way, my table is like:

==============================

id | name | age

1 | "John" | 20

==============================

So now I'm going to write a function which takes "column_name" and "id" as arguments:

@app.route('/test/<device_id>/<column_name>', methods=['GET'])
def test(column_name, device_id):
    sql = "SELECT %s FROM `table` WHERE `id` = %s"
    cursor = con.cursor()
    result_row = cursor.execute(sql, (column_name, device_id))
    query_result = cursor.fetchall()
    cursor.close()
    return jsonify({'result': query_result})

and I expect that if I request to /test/1/name I would get a JSON object:

{"result": ["John"]}

However, I've got the column name instead of column data:

{"result": ["name"]}

I'm wondering if i've got something wrong in the sql, so I use a new sql:

sql = "SELECT `name` FROM `table` WHERE `id` = 1"
result_row = cursor.execute(sql)

And this time it works with this pure sql, but which loses the power of receiving arguments.

So I would like to know what's wrong with my sql in the previous code snippet? or maybe I've messed up with the execute() method?

Don't know much about DB, but I see a difference between 1 and "1" in the code fragments. Could you try: `@app.route('/test//', ...` ?(added `int:` to the id) — VPfB, Mar 28 '18 at 06:16
I've tried, but got the same result. What's the weirdest is whatever weird I specify to the url (e.g. /test/1/asdfasdf), I can still get the JSON result of {"result": ["asdfasdf"]}. So it seems that the sql string is not working here... — puerdon, Mar 28 '18 at 06:53

score 0 · Answer 1 · answered Mar 28 '18 at 06:52

0

you get the result "name" because the real sql running is:

SELECT "name" FROM `table` WHERE `id` = 1

take care with the "name", not `name`

you can get the result you want like this:

sql = "SELECT * FROM `table` WHERE `id` = %s"
cursor = con.cursor()
result_row = cursor.execute(sql, (device_id))
query_result = cursor.fetchall()
for row in query_result:
  print row[column_name]

answered Mar 28 '18 at 06:52

ryanlee

341
3
9

I think you need a tuple in cursor.execute `(device_id,)` – VPfB Mar 28 '18 at 06:57
yes, it could be a solution. But I'm wondering if there is really no way to make the column name an argument. Btw, my query_result is a tuple of tuple `((1, "John", 20),)` , not a tuple of dict, so I can't not access it like `row['name']`. Is is because of the version difference? – puerdon Mar 28 '18 at 07:01
maybe... you can get your the result according to this answer [https://stackoverflow.com/questions/10195139/how-to-retrieve-sql-result-column-value-using-column-name-in-python]@puerdon like this:# Get the fields name (only once!) field_name = [field[0] for field in cursor.description] # Fetch a single row using fetchone() method. values = cursor.fetchone() # create the row dictionary to be able to call row['login'] **row = dict(zip(field_name, values))** # print the dictionary print(row) – ryanlee Mar 28 '18 at 07:19
my `cursor.description` got `None`. But I think I've found the solution from your link. All I have to do is like this: `cursor = con.cursor(pymysql.cursors.DictCursor)` And then the result of the `query_result` will be a list of dicts. – puerdon Mar 28 '18 at 08:41

Why pymysql cursor.execute() gets different results?

1 Answers1