SQL, Docker-compose, Python module

Set up the requirement and docker-compose.

 

1. Create requirement.txt file.

pandas==1.2.4
mysql-connector-python==8.0.28

 

2. Create Docker file.

FROM continuumio/miniconda3

WORKDIR /usr/app

RUN mkdir result

RUN mkdir src

EXPOSE 8888

RUN pip install --no-cache-dir --upgrade pip

COPY ./requirements.txt .

RUN pip install -r requirements.txt

RUN pip install jupyter

RUN jupyter notebook --generate-config --allow-root

ENTRYPOINT jupyter notebook --allow-root --ip=0.0.0.0 --port=8888 --no-browser

 

3. Create docker-compose.yml file.

There are three different containers-mysql, jupyter, adminer. All must connect with same networks.

All environment set up inside docker-compose.

version: '3.1'

services:
  db:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: jh
      MYSQL_DATABASE: DB_SQLSTK
    volumes: 
      - ./DB_SQLSTK_FULL_FOR_BOOK_20210521.sql:/docker-entrypoint-initdb.d/schema.sql:ro
    ports:
      - 3306:3306 
    networks:
      - sql

  jupyter:
    build:
      context: .
    ports:
      - 8888:8888
    volumes:
      - ./result:/usr/app/result
      - ./src:/usr/app/src
    networks:
      - sql
    environment: ## Set up here instead of create .env file
      - SQL_HOST=db
      - SQL_PORT=3306
      - SQL_USER=root
      - SQL_PASSWORD=jh
      - SQL_DATABASE=DB_SQLSTK
      
  adminer: ## Web app that connects to sql database
    image: adminer
    ports:
      - 8080:8080
    networks:
      - sql

networks:
  sql:

Seperate mono code into modules.

 

1. Config module.

Create the config module and store config values in the module.

import os

SQL_HOST=os.getenv('SQL_HOST')
if SQL_HOST==None:
    SQL_HOST='http://localhost'

SQL_PORT=os.getenv('SQL_PORT')
if SQL_PORT==None:
    SQL_PORT=3306
else:
    SQL_PORT=int(SQL_PORT)
    
SQL_USER=os.getenv('SQL_USER')
if SQL_USER==None:
    SQL_USER='root'
    
SQL_PASSWORD=os.environ.get('SQL_PASSWORD')
if SQL_PASSWORD==None:
    SQL_PASSWORD='123'
    
SQL_DATABASE=os.environ.get('SQL_DATABASE')
if SQL_DATABASE==None:
    SQL_DATABASE='db'

SQL_CONFIG={
    'host': SQL_HOST,
    'port': SQL_PORT,
    'user': SQL_USER,
    'password': SQL_PASSWORD,
    'database': SQL_DATABASE
}
print(SQL_CONFIG)

 

2. SQL server connection module.

import mysql.connector
from config import SQL_CONFIG


class SQLconnector:
    def __init__(self):
        print('creating')
        self.mydb=None
        
    def connect(self):
        
        mydb=mysql.connector.connect(host=SQL_CONFIG['host'],port=SQL_CONFIG['port'],
                                     user=SQL_CONFIG['user'],password=SQL_CONFIG['password'],database=SQL_CONFIG['database'])
        print(mydb)
        

        self.mydb=mydb

    def isConnected(self):
        if self.mydb is not None:
            return True
        else:
            return False
        
    def getConnection(self):
        if not self.isConnected():
            self.connect()
        return self.mydb.cursor(buffered=True)

 

3. Query and receive the data module.

from sql_connection import SQLconnector
import pandas as pd
import numpy as np
import mysql.connector
from config import SQL_CONFIG

connector=SQLconnector()
cursor=connector.getConnection()

query=("SELECT STK_CD,STK_NM FROM stock LIMIT 10")
cursor.execute(query)
for (STK_CD, STK_NM) in cursor:
    print(STK_CD, 
          STK_NM)
          
>>>
000020 동화약품
000040 KR모터스
000050 경방

...

query="SELECT STK_CD, STK_NM FROM stock WHERE SEC_NM=%s"
cursor.execute(query, ['증권'])
result_lst=[]
for STK_CD ,STK_NM in cursor:
    result=[STK_CD ,STK_NM]
    result_lst.append(result)
result_lst

>>>
[['001200', '유진투자증권'],
 ['001270', '부국증권'],
 ['001290', '상상인증권'],
 
...

df=pd.DataFrame(result_lst, columns=['STK_CD','STK_NM'])
df

cursor.close()