Trivendra/Database

1import streamlit as st2import mysql.connector3import pandas as pd4 5# Function to create a MySQL database6def create_database():7    connection = mysql.connector.connect(8        host="host1",9        user="trivi",10        password="password"11    )12 13    cursor = connection.cursor()14    cursor.execute("CREATE DATABASE IF NOT EXISTS database1")15    connection.close()16 17# Function to retrieve data from the MySQL database and convert to DataFrame18def retrieve_data():19    connection = mysql.connector.connect(20        host="host1",21        user="trivi",22        password="password",23        database="database1"24    )25 26    cursor = connection.cursor()27    cursor.execute("SELECT * FROM database1")28    data = cursor.fetchall()29    connection.close()30 31    # Convert data to DataFrame32    columns = [i[0] for i in cursor.description]33    df = pd.DataFrame(data, columns=columns)34 35    return df36 37# Function to insert data into the MySQL database38def insert_data(name, age):39    connection = mysql.connector.connect(40        host="host1",41        user="trivi",42        password="password",43        database="database1"44    )45 46    cursor = connection.cursor()47 48    # Adjust the query based on your table structure49    query = "INSERT INTO database1 (name, age) VALUES (%s, %s)"50    values = (name, age)51 52    cursor.execute(query, values)53    connection.commit()54 55    connection.close()56 57# Create the Streamlit app58def main():59    st.title("MySQL Database in Streamlit")60 61    # Create the database on button click62    if st.button("Create MySQL Database"):63        create_database()64        st.success("MySQL Database created successfully!")65 66    # Retrieve and display data from the database as a DataFrame67    if st.button("Retrieve Data"):68        df = retrieve_data()69        st.write("Data retrieved from MySQL Database:")70        st.write(df)71 72    # Insert data form73    st.header("Insert Data")74    name = st.text_input("Enter Name:")75    age = st.number_input("Enter Age:")76    77    if st.button("Insert Data"):78        insert_data(name, age)79        st.success(f"Data for {name} inserted successfully!")80 81if __name__ == "__main__":82    main()