Python
In this tutorial we will learn to select data from MySQL database using Python.
To understand this tutorial you need to have some prior knowledge of MySQL.
Note!
For complete MySQL tutorial click here.
To learn more about SELECT query check out this MySQL - SELECT FROM Table tutorial.
In this tutorial we will be working with an employee table.
employee
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)
To select data from a table we have the following steps.
mysql.connector.connect()
To fetch data we can use the following methods.
fetchone()
fetchall()
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))
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))