python Read Operation

Prev Next

The SELECT Statement The SELECT statement is used to read values from a database. You can restrict the output of a SELECT query using clauses like WHERE, LIMIT, etc.

Fetching All Rows The fetchall() method returns all rows from the result set as a list of tuples. You can iterate over the result to get individual rows.

Example:

import mysql.connector

# Create the connection object
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="google",
    database="PythonDB"
)

# Create the cursor object
cur = conn.cursor()

# Define the SQL query
query = "SELECT * FROM Employee"

# Execute the query
cur.execute(query)

# Fetch all rows
rows = cur.fetchall()

# Print the rows
for row in rows:
    print(row)

# Close the connection
cur.close()
conn.close()

Reading Specific Columns:

You can read specific columns by mentioning their names instead of using *.

Example:

import mysql.connector

# Create the connection object
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="google",
    database="PythonDB"
)

# Create the cursor object
cur = conn.cursor()

# Define the SQL query
query = "SELECT name, id, salary FROM Employee"

# Execute the query
cur.execute(query)

# Fetch all rows
rows = cur.fetchall()

# Print the rows
for row in rows:
    print(f"Name: {row[0]}, ID: {row[1]}, Salary: {row[2]}")

# Close the connection
cur.close()
conn.close()

Using fetchone() Method:

The fetchone() method fetches only one row from the result set. It returns the next row of the result set.

Example:

import mysql.connector

# Create the connection object
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="google",
    database="PythonDB"
)

# Create the cursor object
cur = conn.cursor()

# Define the SQL query
query = "SELECT * FROM Employee"

# Execute the query
cur.execute(query)

# Fetch one row
row = cur.fetchone()

# Print the row
print(row)

# Close the connection
cur.close()
conn.close()

Formatting the Result:

You can format the result by iterating over the rows returned by fetchall() or fetchone() methods. Each result is a tuple, which may not be very readable without formatting.

Example:

import mysql.connector

# Create the connection object
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="google",
    database="PythonDB"
)

# Create the cursor object
cur = conn.cursor()

# Define the SQL query
query = "SELECT * FROM Employee"

# Execute the query
cur.execute(query)

# Fetch all rows
rows = cur.fetchall()

# Print formatted result
for row in rows:
    print(f"ID: {row[1]}, Name: {row[0]}, Salary: {row[2]}, Department ID: {row[3]}")

# Close the connection
cur.close()
conn.close()

Using the WHERE Clause:

You can restrict the result produced by the SELECT statement using the WHERE clause. This will filter rows based on the specified condition.

Example:

import mysql.connector

# Create the connection object
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="google",
    database="PythonDB"
)

# Create the cursor object
cur = conn.cursor()

# Define the SQL query with WHERE clause
query = "SELECT * FROM Employee WHERE salary > 50000"

# Execute the query
cur.execute(query)

# Fetch all rows
rows = cur.fetchall()

# Print the rows
for row in rows:
    print(f"ID: {row[1]}, Name: {row[0]}, Salary: {row[2]}, Department ID: {row[3]}")

# Close the connection
cur.close()
conn.close()

Prev Next