Forums

So I have a Flask website and when checking for duplicate contacts and wanting to remove them from the user's contact list I run the following:

cmd = "DELETE FROM contacts WHERE contact LIKE '{0}'.format( str(contact_) )"    
cur.execute(cmd)

conn.commit()
conn.close()

I've got a print statement in there checking the cmd variable replaces {0} w/ the value of contact_ (the underscore is intentional). So why wouldn't it work?

Extra info because everyone always asks even if it's irrelevant: My OS: Xubuntu Python Version: 3 An example of what the cmd variable could look like is "DELETE FROM contacts WHERE contact LIKE 'Ilovecake'

I would suggesting going to codementor for these sort of questions. You may find it super beneficial to get someone to walk you through it.

@conrad I'd rather not pay somebody $20 for answering a single question of mine. So thanks, but no thanks. Just BTW, this project is only for my own education, I don't plan on turning it into a money making business so don't want to spend money on it.

It's not working because you need a '%' on either side of your like string. Like this:

:::python cmd = "DELETE FROM contacts WHERE contact LIKE '%{0}%'.format( str(contact_) )"

LIKE is regexy and you need the %s to act as "any string of characters is acceptable here"

Thanks glenn. I'm just going to try that. I'll let you know how it turns out. :) Have a nice day

All that seems to do is add percentage signs to either side of the contact's name. So the SQL command ends up looking like "DELETE FROM contacts WHERE contact LIKE '%Ilovecake%'"

<br> If I put the double quotes exactly where you did, it ends up looking like below: <br> "DELETE FROM contacts WHERE contact LIKE '%{0}%'.format( str(contact_) )"

LIKE %ilovecake%

with the % on either side of the string is what you want, I think -- that's how MySQL's pattern-matching works, % is a placeholder for "match anything".

https://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

One thing that looks like a bug to me is that the .format() should be outside the quotes:

cmd = "DELETE FROM contacts WHERE contact LIKE '%{0}%'".format( str(contact_) )

Bonus optional extra: The Python db-api has its own syntax for substituting in parameters in order to avoid sql injection attacks. Sqlite uses ?, mysql uses %s. So you can also try this:

cur.execute(
    "DELETE FROM contacts WHERE contact LIKE %s",
     "%" + contact_ + "%"
)

it's a bit confusing because the % then sort of has two meanings, but it should work.