Where clause with a dangling comma in Python

Question

I'm following a Python tutorial at W3school.

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE address LIKE %s"
adr = ("Yellow Garden 2", )        // I don't understand this part!!!

mycursor.execute(sql, adr) 

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

If I run the above code, I get the following result.

However when I remove a blank space after a comma, I get an error.
adr = ("Yellow Garden 2", ) --> adr = ("Yellow Garden 2" )

1) I want to know why I need a blank space after a comma.

2) Why do I need a bracket ( ) ,too? (When I remove the brackets, I also get an error.) Like adr = "Yellow Garden 2"

Answer 1

adr = ("Yellow Garden 2", )

This defines adr to be a tuple with a single element. You can verify with:

type(adr)

Should be:

<class 'tuple'>

When you remove the comma , (it's the comma that's causing it, not the blank space), adr becomes a simple string:

adr = ("Yellow garden 2")
type(adr)

Should be:

<type 'str'>

The MySQL API expects a tuple to be passed as arguments, hence the error. And the (soemthing,) is just how you define single-element tuples in python.

Answer 2

I based this answer off this blog post. The second parameter to execute is a tuple, and a dangling comma is required, but only if the tuple has just one element in it. As the blog discusses, the dangling comma is required to distinguish:

("Yellow Garden 2",)

the tuple, from:

("Yellow Garden 2")

the literal string expression. Or, at least (1) would be ambiguous as to whether you intended for it to be a tuple or arithmetic expression.

However, in the case of two or more parameters, the dangling comma should be optional, e.g.

sql = "SELECT * FROM customers WHERE address LIKE %s OR address LIKE %s"
adr = ("Yellow Garden 1", "Yellow Garden 2")
mycursor.execute(sql, adr)

Edit:

I will add also that it might be considered best programming practice to always use the dangling comma in a tuple here. Why? Let's say you authored some code which initially used a tuple with two elements in it. You would technically not need the dangling comma. But then, in the future, someone might refactor and remove one element. If you had not included the dangling comma, then the code might break.