Python MySQL - Select Data

You can retrieve/fetch data from a table in MySQL using the SELECT query. This query/statement returns contents of the specified table in tabular form and it is called as result-set.

Syntax

Following is the syntax of the SELECT query −

SELECT column1, column2, columnN FROM table_name;

Example

Assume we have created a table in MySQL with name cricketers_data as −

CREATE TABLE cricketers_data(
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);

And if we have inserted 5 records in to it using INSERT statements as −

insert into cricketers_data values('Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into cricketers_data values('Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into cricketers_data values('Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into cricketers_data values('Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into cricketers_data values('Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');

Following query retrieves the FIRST_NAME and Country values from the table.

mysql> select FIRST_NAME, Country from cricketers_data;
+------------+-------------+
| FIRST_NAME | Country     | 
+------------+-------------+
| Shikhar    | India       |
| Jonathan   | SouthAfrica |
| Kumara     | Srilanka    |
| Virat      | India       |
| Rohit      | India       |
+------------+-------------+
5 rows in set (0.00 sec)

You can also retrieve all the values of each record using * instated of the name of the columns as −

mysql> SELECT * from cricketers_data;
+------------+------------+---------------+----------------+-------------+
| First_Name | Last_Name  | Date_Of_Birth | Place_Of_Birth | Country     |
+------------+------------+---------------+----------------+-------------+
| Shikhar    | Dhawan     | 1981-12-05    | Delhi          | India       |
| Jonathan   | Trott      | 1981-04-22    | CapeTown       | SouthAfrica |
| Kumara     | Sangakkara | 1977-10-27    | Matale         | Srilanka    |
| Virat      | Kohli      | 1988-11-05    | Delhi          | India       |
| Rohit      | Sharma     | 1987-04-30    | Nagpur         | India       |
+------------+------------+---------------+----------------+-------------+
5 rows in set (0.00 sec)

Reading Data From a MYSQL Table Using Python

READ Operation on any database means to fetch some useful information from the database. You can fetch data from MYSQL using the fetch() method provided by the mysql-connector-python.

The cursor.MySQLCursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where,

  • The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones).

  • The fetchone() method fetches the next row in the result of a query and returns it as a tuple.

  • The fetchmany() method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row.

Note − A result set is an object that is returned when a cursor object is used to query a table.

rowcount − This is a read-only attribute and returns the number of rows that were affected by an execute() method.

Example

Following example fetches all the rows of the EMPLOYEE table using the SELECT query and from the obtained result set initially, we are retrieving the first row using the fetchone() method and then fetching the remaining rows using the fetchall() method.

import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb'
)

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving single row
sql = '''SELECT * from EMPLOYEE'''

#Executing the query
cursor.execute(sql)

#Fetching 1st row from the table
result = cursor.fetchone();
print(result)

#Fetching 1st row from the table
result = cursor.fetchall();
print(result)

#Closing the connection
conn.close()

Output

('Krishna', 'Sharma', 19, 'M', 2000.0)
[('Raj', 'Kandukuri', 20, 'M', 7000.0), ('Ramya', 'Ramapriya', 25, 'M', 5000.0)]

Following example retrieves first two rows of the EMPLOYEE table using the fetchmany() method.

Example

import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb'
)

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving single row
sql = '''SELECT * from EMPLOYEE'''

#Executing the query
cursor.execute(sql)

#Fetching 1st row from the table
result = cursor.fetchmany(size =2);
print(result)

#Closing the connection
conn.close()

Output

[('Krishna', 'Sharma', 19, 'M', 2000.0), ('Raj', 'Kandukuri', 20, 'M', 7000.0)]
Advertisements