STEM with Python – Part 5: Python and MySQL Ⅲ

Okay… so I have to go to bed now, but I just found a solution to one of the problems I’ve been struggling with for the last few hours. It is the SQL’s where clause that receives user input and executes a select statement against the database.

Now that it perfectly works, so I want to share it with you.

The code:

Actual code:

import mysql.connector

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

try:
    connection = mysql.connector.connect(host='localhost', 
                user='user', 
                password='ps', 
                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")

Description:

So, there are two parts that need your attention:

The query:

This is what it should be.

sql_query = """select * from sample_tbl where store_name = %s"""

And this is the mistake I made.

sql_query = """select * from sample_tbl where store_name = " + %s"""

The tuple:

This is what it should be. by the way, the comma at the end of the tuple still feels a bit weird… (I’m a Java developer lol)

tuple1 = (store_name,)

And this is the mistake I made. I forgot the comma.

tuple1 = (store_name)

And this is the executed result:

Afterthoughts:

Those days, I’ve been having some sleepless nights all thanks to the programming languages that excite me endlessly. The more I discover new excitements in a language, the less I feel I know about the language, which files my appetite to learn, experiment, and do more with the ever-expanding STEM universe!