GIT DOCKER SQL MongoDB

 

GIT DOCKER SQL MongoDB

Review

 

 

---

 

1. GIT

git clone 
git status
git add
git commit
git push

 

2. DOCKER

docker image pull 이미지이름:tag // 이미지 불러오기

docker image rm 이미지이름 // 이미지 제거

docker image rmi 이미지id // 이미지 아이디로 제거

docker ps // 작동중인 컨테이너 확인

docker ps -a // 모든 컨테이너 확인

docker rm 컨테이너 id, 컨테이너id // 컨테이너 여러개 삭제가능

docker container rm `docker ps -a -q` // 전체삭제

docker inspect 이미지이름 // 환경변수 등 확인 가능

// 컨테이너 RUN
docker container run [options] IMAGE [command] [arg...]
// 예시
docker container run -it --name myName -e NAME=John -e AGE=20 [docker/images:tag] [command:python/bash]
exit // 컨테이너 종료

ctrl+P,Q // 종료없이 나오기
docker start myName // 컨테이너 실행

docker attach myName // 컨테이너 진입

 

 

3. DOCKER COMPOSE

# docker-compose.yaml
version: '3' # 3으로 시작하는 최신버전 사용 

services: # 실행하려는 컨테이너 정의 (컨테이너 대신 서비스라는 개념 사용)

  db: # 서비스 이름을 db로 정의 
    image: postgres:12 # 사용할 이미지 
    restart: unless-stopped # 해당 컨테이너의 실행이 중단되었을때 컨테이너를 알아서 재시작
    environment: # 환경변수 선언
      POSTGRES_PASSWORD: pg_pwd
      POSTGRES_DB: sampleDB
      POSTGRES_USER: postgres_admin
    volumes: # 데이터 저장 ( 불러올 경로 : 저장할 경로 )
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql/

  adminer: # 서비스 이름을 adminer 정의 
    image: dpage/pgadmin4:4 # 사용할 이미지 (혹은 build할 수도 있음)
    restart: unless-stopped # 해당 컨테이너의 실행이 중단되었을때 컨테이너를 알아서 재시작
    environment: # 환경변수 선언
      PGADMIN_DEFAULT_EMAIL: user@user.com
      PGADMIN_DEFAULT_PASSWORD: pwdpwd
    depends_on: #db가 실행되는 것에 따라서 실행 되도록 설정
      - db
    volumes: # 데이터 저장 ( 불러올 경로 : 저장할 경로 )
      - ./servers.json:/pgadmin4/servers.json
    ports:
      - 5050:80 # Host의 포트가 5050 일 때, 해당 Container의 80 포트로 연결

docker-compose up 	# -d를 붙이면 실행 후 콘솔로 빠져나옵니다.
					# --force-recreate 컨테이너를 지운뒤에 다시 만듭니다.
                    # --build 서비스를 시작하기 전에 이미지를 만듭니다.

docker-compose ps	# docker ps와 같은 

docker-compose stop # 서비스를 멈춤

docker-compose stop # 서비스를 시작

docker-compose down # 서비스를 지움
					# --volumne 볼륨까지 삭제
                    
docker-compose exec	# run과 같은 방식, 명령어를 실행

docker-compose logs 서비스명 # 서비스명을 안적으면 모든 로그 보여줌

 

4. SQL

## Queries
SELECT title, director FROM movies;

SELECT * FROM movies where id=6;

SELECT * FROM movies 
where year BETWEEN 2000 AND 2010;

SELECT * FROM movies 
where year NOT BETWEEN 2000 AND 2010;

SELECT * FROM movies
where id < 6;

SELECT * FROM movies
where title LIKE "toy%";

SELECT * FROM movies
where director !="John Lasseter";

SELECT * FROM movies
where title LIKE "WALL-_";

## Filtering and sorting Query results
SELECT DISTINCT director FROM movies
ORDER BY director;

SELECT * FROM movies
ORDER BY year DESC
LIMIT 4;

SELECT * FROM movies
ORDER BY title
LIMIT 5 OFFSET 5;

## Review
SELECT * FROM north_american_cities
WHERE country = "Canada";

SELECT * FROM north_american_cities
WHERE country LIKE "United%"
ORDER BY latitude DESC;

SELECT * FROM north_american_cities
ORDER BY longitude
LIMIT 6;

SELECT * FROM north_american_cities
WHERE country = "Mexico"
ORDER BY population DESC
LIMIT 2;

SELECT * FROM north_american_cities
WHERE country LIKE "United%"
ORDER BY population DESC
LIMIT 2 OFFSET 2;

## INNER JOIN
SELECT * FROM movies
INNER JOIN boxoffice
    ON movies.Id = Movie_id;
    
SELECT * FROM movies
INNER JOIN boxoffice
    ON movies.Id = Movie_id
WHERE domestic_sales<international_sales;

SELECT * FROM movies
INNER JOIN boxoffice
    ON movies.Id = Movie_id
ORDER BY rating DESC;

## OUTER JOIN
SELECT DISTINCT building FROM employees;

SELECT * FROM employees
LEFT JOIN Buildings 
    ON employees.Building = Buildings.Building_name;
    
SELECT DISTINCT building_name, role FROM buildings 
LEFT JOIN employees
  	ON building_name = building;
    
## NULL
SELECT name, role FROM employees
WHERE building IS NULL;

SELECT DISTINCT building_name
FROM buildings 
  LEFT JOIN employees
    ON building_name = building
WHERE role IS NULL;

## with expression 
SELECT title, (domestic_sales + international_sales) / 1000000 AS millions
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;
    
SELECT title, rating * 10 AS rating_percent
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;
    
SELECT title, year
FROM movies
WHERE year % 2 = 0;

## with aggregates 
SELECT MAX(years_employed) as Max_years_employed
FROM employees;

SELECT role, AVG(years_employed) as Average_years_employed
FROM employees
GROUP BY role;

SELECT building, SUM(years_employed) as Sum_years_employed
FROM employees
GROUP BY building;

SELECT building, SUM(years_employed) as Sum_years_employed
FROM employees
GROUP BY building;

SELECT role, COUNT(*) as Number_of_artists
FROM employees
WHERE role = "Artist";

SELECT role, SUM(years_employed)
FROM employees
GROUP BY role
HAVING role = "Engineer"; # Having은 그룹 컨디션 

## Inserting rows*/
INSERT INTO movies 
VALUES (4, "Toy Story 4", "El Directore", 2015, 90);

## Updating rows 
UPDATE movies
SET year = 1999
WHERE id = 3;

UPDATE movies
SET title = "Toy Story 3", director = "Lee Unkrich"
WHERE id = 11;

## Deleting rows 
DELETE FROM movies
where year < 2005;

## Create a new table named Database with the following columns 
CREATE TABLE Database (
    Name TEXT,
    Version FLOAT,
    Download_count INTEGER
);

## Add new columns 
ALTER TABLE Movies
  ADD COLUMN Aspect_ratio FLOAT DEFAULT 2.39;
  
ALTER TABLE Movies
  ADD COLUMN Language TEXT DEFAULT "English";
  
## Drop the table 
DROP TABLE movies;

 

5. SQL Example

## CREATE TABLE
CREATE TABLE Customer (
    customer_id	INTEGER	NOT NULL,
    customer_name VARCHAR(32) NOT NULL,
    customer_age INTEGER,
    CONSTRAINT Customer_PK PRIMARY KEY (customer_id)
);

CREATE TABLE Customer_Package (
    cp_id       INTEGER NOT NULL, 
    customer_id INTEGER,
    package_id  INTEGER,
    CONSTRAINT Customer_Package_PK PRIMARY KEY (cp_id),
    CONSTRAINT Customer_Package_FK FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    CONSTRAINT Customer_Package_FK FOREIGN KEY (package_id) REFERENCES Package(package_id)
);

## Query
SELECT a1.AlbumId 
FROM albums a1
INNER JOIN artists a2 ON a1.ArtistId = a2.ArtistId 
WHERE a2.Name LIKE "%the%";


SELECT InvoiceId FROM invoices i 
WHERE BillingCity = "Stuttgart" OR
	BillingCity = "Oslo" OR
	BillingCity = "Redmond"
ORDER BY InvoiceId;


SELECT i2.InvoiceId FROM customers c2 
	INNER JOIN invoices i2 
	ON	c2.CustomerId =i2.CustomerId 
WHERE i2.CustomerId IN ('29','30','63')
	AND i2.Total >= 1.00 
	AND i2.Total <=3 ;
    
    
SELECT COUNT(country) AS The_Num_of_customers_X_Country  
FROM customers c 
GROUP BY Country  


SELECT Name , COUNT(CustomerId) FROM
(SELECT DISTINCT g.Name, i.CustomerId FROM invoices i
INNER JOIN invoice_items ii ON i.InvoiceId =ii.InvoiceId 
INNER JOIN tracks t ON ii.TrackId = t.TrackId 
INNER JOIN genres g ON t.GenreId = g.GenreId 
GROUP BY g.Name ,i.CustomerId)
GROUP BY Name;


SELECT CustomerId ,upper(City||" "||Country) as saero 
FROM customers c 


SELECT SUBSTR(LOWER(FirstName), 1, 4)||SUBSTR(LOWER(LastName), 1, 2)  
AS id_customer 
FROM customers ;


SELECT c.FirstName||c.LastName||i2.InvoiceId FROM invoices i2 
INNER JOIN customers c ON i2.CustomerId = c.CustomerId ;


SELECT t.Name FROM tracks t 
    INNER JOIN albums a2 on t.AlbumId = a2.AlbumId 
WHERE a2.Title IN	(SELECT Title FROM albums
					 WHERE Title ='Unplugged'
                     OR Title = 'Outbreak');

6. MongoDB

import csv
import os
from pymongo import MongoClient

host = 'host_name'
user = 'user_name'
password = 'password'
database_name = 'myFirstDatabase'
collection_name = 'segment'

MONGO_URI = f"mongodb+srv://{user}:{password}@{host}/{database_name}?retryWrites=true&w=majority"

connection = MongoClient(MONGO_URI)
db = connection.myFirstDatabase

csvfile = open(r'PATH/xxx.csv')
reader = csv.DictReader(csvfile)
header = ["Survived","Pclass","Name","Sex","Age","Siblings/Spouses Aboard","Parents/Children Aboard","Fare"]

for each in reader:
    row={}
    for field in header:
        row[field]=each[field]
    row['Survived'] = int(row['Survived'])
    row['Pclass']   = int(row['Pclass'])
    row['Name']     = str(row['Name'])
    row['Sex']      = str(row['Sex'])
    row['Age']      = float(row['Age'])
    row['Siblings/Spouses Aboard'] = int(row['Siblings/Spouses Aboard'])
    row['Parents/Children Aboard'] = int(row['Parents/Children Aboard'])
    row['Fare']     = float(row['Fare'])

    db.segment.insert_one(row)