Scribd
Upload
74 views3 pages

Data Insertion with Flask & MySQL

This document discusses creating a customer segmentation model using Python and MySQL. It loads customer data from a CSV file into a Pandas dataframe, then builds a Flask API to insert the data into a MySQL database table. Another endpoint in the API performs SQL queries on the table to retrieve limited customer data and return it in the response. The API is run locally on port 5000 and links are provided to test the insert and query endpoints.

Uploaded by

akash.050501
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
74 views3 pages

Data Insertion with Flask & MySQL

This document discusses creating a customer segmentation model using Python and MySQL. It loads customer data from a CSV file into a Pandas dataframe, then builds a Flask API to insert the data into a MySQL database table. Another endpoint in the API performs SQL queries on the table to retrieve limited customer data and return it in the response. The API is run locally on port 5000 and links are provided to test the insert and query endpoints.

Uploaded by

akash.050501
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

01/12/2023, 16:12 Customer_segmentation - Jupyter Notebook

Import necessary libraries


In [1]: 1 from flask import Flask, jsonify
2 import pandas as pd
3 import mysql.connector
4 from mysql.connector import Error
5 import pymysql

Flask
In [2]: 1 app=Flask(__name__)

Read dataset
In [3]: 1 df=pd.read_csv('customer_segmentation.csv')
2 df.head()

Out[3]:
customer_id qtt_order total_spent last_order

0 0 41 321785.93 2022-08-04 01:00:00

1 1 126 185018.15 2022-09-17 11:00:00

2 2 3 357434.58 2022-04-01 14:00:00

3 3 74 2828.39 2022-02-15 18:00:00

4 4 243 76092.70 2022-01-27 04:00:00

In [4]: 1 df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 100000 entries, 0 to 99999
Data columns (total 4 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 customer_id 100000 non-null int64
1 qtt_order 100000 non-null int64
2 total_spent 100000 non-null float64
3 last_order 100000 non-null object
dtypes: float64(1), int64(2), object(1)
memory usage: 3.1+ MB

API to connect data to MySQL

localhost:8888/notebooks/KINGS LABS/API/Jupyter Notebook/MySQL/Customer_segmentation.ipynb 1/3


01/12/2023, 16:12 Customer_segmentation - Jupyter Notebook

In [5]: 1 db_config = {
2 'host': 'localhost',
3 'user': 'root',
4 'password': '050501@Aks',
5 'database': 'akashdb',
6 }
7 ​
8 def connect_and_insert_data(filename):
9 try:
10 connection = mysql.connector.connect(**db_config)
11 ​
12 if connection.is_connected():
13 cursor = connection.cursor()
14 ​
15 for index, row in df.iterrows():
16 cursor.execute("""
17 INSERT INTO cus_seg (
18 customer_id, qtt_order, total_spent,
19 last_order
20 ) VALUES (%s, %s, %s, %s)
21 """, (
22 row['customer_id'], row['qtt_order'], row['total_sp
23 row['last_order']
24 ))
25 ​
26 connection.commit()
27 ​
28 except Error as e:
29 print("Error:", e)
30 ​
31 finally:
32 if connection.is_connected():
33 cursor.close()
34 connection.close()
35 ​
36 @app.route('/insert_data')
37 def insert_data():
38 filename = 'customer_segmentation.csv'
39 connect_and_insert_data(filename)
40 return jsonify({'message': 'Data inserted successfully'})
41 ​
42 if __name__ == '__main__':
43 app.run(debug=False)

* Serving Flask app '__main__'


* Debug mode: off

WARNING: This is a development server. Do not use it in a production deplo


yment. Use a production WSGI server instead.
* Running on http://127.0.0.1:5000 (http://127.0.0.1:5000)
Press CTRL+C to quit
127.0.0.1 - - [01/Dec/2023 16:10:59] "GET /insert_data HTTP/1.1" 200 -

In [6]: 1 # LINK : http://127.0.0.1:5000/insert_data

API to do SQL

localhost:8888/notebooks/KINGS LABS/API/Jupyter Notebook/MySQL/Customer_segmentation.ipynb 2/3


01/12/2023, 16:12 Customer_segmentation - Jupyter Notebook

In [7]: 1 db = pymysql.connect(**db_config)
2 cursor = db.cursor()
3 ​
4 @app.route('/api/data', methods=['GET'])
5 def get_users():
6 try:
7 cursor.execute("SELECT * FROM cus_seg LIMIT 20")
8 users = cursor.fetchall()
9 ​
10 user_list = '<br>'.join([f'{user[0]}, {user[1]}, {user[2]}, {us
11 ​
12 return user_list
13 ​
14 except Exception as e:
15 return str(e)
16 ​
17 if __name__ == '__main__':
18 app.run(debug=False)
19 ​

* Serving Flask app '__main__'


* Debug mode: off

WARNING: This is a development server. Do not use it in a production deplo


yment. Use a production WSGI server instead.
* Running on http://127.0.0.1:5000 (http://127.0.0.1:5000)
Press CTRL+C to quit
127.0.0.1 - - [01/Dec/2023 16:11:41] "GET /api/data HTTP/1.1" 200 -
127.0.0.1 - - [01/Dec/2023 16:11:43] "GET /api/data HTTP/1.1" 200 -

In [8]: 1 # LINK : http://127.0.0.1:5000/api/data

localhost:8888/notebooks/KINGS LABS/API/Jupyter Notebook/MySQL/Customer_segmentation.ipynb 3/3

You might also like