How to check if record exists with Python MySQdb

Question

Im creating a python program that connects to mysql.

i need to check if a table contains the number 1 to show that it has connected successfully, this is my code thus far:

xcnx.execute('CREATE TABLE settings(status INT(1) NOT NULL)')
  xcnx.execute('INSERT INTO settings(status) VALUES(1)')
  cnx.commit()
  sqlq = "SELECT * FROM settings WHERE status = '1'"
  xcnx.execute(sqlq)
  results = xcnx.fetchall()
  if results =='1':
    print 'yep its connected'
  else:
    print 'nope not connected'

what have i missed? i am an sql noob, thanks guys.

Answer 1

I believe the most efficient "does it exist" query is just to do a count:

sqlq = "SELECT COUNT(1) FROM settings WHERE status = '1'"
xcnx.execute(sqlq)
if xcnx.fetchone()[0]:
    # exists

Instead of asking the database to perform any count operations on fields or rows, you are just asking it to return a 1 or 0 if the result produces any matches. This is much more efficient that returning actual records and counting the amount client side because it saves serialization and deserialization on both sides, and the data transfer.

In [22]: c.execute("select count(1) from settings where status = 1")
Out[22]: 1L  # rows

In [23]: c.fetchone()[0]
Out[23]: 1L  # count found a match

In [24]: c.execute("select count(1) from settings where status = 2")
Out[24]: 1L  # rows

In [25]: c.fetchone()[0]
Out[25]: 0L  # count did not find a match

count(*) is going to be the same as count(1). In your case because you are creating a new table, it is going to show 1 result. If you have 10,000 matches it would be 10000. But all you care about in your test is whether it is NOT 0, so you can perform a bool truth test.

Update

Actually, it is even faster to just use the rowcount, and not even fetch results:

In [15]: if c.execute("select (1) from settings where status = 1 limit 1"): 
            print True
True

In [16]: if c.execute("select (1) from settings where status = 10 limit 1"): 
            print True

In [17]: 

This is also how django's ORM does a queryObject.exists().

Answer 2

If all you want to do is check if you have successfully established a connection then why are you trying to create a table, insert a row, and then retrieve data from it?

You could simply do the following...

sqlq = "SELECT * FROM settings WHERE status = '1'"
xcnx.execute(sqlq)
results = xcnx.fetchone()
if results =='1':
  print 'yep its connected'
else:
  print 'nope not connected'

In fact if your program has not thrown an exception so far indicates that you have established the connection successfully. (Do check the code above, I'm not sure if fetchone will return a tuple, string, or int in this case).

By the way, if for some reason you do need to create the table, I would suggest dropping it before you exit so that your program runs successfully the second time.

Answer 3

When you run results = xcnx.fetchall(), the return value is a sequence of tuples that contain the row values. Therefore when you check if results == '1', you are trying to compare a sequence to a constant, which will return False. In your case, a single row of value 0 will be returned, so you could try this:

results = xcnx.fetchall()
# Get the value of the returned row, which will be 0 with a non-match
if results[0][0]:
  print 'yep its connected'
else:
  print 'nope not connected'

You could alternatively use a DictCursor (when creating the cursor, use .cursor(MySQLdb.cursors.DictCursor) which would make things a bit easier to interpret codewise, but the result is the same:

if results[0]['COUNT(*)]':
    # Continues...

Also, not a big deal in this case, but you are comparing an integer value to a string. MySQL will do the type conversion, but you could use SELECT COUNT(*) FROM settings WHERE status = 1 and save a (very small) bit of processing.

Answer 4

I recently improved my efficiency by instead of querying select, just adding a primary index to the unique column and then adding it. MySQL will only add it if it doesn't exist.

So instead of 2 statements:

 Query MySQL for exists:
     Query MySQL insert data

Just do 1 and it will only work if it's unique:

Query MySQL insert data

1 Query is better than 2.