Passing variables into MySQL queries

Question

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)

Ilja Everilä · Accepted Answer · 2020-06-01 11:50:28Z

1

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..

edited Jun 1, 2020 at 11:50

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

answered May 26, 2020 at 23:18

Boyke Ferdinandes

4112 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

danblack Over a year ago

ref: cursor.execute

johnrogeksu Over a year ago

Sometimes I need all the slugs though so the location doesn't matter. If I execute without a location Ill get errors right?

Boyke Ferdinandes Over a year ago

Im just writing it based on your string.. I'll edit my answer to cover your comment

Collectives™ on Stack Overflow

Passing variables into MySQL queries

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related