Python3 _ MySQLのselect文でSyntaxエラー

Question

PythonでMySQLサーバにアクセスし、簡単なselect文を実行したいのですが、
下記の様なエラーが発生してしまいます。

やりたいこととしては、
「store_name = str(input("What is the store name? "))」でユーザ名を取得、
受け取ったユーザ名を使ってselect文を発行。

エラー文

What is the store name? amazon
parameterized query failed You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '" + ?' at line 1
MySQL connection is closed

下記が実行ファイルになります。

import mysql.connector

store_name = str(input("What is the store name? "))

try:
    connection = mysql.connector.connect(host='localhost', user='username', password='pasword', database='db')

    cursor = connection.cursor(prepared=True)
    sql_query = """select * from sample_tbl where store_name = " + %s"""
    tuple1 = (store_name)
    cursor.execute(sql_query, tuple1)

    for tbl in cursor:
        print(tbl)
   
    connection.commit()
    

except mysql.connector.Error as error:
    print("parameterized query failed {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

mysql> select * from sample_tbl;
+----+------------+------------+--------------+--------------+-------+---------------------+
| id | date       | store_name | product_name | product_type | price | created_at          |
+----+------------+------------+--------------+--------------+-------+---------------------+
|  1 | 2022-09-14 | Amazon     | Echo show    | gadget       |  56.7 | 2022-09-14 10:06:04 |
|  3 | 2022-09-21 | Google     | Pixel 6 Pro  | gadget       | 68000 | 2022-09-21 23:10:48 |
+----+------------+------------+--------------+--------------+-------+---------------------+

大変お手数ではございますが、解決の為ヒントなどがあれご教示頂けますと幸いです。
よろしくお願いいたします。

Answer 1

クオートの閉じが変なのと、タプルの最後にカンマを加える必要があるんですかね

import mysql.connector

store_name = str(input("What is the store name? "))

try:
    connection = mysql.connector.connect(host='localhost', user='username', password='pasword', database='db')

    cursor = connection.cursor(prepared=True)
    # sql_query = """select * from sample_tbl where store_name = " + %s"""
    # tuple1 = (store_name)
    sql_query = """select * from sample_tbl where store_name = %s"""
    tuple1 = (store_name,)
    cursor.execute(sql_query, tuple1)

    for tbl in cursor:
        print(tbl)
   
    connection.commit()


except mysql.connector.Error as error:
    print("parameterized query failed {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")
        
import mysql.connector

Comments

[Yakusugi]

ありがとうございます！
うまくいきました！