-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathapp.py
More file actions
148 lines (116 loc) · 5.03 KB
/
app.py
File metadata and controls
148 lines (116 loc) · 5.03 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
from flask import Flask, render_template, request, send_from_directory
import pandas as pd
import os
app = Flask(__name__)
# Set the upload folder
UPLOAD_FOLDER = 'uploads'
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
@app.route('/')
def index():
return "Welcome to Power Query ETL!"
@app.route('/upload', methods=['GET', 'POST'])
def upload():
if request.method == 'POST':
# Check if the POST request has a file part
if 'file' not in request.files:
return "No file part"
file = request.files['file']
# If the user does not select a file, the browser submits an empty file without a filename
if file.filename == '':
return "No selected file"
if file:
# Save the uploaded file to the upload folder
filename = os.path.join(app.config['UPLOAD_FOLDER'], file.filename)
file.save(filename)
# Read the uploaded file
df = pd.read_csv(filename)
# Get column names and data types
column_info = df.dtypes.reset_index()
column_info.columns = ['Column Name', 'Data Type']
# Get the number of records
num_records = len(df)
# Render the file_info.html template with file name, column names, data types, and number of records
return render_template('file_info.html', filename=file.filename, column_info=column_info.to_html(classes='table table-bordered table-striped', index=False), num_records=num_records)
return render_template('upload.html')
@app.route('/filter_data/<filename>', methods=['GET', 'POST'])
def filter_data(filename):
file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
if not os.path.exists(file_path):
return "File not found."
df = pd.read_csv(file_path)
num_records = len(df)
columns = df.columns.tolist()
filtered_data = df.copy()
if request.method == 'POST':
# Apply filters based on user input
for column in columns:
filter_value = request.form.get(column, '').strip()
if filter_value:
try:
# Try to convert the filter value to the appropriate data type of the column
if df[column].dtype == 'int64' or df[column].dtype == 'float64':
filter_value = float(filter_value)
elif df[column].dtype == 'datetime64':
filter_value = pd.to_datetime(filter_value)
# Apply the filter
filtered_data = filtered_data[filtered_data[column] == filter_value]
except ValueError:
# Handle invalid filter values here
pass
filtered_data = filtered_data.values.tolist()
return render_template('filter_data.html', filename=filename, num_records=num_records, columns=columns,
filtered_data=filtered_data)
if __name__ == '__main__':
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
app.run(debug=True)
----------------------------------------
from flask import Flask, jsonify
import pyodbc
app = Flask(__name__)
# Configure SQL Server connection
server = 'your_server'
database = 'your_database'
username = 'your_username'
password = 'your_password'
driver = '{ODBC Driver 17 for SQL Server}'
connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}'
def get_db_connection():
return pyodbc.connect(connection_string)
@app.route('/table1', methods=['GET'])
def get_table1_data():
try:
conn = get_db_connection()
cursor = conn.cursor()
cursor.execute("SELECT id, name, value FROM table1")
records = cursor.fetchall()
conn.close()
data = [{'id': rec[0], 'name': rec[1], 'value': rec[2]} for rec in records]
return jsonify(data)
except Exception as e:
return jsonify({'error': str(e)})
@app.route('/table2', methods=['GET'])
def get_table2_data():
try:
conn = get_db_connection()
cursor = conn.cursor()
cursor.execute("SELECT id, description, amount FROM table2")
records = cursor.fetchall()
conn.close()
data = [{'id': rec[0], 'description': rec[1], 'amount': rec[2]} for rec in records]
return jsonify(data)
except Exception as e:
return jsonify({'error': str(e)})
if __name__ == '__main__':
app.run(debug=True)
let
Source = Json.Document(Web.Contents("https://your-domain.atlassian.net/rest/api/3/project/MYPROJ/versions",
[
Headers = [
Authorization="Basic your_base64_encoded_auth",
#"Content-Type"="application/json"
]
])),
Releases = Table.FromList(Source, Record.FieldValues, {"Release"}),
Expanded = Table.ExpandRecordColumn(Releases, "Release", {"id", "name", "released", "releaseDate", "archived"})
in
Expanded