CODEWITHSUNDEEP
pythonmysql
johnrogeksu
johnrogeksu

Reputation: 85

Passing variables into MySQL queries

I am trying to pass a variable loc into my execute statement to get an individual slug number from my db but cant figure out how to pass it through. I have my methods set up this way because there are several different queries I will possibly perform and didnt want a bunch of repeat code.

def query(sql): 
    db = mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="password!",
        database="db"
    )

    cursor = db.cursor()
    cursor.execute(sql)
    final_result = [i[0] for i in cursor.fetchall()]
    return final_result


def individual(loc):
    sql = "SELECT slug FROM auctions WHERE location = %s", loc
    return query(sql)

Upvotes: 0

Views: 39

Answers (1)

Boyke Ferdinandes
Boyke Ferdinandes

Reputation: 411

I usually do it like this

sql = """SELECT slug FROM auctions WHERE location = %(loc_name)s
    """
cursor.execute(sql, {"loc_name": loc,})

pass the value as dictionary. feels safer and tidier :)

if 'sometimes' you need all slugs and 'sometimes' you need a specific slug based on location

if loc: #check if there is loc value
    sql = """SELECT slug FROM auctions WHERE location = %(loc_name)s"""
    cursor.execute(sql, {"loc_name": loc,})
else:
    sql = """SELECT slug FROM auctions"""
    cursor.execute(sql)

that will work, but feels like there is a better writing style.. hmm..

Upvotes: 1

Related Questions