Python MySQL Select

In this Python MySQL Select tutorial, we are going to learn all about how to select all the records from the table using the Python MySQL connector.
In the previous tutorial, we have seen a complete process to insert new records into the database.

Make sure you have already installed MySQL connector in your machine using pip as well as create database and table.

Headings of Contents

1 Python MySQL Select
- 1.1 Example:
2 SELECT Columns:
- 2.1 Example:
3 Select Only first row
- 3.1 Example:
4 Use LIMIT statement:
5 Conclusion

To select all the records from the database table, MySQL provide “SELECT” statement.

Example:

Here we will select all the records from the table named “students“.

import mysql.connector
mydb = mysql.connector.connect(

	host="localhost",
	user="root",
	password="root21",
	database = "demodb",
    port = 3308
	
)
cursor = mydb.cursor()
cursor.execute('SELECT * FROM students')
all_students = cursor.fetchall()
for record in all_students:
	print(record)

Output

(1, 'Vishvajit', 12)
(2, 'John', 22)
(3, 'Alex', 32)
(4, 'Peter', 12)
(5, 'Alex Peter', 12)
(6, 'Alsena', 45)
(7, 'Jaini', 56)

SELECT Columns:

Here we will select some specific columns from the table named “students“.

Example:

import mysql.connector
mydb = mysql.connector.connect(

	host="localhost",
	user="root",
	password="root21",
	database = "demodb",
    port = 3308
	
)
cursor = mydb.cursor()
cursor.execute('SELECT id, name FROM students')
all_students = cursor.fetchall()
for record in all_students:
	print(record)

Output:

(1, 'Vishvajit')
(2, 'John')
(3, 'Alex')
(4, 'Peter')
(5, 'Alex Peter')
(6, 'Alsena')
(7, 'Jaini')

Note:- Above credentials may be different according to your MySQL configuration. I am showing all the credentials for only testing, It will not working in your case.

Select Only first row

To select only first row from the table, use fetchone() method.

Example:

import mysql.connector
mydb = mysql.connector.connect(

	host="localhost",
	user="root",
	password="root21",
	database = "demodb",
    port = 3308
	
)
cursor = mydb.cursor()
cursor.execute('SELECT id, name FROM students')
student = cursor.fetchone()
print(student)

Output will be:- (1, ‘Vishvajit’)

Use LIMIT statement:

When you want to select first five records, Then you can use LIMIT keyword.

import mysql.connector
mydb = mysql.connector.connect(

	host="localhost",
	user="root",
	password="root21",
	database = "demodb",
    port = 3308
	
)
cursor = mydb.cursor()
cursor.execute('SELECT * FROM students LIMIT 5')
all_students = cursor.fetchall()
for record in all_students:
	print(record)

Output:

(1, 'Vishvajit', 12)
(2, 'John', 22)
(3, 'Alex', 32)
(4, 'Peter', 12)
(5, 'Alex Peter', 12)

Conclusion

In this Python MySQL Select, you have learned all about how to select records from the database table.
Python MySQL connector is the best for connecting Python applications to the MySQL database and executes SQL queries. After created of database successfully, you can perform a query on that particular database.

If this article for you, please share and keep visiting for further Python MySQL database tutorials.

More Tutorials