- How to select all the data from a table using MySQL in Python?
- Syntax
- Steps you need to follow to select all the data from a table using MySQL in python
- Example
- Output
- Python MySQL Select From
- Example
- Selecting Columns
- Example
- Using the fetchone() Method
- Example
- COLOR PICKER
- Report Error
- Thank You For Helping Us!
How to select all the data from a table using MySQL in Python?
The tables in MySQL consist of rows and columns. The columns specify the fields and the rows specify the records of data. The data in the tables needs to be fetched to use. We may at times need to fetch all the data from the MySQL table.
All the rows can be fetched from the table using the SELECT * statement.
Syntax
The * in the above syntax means to fetch all the rows from the table.
Steps you need to follow to select all the data from a table using MySQL in python
- import MySQL connector
- establish connection with the connector using connect()
- create the cursor object using cursor() method
- create a query using the appropriate mysql statements
- execute the SQL query using execute() method
- close the connection
Suppose we have a table named ‘MyTable’ and we want to fetch all the data from this table.
+----------+---------+-----------+------------+ | Name | Class | City | Marks | +----------+---------+-----------+------------+ | Karan | 4 | Amritsar | 95 | | Sahil | 6 | Amritsar | 93 | | Kriti | 3 | Batala | 88 | | Khushi | 9 | Delhi | 90 | | Kirat | 5 | Delhi | 85 | +----------+---------+-----------+------------+
Example
import mysql.connector db=mysql.connector.connect(host="your host", user="your username", password="your password",database="database_name") cursor=db.cursor() query="SELECT * FROM MyTable" cursor.execute(query) for row in cursor: print(row)
The above code execute the select * query which fetches all the rows from the table. Later , we print all the rows using for statement.
Output
(‘Karan’, 4, ‘Amritsar’ , 95) (‘Sahil’ , 6, ‘Amritsar’ ,93) (‘Kriti’ , 3 ‘Batala’ , 88) (‘Khushi’ , 9, ‘Delhi’ , 90) (‘Kirat’ , 5, ‘Delhi’ ,85)
Python MySQL Select From
To select from a table in MySQL, use the «SELECT» statement:
Example
Select all records from the «customers» table, and display the result:
mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)
mycursor.execute(«SELECT * FROM customers»)
Note: We use the fetchall() method, which fetches all rows from the last executed statement.
Selecting Columns
To select only some of the columns in a table, use the «SELECT» statement followed by the column name(s):
Example
Select only the name and address columns:
mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)
mycursor.execute(«SELECT name, address FROM customers»)
Using the fetchone() Method
If you are only interested in one row, you can use the fetchone() method.
The fetchone() method will return the first row of the result:
Example
mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)
mycursor.execute(«SELECT * FROM customers»)
COLOR PICKER
Report Error
If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:
Thank You For Helping Us!
Your message has been sent to W3Schools.
Top Tutorials
Top References
Top Examples
Get Certified
W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookie and privacy policy.