Bancos de Dados Relacionais (SQL) na AWS com Amazon RDS

Overview

Bancos de Dados Relacionais (SQL) na AWS com Amazon RDS

Repositório para o Live Coding DIO do dia 24/11/2021

Serviços utilizados

  • Amazon RDS
  • AWS Lambda
  • MySQL Workbench

Criando o banco de dados no Amazon RDS

  • AWS Console -> Amazon RDS -> Create database -> Standard create -> MySQL -> Versão padrão -> Free Tier -> DB instance identifier [dio-live-db] -> Master username [admin] -> Master password [sua_senha_forte] -> DB instance size - padrão -> Storage - configurações padrão -> Connectivity - vpc padrão -> Publicly accessible [yes] -> VPC Security - padrão -> Database authentication [password authentication] -> Create database
  • Selecionar o DB criado -> Connectivity & security -> Copiar endpoint.

No MySQL Workbench

  • MySQL Connections -> New -> Connection name [DioLive] -> Hostname - colar o endpoint copiado no passo anterior -> Username [admin] -> Teste Connection -> Password [sua_senha]

Em caso de problemas na conexão

  • Security -> VPC security groups -> Acessar o SG criado -> Inbound -> Edit -> Add rule -> type [All traffic] -> Source [Anywhere] -> Save

No MySQL Workbench

  • Selecionar a conexão criada -> Password [sua_senha_forte]

Criando queries

  • Criar um database:

    CREATE DATABASE PERMISSIONS_DB;

  • Acessar o db criado

    USE PERMISSIONS_DB;

  • Criar uma tabela de usuários

    CREATE TABLE user (
      id bigint(20) NOT NULL, 
      email varchar(40) NOT NULL,
      username varchar(15) NOT NULL,
      password varchar(100) NOT NULL,
      PRIMARY KEY (id)
    );
    
  • Criar uma tabela de carrinho de compras

    CREATE TABLE role (
      id bigint(20) NOT NULL,
      name varchar(60) NOT NULL, 
      PRIMARY KEY (id)
    );
    
  • Criar uma tabela associativa de itens em um carrinho de compras

    CREATE TABLE ITEMS (
      cart_id INT NOT NULL,
      product_id INT NOT NULL,
      quantity DECIMAL(15,2) NOT NULL,
      FOREIGN KEY (cart_id) REFERENCES CARTS (id),
      FOREIGN KEY (product_id) REFERENCES PRODUCTS (id)
    );
    
  • Descrevendo o esquema de uma tabela

    CREATE TABLE user_roles (
      user_id bigint(20) NOT NULL,
      role_id bigint(20) NOT NULL,
      FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE CASCADE,
      FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE RESTRICT ON UPDATE CASCADE,
      PRIMARY KEY (user_id, role_id)
    );
    
  • Inserindo dados em tabelas

    INSERT INTO user VALUES (1, '[email protected]', 'Cassiano', 'strongpasswd');
    INSERT INTO user VALUES (2, '[email protected]', 'Joao', 'strongpasswd');
    
    INSERT INTO role VALUES (3, 'ADMIN');
    INSERT INTO role VALUES (4, 'USER');
    
    INSERT INTO user_roles VALUES (1, 3);
    INSERT INTO user_roles VALUES (1, 4);
    INSERT INTO user_roles VALUES (2, 4);
    
  • Selecionando todos os registros de uma tabela

    SELECT * FROM [table_name];
    
  • Selecionando dados da tabela associativa

    SELECT user.id, user.email, user.username, role.id AS role_id, role.name AS role_name
    FROM user 
    JOIN user_roles on (user.id=user_roles.user_id)
    JOIN role on (role.id=user_roles.role_id);
    

Realizando queries no Amazon RDS a partir de uma função no AWS Lambda

Criando a função Lambda

  • Acessar o AWS Lambda console -> Create function -> Author from scratch -> Function name [RDSQuery] -> Runtime - Python3.9 -> Create new role from AWS policy template -> Role name [RDSQueryFromLambdaRole] -> Create function

Configurando permissões de acesso ao RDS

  • Selecionar a função criada -> Configuration -> Permissions -> Selecionar a função criada e abrir no console do AWS IAM
  • Attach policies -> Pesquisar pela policy AWSLambdaVPCAccessExecutionRole -> Attach policy

Desenvolvendo o código da função Lambda

  • Editor de código da função criada -> Inserir o código disponível na pasta src deste projeto

Importando a biblioteca pymysql utilizando Lambda Layers

  • Lambda Dashboard -> Layers -> Create layer -> Name [pymysql_layer] -> Upload a .zip file - o arquivo pyton.zip está disponível na pasta src do projeto -> Compatible architectures x86_64 -> Compatible runtimes - Python 3.9 -> Create
  • Lambda Dashboard -> selecionar a função criada -> Layers -> Add a layer -> Custom layers -> selecionar o layer criado anteriormente -> Add

Testando a função criada

  • Test -> New event -> Template -> Hello World -> Name [test] -> Save changes -> Test
Owner
Cassiano Ricardo de Oliveira Peres
NodeJs, blockchain and cloud developer. Cryptocurrency enthusiastic.
Cassiano Ricardo de Oliveira Peres
A small module to communicate with Triller's API

A small, UNOFFICIAL module to communicate with Triller's API. I plan to add more features/methods in the future.

A3R0 1 Nov 01, 2022
A powerful application to automatically deploy GitHub Release.

A powerful application to automatically deploy GitHub Release.

Fentaniao 43 Sep 17, 2022
Library written in Python that wraps Halo Infinite API.

haloinfinite Library written in Python that wraps Halo Infinite API. Before start It's unofficial, reverse-engineered, neither stable nor production r

Miguel Ferrer 4 Dec 28, 2022
An powerfull telegram group management anime themed bot.

ErzaScarlet Erza Scarlet is the female deuteragonist of the anime/manga series Fairy Tail. She is an S-class Mage from the Guild Fairy Tail. Like most

ꜱōʜᴇʀᴜ ᴋāɴ (AKA) ꜱᴏʜᴀɪʟ ᴋʜᴀɴ 2 May 19, 2022
Python linting made easy. Also a casual yet honorific way to address individuals who have entered an organization prior to you.

pysen What is pysen? pysen aims to provide a unified platform to configure and run day-to-day development tools. We envision the following scenarios i

Preferred Networks, Inc. 452 Jan 05, 2023
Listen to the radio station from your favorite broadcast

Latest news Listen to the radio station from your favorite broadcast MyCroft Radio Skill for testing and copy at docker skill About Play regional radi

1 Dec 22, 2021
An all-in-one financial analytics and smart portfolio creator as a Discord bot!

An all-in-one financial analytics bot to help you gain quantitative financial insights. Finn is a Discord Bot that lets you explore the stock market like you've never before!

6 Jan 12, 2022
A telegram bot writen in python for mirroring files on the internet to Google Drive

owner of this repo :- AYUSH contact me :- AYUSH Slam Mirror Bot This is a telegram bot writen in python for mirroring files on the internet to our bel

Thanusara Pasindu 1 Nov 21, 2021
Graviti TensorBay Python SDK

TensorBay Python SDK is a python library to access TensorBay and manage your datasets. It provides: A pythonic way to access your

Graviti 72 Aug 22, 2022
A Simple and User-Friendly Google Collab Notebook with UI to transfer your data from Mega to Google Drive.

Mega to Google Drive (UI Added! 😊 ) A Simple and User-Friendly Google Collab Notebook with UI to transfer your data from Mega to Google Drive. ⚙️ How

Dr.Caduceus 18 Aug 16, 2022
Fortnite Dumper for anyone's Save the World profiles.

Anyone's Fortnite Save the World Profile Dumper This program allows you to dump anyone's Fortnite Save the World Profiles. How to use it? After starti

PRO100KatYT 6 Apr 13, 2022
Mandatory join to channel using pyTelegramBotAPI

Running set your bot token to config.py set channel username to config.py set channel url to config.py $ python join.py Attention Bot must be administ

Abdulatif 6 Oct 08, 2022
A multipurpose, semi-modular Discord bot written in Python with the new discord.py module.

Discord.py Reaction Bot MIRAI KURIYAMA A multipurpose, semi-modular Discord bot written in Python with the new discord.py module. Installing dependenc

1 Dec 02, 2021
Códigos pela Força Bruta e Algoritmo Genético para o Problema da Mochila

O problema da mochila é um problema de optimização combinatória. O nome dá-se devido ao modelo de uma situação em que é necessário preencher uma mochi

Hemili Beatriz 1 Jan 08, 2022
Space Bot, a Discord bot built for HackerSpace Club of PES University

Space Bot Space Bot, a Discord bot built for HackerSpace Club of PES University What can Space Bot do? Space Bot allows you to lookup any mentor or to

HackerSpace @PESU 7 Oct 23, 2022
Requests based multi-threaded script for increasing followers on Spotify

Proxyless Spotify Follow Bot Requests based multi-threaded script for increasing followers on Spotify. Click here to report bugs. Usage Download ZIP h

397 Jan 03, 2023
A Really Simple and Advanced Google Colab NoteBook to Setup and start using Rclone on Google Colab easily.

Rclone on Google Colab (Advanced!) 🔥 1.Setup and Start using Rclone on Google Colab and Create/Edit/View and delete your Rclone config file and keep

Dr.Caduceus 14 May 24, 2022
stories-matiasucker created by GitHub Classroom

Stories do Instagram Este projeto tem como objetivo desenvolver uma pequena aplicação que simule os efeitos e funcionalidades ao estilo Instagram. A a

1 Dec 20, 2021
Total time of all YouTube videos in a playlist.

Youtube Playlist Total Times Total time of all YouTube videos in a playlist. How to Use Download chromedriver depending on your os and chrome version

Mohammad Dori 3 Jul 15, 2022
Telegram Bot for generating and decoding QR-codes

Telegram openqrgen_bot Telegram Bot that generates from user's messages and decodes QR-codes from photos. Also contains rickroll detection :) Just typ

2 Nov 14, 2021