Python - MySQL - Select data

In this tutorial we will learn to select data from MySQL database using Python.

Prerequisite

To understand this tutorial you need to have some prior knowledge of MySQL.

In this tutorial we will be working with an employee table.

The table already has some rows which are shown below.

mysql< SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2018-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2018-01-01 12:13:14 |
| e03        | Peter     | Parker   | 2018-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2018-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2018-01-04 12:00:00 |
| e06        | Tony      | Stark    | 2018-01-05 12:13:14 |
| e07        | Doctor    | Strange  | 2018-01-05 13:14:15 |
| e08        | Doctor    | Who      | 2018-01-05 13:14:15 |
+------------+-----------+----------+---------------------+
8 rows in set (0.00 sec)

Select data from table

To select data from a table we have the following steps.

1. Connect to the MySQL server using the mysql.connector.connect() method.
2. Get MySQL cursor from the connection created in step 1.
3. Use the cursor to execute the SELECT query.
4. Fetch the date from the result set.
5. Close connection.

Methods to fetch data

To fetch data we can use the following methods.

• fetchone() method which will fetch one row at a time from the result set.
• fetchall() method which will fetch all the rows at once from the result set.

The fetchone() method

To fetch one row at a time we use the fetchone() method.

In the following Python program we are fetching one row at a time.

# import module
import mysql.connector

# import errorcode
from mysql.connector import errorcode

# get db connection
try:

    cnx = mysql.connector.connect(
        user='yusufshakeel',
        password='',
        host='127.0.0.1',
        database='mydb'
    )

except mysql.connector.Error as err:
    
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print('Invalid credential. Unable to access database.')
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print('Database does not exists')
    else:
        print('Failed to connect to database')

# now fetch data
try:
    # cursor
    cnxCursor = cnx.cursor()
    
    # sql query
    query = "SELECT * FROM employee"
    
    # execute
    cnxCursor.execute(query)
    
    # fetch
    row = cnxCursor.fetchone()
    
    # loop
    while row is not None:
        # print result
        print(row)
        
        # fetch another row
        row = cnxCursor.fetchone()
    
except mysql.connector.Error as err:
    
    print("Error:", err.message)
    
    # close connection
    cnx.close()

except:

    print("Unknown error occurred!")

    # close connection
    cnx.close()

finally:
    # close cursor
    cnxCursor.close()
    # close connection
    cnx.close()

The above code will give us a similar output.

(u'e01', u'John', u'Doe', datetime.datetime(2018, 1, 1, 10, 20, 30))
(u'e02', u'Jane', u'Doe', datetime.datetime(2018, 1, 1, 12, 13, 14))
(u'e03', u'Peter', u'Parker', datetime.datetime(2018, 1, 2, 15, 16, 17))
(u'e04', u'Bruce', u'Banner', datetime.datetime(2018, 1, 3, 10, 20, 30))
(u'e05', u'Bruce', u'Wayne', datetime.datetime(2018, 1, 4, 12, 0))
(u'e06', u'Tony', u'Stark', datetime.datetime(2018, 1, 5, 12, 13, 14))
(u'e07', u'Doctor', u'Strange', datetime.datetime(2018, 1, 5, 13, 14, 15))
(u'e08', u'Doctor', u'Who', datetime.datetime(2018, 1, 5, 13, 14, 15))

The fetchall() method

To select all the rows from a table we use the fetchall() method.

In the following Python program we are fetching all the rows from the table.

# import module
import mysql.connector

# import errorcode
from mysql.connector import errorcode

# get db connection
try:

    cnx = mysql.connector.connect(
        user='yusufshakeel',
        password='',
        host='127.0.0.1',
        database='mydb'
    )

except mysql.connector.Error as err:
    
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print('Invalid credential. Unable to access database.')
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print('Database does not exists')
    else:
        print('Failed to connect to database')

# now fetch data
try:
    # cursor
    cnxCursor = cnx.cursor()
    
    # sql query
    query = "SELECT * FROM employee"
    
    # execute
    cnxCursor.execute(query)
    
    # result
    result = cnxCursor.fetchall()
    
    # print result
    print('Total rows: %d' % cnxCursor.rowcount)
    for row in result:
        print(row)
    
except mysql.connector.Error as err:
    
    print("Error:", err.message)
    
    # close connection
    cnx.close()

except:

    print("Unknown error occurred!")

    # close connection
    cnx.close()

finally:
    # close cursor
    cnxCursor.close()
    # close connection
    cnx.close()

The above code will print the following output.

Total rows: 8
(u'e01', u'John', u'Doe', datetime.datetime(2018, 1, 1, 10, 20, 30))
(u'e02', u'Jane', u'Doe', datetime.datetime(2018, 1, 1, 12, 13, 14))
(u'e03', u'Peter', u'Parker', datetime.datetime(2018, 1, 2, 15, 16, 17))
(u'e04', u'Bruce', u'Banner', datetime.datetime(2018, 1, 3, 10, 20, 30))
(u'e05', u'Bruce', u'Wayne', datetime.datetime(2018, 1, 4, 12, 0))
(u'e06', u'Tony', u'Stark', datetime.datetime(2018, 1, 5, 12, 13, 14))
(u'e07', u'Doctor', u'Strange', datetime.datetime(2018, 1, 5, 13, 14, 15))
(u'e08', u'Doctor', u'Who', datetime.datetime(2018, 1, 5, 13, 14, 15))