MySQL server is an open-source relational database management system which is a major support for web-based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. In order to access MySQL databases from a web server(here XAMPP) we use various modules in Python such as PyMySQL, mysql.connector, etc.
In this article, we will see how to get the all rows of a MySQL table by making a database connection between python and MySQL.
First, we are going to connect to a database having a MySQL table. The SQL query to be used to get all the rows:
SELECT * FROM table-name
Finally, after getting all the rows, display each row in the table using an iterator.
Below are some programs which depict how to extract rows from a MySQL table in a Database:
Example 1:
Below is the table Lazyroardemo is database geek which is going to be accessed by a Python script:
Below is the program to get all the rows in an MYSQL table:
Python3
# import required modulesimport pymysqlpymysql.install_as_MySQLdb()import MySQLdb # connect python with mysql with your hostname, # username, password and databasedb= MySQLdb.connect("localhost", "root", "", "GEEK") # get cursor objectcursor= db.cursor() # execute your querycursor.execute("SELECT * FROM Lazyroardemo") # fetch all the matching rows result = cursor.fetchall() # loop through the rowsfor row in result: print(row) print("\n") |
Output:
Example 2:
Here is another example to extracts all the rows from a table in a given database, below is the table scheme and rows:
Below is the Python script extract all each row from the table:
Python3
# import required modulesimport MySQLdbimport pymysqlpymysql.install_as_MySQLdb() # connect python with mysql with your hostname,# username, password and databasedb = MySQLdb.connect("localhost", "root", "", "techLazyroar") # get cursor objectcursor = db.cursor() # execute your querycursor.execute("SELECT * FROM techcompanies") # fetch all the matching rowsresult = cursor.fetchall() # loop through the rowsfor row in result: print(row, '\n') |
Output:
