
Python MySQL Tutorial – How to SELECT Data from a Table Using Python
Last updated 2 weeks, 1 day ago | 33 views 75 5

Retrieving data from a MySQL table is one of the most essential tasks when working with databases. In this step-by-step tutorial, you’ll learn how to fetch records from a MySQL table using Python, with practical examples, explanations, and a complete working script.
Table of Contents
-
Prerequisites
-
Install MySQL Connector
-
Connect to a MySQL Database
-
SELECT All Rows
-
SELECT Specific Columns
-
Use WHERE Clause
-
Fetch One or All Results
-
Loop Through Results
-
Full Working Example
-
Common Pitfalls and Tips
✅ 1. Prerequisites
Make sure you have the following:
-
Python installed
-
MySQL server running
-
A MySQL database and table with data
-
The
mysql-connector-python
library installed
2. Install MySQL Connector
Install the official MySQL connector package with pip:
pip install mysql-connector-python
3. Connect to a MySQL Database
Start by connecting to your MySQL database:
import mysql.connector
db = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="mydatabase"
)
cursor = db.cursor()
Replace
your_username
,your_password
, andmydatabase
with your own credentials.
4. SELECT All Rows
Use SELECT * FROM table_name
to retrieve all rows from a table.
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
print(row)
5. SELECT Specific Columns
Select only certain fields (columns) for better performance:
cursor.execute("SELECT name, email FROM users")
results = cursor.fetchall()
for name, email in results:
print(f"Name: {name}, Email: {email}")
6. Use WHERE Clause to Filter
Use the WHERE
clause to filter your data:
sql = "SELECT * FROM users WHERE age > %s"
val = (25,)
cursor.execute(sql, val)
results = cursor.fetchall()
for row in results:
print(row)
Use
%s
placeholders to prevent SQL injection.
7. Fetch One or All Results
-
fetchone()
returns the first row of the result. -
fetchall()
returns all rows as a list.
cursor.execute("SELECT * FROM users")
first_row = cursor.fetchone()
print("First Row:", first_row)
8. Loop Through Results
You can loop through the results easily using a for
loop:
cursor.execute("SELECT * FROM users")
for (id, name, email, age) in cursor:
print(f"{id}: {name} ({email}), Age: {age}")
9. Full Working Example
import mysql.connector
from mysql.connector import Error
def select_users():
try:
db = mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="mydatabase"
)
cursor = db.cursor()
# Select users older than 25
sql = "SELECT id, name, email FROM users WHERE age > %s"
val = (25,)
cursor.execute(sql, val)
results = cursor.fetchall()
print("Users older than 25:")
for row in results:
print(row)
except Error as e:
print("Error:", e)
finally:
if db.is_connected():
db.close()
print("Database connection closed.")
# Run the function
select_users()
⚠️ 10. Common Pitfalls and Tips
Issue | Solution |
---|---|
fetchone() returns None |
No results matched the query |
SQL error | Check for typos in SQL syntax |
Incorrect number of placeholders | Make sure the tuple length matches placeholders |
Data not retrieved | Ensure the table has data before selecting |
✅ Tips
-
Always use parameterized queries (with
%s
) to protect against SQL injection. -
Use
LIMIT
in your queries to restrict large result sets. -
Don’t forget to close the database connection in a
finally
block. -
Fetch only what you need to optimize performance.
Summary Table
Task | Method |
---|---|
Select all rows | SELECT * FROM users |
Select specific columns | SELECT name, email FROM users |
Filter results | SELECT * FROM users WHERE age > %s |
Fetch all rows | cursor.fetchall() |
Fetch one row | cursor.fetchone() |
Final Thoughts
Using Python to select data from a MySQL database is efficient and scalable with the help of the mysql-connector-python
library. Whether you're fetching all rows, filtering with conditions, or selecting specific columns, Python makes it easy to interact with your data securely and efficiently.
Tips and Tricks
What is pass in Python?
Python | Pass Statement
The pass statement is used as a placeholder for future code. It represents a null operation in Python. It is generally used for the purpose of filling up empty blocks of code which may execute during runtime but has yet to be written.
def myfunction():
pass
How can you generate random numbers?
Python | Generate random numbers
Python provides a module called random using which we can generate random numbers. e.g: print(random.random())
We have to import a random module and call the random() method as shown below:
import random
print(random.random())
The random() method generates float values lying between 0 and 1 randomly.
To generate customized random numbers between specified ranges, we can use the randrange() method
Syntax: randrange(beginning, end, step)
import random
print(random.randrange(5,100,2))
What is lambda in Python?
Python | Lambda function
A lambda function is a small anonymous function. This function can have any number of parameters but, can have just one statement.
Syntex:
lambda arguments : expression
a = lambda x,y : x+y
print(a(5, 6))
It also provides a nice way to write closures. With that power, you can do things like this.
def adder(x):
return lambda y: x + y
add5 = adder(5)
add5(1) #6
As you can see from the snippet of Python, the function adder takes in an argument x and returns an anonymous function, or lambda, that takes another argument y. That anonymous function allows you to create functions from functions. This is a simple example, but it should convey the power lambdas and closures have.
What is swapcase() function in the Python?
Python | swapcase() Function
It is a string's function that converts all uppercase characters into lowercase and vice versa. It automatically ignores all the non-alphabetic characters.
string = "IT IS IN LOWERCASE."
print(string.swapcase())
How to remove whitespaces from a string in Python?
Python | strip() Function | Remove whitespaces from a string
To remove the whitespaces and trailing spaces from the string, Python provides a strip([str]) built-in function. This function returns a copy of the string after removing whitespaces if present. Otherwise returns the original string.
string = " Python "
print(string.strip())
What is the usage of enumerate() function in Python?
Python | enumerate() Function
The enumerate() function is used to iterate through the sequence and retrieve the index position and its corresponding value at the same time.
lst = ["A","B","C"]
print (list(enumerate(lst)))
#[(0, 'A'), (1, 'B'), (2, 'C')]
Can you explain the filter(), map(), and reduce() functions?
Python | filter(), map(), and reduce() Functions
- filter() function accepts two arguments, a function and an iterable, where each element of the iterable is filtered through the function to test if the item is accepted or not.
>>> set(filter(lambda x:x>4, range(7))) # {5, 6}
-
map() function calls the specified function for each item of an iterable and returns a list of result
>>> set(map(lambda x:x**3, range(7))) # {0, 1, 64, 8, 216, 27, 125}
-
reduce() function reduces a sequence pair-wise, repeatedly until we arrive at a single value..
>>> reduce(lambda x,y:y-x, [1,2,3,4,5]) # 3
Let’s understand this:
2-1=1
3-1=2
4-2=2
5-2=3Hence, 3.
What is a namedtuple?
Python | namedtuple
A namedtuple will let us access a tuple’s elements using a name/label. We use the function namedtuple() for this, and import it from collections.
>>> from collections import namedtuple
#format
>>> result=namedtuple('result','Physics Chemistry Maths')
#declaring the tuple
>>> Chris=result(Physics=86,Chemistry=92,Maths=80)
>>> Chris.Chemistry
# 92
Write a code to add the values of same keys in two different dictionaries and return a new dictionary.
We can use the Counter method from the collections module
from collections import Counter
dict1 = {'a': 5, 'b': 3, 'c': 2}
dict2 = {'a': 2, 'b': 4, 'c': 3}
new_dict = Counter(dict1) + Counter(dict2)
print(new_dict)
# Print: Counter({'a': 7, 'b': 7, 'c': 5})
Python In-place swapping of two numbers
Python | In-place swapping of two numbers
>>> a, b = 10, 20
>>> print(a, b)
10 20
>>> a, b = b, a
>>> print(a, b)
20 10
Reversing a String in Python
Python | Reversing a String
>>> x = 'PythonWorld'
>>> print(x[: : -1])
dlroWnohtyP
Python join all items of a list to convert into a single string
Python | Join all items of a list to convert into a single string
>>> x = ["Python", "Online", "Training"]
>>> print(" ".join(x))
Python Online Training
python return multiple values from functions
Python | Return multiple values from functions
>>> def A():
return 2, 3, 4
>>> a, b, c = A()
>>> print(a, b, c)
2 3 4
Python Print String N times
Python | Print String N times
>>> s = 'Python'
>>> n = 5
>>> print(s * n)
PythonPythonPythonPythonPython
Python check the memory usage of an object
Python | Check the memory usage of an object
>>> import sys
>>> x = 100
>>> print(sys.getsizeof(x))
28