Spaces:
Sleeping
Sleeping
| import streamlit as st | |
| import sqlite3 | |
| import pandas as pd | |
| def insert_record(table_name): | |
| """ | |
| Inserts a new record into the specified table in the LGD database. | |
| Parameters: | |
| - table_name (str): The name of the table to insert the record into. | |
| Returns: | |
| - None | |
| """ | |
| st.header("Insert Record") | |
| # Connect to the SQLite database | |
| conn = sqlite3.connect("lgd_database.db") | |
| cursor = conn.cursor() | |
| # Retrieve the first 5 rows from the table | |
| select_query = f"SELECT * FROM {table_name}" | |
| cursor.execute(select_query) | |
| rows = cursor.fetchall() | |
| # Convert the rows to a DataFrame | |
| columns = [description[0] for description in cursor.description] | |
| df = pd.DataFrame(rows, columns=columns) | |
| df = df.iloc[:, :-1] | |
| # Display the retrieved rows in a table | |
| st.subheader("Existing Records") | |
| st.write(df) | |
| # Set default values for level_name and level_code based on table_name | |
| if table_name == "states": | |
| level_name = "State" | |
| level_code = 1 | |
| elif table_name == "district": | |
| level_name = "District" | |
| level_code = 2 | |
| elif table_name == "sub_district": | |
| level_name = "Sub-district" | |
| level_code = 3 | |
| elif table_name == "block": | |
| level_name = "Block" | |
| level_code = 4 | |
| elif table_name == "gp": | |
| level_name = "Gram Panchayats" | |
| level_code = 5 | |
| else: | |
| level_name = "" | |
| level_code = 0 | |
| # Input fields for entityLGDCode and levelCode | |
| entity_lgd_code = st.number_input("Entity LGD Code", min_value=0, step=1) | |
| # Input field for entityName | |
| entity_name = st.text_input("Entity Name") | |
| # Input field for entityNameVariants | |
| entity_name_variants = st.text_input("Entity Name Variants") | |
| # Input field for entityParent | |
| entity_parent = st.number_input("Entity Parent", min_value=0, step=1) | |
| if level_name and level_code: | |
| st.text(f"Level Name: {level_name}") | |
| st.text(f"Level Code: {level_code}") | |
| else: | |
| # Input fields for levelName and levelCode | |
| level_name = st.text_input("Level Name") | |
| level_code = st.number_input("Level Code", min_value=0, step=1) | |
| # Insert button | |
| if st.button("Insert"): | |
| # Perform validation checks before inserting the record | |
| errors = [] | |
| if entity_lgd_code == 0: | |
| errors.append("Entity LGD Code cannot be zero.") | |
| if level_code == 0: | |
| errors.append("Level Code cannot be zero.") | |
| if not entity_name or not isinstance(entity_name, str): | |
| errors.append("Entity Name is required and must be a text.") | |
| if not level_name or not isinstance(level_name, str): | |
| errors.append("Level Name is required and must be a text.") | |
| if not entity_name_variants or not isinstance(entity_name_variants, str): | |
| errors.append("Entity Name Variants is required and must be a text.") | |
| if not entity_parent or not isinstance(entity_parent, int): | |
| errors.append("Entity Parent is required and must be an integer.") | |
| if errors: | |
| st.error("\n".join(errors)) | |
| else: | |
| # Connect to the SQLite database | |
| conn = sqlite3.connect("lgd_database.db") | |
| cursor = conn.cursor() | |
| # Check if the entityLGDCode already exists in the table | |
| select_query = f"SELECT entityLGDCode FROM {table_name} WHERE entityLGDCode = ?" | |
| cursor.execute(select_query, (entity_lgd_code,)) | |
| existing_code = cursor.fetchone() | |
| if existing_code: | |
| st.error("Entity LGD Code already exists in the table.") | |
| else: | |
| # Prepare the SQL query for creating the table if it doesn't exist | |
| create_table_query = f""" | |
| CREATE TABLE IF NOT EXISTS {table_name} ( | |
| entityLGDCode INTEGER PRIMARY KEY, | |
| entityName TEXT, | |
| levelCode INTEGER, | |
| levelName TEXT, | |
| entityNameVariants TEXT, | |
| entityParent INTEGER | |
| ) | |
| """ | |
| cursor.execute(create_table_query) | |
| # Prepare the SQL query for inserting the record | |
| insert_query = f""" | |
| INSERT INTO {table_name} (entityLGDCode, entityName, levelCode, levelName, entityNameVariants, entityParent) | |
| VALUES (?, ?, ?, ?, ?, ?) | |
| """ | |
| values = (entity_lgd_code, entity_name, level_code, level_name, entity_name_variants, entity_parent) | |
| try: | |
| # Execute the SQL query | |
| cursor.execute(insert_query, values) | |
| conn.commit() | |
| st.success("Record inserted successfully!") | |
| except sqlite3.Error as e: | |
| st.error("An error occurred while inserting the record: {}".format(e)) | |
| conn.rollback() | |
| finally: | |
| # Close the database connection | |
| cursor.close() | |
| conn.close() | |
| # Reset the input fields | |
| entity_lgd_code = 0 | |
| level_code = 0 | |
| entity_name = "" | |
| level_name = "" | |
| entity_name_variants = "" | |
| entity_parent = 0 | |
| def update_record(table_name): | |
| """ | |
| Update a record in a SQLite database table. | |
| :param table_name: The name of the table to update the record in. | |
| :type table_name: str | |
| :return: None | |
| :rtype: None | |
| """ | |
| st.header("Update Record") | |
| # Input field for entityLGDCode | |
| entity_lgd_code = st.number_input("Entity LGD Code", min_value=0, step=1) | |
| # Input field for entityName | |
| entity_name = st.text_input("Entity Name") | |
| # Input field for entityNameVariants | |
| entity_name_variants = st.text_input("Entity Name Variants") | |
| # Input field for entityParent | |
| entity_parent = st.number_input("Entity Parent", min_value=0, step=1) | |
| # Update button | |
| if st.button("Update"): | |
| # Perform validation checks before updating the record | |
| errors = [] | |
| if entity_lgd_code == 0: | |
| errors.append("Entity LGD Code cannot be zero.") | |
| if not entity_name or not isinstance(entity_name, str): | |
| errors.append("Entity Name is required and must be a text.") | |
| if not entity_name_variants or not isinstance(entity_name_variants, str): | |
| errors.append("Entity Name Variants is required and must be a text.") | |
| if not entity_parent or not isinstance(entity_parent, int): | |
| errors.append("Entity Parent is required and must be an integer.") | |
| if errors: | |
| st.error("\n".join(errors)) | |
| else: | |
| # Connect to the SQLite database | |
| conn = sqlite3.connect("lgd_database.db") | |
| cursor = conn.cursor() | |
| # Prepare the SQL query for updating the record | |
| update_query = f""" | |
| UPDATE {table_name} | |
| SET entityName = ?, entityNameVariants = ?, entityParent = ? | |
| WHERE entityLGDCode = ? | |
| """ | |
| values = (entity_name, entity_name_variants, entity_parent, entity_lgd_code) | |
| try: | |
| # Execute the SQL query | |
| cursor.execute(update_query, values) | |
| conn.commit() | |
| st.success("Record updated successfully!") | |
| except sqlite3.Error as e: | |
| st.error("An error occurred while updating the record: {}".format(e)) | |
| conn.rollback() | |
| finally: | |
| # Close the database connection | |
| cursor.close() | |
| conn.close() | |
| # Reset the input fields | |
| entity_lgd_code = 0 | |
| entity_name = "" | |
| entity_name_variants = "" | |
| entity_parent = 0 | |
| def delete_record(table_name): | |
| """ | |
| Deletes a record from the specified table in an SQLite database based on the entityLGDCode. | |
| :param table_name: Name of the table to delete the record from. | |
| :type table_name: str | |
| :return: None | |
| :rtype: None | |
| """ | |
| st.header("Delete Record") | |
| # Input field for entityLGDCode | |
| entity_lgd_code = st.number_input("Entity LGD Code", min_value=0, step=1) | |
| # Delete button | |
| if st.button("Delete"): | |
| # Connect to the SQLite database | |
| conn = sqlite3.connect("lgd_database.db") | |
| cursor = conn.cursor() | |
| # Prepare the SQL query for deleting the record | |
| delete_query = f""" | |
| DELETE FROM {table_name} | |
| WHERE entityLGDCode = ? | |
| """ | |
| values = (entity_lgd_code,) | |
| try: | |
| # Execute the SQL query | |
| cursor.execute(delete_query, values) | |
| conn.commit() | |
| st.success("Record deleted successfully!") | |
| except sqlite3.Error as e: | |
| st.error("An error occurred while deleting the record: {}".format(e)) | |
| conn.rollback() | |
| finally: | |
| # Close the database connection | |
| cursor.close() | |
| conn.close() | |
| # Reset the input fields | |
| entity_lgd_code = 0 | |
| """ if __name__ == "__main__": | |
| table_name = "block" # Provide the table name here | |
| # Usage examples | |
| update_record(table_name) | |
| delete_record(table) """ | |