variable returning empty in if statement python

Question

title = article.title
title = re.sub(r' - Wikipedia, the free encyclopedia','',title)
title_lower = title.lower()
title_lower = title_lower.replace(' ','-')
print title
print title_lower
title_query = ("INSERT INTO  myguests "
               "(firstname) "
               "VALUES (%s)")

cursor.execute("SELECT id FROM myguests "
                 "WHERE firstname='"+title+"'")

row = cursor.fetchall()
if row !=[]:
    print "List is not empty"
if not row:
    print "List is empty"
    title_query = title
print title

For some reason in my if statement my title variable keeps coming back empty when it is called if not row:

I am trying to insert variable if the variable dose not exist in column.

`cursor.execute("SELECT id FROM myguests " "WHERE firstname='"+title+"'")` **SQL Injection** — Lukasz Szozda, Dec 28 '15 at 11:55
That part of the code is working but when i get to the if statement if i return the title variable is empty it dose not contain the string that is assigned to it before the if statement — Real Connect, Dec 28 '15 at 12:06
@RealConnect: lad2025 is warning you that code that uses string concatenation to build SQL statements might be vulnerable to SQL injection attacks, particularly if the data comes from the user. — mhawke, Dec 28 '15 at 12:12
could u point me into the right direction on going on about this basically i want to update my database if the variable dosent exist in my sql database — Real Connect, Dec 28 '15 at 12:17

score 0 · Answer 1 · edited May 23 '17 at 11:59

If no rows are returned then it's probably because there is no data in the table with the requested firstname. Try adding some debugging to your code and use a parameterised query rather than string concatenation:

cursor.execute("SELECT id FROM myguests WHERE firstname = %s", (title,))
row = cursor.fetchall()
print 'Got row: {!r}'.format(row)    # some debugging

if row:
    print "Record for {} already exists".format(title)
else:
    print "List is empty, attempting to insert"
    cursor.execute(title_query, (title,))

But there is a potential race condition with this approach; what if some other process adds the value to the database between the initial check and the subsequent insert? Depending on your application this may or may not be a problem.

Regarding "insert if not exists", one way is to set a UNIQUE INDEX on the firstname column. and then simply attempt to insert a new row without checking first. If a row exists with the same value for firstname the insert will fail. If no such row exists, the insert will be attempted (it might still fail, but for other reasons). Your code needs to handle insertion failure due to duplicate key.

Or you could investigate use of INSERT IGNORE into myguests ..., or some of the other options discussed here: How to 'insert if not exists' in MySQL?.

But are you really sure that firstname should be unique? It seems to me likely that many guests could have the same first name.

variable returning empty in if statement python

1 Answers1