Python MySQL select()

In this Python tutorial, we will see how to select data in mysql database using select clause through python.

 

We will apply select statement on this table in various ways and see the result.

 

SELECT *

Applying SELECT * on table will fetch all the data (rows and coloumn) from the table.

 

Syntax of SELECT *

SELECT * FROM table_name;

SELECT columns

Suppose we only want to select particular columns from the table instead of all the data. We can easily do it using SELECT statement.

 

Syntax

SELECT column1, column2,....,columnN FROM table_name;

Now, let' see how to perform SELECT in xampp server through python.

Before that make sure that you have created a database named - tutorialsinhand  and table inside it  market with the following records.

Steps:

1. Import mysql.connector module
    Syntax:
    import mysql.connector

2. Create the connection object to connect with xampp server using the below credentials.
    Syntax:
    connection_obj = mysql.connector.connect(
                          host="localhost",
                          user="root",
                          password=""
                                                                        )
3. Create cursor object to execute the query.
    Syntax:
    connection_obj.cursor()

4. Write sql query to select columns from the table.
   Syntax:
   SELECT * FROM table_name
5. Fetch the resulted rows using fetchall().

    Syntax:
    cursor.fetchall()

6. Display using for loop 

     Syntax:
         for i in results:
            print(i)

Note - It will return the values in a tuple.

Example 1:

In this example, we are selecting all columns from market table and display.

#import the module
import mysql.connector

#create the connection by specifying the 
#hostname-localhost
#username-root
#password-''(empty) and
#database is tutorialsinhand
connection_obj = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database="tutorialsinhand"
)

#creating cursor object
cursor_obj = connection_obj.cursor()



#select all columns from table
cursor_obj.execute("SELECT * FROM market")

#fetch the rows
results = cursor_obj.fetchall()

#get the values
for i in results:
  print(i)

Output:

We can see that all column values are selected.

('koti-cente', 'kakumanu', 8)
('bakery', 'hyderabad', 5)
('hali', 'hyderabad', 7)
('clothstore', 'bengal', 2)

Example 2:

In this example, we are selecting marhet_name and rating  columns from market table and display.

#import the module
import mysql.connector

#create the connection by specifying the 
#hostname-localhost
#username-root
#password-''(empty) and
#database is tutorialsinhand
connection_obj = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database="tutorialsinhand"
)

#creating cursor object
cursor_obj = connection_obj.cursor()



#select market_name,rating columns from table
cursor_obj.execute("SELECT market_name,rating FROM market")

#fetch the rows
results = cursor_obj.fetchall()

#get the values
for i in results:
  print(i)

Output:

values from market_name and rating columns are returned.

('koti-cente', 8)
('bakery', 5)
('hali', 7)
('clothstore', 2)

 

Example:

If we want to return one by one, then use index position to display one by one value with separate print() statements.

#import the module
import mysql.connector

#create the connection by specifying the 
#hostname-localhost
#username-root
#password-''(empty) and
#database is tutorialsinhand
connection_obj = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database="tutorialsinhand"
)

#creating cursor object
cursor_obj = connection_obj.cursor()



#select market_name,rating columns from table
cursor_obj.execute("SELECT market_name,rating FROM market")

#fetch the rows
results = cursor_obj.fetchall()

#get the values
for i in results:
  print("Market Name: ",i[0])
  print("Market Rating: ",i[1])
  print()

Output:

Market Name:  koti-cente
Market Rating:  8

Market Name:  bakery
Market Rating:  5

Market Name:  hali
Market Rating:  7

Market Name:  clothstore
Market Rating:  2