from flask import request, jsonify, render_template, request,flash,redirect,url_for,session,send_file import io import hashlib from sqlalchemy import create_engine, MetaData, Table,insert, Column,Integer, Float, DateTime, Boolean,String,inspect,select from app.models.model import User,DBData,db,data,data_error from werkzeug.security import generate_password_hash from werkzeug.utils import secure_filename import logging import os import pandas as pd from datetime import datetime import numpy as np import json from sqlalchemy.exc import NoSuchTableError,IntegrityError,SQLAlchemyError from pandas.api.types import infer_dtype import re from dateutil.parser import parse import mysql.connector def register(): return render_template('register.html') def add_user(): return render_template('create_user.html') def new_create(): username = request.form.get('username') email = request.form.get('email') password = request.form.get('password') hashed_password = generate_password_hash(password) created_by = session.get('id') add_users(username, email, hashed_password, created_by) flash('User created successfully') return redirect(url_for('auth_blueprint.login')) def create(): username = request.form.get('username') role = request.form.get('role').lower() email = request.form.get('email') password = request.form.get('password') hashed_password = generate_password_hash(password) created_by = session.get('id') add_users(username, role, email, hashed_password, created_by) flash('User added successfully') return redirect(url_for('auth_blueprint.users_display')) def add_users(username, role, email, hashed_password, created_by): user = User(username=username, role=role, email=email, password=hashed_password, created_by=created_by) try: db.session.add(user) db.session.commit() except Exception as e: db.session.rollback() #def add_db(): # return render_template('add_db.html') # #def create_db(): # connection_name=request.form.get('connection_name') # hostname=request.form.get('hostname') # port=request.form.get('port') # username=request.form.get('username') # schemaname=request.form.get('schemaname') # password=request.form.get('password') # tablename=request.form.get('tablename') # created_by=session['id'] # status=1 # print('dddddddddddd') # return insert_db(connection_name,hostname,port,username,password,schemaname,tablename,created_by,status) #def insert_db(connection_name,hostname,port,username,schemaname,password,tablename,created_by,status): # print() # Db=DBData(connection_name=connection_name,hostname=hostname,port=port,username=username,schemaname=schemaname,password=password,tablename=tablename,created_by=created_by,status=status) # db.session.add(Db) # db.session.commit() # flash(' Data Source added successfully') # return redirect(url_for('auth_blueprint.data_display')) def edit_user(user_id): # Handle GET request to display the edit page if request.method == 'GET': user = User.query.get(user_id) return render_template('edit_user.html', user=user) # Handle POST request to update the user data elif request.method == 'POST': user = User.query.get(user_id) if user is None: return "User not found!" username = request.form.get('username') email = request.form.get('email') user.username = username user.email = email db.session.commit() flash('User edited successfully') return redirect(url_for('auth_blueprint.users_display')) def edit_db(user_id): # Handle GET request to display the edit page if request.method == 'GET': db_data = DBData.query.get(user_id) return render_template('edit_db.html', db_data=db_data) # Handle POST request to update the user data elif request.method == 'POST': db_data = DBData.query.get(user_id) if db_data is None: return "User not found!" connection_name=request.form.get('connection_name') hostname=request.form.get('hostname') port=request.form.get('port') username=request.form.get('username') password=request.form.get('password') schemaname=request.form.get('schemaname') tablename=request.form.get('tablename') db_data.connection_name = connection_name db_data.hostname = hostname db_data.port = port db_data.username = username db_data.password = password db_data.schemaname = schemaname db_data.tablename = tablename db.session.commit() flash(' Data Source edited successfully') return redirect(url_for('auth_blueprint.data_display')) def upload_file(): connection_name = DBData.query.with_entities(DBData.connection_name).all() print(connection_name) return render_template('upload.html',connection_name=connection_name) def save_file_user(): print('inside save_file_user ') file = request.files['excelFile'] uploaded_by = session['id'] original_file = file.filename file.save(original_file) print('jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj') # MySQL database connection configuration config = { 'user': 'root', 'password': '', 'host': '127.0.0.1', 'port': 3306, 'database': 'data_uploader', 'raise_on_warnings': True, } # Connect to the MySQL database connection = mysql.connector.connect(**config) cursor = connection.cursor() # Read the Excel file into a DataFrame path = original_file print('file_path:', path) df = pd.read_excel(path) # Get the number of rows num_rows = df.shape[0] # Create a new data object and associate it with the user uploaded_file = data(file_path=original_file, uploaded_by=uploaded_by,inserted_rows=num_rows) db.session.add(uploaded_file) db.session.commit() uploaded_file_id = uploaded_file.id # Add additional columns to the DataFrame df['added_by'] = uploaded_by df['file_id'] = uploaded_file_id df.columns = df.columns.str.strip() # Create a logger to log the errors logging.basicConfig(filename='data_insertion_errors.log', level=logging.ERROR) # Loop through the rows and insert data into the MySQL table for _, row in df.iterrows(): # Prepare a list to hold values of existing columns in the DataFrame values = [] # List of columns you want to extract data from columns_to_extract = [ 'Airline_Code', 'Airline', 'Airline_PNR', 'First_Name', 'Last_Name', 'E-mail', 'Origin', 'Destination', 'Amount', 'Refund_Amount', 'Refund_Status', 'Booking_Status', 'Fare_Analyst', 'Fare_amt', 'all_Taxes' ] print('kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk') for col in columns_to_extract: if col in row.index: # If the column exists, append its value to the 'values' list value = row[col] # Convert NaN values to None value = None if pd.isna(value) else value values.append(value) else: # If the column does not exist, append None to the 'values' list values.append(None) updated_by=None # Append 'uploaded_by' and 'uploaded_file_id' to the 'values' list values.extend([uploaded_by, uploaded_file_id,updated_by]) print(values) # Prepare the SQL query for insertion sql = f"INSERT INTO ticket_data (Airline_Code,Airline,Airline_PNR, First_Name, Last_Name, `E-mail`, Origin, Destination, Amount, Refund_Amount, Refund_Status, Booking_Status, Fare_Analyst, Fare_amt, all_Taxes, added_by, file_id,updated_by) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # print(sql,values) try: cursor.execute(sql, values) except mysql.connector.IntegrityError as e: # Check if the error code corresponds to a duplicate entry if e.errno == 1062: connection.rollback() # Roll back changes if an error occurs # Commit the changes and close the connection connection.commit() cursor.close() connection.close() return 'Inserted Sucessfully' def update_file_ckecker(): print('llllllllllllllllllllllllllllllllll') updated_by = session['id'] file = request.files['excelFile'] # uploaded_by = session['id'] original_file = file.filename file.save(original_file) print('mmmmmmmmmmmmmmmmm') # MySQL database connection configuration config = { 'user': 'root', 'password': '', 'host': '127.0.0.1', 'port': 3306, 'database': 'data_uploader', 'raise_on_warnings': True, } # Connect to the MySQL database connection = mysql.connector.connect(**config) cursor = connection.cursor() # Read the Excel file into a DataFrame df = pd.read_excel(original_file) df.columns = df.columns.str.strip() # Prepare the update query update_sql = ( "UPDATE ticket_data SET " "Airline_Code = %s, " "Airline = %s, " "Airline_PNR = %s, " "First_Name = %s, " "Last_Name = %s, " "`E-mail` = %s, " "Origin = %s, " "Destination = %s, " "Amount = %s, " "Refund_Amount = %s, " "Refund_Status = %s, " "Booking_Status = %s, " "Fare_Analyst = %s, " "Fare_amt = %s, " "all_Taxes = %s, " "added_by = %s, " "file_id = %s, " "updated_by = %s " "WHERE Airline_PNR = %s AND First_Name = %s AND Last_Name = %s AND Origin = %s AND Amount = %s" ) print('update_sql',update_sql) for _, row in df.iterrows(): print('inide for') # Prepare a list to hold values of existing columns in the DataFrame values = [] columns_to_extract = [ 'Airline_Code', 'Airline', 'Airline_PNR', 'First_Name', 'Last_Name', 'E-mail', 'Origin', 'Destination', 'Amount', 'Refund_Amount', 'Refund_Status', 'Booking_Status', 'Fare_Analyst', 'Fare_amt', 'all_Taxes','added_by','file_id' ] for col in columns_to_extract: if col in row.index: value = row[col] print("value:",value) # Convert NaN values to None value = None if pd.isna(value) else value values.append(value) else: values.append(None) print('in mid') try: # print('trying') update_values = ( values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], values[9], values[10], values[11], values[12], values[13], values[14], values[15],values[16], updated_by, values[2], values[3], values[4], values[6], values[8] ) try: # print('update_values', update_values) cursor.execute(update_sql, update_values) connection.commit() logging.info("Update successful") except Exception as e: connection.rollback() # Roll back the transaction in case of an error logging.error("Error during update: %s" % str(e)) except Exception as update_error: # print('update_values',update_values) connection.rollback() # Roll back changes if an error occurs logging.error(f"Update Error: {update_error}") file_id = values[16] print('file_id:', file_id) query = "SELECT COUNT(*) FROM ticket_data WHERE file_id = %s AND updated_by IS NOT NULL" print('Executing query:', query) cursor.execute(query, (file_id,)) updated_rows = cursor.fetchone()[0] print('updated_rows:', updated_rows) try: data_entry = data.query.filter_by(id=file_id).first() if data_entry: data_entry.updated_rows = updated_rows db.session.commit() print("Record updated successfully.") else: print("No record found for the given file_id.") except SQLAlchemyError as e: print('SQLAlchemy Error:', e) connection.commit() cursor.close() connection.close() flash("Data updated successfully.") return redirect(url_for('auth_blueprint.file_display')) # def save_file(): # file = request.files['excelFile'] # uploaded_by = session['id'] # original_file = file.filename # file.save(original_file) # # Create a new data object and associate it with the user # uploaded_file = data(file_path=original_file, uploaded_by=uploaded_by) # db.session.add(uploaded_file) # db.session.commit() # uploaded_file_id = uploaded_file.id # # MySQL database connection configuration # config = { # 'user': 'root', # 'password': '', # 'host': '127.0.0.1', # 'port': 3306, # 'database': 'data_uploader', # 'raise_on_warnings': True, # } # # Connect to the MySQL database # connection = mysql.connector.connect(**config) # cursor = connection.cursor() # # Read the Excel file into a DataFrame # path = original_file # print('file_path:', path) # df = pd.read_excel(path) # # Add additional columns to the DataFrame # df['added_by'] = uploaded_by # df['file_id'] = uploaded_file_id # df.columns = df.columns.str.strip() # # Create a logger to log the errors # logging.basicConfig(filename='data_insertion_errors.log', level=logging.ERROR) # # Loop through the rows and insert data into the MySQL table # for _, row in df.iterrows(): # # Prepare a list to hold values of existing columns in the DataFrame # values = [] # # List of columns you want to extract data from # columns_to_extract = [ # 'Airline_Code', # 'Airline', # 'Airline_PNR', # 'First_Name', # 'Last_Name', # 'E-mail', # 'Origin', # 'Destination', # 'Amount', # 'Refund_Amount', # 'Refund_Status', # 'Booking_Status', # 'Fare_Analyst', # 'Fare_amt', # 'all_Taxes', # ] # for col in columns_to_extract: # if col in row.index: # # If the column exists, append its value to the 'values' list # value = row[col] # # Convert NaN values to None # value = None if pd.isna(value) else value # values.append(value) # else: # # If the column does not exist, append None to the 'values' list # values.append(None) # updated_by=None # # Append 'uploaded_by' and 'uploaded_file_id' to the 'values' list # values.extend([uploaded_by, uploaded_file_id,updated_by]) # # Prepare the SQL query for insertion # sql = f"INSERT INTO ticket_data (Airline_Code,Airline,Airline_PNR, First_Name, Last_Name, `E-mail`, Origin, Destination, Amount, Refund_Amount, Refund_Status, Booking_Status, Fare_Analyst, Fare_amt, all_Taxes, added_by, file_id,updated_by) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # # print(sql,values) # try: # cursor.execute(sql, values) # except mysql.connector.IntegrityError as e: # # Check if the error code corresponds to a duplicate entry # if e.errno == 1062: # Error code for duplicate entry # updated_by = uploaded_by # error_message = str(e) # # Extract the duplicate values from the error message # start_index = error_message.find("'") + 1 # Find the index of the first single quote # end_index = error_message.find("'", start_index) # Find the index of the second single quote # duplicate_values = error_message[start_index:end_index] # # Split the duplicate values using the '-' separator # duplicate_values_list = duplicate_values.split('-') # print('sssssssssssssssssssssssssssssssssss ssssssssssss ssssssssssssssssssssssssssssssss') # duplicate_airline_pnr = duplicate_values_list[0] # duplicate_first_name = duplicate_values_list[1] # duplicate_last_name = duplicate_values_list[2] # print(duplicate_airline_pnr) # print(duplicate_first_name) # print(duplicate_last_name) # select_sql = ( # "SELECT added_by FROM ticket_data " # "WHERE Airline_PNR = %s AND First_Name = %s AND Last_Name = %s" # ) # cursor.execute(select_sql, (duplicate_airline_pnr, duplicate_first_name, duplicate_last_name)) # current_added_by = cursor.fetchone()[0] # print(current_added_by) # # Prepare the SQL update query # update_sql = ( # "UPDATE ticket_data SET " # "Airline_Code = %s, " # "Airline = %s, " # "Airline_PNR = %s, " # "First_Name = %s, " # "Last_Name = %s, " # "`E-mail` = %s, " # "Origin = %s, " # "Destination = %s, " # "Amount = %s, " # "Refund_Amount = %s, " # "Refund_Status = %s, " # "Booking_Status = %s, " # "Fare_Analyst = %s, " # "Fare_amt = %s, " # "all_Taxes = %s, " # "added_by = %s, " # "file_id = %s, " # "updated_by = %s " # "WHERE Airline_PNR = %s AND First_Name = %s AND Last_Name = %s" # ) # print(update_sql) # print(values) # try: # # Execute the update query with extracted values # update_values = ( # values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], # values[9], values[10], values[11], values[12], values[13], values[14], current_added_by, values[16], updated_by, # duplicate_values_list[0], duplicate_values_list[1], duplicate_values_list[2] # ) # print(update_values) # cursor.execute(update_sql, update_values) # connection.commit() # logging.info("Update successful") # except Exception as update_error: # connection.rollback() # Roll back changes if an error occurs # logging.error(f"Update Error: {update_error}") # else: # logging.error(f"Insert Error: {e}") # # Commit the changes and close the connection # connection.commit() # cursor.close() # connection.close() # flash("Data inserted successfully.") # return redirect(url_for('auth_blueprint.file_display')) # def save_file(): # file = request.files['excelFile'] # uploaded_by = session['id'] # role=session['role'] # original_file = file.filename # file.save(original_file) # # Create a new data object and associate it with the user # uploaded_file = data(file_path=original_file, uploaded_by=uploaded_by) # db.session.add(uploaded_file) # db.session.commit() # uploaded_file_id = uploaded_file.id # # MySQL database connection configuration # config = { # 'user': 'root', # 'password': '', # 'host': '127.0.0.1', # 'port': 3306, # 'database': 'data_uploader', # 'raise_on_warnings': True, # } # # Connect to the MySQL database # connection = mysql.connector.connect(**config) # cursor = connection.cursor() # # Read the Excel file into a DataFrame # path = original_file # print('file_path:', path) # df = pd.read_excel(path) # # Add additional columns to the DataFrame # df['added_by'] = uploaded_by # df['file_id'] = uploaded_file_id # df.columns = df.columns.str.strip() # # Create a logger to log the errors # logging.basicConfig(filename='data_insertion_errors.log', level=logging.ERROR) # # Loop through the rows and insert data into the MySQL table # for _, row in df.iterrows(): # # Prepare a list to hold values of existing columns in the DataFrame # values = [] # # List of columns you want to extract data from # columns_to_extract = [ # 'Airline_Code', # 'Airline', # 'Airline_PNR', # 'First_Name', # 'Last_Name', # 'E-mail', # 'Origin', # 'Destination', # 'Amount', # 'Refund_Amount', # 'Refund_Status', # 'Booking_Status', # 'Fare_Analyst', # 'Fare_amt', # 'all_Taxes', # ] # for col in columns_to_extract: # if col in row.index: # # If the column exists, append its value to the 'values' list # value = row[col] # # Convert NaN values to None # value = None if pd.isna(value) else value # values.append(value) # else: # # If the column does not exist, append None to the 'values' list # values.append(None) # updated_by=None # # Append 'uploaded_by' and 'uploaded_file_id' to the 'values' list # values.extend([uploaded_by, uploaded_file_id,updated_by]) # # Prepare the SQL query for insertion # sql = f"INSERT INTO ticket_data (Airline_Code,Airline,Airline_PNR, First_Name, Last_Name, `E-mail`, Origin, Destination, Amount, Refund_Amount, Refund_Status, Booking_Status, Fare_Analyst, Fare_amt, all_Taxes, added_by, file_id,updated_by) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # # print(sql,values) # try: # print('inside try') # if role == 'user': # cursor.execute(sql, values) # else: # raise Exception("Testing exception") # except: # print('ddddddddddd') # # Check if the error code corresponds to a duplicate entry # if role == 'checker': # Error code for duplicate entry # print('passssssssssssssss') # updated_by = uploaded_by # error_message = str(e) # # Extract the duplicate values from the error message # start_index = error_message.find("'") + 1 # Find the index of the first single quote # end_index = error_message.find("'", start_index) # Find the index of the second single quote # duplicate_values = error_message[start_index:end_index] # # Split the duplicate values using the '-' separator # duplicate_values_list = duplicate_values.split('-') # print('sssssssssssssssssssssssssssssssssss ssssssssssss ssssssssssssssssssssssssssssssss') # duplicate_airline_pnr = duplicate_values_list[0] # duplicate_first_name = duplicate_values_list[1] # duplicate_last_name = duplicate_values_list[2] # print(duplicate_airline_pnr) # print(duplicate_first_name) # print(duplicate_last_name) # select_sql = ( # "SELECT added_by FROM ticket_data " # "WHERE Airline_PNR = %s AND First_Name = %s AND Last_Name = %s" # ) # cursor.execute(select_sql, (duplicate_airline_pnr, duplicate_first_name, duplicate_last_name)) # current_added_by = cursor.fetchone()[0] # print(current_added_by) # # Prepare the SQL update query # update_sql = ( # "UPDATE ticket_data SET " # "Airline_Code = %s, " # "Airline = %s, " # "Airline_PNR = %s, " # "First_Name = %s, " # "Last_Name = %s, " # "`E-mail` = %s, " # "Origin = %s, " # "Destination = %s, " # "Amount = %s, " # "Refund_Amount = %s, " # "Refund_Status = %s, " # "Booking_Status = %s, " # "Fare_Analyst = %s, " # "Fare_amt = %s, " # "all_Taxes = %s, " # "added_by = %s, " # "file_id = %s, " # "updated_by = %s " # "WHERE Airline_PNR = %s AND First_Name = %s AND Last_Name = %s" # ) # print(update_sql) # print(values) # try: # # Execute the update query with extracted values # update_values = ( # values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], # values[9], values[10], values[11], values[12], values[13], values[14], current_added_by, values[16], updated_by, # duplicate_values_list[0], duplicate_values_list[1], duplicate_values_list[2] # ) # print(update_values) # cursor.execute(update_sql, update_values) # connection.commit() # logging.info("Update successful") # except Exception as update_error: # connection.rollback() # Roll back changes if an error occurs # logging.error(f"Update Error: {update_error}") # else: # logging.error(f"Insert Error: {e}") # # Commit the changes and close the connection # connection.commit() # cursor.close() # connection.close() # flash("Data inserted successfully.") # return redirect(url_for('auth_blueprint.file_display')) #ef edit_excel(item_id): # row_count=request.args.get('row_count') # print(row_count) # file = data.query.get(item_id) # file_id=file.id # file_path=file.file_path # data_source=file.data_source # session['file_path'] = file_path # file_data = pd.read_excel(file_path) # # # Replace NaN values with empty strings # file_data.replace(np.nan, '', inplace=True) # # column_data_types = {} # # connection_details = get_connection_details(data_source) # print(connection_details) # table_name = DBData.query.get(data_source) # if connection_details: # hostname, port, username, schemaname, password = connection_details # connection_string = f"mysql+mysqlconnector://{username}:{password}@{hostname}:{port}/{schemaname}" # engine = create_engine(connection_string) # # inspector = inspect(engine) # # table_name = table_name.tablename # for column in inspector.get_columns(table_name): # column_name = column['name'] # data_type = column['type'] # python_data_type = map_to_python_data_type(data_type) # column_data_types[column_name] = python_data_type # print(column_data_types) # # # session['column_data_types'] =column_data_types # print(column_data_types) # # # Convert datetime columns to MySQL date format # for column, data_type in column_data_types.items(): # if data_type == 'datetime': # file_data[column] = file_data[column].apply(convert_to_mysql_datetime) # # # Define the page size # if row_count==None: # row_count=20 # else: # row_count =int(row_count) # # # Get the current page from the request parameters # current_page = int(request.args.get('page', 1)) # # # Calculate the starting and ending indices for the current page # start_index = (current_page - 1) * row_count # end_index = start_index + row_count # # # Retrieve the data for the current page # page_data = file_data.iloc[start_index:end_index] # print(page_data) # # Calculate the total number of pages # total_rows = len(file_data) # num_pages = (total_rows + row_count - 1) // row_count # print(num_pages) # # return render_template('edit_file.html',file_id=file_id, page_data=page_data, current_page=current_page, num_pages=num_pages, row_count=row_count, start_index=start_index, end_index=end_index,item_id=item_id,data_source=data_source,column_data_types=column_data_types) def show_data(item_id): row_count=request.args.get('row_count') print(row_count) data_source=1 connection_details = get_connection_details(data_source) print(connection_details) table_name = DBData.query.get(data_source) table_name = table_name.tablename if connection_details: hostname, port, username, schemaname, password = connection_details connection_string = f"mysql+mysqlconnector://{username}:{password}@{hostname}:{port}/{schemaname}" # Establish a connection to the database connection = mysql.connector.connect( host=hostname, port=port, user=username, password=password, database=schemaname ) cursor = connection.cursor(dictionary=True) # Execute a raw SQL query to fetch data query = f"SELECT * FROM {table_name} WHERE file_id = %s" cursor.execute(query, (item_id,)) data_array = cursor.fetchall() # Clean up and close the connection cursor.close() connection.close() # Remove the "added_by" column from the data_array for row in data_array: row.pop('added_by', None) row.pop('file_id',None) print(data_array) # Define the page size if row_count==None: row_count=20 else: row_count =int(row_count) # Get the current page from the request parameters current_page = int(request.args.get('page', 1)) # Calculate the starting and ending indices for the current page start_index = (current_page - 1) * row_count end_index = start_index + row_count # Retrieve the data for the current page page_data = data_array[start_index:end_index] print(page_data) # Calculate the total number of pages total_rows = len(data_array) num_pages = (total_rows + row_count - 1) // row_count print(num_pages) return render_template('show_data.html', page_data=page_data, current_page=current_page, num_pages=num_pages, row_count=row_count, start_index=start_index, end_index=end_index,item_id=item_id,data_source=data_source) #def map_to_python_data_type(db_data_type): # # Mapping MySQL data types to Python data types # print(db_data_type) # data_type_mapping = { # 'VARCHAR': 'str', # 'INTEGER': 'int', # 'FLOAT': 'float', # 'DATETIME': 'datetime', # # Add more mappings for other data types as needed # } # # # Try to map the database data type to the corresponding Python data type # return data_type_mapping.get(str(db_data_type), 'str') # #def convert_to_mysql_datetime(value): # try: # # Try parsing the value as a datetime using dateutil.parser.parse # datetime_value = parse(value) # return datetime_value.strftime('%Y-%m-%d %H:%M:%S') # except Exception: # # If the parsing fails, return the original value (assumed to be already in the desired format) # return value #def save_excel(): # data = request.form.get('data') # if data: # data = json.loads(data) # print('dataaaa=',data) # filepath = session.get('file_path') # column_data_types=session['column_data_types'] # success = update_excel(filepath, data) # print(success,'success') # return "Excel file saved" # return "Invalid data" #def update_excel(filepath, data): # # df = pd.read_excel(filepath, dtype={'column_name': float}) # maped_data=datatypemapping(data) # print('maped_data=',maped_data) # for row in maped_data: # row_index = row.get('rowIndex') # for column_name, edited_value in row.items(): # if column_name != 'rowIndex': # df.at[row_index, column_name] = edited_value # # df.to_excel(filepath, index=False) #def datatypemapping(data): # column_data_types = session.get('column_data_types') # print("column_data_types:", column_data_types) # # # Define a mapping for string representations to Python types or callable functions # type_mapping = { # 'str': str, # 'int': int, # 'float': float, # 'datetime': lambda x: datetime.strptime(x, "%Y-%m-%d %H:%M:%S"), # } # # for item in data: # for column, data_type in column_data_types.items(): # if column in item: # print("Converting column:", column) # print("Original value:", item[column]) # # # Check if the data_type is valid in the type_mapping # if data_type in type_mapping: # try: # # Call the corresponding Python type or callable function to perform data type conversion # item[column] = type_mapping[data_type](item[column]) # except ValueError: # # item[column] = None # print("Converted value:", item[column]) # else: # # item[column] = None # # return data def get_connection_details(data_source): connection = DBData.query.get(data_source) if connection: return ( connection.hostname, connection.port, connection.username, connection.schemaname, connection.password, ) else: return None def check_table_exists(connection_id, tablename): connection_details = get_connection_details(connection_id) print(connection_details) if connection_details: hostname, port, username, schemaname, password = connection_details connection_string = f"mysql+mysqlconnector://{username}:{password}@{hostname}:{port}/{schemaname}" engine = create_engine(connection_string) metadata = MetaData(bind=engine) try: table = Table(tablename, metadata, autoload=True, autoload_with=engine) table_exists = table.exists() except NoSuchTableError: table_exists = False return table_exists else: return False def save_data_db(): item_id = request.form.get('item_id') file_id = request.form.get('file_id') print('item ID', item_id) username='root' password='' hostname='127.0.0.1' port='3306' schemaname='data_uploader' tablename='ticket_detils' connection_string = f"mysql+mysqlconnector://{username}:{password}@{hostname}:{port}/{schemaname}" engine = create_engine(connection_string) metadata = MetaData(bind=engine) path = data.query.get(item_id) file_path = path.file_path print('file_path:', file_path) df = pd.read_excel(file_path) with engine.begin() as connection: print('inside_engin') df.to_sql(tablename, connection, if_exists='append', index=False) return "Data inserted successfully." def download_excel(): downloader=session.get('role') print(downloader) if downloader == 'checker': print('gi') config = { 'user' :'root', 'password': '', 'host': '127.0.0.1', 'port': 3306, 'database': 'data_uploader', 'raise_on_warnings': True, } # Connect to the database connection = mysql.connector.connect(**config) cursor = connection.cursor() # Fetch data from the database (You need to replace 'your_table_name' with the actual table name) query = 'SELECT * FROM ticket_data;' cursor.execute(query) data = cursor.fetchall() # Close the database connection cursor.close() connection.close() # Create a DataFrame from the data (assuming you have columns named 'id', 'file_path', and 'created_at') df = pd.DataFrame(data, columns=['id','added_by','updated_by','file_id','Airline_Code', 'Airline', 'Airline_PNR', 'First_Name', 'Last_Name', 'E-mail', 'Origin', 'Destination', 'Amount', 'Refund_Amount', 'Refund_Status', 'Booking_Status', 'Fare_Analyst', 'Fare_amt', 'all_Taxes']) # Create an Excel file from the DataFrame excel_data = io.BytesIO() with pd.ExcelWriter(excel_data, engine='xlsxwriter') as writer: df.to_excel(writer, sheet_name='Sheet1', index=False) excel_data.seek(0) # Send the Excel file as a response to the client return send_file( excel_data, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', as_attachment=True, download_name='data.xlsx' ) else: config = { 'user' :'root', 'password': '', 'host': '127.0.0.1', 'port': 3306, 'database': 'data_uploader', 'raise_on_warnings': True, } # Connect to the database connection = mysql.connector.connect(**config) cursor = connection.cursor() user=session.get('id') print(user) # Fetch data from the database (You need to replace 'your_table_name' with the actual table name) query = "SELECT * FROM ticket_data WHERE added_by = %s;" cursor.execute(query, (user,)) data = cursor.fetchall() # Close the database connection cursor.close() connection.close() # Create a DataFrame from the data (assuming you have columns named 'id', 'file_path', and 'created_at') df = pd.DataFrame(data, columns=['id','added_by','updated_by','file_id','Airline_Code', 'Airline', 'Airline_PNR', 'First_Name', 'Last_Name', 'E-mail', 'Origin', 'Destination', 'Amount', 'Refund_Amount', 'Refund_Status', 'Booking_Status', 'Fare_Analyst', 'Fare_amt', 'all_Taxes']) # Create an Excel file from the DataFrame excel_data = io.BytesIO() with pd.ExcelWriter(excel_data, engine='xlsxwriter') as writer: df.to_excel(writer, sheet_name='Sheet1', index=False) excel_data.seek(0) # Send the Excel file as a response to the client return send_file( excel_data, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', as_attachment=True, download_name='data.xlsx' ) def download_excel_file(item_id): print('item_id',item_id) config = { 'user' :'root', 'password': '', 'host': '127.0.0.1', 'port': 3306, 'database': 'data_uploader', 'raise_on_warnings': True, } # Connect to the database connection = mysql.connector.connect(**config) cursor = connection.cursor() user=session.get('id') file_id=item_id print(user) # Fetch data from the database (You need to replace 'your_table_name' with the actual table name) query = "SELECT * FROM ticket_data WHERE file_id = %s;" cursor.execute(query,(file_id,)) data = cursor.fetchall() # Close the database connection cursor.close() connection.close() # Create a DataFrame from the data (assuming you have columns named 'id', 'file_path', and 'created_at') df = pd.DataFrame(data, columns=['id','added_by','updated_by','file_id','Airline_Code', 'Airline', 'Airline_PNR', 'First_Name', 'Last_Name', 'E-mail', 'Origin', 'Destination', 'Amount', 'Refund_Amount', 'Refund_Status', 'Booking_Status', 'Fare_Analyst', 'Fare_amt', 'all_Taxes']) # Create an Excel file from the DataFrame excel_data = io.BytesIO() with pd.ExcelWriter(excel_data, engine='xlsxwriter') as writer: df.to_excel(writer, sheet_name='Sheet1', index=False) excel_data.seek(0) # Send the Excel file as a response to the client return send_file( excel_data, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', as_attachment=True, download_name='data.xlsx' ) # Set up logging configuration logging.basicConfig(filename='error_log.txt', level=logging.WARNING, format='%(asctime)s - %(levelname)s - %(message)s')