AI

This Python script defines two functions, search_database and main, to search for and display results from a MySQL database:

search_database Function:

• Establishes a connection to a MySQL database using mysql.connector. It attempts to connect with the username 'root', password 'laboni21', host 'localhost', and a database named 'python'.
• In case of a connection error, it catches the exception, prints the error message, and returns None.
• It takes a query parameter, formats it for a SQL LIKE search, and executes a SQL query to select all records from the data table where the name column matches the query pattern.
• Fetches all matching results and closes the database connection before returning these results.

main Function:

• Acts as the user interface, prompting the user to enter a name to search.
• Calls search_database with the user's input and stores the results.
• If results are found, it prints them; if not, it displays a message indicating no results were found.

Script Execution:

• The if __name__ == "__main__": block ensures that main is called when the script is run directly, initiating the program flow.

Overall, the script is a simple command-line tool for searching names in a MySQL database and displaying the search results

import mysql.connector


def search_database(query):
    try:
        conn = mysql.connector.connect(
            user='root',
            password='laboni21',
            host='localhost',
            database='python'
        )
    except mysql.connector.Error as err:
        print("Error connecting to MySQL:", err)
        return None
    cursor = conn.cursor()

    # Execute the search query
    query = f"%{query}%"
    cursor.execute("SELECT * FROM data WHERE name LIKE %s", (query,))

    results = cursor.fetchall()

    # Close the connection
    conn.close()

    return results


# User interface for the search
def main():
    query = input("Enter a name to search: ")
    results = search_database(query)
    if results:
        print("Search Results:")
        for row in results:
            print(row)
    else:
        print("No results found.")


if __name__ == "__main__":
    main()

To create a SQL script for inserting data into a table named data with a column name in MySQL Workbench or any other MySQL interface, you would follow these steps:

Define the Table Structure: First, ensure your table data is created with the appropriate structure. If not already created, you can use the following SQL command to create it:

CREATE TABLE data (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(255) NOT NULL
);

This creates a table with an auto-incrementing id column and a name column to store the names.

Insert Data into the Table: The SQL command to insert data into this table is straightforward. For example, to insert a few names, you would use:

INSERT INTO data (name) VALUES ('Alice'), ('Bob'), ('Charlie');

This command inserts three rows into the data table with the names 'Alice', 'Bob', and 'Charlie'.

Using in MySQL Workbench:

• Open MySQL Workbench and connect to your database.
• Navigate to the query tab where you can write and execute SQL queries.
• First, run the CREATE TABLE command if the table does not exist.
• Then, run the INSERT INTO command to insert the data.

Remember, each time you run the INSERT INTO command, new rows will be added to the table. If you're inserting data as part of testing, you might want to clear the table first using DELETE FROM data; or TRUNCATE TABLE data; (be cautious with these commands as they will remove all data from the table).

Also, ensure that your database user has the necessary permissions to create tables and insert data. If you encounter any permission errors, you may need to adjust your database user's privileges.