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
Automatically searching for vaccine appointments

Vaccine Appointments Automatically searching for vaccine appointments Usage To copy this package, run: git clone https://github.com/TheIronicCurtain/v

58 Apr 13, 2021
Twitter for Python!

Tweepy: Twitter for Python! Installation The easiest way to install the latest version from PyPI is by using pip: pip install tweepy You can also use

9.4k Jan 07, 2023
One of Best renamer bot with python

🌀 One of Best renamer bot repo Please Give a ☆ if You like This Open Source and Don't Forget to Follow Me On Github For More Repos And Codes. Scrappe

1 Dec 14, 2021
An inline real-time media searching robot without any database.

MediaBuddy A Telegram Inline media searching robot without any database. About mediaBuddy is an inline media searching robot. If you have so many movi

Renjith Mangal 28 Oct 21, 2022
📅 Calendar file generator for triathlonlive.tv upcoming events

Triathlon Live Calendar Calendar file generator for triathlonlive.tv upcoming events. Install Requires Python 3.9.4 and Poetry. $ poetry install Runni

Eduardo Cuducos 4 Sep 02, 2022
Python script for download course from platzi.com

Platzi Downloader Tool Esta es una pequeña herramienta que hace mucho y que te ahorra una gran cantidad de trabajo a la hora de descargar cursos de Pl

Devil64-Dev 21 Sep 22, 2022
discord bot made in discord.py

udeline discord bot made in discord.py, which's main features include: general use server moderation fun commands other cool commands dependencies dis

1 Feb 08, 2022
UNLIMITED CALL AND SMS BOMBING PYTHON SCRIPT

cc_sim_crack v.1 An open-source SMS/call bomber for Linux And Termux. Note: Due misusing of cc_sim_crack, several API's died. Don't be afraid if you d

CYBER CRACKER OFFICIAL 3 Jul 05, 2021
The purpose of this bot is to take soundcloud track requests, that are posted in the stream-requests channel, and add them to a playlist, to make the process of scrolling through the requests easier for Root

Discord Song Collector Dont know if anyone is actually going to read this, but the purpose of this bot is to check every message in the stream-request

2 Mar 01, 2022
Kakatua discord music bot

Donate Ayo donasi! Lokal Internasional Ucapan Terima Kasih Tentu saja, donatur Bunga dan talent-talent h!mawari. Semoga rezeki teman-teman semakin lan

1 Oct 30, 2021
This is a bot which you can use in telegram to spam without flooding and enjoy being in the leaderboard

Telegram-Count-spamming-Bot This is a bot which you can use in telegram to spam without flooding and enjoy being in the leaderboard You can avoid the

Lalan Kumar 1 Oct 23, 2021
Baby Villager Bot

This script scrapes and stores the availability of timeslots for Car Driving Test at all RTA Serivce NSW centres in the state. Dependencies Account wi

Milo Weinberg 3 Dec 16, 2021
Código que verifica se o grafo é Hamiltoniano (Em Python)

Código para encontrar um ciclo de Hamilton em um dado grafo e a partir daí verificar se o grafo é hamiltoniano. Um ciclo hamiltoniano é um ciclo gerad

Hemili Beatriz 1 Jan 08, 2022
Python lib for Embedly

embedly-python Python library for interacting with Embedly's API. To get started sign up for a key at embed.ly/signup. Install Install with Pip (recom

Embedly 80 Oct 05, 2022
Dashboard to monitor the performance of your Binance Futures account

futuresboard A python based scraper and dashboard to monitor the performance of your Binance Futures account. Note: A local sqlite3 database config/fu

86 Dec 29, 2022
A small and fun Discord Bot that is written in Python and discord-interactions (with discord.py)

Articuno (discord-interactions) A small and fun Discord Bot that is written in Python and discord-interactions (with discord.py) Get started If you wa

Blue 8 Dec 26, 2022
Osmnx-examples - Usage examples, demos, and tutorials for OSMnx.

OSMnx Examples OSMnx is a Python package to work with street networks and other spatial data from OpenStreetMap: retrieve, model, analyze, and visuali

Geoff Boeing 1.2k Jan 03, 2023
🛰️ Scripts démontrant l'utilisation de l'imagerie RADARSAT-1 à partir d'un seau AWS | 🛰️ Scripts demonstrating the use of RADARSAT-1 imagery from an AWS bucket

🛰️ Scripts démontrant l'utilisation de l'imagerie RADARSAT-1 à partir d'un seau AWS | 🛰️ Scripts demonstrating the use of RADARSAT-1 imagery from an AWS bucket

Agence spatiale canadienne - Canadian Space Agency 4 May 18, 2022
This is a music bot for discord written in python

this is a music bot for discord written in python, it is designed for educational use ONLY, I do not take any responsibility for uses outside of educational use

5 Dec 24, 2021
API RestFull de uma clinica, onde vai efetuar os agendamentos dos pacientes e mostrar o historicos de cada agendamentos

API REstFull O que tem na API Usado para clinicas. Cadastro de pacientes. Agendamentos de pacientes. Históricos dos agendamentos vinculados com a tabe

Lucas Silva 3 Aug 29, 2022