Learn Python – Read Operation- Basic and advance

The SELECT announcement is used to examine the values from the databases. We can prevent the output of a pick question via the use of a variety of clause in SQL like where, limit, etc.

Python provides the fetchall() technique returns the facts saved internal the desk in the shape of rows. We can iterate the result to get the man or woman rows.

In this section of the tutorial, we will extract the data from the database with the aid of the use of the python script. We will additionally layout the output to print it on the console.

Example

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
  
try:  
    #Reading the Employee data      
    cur.execute("select * from Employee")  
  
    #fetching the rows from the cursor object  
    result = cur.fetchall()  
    #printing the result  
      
    for x in result:  
        print(x);  
except:  
    myconn.rollback()  
  
myconn.close()  

Output:

('John', 101, 25000.0, 201, 'Newyork')
('John', 102, 25000.0, 201, 'Newyork')
('David', 103, 25000.0, 202, 'Port of spain')
('Nick', 104, 90000.0, 201, 'Newyork')
('Mike', 105, 28000.0, 202, 'Guyana')

Reading specific columns

We can read the precise columns by using bringing up their names instead of the usage of star (*).

In the following example, we will study the name, id, and earnings from the Employee table and print it on the console.

Example

import mysql.connector  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
#creating the cursor object  
cur = myconn.cursor()  
try:  
    #Reading the Employee data      
    cur.execute("select name, id, salary from Employee")  
  
    #fetching the rows from the cursor object  
    result = cur.fetchall()  
    #printing the result  
    for x in result:  
        print(x);  
except:  
    myconn.rollback()  
myconn.close()  

Output:

('John', 101, 25000.0)
('John', 102, 25000.0)
('David', 103, 25000.0)
('Nick', 104, 90000.0)
('Mike', 105, 28000.0)

The fetchone() method

The fetchone() technique is used to fetch only one row from the table. The fetchone() method returns the subsequent row of the result-set.

Consider the following example.

Example

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
  
try:  
    #Reading the Employee data      
    cur.execute("select name, id, salary from Employee")  
  
    #fetching the first row from the cursor object  
    result = cur.fetchone()  
  
    #printing the result  
    print(result)  
  
except:  
    myconn.rollback()  
      
myconn.close()  

Output:

('John', 101, 25000.0)

Formatting the result

We can structure the result via iterating over the end result produced with the aid of the fetchall() or fetchone() approach of cursor object due to the fact that the end result exists as the tuple object which is no longer readable.

Consider the following example.

Example

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
  
try:  
  
    #Reading the Employee data      
    cur.execute("select name, id, salary from Employee")  
  
    #fetching the rows from the cursor object  
    result = cur.fetchall()  
  
    print("Name    id    Salary");  
    for row in result:  
        print("%s    %d    %d"%(row[0],row[1],row[2]))  
except:  
    myconn.rollback()  
  
myconn.close()  

Output:

Name    id    Salary
John    101    25000
John    102    25000
David    103    25000
Nick    104    90000
Mike    105    28000

Using where clause

We can restrict the result produced through the choose declaration by the use of the where clause. This will extract solely those columns which fulfill the the place condition.

Consider the following example.

Example: printing the names that start with j

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
  
try:  
    #Reading the Employee data      
    cur.execute("select name, id, salary from Employee where name like 'J%'")  
  
    #fetching the rows from the cursor object  
    result = cur.fetchall()  
  
    print("Name    id    Salary");  
  
    for row in result:  
        print("%s    %d    %d"%(row[0],row[1],row[2]))  
except:  
    myconn.rollback()  
  
myconn.close()  

Output:

Name    id    Salary
John    101    25000
John    102    25000

Example: printing the names with id = 101, 102, and 103

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
  
try:  
    #Reading the Employee data      
    cur.execute("select name, id, salary from Employee where id in (101,102,103)")  
  
    #fetching the rows from the cursor object  
    result = cur.fetchall()  
  
    print("Name    id    Salary");  
  
    for row in result:  
        print("%s    %d    %d"%(row[0],row[1],row[2]))  
except:  
    myconn.rollback()  
      
myconn.close()  

Output:

Name    id    Salary
John    101    25000
John    102    25000
David    103    2500

Ordering the result

The ORDER BY clause is used to order the result. Consider the following example.

Example

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
  
try:  
    #Reading the Employee data      
    cur.execute("select name, id, salary from Employee order by name")  
  
    #fetching the rows from the cursor object  
    result = cur.fetchall()  
  
    print("Name    id    Salary");  
  
    for row in result:  
        print("%s    %d    %d"%(row[0],row[1],row[2]))  
except:  
    myconn.rollback()  
  
myconn.close()  

Output:

Name    id    Salary
David   103    25000
John    101    25000
John    102    25000
Mike    105    28000
Nick    104    90000

Order by DESC

This orders the end result in the decreasing order of a particular column.

Example

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
  
try:  
    #Reading the Employee data      
    cur.execute("select name, id, salary from Employee order by name desc")  
  
    #fetching the rows from the cursor object  
    result = cur.fetchall()  
  
    #printing the result  
    print("Name    id    Salary");  
    for row in result:  
        print("%s    %d    %d"%(row[0],row[1],row[2]))  
  
except:  
    myconn.rollback()  
  
myconn.close()  

Output:

Name    id    Salary
Nick    104    90000
Mike    105    28000
John    101    25000
John    102    25000
David    103    25000