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

PythonServer Side ProgrammingProgramming

We may at times need to check if a particular record exists in a table or not.

This can de done using the EXISTS statement. 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 in a table using MySQL in python

  • 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  |
+------------+---------+

Example

import mysql.connector
db=mysql.connector.connect(host="your host", user="your username", password="your
password",database="database_name")

cursor=db.cursor()

query="SELECT sale_price FROM Sales WHERE EXISTS(SELECT * FROM Sales WHERE tax>150)"
cursor.execute(query)

rows=cursor.fetchall()
for row in rows:
   print(row)

db.close()

The subquery in the above code returns TRUE since there are records where the tax is greater than 150. Thus the EXISTS statement returns true. If there were no records with tax greater than 150, then the EXISTS would have returned false.

Output

1000
500
700
raja
Published on 10-Jun-2021 13:25:34
Advertisements