How can you test if some record exists or not in a MySQL table using Python?

We may at times need to check if a particular record exists in a MySQL table. This can be done using the EXISTS statement or by checking if a query returns any rows. The EXISTS statement returns true if the following subquery returns one or more records.

Syntax

SELECT * FROM table_name WHERE EXISTS(sub_query)

The subquery if returns one or more rows, the EXISTS will return true.

Steps to Check if a Record Exists

• Import MySQL connector

• Establish connection with the connector using connect()

• Create the cursor object using cursor() method

• Create a query using the appropriate MySQL statements

• Execute the SQL query using execute() method

• Close the connection

Suppose we have the following table named "Sales" ?

+------------+---------+
| sale_price |   tax   |
+------------+---------+
|    1000    |   200   |
|    500     |   100   |
|    50      |   50    |
|    180     |   180   |
+------------+---------+

Method 1: Using EXISTS Statement

The EXISTS statement checks if a subquery returns any records ?

import mysql.connector

# Establish connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Check if any record exists with tax > 150
query = "SELECT sale_price FROM Sales WHERE EXISTS(SELECT * FROM Sales WHERE tax > 150)"
cursor.execute(query)

rows = cursor.fetchall()
if rows:
    print("Records found with tax > 150:")
    for row in rows:
        print(row[0])
else:
    print("No records found with tax > 150")

db.close()

The output of the above code is ?

Records found with tax > 150:
1000
500
50
180

Method 2: Using COUNT to Check Existence

A more direct approach is to count matching records ?

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Check if specific record exists
query = "SELECT COUNT(*) FROM Sales WHERE sale_price = 1000"
cursor.execute(query)

count = cursor.fetchone()[0]
if count > 0:
    print("Record with sale_price = 1000 exists")
else:
    print("Record with sale_price = 1000 does not exist")

db.close()

The output of the above code is ?

Record with sale_price = 1000 exists

Method 3: Using fetchone() to Check Existence

You can also use fetchone() to check if any record is returned ?

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Check if specific record exists
query = "SELECT 1 FROM Sales WHERE tax = 200 LIMIT 1"
cursor.execute(query)

result = cursor.fetchone()
if result:
    print("Record with tax = 200 exists")
else:
    print("Record with tax = 200 does not exist")

db.close()

The output of the above code is ?

Record with tax = 200 exists

Comparison of Methods

Conclusion

Use fetchone() with LIMIT 1 for the fastest single record existence checks. Use EXISTS for complex subqueries and COUNT(*) for simple counting scenarios.