nighthawkcoders / flask_2025

This project is a web application for managing Classroom instruction and operations. It is built using Flask, SQLAlchemy, and other requirements.txt dependencies.
https://flask2025.nighthawkcodingsociety.com/
0 stars 12 forks source link

Phases for Data Migration #19

Open tanishapatil1234 opened 3 months ago

tanishapatil1234 commented 3 months ago

Phases for Data Migration

Tanisha Patil This guide aims to outline the phases for data migration from JSON endpoint to RDS using the SQLTools extension.

Current RDS schema is :

image

Phase 1 : Obtain new JSON data from API endpoint

{ "kasm_server_needed": false, "name": "testUser", "role": "User", "sections": [ { "abreviation": "CSP" }, { "abreviation": "CSA" } ], "uid": "testUser" }

Phase 2: Connect to RDS using SQLTools

Installations

  1. Navigate to extensions in VSCODE.
  2. Install the following : Screen Shot 2024-07-08 at 8 13 08 AM Screen Shot 2024-07-08 at 8 13 53 AM

Using SQL Tools

  1. Navigate to the SQL tools icon on the side bar

    image
  2. On the top banner, click 'Add New Connection' button image

  3. When prompted, select 'MySQL'

    image
  4. When prompted, fill out connection settings :

    image
  5. Test and then Save working Connection

    image

    Now, under connections you should see the new connection just made:

    image
  6. Now, a .sql file named after the connection should appear in editor:

    image

Phase 3: Empty data from RDS

  1. Paste the SQL queries in this order (to avoid violating foreign key constraints) and click 'Run on active connection' to empty data from RDS :

DELETE FROM user_sections WHERE user_id IN (SELECT id FROM users);

DELETE FROM users;

Phase 4: Prompt ChatGPT for SQL query from new JSON data to insert endpoint data into RDS schema

image
  1. Insert the users into the users table.

INSERT INTO users (_name, _uid, _role, kasm_server_needed, _password, _pfp, status) VALUES ('Luna Iwazaki', 'lunaiwa', 'User', false, '', '', 1), ('Aliya Tang', 'aliyatang', 'User', false, '', '', 1), ('Tay Kim', 'TayKimmy', 'User', false, '', '', 1), ('Ethan Tran', 'realethantran', 'User', false, '', '', 1), ('Raymond Sheng', 'raymondYsheng', 'User', false, '', '', 1), ('Eunseo Lim', 'eunseolim123', 'User', false, '', '', 1), ('Jordan Pham', 'jntpham', 'User', false, '', '', 1), ('Aashray Reddy', 'KinetekEnergy', 'User', false, '', '', 1), ('Avanthika Daita', 'avanthikadaita', 'User', false, '', '', 1), ('Ryan Liu', 'RyanZLiu', 'User', false, '', '', 1), ('Dante Atanassov', 'DanteA-tech', 'User', false, '', '', 1), ('Aditya D', 'ad1tyad3sa1', 'User', false, '', '', 1), ('Saaras', 'Saaras859', 'User', false, '', '', 1), ('William Cheng', 'will-w-cheng', 'User', false, '', '', 1), ('Rayane Souissi', 'rayanesouuuu1234', 'User', false, '', '', 1), ('Timo Al-Faraje', 'TimoA35', 'User', false, '', '', 1), ('Jake Shim', 'jkaeshim', 'User', false, '', '', 1), ('Daniel Lee', 'daniel-s-lee', 'User', false, '', '', 1), ('Shubhay Choubey', 'Vynz123', 'User', false, '', '', 1), ('Cayden Shi', 'Deeskili', 'User', false, '', '', 1), ('Grayson Guyot', 'Delta760', 'User', false, '', '', 1), ('Lakshanya S', 'lakshusan', 'User', false, '', '', 1), ('Anthony Yoo', 'yooanthony', 'User', false, '', '', 1), ('Sri Vaidya S', 'srivaidyas', 'User', false, '', '', 1), ('Arushi Pandey', 'Arushi-maker', 'User', false, '', '', 1);

image image
  1. Insert the relationships into the user_sections table. Note, CSA has id 1 and CSP has id 2

    image

    -- CSA has id 1 and CSP has id 2 INSERT INTO user_sections (user_id, section_id) SELECT u.id, s.id FROM users u JOIN sections s ON s._abbreviation = 'CSA' WHERE u._uid IN ('lunaiwa', 'aliyatang', 'TayKimmy', 'realethantran', 'raymondYsheng');

    INSERT INTO user_sections (user_id, section_id) SELECT u.id, s.id FROM users u JOIN sections s ON s._abbreviation = 'CSP' WHERE u._uid IN ('lunaiwa', 'aliyatang', 'TayKimmy', 'realethantran', 'eunseolim123', 'jntpham', 'KinetekEnergy', 'avanthikadaita', 'RyanZLiu', 'DanteA-tech', 'ad1tyad3sa1', 'Saaras859', 'will-w-cheng', 'rayanesouuuu1234', 'TimoA35', 'jkaeshim', 'daniel-s-lee', 'Vynz123', 'Deeskili', 'Delta760', 'lakshusan', 'yooanthony', 'srivaidyas', 'Arushi-maker');

image

Done! All data from endpoint has now been migrated into the RDS system.

jm1021 commented 2 months ago

This article belongs on portfolio_2025, but it could have use in flask_2025 discussion on migrating and changing schema.