Description
You’ve successfully created a database, and inserted data into a table. Now, how to retrieve this data? And how do we pick certain columns to be displayed? This is where the Python MySQL SELECT FROM command comes in.
The SELECT statement has two modes. The first is a “select all” mode, and the second is more selective kind of approach, that picks only certain columns.
Example
Remember to import the mysql.connector and create a connection to your database as shown below before attempting anything. Follow up by creating a cursor object which we’ll be using to manipulate to the database.
import mysql.connector
db = mysql.connector.connect(
host = "localhost",
user = "root",
password = "*********",
database = "mydatabase"
)
cursor = db.cursor()
cursor.execute("SELECT * FROM student")
results = cursor.fetchall()
print(results)
The * in the SELECT statement means that the all the data in the Table should be selected and returned. To retrieve all these values at once, we use the fetchall method. Lastly, we simply print out the results stored in the results variable.
[('Bob', '16', 'Male'), ('Alice', '15', 'Female'), ('David', '17', 'Male'), ('George', '12', 'Male')]Note, we don’t have to use db.commit() here as we aren’t making changes to the database itself.
Selecting certain Columns
Perhaps you don’t want all the data at once. Especially in bigger databases, scanning through so much data can take a lot of time. If you only need the names of everyone for instance, why not only select the name column.
The column names are separated by a simple comma.
import mysql.connector
db = mysql.connector.connect(
host = "localhost",
user = "root",
password = "*********",
database = "mydatabase"
)
cursor = db.cursor()
cursor.execute("SELECT name, age FROM student")
results = cursor.fetchall()
print(results)
As you can see, we’ve only picked out the Names and Ages of all the students.
[('Bob', '16'), ('Alice', '15'), ('David', '17'), ('George', '12')]Retrieving rows one at a time
You might not want all the data retrieved from the cursor object at once. While you could always iterate over the result of the fetchall() statement, python offers us a better way with fetchone() function.
The fetchone(), as it’s name implies, fetches one row at a time.
import mysql.connector
db = mysql.connector.connect(
host = "localhost",
user = "root",
password = "*********",
database = "mydatabase"
)
cursor = db.cursor()
cursor.execute("SELECT * FROM student")
result = cursor.fetchone()
print(result)
('Bob', '16', 'Male')This marks the end of our Python MySQL SELECT FROM Article. Let us know if you have any suggestions or corrections in this Article to make. Contributions to help grow CodersLegacy are more than welcome.
Use this link to head back to main Python MySQL page: link
