Be great

seaborn 에서 clustermap에 color label 입히기.

condition_labels = df_data.condition
condition_uniq = sorted(condition_labels.unique())
condition_pal = sns.color_palette('husl',len(condition_uniq))
condition_lut = dict(zip(map(str, condition_uniq), condition_pal))
condition_colors = pd.Series(condition_labels).map(condition_lut)

tnm_labels = df_data.tnm
tnm_uniq = sorted(tnm_labels.unique())
tnm_pal = sns.color_palette('Paired',len(tnm_uniq))
tnm_lut = dict(zip(map(str, tnm_uniq), tnm_pal))
tnm_lut['NA']=(1,1,1)
tnm_colors = pd.Series(tnm_labels).map(tnm_lut)

age_labels = df_data.age
age_uniq = sorted(age_labels.unique())
age_pal = sns.color_palette('flare',len(age_uniq))
age_lut = dict(zip(map(str, sorted(age_labels.unique())), age_pal))
age_lut['NA']=(1,1,1)
age_colors = pd.Series(age_labels).map(age_lut)

condition_node_colors = pd.DataFrame(condition_colors).join(pd.DataFrame(tnm_colors)).join(pd.DataFrame(age_colors))
plt.figure(figsize=(100,120))
g = sns.clustermap(df_data.iloc[:,3:].T, cmap="vlag", col_colors = condition_node_colors)

for label in tnm_uniq:
    g.ax_col_dendrogram.bar(0, 0, color=tnm_lut[label], label=label, linewidth=10)
l2 = g.ax_col_dendrogram.legend(title='tnm', loc='center', ncol=2, bbox_to_anchor=(0.65, 1.05), bbox_transform=gcf().transFigure)
xx = []
for label in condition_uniq:
    x = g.ax_col_dendrogram.bar(0, 0, color=condition_lut[label], label=label, linewidth=10)
    xx.append(x)
#l1 = g.ax_col_dendrogram.legend(title='condition', loc='center', ncol=2, bbox_to_anchor=(0.2, 1.05), bbox_transform=gcf().transFigure)
legend3 = plt.legend(xx, condition_uniq, loc='center', title='condition', ncol=2, bbox_to_anchor=(0.35, 1.05), bbox_transform=gcf().transFigure)
yy = []
for label in age_uniq:
    y = g.ax_col_dendrogram.bar(0, 0, color=age_lut[label], label=label, linewidth=10)
    yy.append(y)
#l1 = g.ax_col_dendrogram.legend(title='condition', loc='center', ncol=2, bbox_to_anchor=(0.2, 1.05), bbox_transform=gcf().transFigure)
legend4 = plt.legend(yy, age_uniq, loc='center', title='age', ncol=5, bbox_to_anchor=(0.5, 1.15), bbox_transform=gcf().transFigure)

app.py

from database import db_session
from models import employees
from flask import Flask, Response, request
import pandas as pd
import json
import datetime

app = Flask(__name__)

@app.route('/employees/select', methods=['GET'])
def select():
        queryset = employees.query.limit(5)
        print(queryset)
        df = pd.read_sql(queryset.statement, queryset.session.bind)
        print(df)
        return Response(df.to_json(orient="records"), mimetype='application/json')

@app.route('/employees/insert', methods=['POST'])
def insert():
        emp_no = request.args.get('emp_no', default = 1, type = int)
        birth_date = request.args.get('birth_date', default = '9999-01-01')
        first_name = request.args.get('first_name', default='Gil-Dong', type=str)
        last_name = request.args.get('last_name', default='Hong', type=str)
        gender = request.args.get('gender', default='M')
        a = employees(emp_no, birth_date, first_name, last_name, gender)
        db_session.merge(a)
        db_session.commit()
        return 'done\n'

app.run(debug=True)

models.py

from sqlalchemy import Column, Integer, String, DateTime
from database import Base
import datetime

class employees(Base):
        __tablename__ = 'employees'
        emp_no = Column(Integer, primary_key=True)
        birth_date = Column(DateTime)
        first_name = Column(String)
        last_name = Column(String)
        gender = Column(String)
        hire_date = Column(DateTime)

        def __init__(self, emp_no, birth_date, first_name, last_name, gender):
                self.emp_no = emp_no
                self.birth_date = birth_date
                self.first_name = first_name
                self.last_name = last_name
                self.gender = gender
                self.hire_date =  datetime.date.today().strftime("%y-%m-%d")

        def __repr__(self):
                return f'{self.emp_no} : {self.first_name}'

database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('mysql+mysqlconnector://root:0000@localhost/employees?charset=utf8', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
    # import all modules here that might define models so that
    # they will be registered properly on the metadata.  Otherwise
    # you will have to import them first before calling init_db()
    import models
    Base.metadata.create_all(bind=engine)

#2022-05-27 재작성

single node 기준. (서버와 노드가 하나의 컴퓨터)

slurm, munge 데몬 설치.

sudo dnf install slurm slurm-slurmd slurm-slurmctld munge

slurm 계정 및 그룹 생성. 

groupadd -g 900 slurm
useradd  -m -c "SLURM workload manager" -d /var/lib/slurm -u 900 -g slurm -s /sbin/nologin slurm

sudo chown slurm:slurm -R /var/spool/slurm/

munge key 생성

sudo /usr/sbin/create-munge-key -r

필요에 따라 conf 파일을 수정해야함. 위치는 /etc/slurm/slurm.conf

SlurmUser=slurm
SlurmctldLogFile=/var/log/slurmctld.log
SlurmdLogFile=/var/log/slurmd.log

로그 파일의 수정 권한을 SlurmUser가 가지고 있어야함.

foreground에서 slurmctld 데몬 실행해서 에러가 나오는지 확인.

sudo slurmctld -D

정상적으로 작동되는지 확인.

sudo systemctl start slurmctld
sudo systemctl enable slurmctld
sudo systemctl status slurmctld

여러 사람이 동시에 편집해서 사용할 수 있는 구글 스프레드시트에 데이터 베이스를 연동하면 접근성이 좋아짐.

단점은 스프레드시트의 appscript에서 각 함수가 실행 되는 시간을 max 30분으로 지정했는데

데이터의 양이 많아지면 time out으로 강제 종료 될 수 있음.

var connectionName = 'database_address:3306'; // 접속할 MySQL 서버의 IP와 Port(Default:3306)
var user = 'userID'; // MySQL 유저 ID
var userPwd = 'password'; // MySQL 유저 PW
var db = 'db_name'; // 접속할 MySQL DB명
var instanceUrl = 'jdbc:mysql://' + connectionName;
var dbUrl = instanceUrl + '/' + db + '?characterEncoding=UTF-8';

function exportDatabase() {
  query="select * from table"
  var start = new Date();
  var conn = Jdbc.getConnection(dbUrl, user, userPwd); // DB 연결
  var stmt = conn.createStatement();
  stmt.setMaxRows(5000);
  var results = stmt.executeQuery(query); // 쿼리
  var metaData = results.getMetaData()
  var numCols = metaData.getColumnCount();
  var sheetname = SpreadsheetApp.getActive();
  var sheet = sheetname.getSheetByName('sheet1');
  sheet.clearContents();

  var arr = [];
  for (var col = 0; col < numCols; col++) {
    arr.push(metaData.getColumnName(col + 1));
  }
  sheet.appendRow(arr); #write header

  while (results.next()) {
    arr=[];
    for (var col = 0; col < numCols; col++) {
    arr.push(results.getString(col + 1));
    } 
    sheet.appendRow(arr); #write data line by line
  }

  var end = new Date();
  Logger.log("Time spend : "+((end - start)/(1000*60) % 60).toFixed(3)+" min");
  //sheet.autoResizeColumns(1, numCols+1);

  results.close();
  stmt.close();
}

./configure --enable-loadable-sqlite-extensions

configure와 make까지 진행했을 때 설치를 더 진행해도 되지만 아래 메세지를 확인하고 설치가 안되는 모듈이 있음을 확인해야함.

Python build finished successfully!
The necessary bits to build these optional modules were not found:
_tkinter                                                       
To find the necessary bits, look in setup.py in detect_modules() for the module's name.

환경 변수를 설정했음에도 _sqlite3 가 지속적으로 보여서 확인해보니 setup.py 파일에서 직접 수정을 해야 했음.

sqlite_incdir = sqlite_libdir = None
sqlite_inc_paths = [ '/usr/include']