Best post on Select Query to Search & Read: Python + MySQL-4

In this blog we are going to use select query to search and read data in python from MySQL. Database connectivity plays important role in developing dynamic applications.

 

After establishing connection with SQL, we can create new databases and tables using python script to automate work.

In previous post we have learnt about create and insert query to create database and table in MySQL using python.

Now let us learn how to read data from MySQL and how to search data from it using Python interface.

 

Select Query to read data from MySQL

Select Query is used to fetch records from existing table or database using python interface.

We can either read single record, multiple records or all records from table.

Syntax:

cur.execute("Select * from tablename")

Note: 

Before using select query it is mandatory to use or create database and related table. Also values must be present to be fetched from table else no rows will be fetched.

Example to read

'''
Read Operation in SUSCRIBERS Table
'''
import mysql.connector
db=mysql.connector.connect(user='root',passwd='root',host='127.0.0.1',database='itvoyagers')
# prepare a cursor object using cursor() method
cursor = db.cursor()
sql = "SELECT * FROM SUSCRIBERS"
try:

# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
SUSCRIBER_FNAME = row[0]
SUSCRIBER_LNAME = row[1]
AGE = row[2]
GENDER = row[3]
INTERESTS = row[4]
#Now print fetched result
print (SUSCRIBER_FNAME, SUSCRIBER_LNAME, AGE, GENDER, INTERESTS )
except:
print ("Error: unable to fetch data")
# disconnect from server
db.close()

 

OUTPUT

>>> 
Kiara R 21 F Python

Select Query to search data from MySQL

Select Query is used to fetch records from existing table or database using python interface.

We can either search single record or multiple records from table using where clause with select query.

Syntax:

cur.execute("Select * from tablename where condition")

Here,

where : is used to give desired condition

 

Note: 

Before using select query it is mandatory to use or create database and related table. Also values must be present to be fetched from table else no rows will be fetched.

Example to search

'''
Search Operation in SUSCRIBERS Table
'''
import mysql.connector
db=mysql.connector.connect(user='root',passwd='root',host='127.0.0.1',database='itvoyagers')
# prepare a cursor object using cursor() method
cursor = db.cursor()
sql = "SELECT * FROM SUSCRIBERS WHERE AGE > 20"
try:

# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
SUSCRIBER_FNAME = row[0]
SUSCRIBER_LNAME = row[1]
AGE = row[2]
GENDER = row[3]
INTERESTS = row[4]
#Now print fetched result
print (SUSCRIBER_FNAME, SUSCRIBER_LNAME, AGE, GENDER, INTERESTS )
except:
print ("Error: unable to fetch data")
# disconnect from server
db.close()

OUTPUT

>>> 
Kiara R 21 F Python

Leave a Comment