Python MySQL – Select Query

 Python MySQL – Select Query

Python Database API ( Application Program Interface ) is the Database interface for the standard Python. This standard is adhered to by most Python Database interfaces. There are various Database servers supported by Python Database such as MySQL, GadFly, mySQL, PostgreSQL, Microsoft SQL Server 2000, Informix, Interbase, Oracle, Sybase etc. To connect with MySQL database server from Python, we need to import the mysql.connector module. Below is a program to connect with MySQL database geeks. 

Here is a Python example to perform a SELECT query from a MySQL database using the mysql-connector-python library:

Step 1: Install the Required Library

If you haven't already installed the library, run:

bash
pip install mysql-connector-python

---

Step 2: Example Code for SELECT Query

python
import mysql.connector
from mysql.connector import Error

try:
    # Connect to the MySQL database
    connection = mysql.connector.connect(
        host='your_host',         # e.g., 'localhost' or IP
        user='your_username',     # Your MySQL username
        password='your_password', # Your MySQL password
        database='your_database'  # Database name
    )

    if connection.is_connected():
        print("Connected to MySQL database")

        # Prepare and execute a SELECT query
        cursor = connection.cursor()
        query = "SELECT id, name, age FROM your_table"  # Replace 'your_table' with your table name
        cursor.execute(query)

        # Fetch all rows
        rows = cursor.fetchall()

        # Print each row
        print("Fetched Data:")
        for row in rows:
            print(f"ID: {row[0]}, Name: {row[1]}, Age: {row[2]}")

except Error as e:
    print(f"Error: {e}")
finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection closed")

Explanation:

1. Connection Setup:

   • Use mysql.connector.connect() to connect to your MySQL database.
   • Specify host, user, password, and database parameters.

2. Cursor Execution:

   • Create a cursor object with connection.cursor().
   • Execute the SELECT query using cursor.execute(query).

3. Fetching Data:

   • Use cursor.fetchall() to retrieve all rows from the executed query.
   • Each row is a tuple containing the selected columns.

4. Error Handling:

   • The try-except block handles database connection or execution errors.

5. Cleanup:

   • Close the cursor and connection in the finally block to ensure proper resource management.

Output Example:

Assume the table your_table contains:

id	name	age
1	Alice	25
2	Bob	30

The output will be:

yaml
Connected to MySQL database
Fetched Data:
ID: 1, Name: Alice, Age: 25
ID: 2, Name: Bob, Age: 30
MySQL connection closed

Let's Have A Practical Example

#!C:/Python3.9.1/python.exe

print("content-type: text/html\n\n" )

import mysql.connector

mydb = mysql.connector.connect(

  host="localhost",

  user="root",

  password="",

  database="rcf_entry_system"

)

mycursor = mydb.cursor()

mycursor.execute("SHOW TABLES")

for x in mycursor:

  print(x)

http://localhost:81/python/hi.py  open in browser(WAMP + MYSQL CONNECTOR INSTALLED USING PYTHON THEN WORK)

LET MY END IT WERE

('actual_visit_detail',) ('rcf_visitor_mas',) ('user_master',) ('visit_mas',) ('visitor_matser_person',)

2. LET FETCH DATA FROM rcf_visitor_mas

#!C:/Python3.9.1/python.exe

print("content-type: text/html\n\n" )

import mysql.connector

mydb = mysql.connector.connect(

  host="localhost",

  user="root",

  password="",

  database="rcf_entry_system"

)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM rcf_visitor_mas")

myresult = mycursor.fetchall()

for x in myresult:

  print(x)

http://localhost:81/python/hi.py  open in browser(WAMP + MYSQL CONNECTOR INSTALLED USING PYTHON THEN WORK)

('8567859566', 'akhil', '06012021', 'trtF', ' OFFICE', 'K', 'P', '14470', 2121, 1)

HERE hi,py =my file name u can name as per ur wishes

The cursor.MySQLCursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where,

1. The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones).

2. The fetchone() method fetches the next row in the result of a query and returns it as a tuple.

3. The fetchmany() method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row.

You can retrieve/fetch data from a table in MySQL using the SELECT query. This query/statement returns contents of the specified table in tabular form and it is called as result-set.