MySQLdb - Check if row exists Python

Question

I am trying to check if a row exist with the same Name my database with python and can't quite get it here is what I am trying: (I know the connection is wokring)

try:
    cursor.execute("SELECT Name, COUNT(*) FROM Item_Info WHERE Name = %s GROUP BY Name"), (item_name)
catch:
     print "it does not exist"

Can someone help me out here

Thanks

@VigneshKalai it is trowing an exception when the item does exist — spen123, Jul 29 '15 at 06:05

score 20 · Accepted Answer · edited Feb 02 '21 at 05:24

First of all you have a wrong syntax in your code. Python doesn't have a try...catch block. It has try...except block which is used like this:

try:
    # something here
except:
    # something here

MySQL does not return an error when you use SELECT command. However there are two different ways you can find out if it returned something or not.

PYTHON 2.7

cursor.execute(
    "SELECT Name, COUNT(*) FROM Item_Info WHERE Name = %s GROUP BY Name",
    (item_name,)
)
# gets the number of rows affected by the command executed
row_count = cursor.rowcount
print "number of affected rows: {}".format(row_count)
if row_count == 0:
    print "It Does Not Exist"

PYTHON 3+

cursor.execute(
    "SELECT Name, COUNT(*) FROM Item_Info WHERE Name = %s GROUP BY Name",
    (item_name,)
)
# gets the number of rows affected by the command executed
row_count = cursor.rowcount
print ("number of affected rows: {}".format(row_count))
if row_count == 0:
    print ("It Does Not Exist")

Another way to do this would be to fetch the statement and check if it is empty:

# execute statement same as above  
msg = cursor.fetchone()  
# check if it is empty and print error
if not msg:
    print 'It does not exist'

~~This is my first answer, so I don't know how to style the code in the answer properly, it also seems messy because of that. Sorry for that.~~

Also i use Python 3 and pymysql, so there may be some syntax error but I have tried to write the code according to python 2.7 from what I could remember about it.

EDIT (5/1/2020)

Thanks to @Arishta for pointing out that the first method will require you to fetch all rows before using row_count. i.e adding cursor.fetchall() before the row_count = cursor.rowcount

cursor.execute(
    "SELECT Name, COUNT(*) FROM Item_Info WHERE Name = %s GROUP BY Name",
    (item_name,)
)
# Add THIS LINE
results = cursor.fetchall()
# gets the number of rows affected by the command executed
row_count = cursor.rowcount
print("number of affected rows: {}".format(row_count))
if row_count == 0:
    print("It Does Not Exist")

Use the cursor.fetchone() if you only care if the record exists or not.

Nice first answer, keep up the good job :^) See also the [`EXISTS`](http://stackoverflow.com/questions/5264658/is-exists-more-efficient-than-count0) MySQL keyword. — bufh, Jul 29 '15 at 13:11
Thank you, yes 'Exists' certainly looks better since, it stops iterating after a row has been returned. — Digvijayad, Jul 29 '15 at 13:41
Thanks, it works. You need parenthesis around your print statement though. — Dan Cook, Jan 31 '18 at 08:39
@Dan cook the question was answered for python 2.7 which has different syntax than python 3 and above — Digvijayad, Feb 02 '18 at 17:28
Your first method of directly using rowcount without fetching the rows first won't work. It will always return 0 irrespective of the actual number of rows. The documentation states that: "For nonbuffered cursors, rows are not fetched from the server until a row-fetching method is called. In this case, you must be sure to fetch all rows of the result set before executing any other statements on the same connection" — Arishta, Jun 05 '19 at 06:34

score 2 · Answer 2 · answered Feb 01 '21 at 22:10

If you want to check for empty results, try if cur.description is None:

if cursor.description is None:
    # No recordset for INSERT, UPDATE, CREATE, etc
    pass
else:
    # Recordset for SELECT

As well as:

exist = cursor.fetchone()
if exist is None:
  ... # does not exist
else:
  ... # exists

If you are running a statement that would never return a result set (such as INSERT without RETURNING, or SELECT ... INTO), then you do not need to call .fetchall(); there won't be a result set for such statements. Calling .execute() is enough to run the statement.

score 1 · Answer 3 · answered May 16 '19 at 07:02

cursor.execute("SELECT * FROM userinfo WHERE User_Name=%s",(userid,))
data="error" #initially just assign the value
for i in cursor:
    data=i #if cursor has no data then loop will not run and value of data will be 'error'
if data=="error":
    print("User Does not exist")
else:
    print("User exist")

MySQLdb - Check if row exists Python

3 Answers3

Linked