Select data from Table-MySQL

In MySQL, columns and rows make up the tables. The rows and columns of data records define the fields and records, respectively. To use the data from the tables, it must first be obtained. At times, we might need to retrieve all the information from the MySQL table.

All the rows can be fetched from the table using the SELECT * statement.

Syntax:

SELECT * FROM table_name

The * in the above syntax means to fetch all the rows from the table.

Retrieve All records from MySQL Table:

 Let us retrieve all the data from the  table which we inserted before:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "username",
    passwd = "password",
    database = "Mydatabase"
)

cursor=db.cursor()

query="SELECT * FROM MyTable"
cursor.execute(query)
for row in cursor:
   print(row)

Retrieve data from specific Column(s) of a Table:

In order to select data from some columns of the table just mention the column name after the SELECT in the syntax mentioned above:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "username",
    passwd = "password",
    database = "Mydatabase"
)

cursor=db.cursor()

query="SELECT name FROM MyTable"
cursor.execute(query)
for row in cursor:
   print(row)

The above code will fetch the name column from the MyTable table.

Selecting Multiple columns from a Table:

By including several column names in the syntax above, you can also choose several columns from a table at once. Let’s look at the provided code example for clarity:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "username",
    passwd = "password",
    database = "Mydatabase"
)

cursor=db.cursor()

query="SELECT name,roll FROM MyTable"
cursor.execute(query)
for row in cursor:
   print(row)

The above code will fetch the name, roll column from the MyTable table.

To fetch the first record – fetchone():

Because the fetchall() function was used in the cases above, all rows were fetched. The fetchone() method will now be used to fetch only a single row. The first row from the records the query fetched will be returned by this function. Let’s look at the following line of code:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "username",
    passwd = "password",
    database = "Mydatabase"
)

cursor = db.cursor()
cursor.execute("SELECT * FROM MyTable")

myresult = cursor.fetchone() ##fetches first row of the record
s
   print(myresult)

Thus in the output the first row of the record will be fetched.

Note: also read about Insert data in Table-MySQL

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py