redis-cli -h 12.345.678.99 -p 6379

select 2 # db번호
keys * # 2번 db에 들어있는 키를 모두 보여준다

set notice 24926 #키, 밸류 설정, 주어진 키에 하나의 값만 저장
"OK"
get notice #키로 밸류값 찾기
"24926"
SET anotherkey "will expire in a minute" EX 60 #1분 뒤 삭제되도록 설정
"OK"

class RedisConfig:
    config_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'config.ini')
    config = configparser.ConfigParser()
    config.read(config_path)

    server_name = config['DEFAULT']['SERVER_NAME']

    redis_intra = connect_redis_user(server_name)
    redis_info = config[redis_intra]

    REDIS_INFO = {
        'block_board': {
            "host": redis_info['host'], "port": redis_info['port'], "db": 12 # 차단 게시글
        }
    }

from configuration import redis_block_board

block_boards = redis_block_board.keys("*")

 if block_boards:
            block_boards_list = str(block_boards).strip('[,]')
            for block_board in block_boards:
                read_sql = f""" select user_seq from db_community.tb_board where board_seq = {block_board}"""
                cursor.execute(read_sql)
                row = cursor.fetchone()
                if user_seq != row['user_seq']:
                    sql += f""" and b.board_seq != {block_board}"""

import functools, time
from django.db   import connection, reset_queries
from django.conf import settings


def query_debugger(func):
    @functools.wraps(func)
    def wrapper(*args, **kwargs):
        reset_queries()
        number_of_start_queries = len(connection.queries)
        start  = time.perf_counter()
        result = func(*args, **kwargs)
        end    = time.perf_counter()
        number_of_end_queries = len(connection.queries) - 2 if len(connection.queries) else 0
        print(f"-------------------------------------------------------------------")
        print(f"Function : {func.__name__}")
        print(f"Number of Queries : {number_of_end_queries-number_of_start_queries}")
        print(f"Finished in : {(end - start):.2f}s")
        print(f"-------------------------------------------------------------------")
        return result
    return wrapper

from django.views           import View
from django.http            import JsonResponse
from django.db.models       import Prefetch, Count
from django.db.models.query import QuerySet

from .models      import Book, Store, Publisher
from decorators   import query_debugger

#################################
# Lazy Loading (지연 로딩)
#################################
class LazyLoadingCheckView(View):
    @query_debugger
    def get(self, request):
        # Lazy Loading - queryset1, queryset2, queryset3는 즉시 호출되지 않음.
        queryset = Publisher.objects.all()
        queryset2 = queryset.exclude(id=2).annotate(count=Count('book'))

        # Lazy Loading시 쿼리는 어디에 저장되어 있는가?
        print("queryset.query에 저장된 SQL문 :: ", queryset.query)
        print("queryset2.query에 저장되 SQL문 :: ", queryset2.query)
#
        # Queryset Evaluation - 실제로 db를 호출하는 시점 : Slicing, Iteration, repr(), len(), list(), bool() ..
        # Example 1. list(queryset3)
#        list(queryset)

        # Example 2. Iteration
#        for i in queryset2:
#            print(i.name)
#
        # Lazy Loading 때문에 발생하는 문제 - 매번 DB에 요청을 보낸다.
#        list(queryset2)
        list(queryset2)
        queryset2[0]
        queryset2[0]
        queryset2[0]
#
        return JsonResponse({'message' : 'SUCCESS' }, status=200)



#############################
# Caching
#############################
class CachingCheckView(View):
    @query_debugger
    def get(self, request):
        queryset = Publisher.objects.all().annotate(count=Count('book'))


        # queryset이 평가될 때, data가 caching되지 않는 경우
#        print("before queryset._result_cache :: ", queryset._result_cache)
#        queryset[0]
#        print("after queryset._result_cache :: ", queryset._result_cache)
#        queryset[0]
#        print("after queryset._result_cache :: ", queryset._result_cache)
#        queryset[0]

        # queryset이 평가될 때, data가 caching 되는 경우        
        # queryset이 평가될 때, 값을 QuerySet._result_cache에 저장한다.
        print("before queryset._result_cache :: ", queryset._result_cache)
        for publisher in queryset:
            a = publisher.id
        print("after queryset._result_cache :: ", queryset._result_cache)
        
        queryset[0]
        queryset[0]
        queryset[0]
        queryset[0]

        return JsonResponse({'message' : 'SUCCESS' }, status=200)


#############################
# N + 1 Problems
#############################
class BooksWithAllMethodView(View):
    @query_debugger
    def get(self, request):
        print('Book에서 Publisher Instance에 접근하는 경우 <정참조>')
        # Publisher(One) - Book(Many)

        queryset = Book.objects.all()
        books    = []

        # QuerySet이 평가(Evaluation)될 때, N + 1 Problems 발생
        # 모든 book을 조회하는 SQL 1번 실행
        # book 하나당 publisher를 매번 조회하는 SQL N번 실행
        for book in queryset:
            books.append({
                'id': book.id,
                'name': book.name,
                'publisher': book.publisher.name # book.publisher에 접근, 캐싱되지 않은 데이터이므로 query 발생
                }
            )

        """
        SELECT * FROM books ; 1번
        SELECT `publishers`.`id`, `publishers`.`name` FROM `publishers` WHERE `publishers`.`id` = 19
        SELECT `publishers`.`id`, `publishers`.`name` FROM `publishers` WHERE `publishers`.`id` = 20
        SELECT `publishers`.`id`, `publishers`.`name` FROM `publishers` WHERE `publishers`.`id` = 21
        """

        return JsonResponse({'books_with_all_method' : books }, status=200)


########################################
# Eager Loading (select_related)
#######################################
class BooksWithSelectRelatedView(View):
    @query_debugger
    def get(self, request):
        queryset = Book.objects.all().select_related("publisher")
        print("queryset.query에 저장된 SQL문 :: ", queryset.query)

        books = []

        for book in queryset:
            books.append({
                'id': book.id,
                'name': book.name,
                'publisher': book.publisher.name
                }
            )

        return JsonResponse({'books_with_all_method' : books }, status=200)



#############################
# N + 1 Problems
#############################
class StoresWithAllMethodView(View):
    @query_debugger
    def get(self, request):
        print(f'Store에서 Book Instance에 접근하는 경우 <역참조>')
        queryset = Store.objects.all()
        stores   = []

        for store in queryset:
            books = [book.name for book in store.books.all()]
            stores.append({
                'id': store.id,
                'name': store.name,
                'books': books
                }
            )
        """
        SELECT * FROM store ; 1번
        SELECT `publishers`.`id`, `publishers`.`name` FROM `publishers` WHERE `publishers`.`id` = 19
        SELECT `publishers`.`id`, `publishers`.`name` FROM `publishers` WHERE `publishers`.`id` = 19
        SELECT `publishers`.`id`, `publishers`.`name` FROM `publishers` WHERE `publishers`.`id` = 19
        SELECT `publishers`.`id`, `publishers`.`name` FROM `publishers` WHERE `publishers`.`id` = 19
        SELECT `publishers`.`id`, `publishers`.`name` FROM `publishers` WHERE `publishers`.`id` = 19
        """

        return JsonResponse({'stores_with_all_method' : stores }, status=200)


########################################
# Eager Loading (prefetch_related)
#######################################
class StoresWithPrefetchRelatedView(View):
    @query_debugger
    def get(self, request):
        queryset = Store.objects.all().prefetch_related("books")
        print("queryset.query에 저장된 SQL문 :: ", queryset.query)
        print("final after queryset._result_cache :: ", queryset._result_cache)
        print("final after queryset._prefetch_related_lookups :: ", queryset._prefetch_related_lookups)

        stores = []

        for store in queryset:
            books = [book.name for book in store.books.all()]
            stores.append({
                'id': store.id,
                'name': store.name,
                'books': books
            })

        print("!!!! result_cache :: ", queryset._result_cache)

#        stores2 = []
#
#        for store in queryset:
#            books = [book.name for book in store.books.all()]
#            stores2.append({'id': store.id, 'name': store.name, 'books': books})
#
        return JsonResponse({'stores_with_prefetch_related' : stores }, status=200)


##################################################
# Eager Loading (prefetch_related ) when filtering
##################################################
class StoresWithPrefetchNoneObjectView(View):
    @query_debugger
    def get(self, request):
        queryset = Store.objects.all().prefetch_related("books")

        stores = []

        for store in queryset:
            total_books    = [book.name for book in store.books.all()]
            filtered_books = [book.name for book in store.books.filter(name='Book9991')]
            stores.append({
                'id'          : store.id,
                'name'        : store.name,
                'total_books' : total_books,
                'filterd_books' : filtered_books
            })

        return JsonResponse({'stores_with_prefetch_related' : stores }, status=200)


##################################################
# Eager Loading (prefetch_related ) when filtering
##################################################
class StoresWithPrefetchObjectView(View):
    @query_debugger
    def get(self, request):
        queryset = Store.objects.prefetch_related(
            Prefetch('books', queryset=Book.objects.all(), to_attr='total_books'),
            Prefetch('books', queryset=Book.objects.filter(name='Book9991'), to_attr='filtered_books'),
        )

        print("queryset.query에 저장된 SQL문 :: ", queryset.query)
        print("final after queryset._result_cache :: ", queryset._result_cache)
        print("final after queryset._prefetch_related_lookups :: ", queryset._prefetch_related_lookups)

        stores = []

        for store in queryset:
            total_books    = [book.name for book in store.total_books]
            filtered_books = [book.name for book in store.filtered_books]
            stores.append({
                'id'          : store.id,
                'name'        : store.name,
                'total_books' : total_books,
                'filterd_books' : filtered_books
            })

        return JsonResponse({'stores_with_prefetch_related' : stores }, status=200)

#config > config.ini
[TEST_DOCUMENT_DB]
HOST = 15.123.456.78
PORT = 12345
ID = lunayyko
PASSWORD = claire1234

#config > config_doc_db.py
import os
import configparser
from config.direction import connect_doc_db

config_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'config.ini')
config = configparser.ConfigParser()
config.read(config_path)
server_name = config['DEFAULT']['SERVER_NAME']


class DocumentDB:
    config_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'config.ini')
    config = configparser.ConfigParser()
    config.read(config_path)

    doc_db = connect_doc_db(server_name)
    doc_db_info = config[doc_db]

    DOC_DB_INFO = {
        "host": doc_db_info['HOST'],
        "port": int(doc_db_info['PORT']),
        "username": doc_db_info["ID"],
        "password": doc_db_info["PASSWORD"]
    }

#api 파일
from config.config_doc_db import DocumentDB as con_DocDB

def apifile(conn_mongo):
    doc_db = conn_mongo.user # db이름 user
    success = save_doc_db(db='community', table='board', key='board_seq', args=result, method='POST')
    return True

def save_doc_db(db, table, key, args, method):
    database = conn_mongo[db]
    collection = database[table]

    if method == 'DELETE':
        collection.delete_one({"_id": args[key]})
        print('doc_db 삭제 성공')
        return
    elif method == 'POST':
        post_id = collection.insert_one(doc_dict)
        print(f'doc_db {table} 저장 성공', post_id)
    else:
        post_id = collection.save(doc_dict)
        print(f'doc_db {table} 수정 성공', post_id)

    return True

if __name__ == '__main__':
    conn_mongo = MongoClient(**con_DocDB.DOC_DB_INFO, **{'retryWrites': False})

#시리즈 형태
df['english']

#데이터프레임 형태
df[['english']]

df.loc[]
df.loc[df['math'] >= 80]

for user in user_df['user_seq']:
    user_df['notification_yn'] = weekend_night_off(user)

device_token_list = user_df['device_token'].tolist()
# None 제거
device_token_list = [x for x in device_token_list if x is not None]
# 중복제거
device_token_list = list(dict.fromkeys(device_token_list))

sql = 'select board_seq, user_seq from board'

board_df = pd.read_sql(sql, conn)
board_list = board_df.to_dict(orient='records') #1번
board_list = board_df['board_seq'].tolist() #2번

import pymysql
import pandas as pd
from config.config_db  import DataBaseConfig as con_DB
from configuration     import server_name, send_slack

def target_price_check(conn, conn_finance):
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = f"""
            select a.board_seq, a.symbol, a.target_price
              from db_community.tb_board_best a inner join db_community.tb_board b
                                                        on a.board_seq = b.board_seq
                                                       and b.del_yn = 0
             where a.is_accuracy = 0 
               and a.target_price is not null and a.target_price != 0 
          """
    data_df = pd.read_sql(sql, conn)
    args_list = data_df.to_dict(orient='records')
    symbol_list = [ args['symbol'] for args in args_list ]
    # print('symbol_list', symbol_list)

    cursor = conn_finance.cursor(pymysql.cursors.DictCursor)
    finance_sql = f"""
            select symbol, adj_low_prc, adj_close_prc, adj_high_prc
              from dg_db_refine_daily.tb_price_tango_data 
             where trd_dt = date_format(DATE_ADD(now(), INTERVAL -1 DAY), '%Y%m%d')
               and symbol in ({str(symbol_list).strip('[]')})			
        """
    finance_df = pd.read_sql(finance_sql, conn_finance)
    # print('finance_df', finance_df)

    df = pd.merge(data_df, finance_df, how='left')
    # print('df', df)

    true_df = df.loc[(df['adj_low_prc'] < df['target_price']) & (df['target_price'] < df['adj_high_prc'])]
    # print('true_df', true_df)

    if true_df.empty:
        send_slack(f'목표주가 달성 게시글 없음', f' tangopick_{server_name}_accuracy')
    else:
        board_list = true_df['board_seq'].tolist()
        # print('board_list', board_list)

        cursor = conn.cursor(pymysql.cursors.DictCursor)
        update_sql = f"""
            update db_community.tb_board_best
               set is_accuracy = 1, accuracy_dt = now()
             where board_seq in ({str(board_list).strip('[]')})
        """
        cursor.execute(update_sql)
        conn.commit()
        # board_list = list(map(int, board_list))
        for board_seq in board_list:
            row = df.loc[df['board_seq'] == board_seq]
            send_slack(f' 목표달성글 : https://{""if server_name == "prod" else "dev."}tangopick.co.kr/community/1/board/{board_seq} \n'
                       f' {row} ',
                       f' tangopick_{server_name}_accuracy')

    return True

if __name__ == '__main__':
    conn = pymysql.connect(**con_DB.tangopick_db_config(read=False)[0])
    conn_finance = pymysql.connect(**con_DB.finance_db_config(read=False))
    target_price_check(conn, conn_finance)
    conn.close()
    conn_finance.close()

def save_insert_query(df, table_nm, db_name):
    df = df.where(pd.notnull(df), None)
    rows = 0

    before_sql = f"""
            select user_seq, nickname, sub_cnt, grade
              from db_user.tb_user_grade
             where grade != 'influencer'
               and nickname != '탱고픽'
        """
    df_before = pd.read_sql(before_sql, conn_tangopick)

    columns = list(df.columns)
    header_sql = f"""insert into {db_name}.{table_nm}
                     ({str(columns).strip('[]').replace("'", '`')}) 
                     values 
                   """
    body_sql = str(['%s' for i in columns]).replace("[", "(").replace("]", ")").replace("'", "")
    key_placeholders = ', '.join(['`{0}`=VALUES(`{0}`)'.format(k) for k in columns])
    footer_sql = f" on duplicate key update {key_placeholders}"

    total_query = header_sql + body_sql + footer_sql

    cursor = conn_tangopick.cursor()
    affected_rows = cursor.executemany(total_query, df.values.tolist())
    conn_tangopick.commit()

    compare_result(df_before)

    return affected_rows


def compare_result(df_before):
    after_sql = f"""
            select user_seq, nickname, sub_cnt, grade
              from db_user.tb_user_grade
             where grade != 'influencer'
               and nickname != '탱고픽'
        """
    df_after = pd.read_sql(after_sql, conn_tangopick)

    print('df_before[grade]', df_before['grade'])
    print('df_before[grade] == df_after[grade]', df_before['grade'] == df_after['grade'])
    print('df_before[(df_before[grade] == df_after[grade]) == False', df_before[(df_before['grade'] == df_after['grade']) == False])

    before = df_before[(df_before['grade'] == df_after['grade']) == False]
    after = df_after[(df_before['grade'] == df_after['grade']) == False]
    num = len(df_after[(df_before['grade'] == df_after['grade']) == False])

    if ((datetime.now().hour == 10 and datetime.now().minute == 00) or (datetime.now().hour == 11 and datetime.now().minute == 00) or
        (datetime.now().hour == 13 and datetime.now().minute == 00) or (datetime.now().hour == 14 and datetime.now().minute == 00) or
        (datetime.now().hour == 15 and datetime.now().minute == 00) or (datetime.now().hour == 16 and datetime.now().minute == 17) or
        (datetime.now().hour == 17 and datetime.now().minute == 00) or (datetime.now().hour == 18 and datetime.now().minute == 00)):
        if after.empty:
            send_slack(f'유저등급조정없음', f' tangopick_{server_name}_grade')
        else:
            send_slack(f' 유저등급조정 : {num}명 조정, \n'
                       f' 조정전: {before[["user_seq", "nickname", "grade"]]}, \n'
                       f' 조정후: {after[["user_seq", "nickname", "grade"]]}',
                       f' tangopick_{server_name}_grade')
    return True

*/10 * * * * docker exec user python /home/ubuntu/app/batch/v3_1/batch_reserve_publish.py > /home/ubuntu/workspace/user/src/log/batch_reserve_publish_log.log 2>&1
#10분마다 계속 실행

0-59 9-23 * * 1-5 docker exec create_engine_v2 python /home/ubuntu/app/process_tool/v3_1/realtime/idea_update_period.py
#9시부터 11시까지 월-금 매분 실행

# target_accuracy
0 8 30 * * 2-6 docker exec user python /home/ubuntu/app/batch/v3_2/batch_price_accuracy.py > /home/ubuntu/app/batch/v3_2/batch_price_accuracy.log 2>&1
#화-토 매일 8시30분에 실행

import pymysql
from datetime             import datetime
from config.config_db     import DataBaseConfig as con_DB
from configuration        import server_name, send_slack

def delete_temp(conn, now):
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = f"""
        update db_community.tb_board_draft
           set del_yn = 1, del_dt = '{now}'
         where reg_dt < DATE_ADD('{now}', INTERVAL -60 DAY)
    """
    affected_rows = cursor.execute(sql)

    assert affected_rows != -1, '쿼리 실행에 실패하였습니다'

    if affected_rows != 0:
        send_slack(f' 60일 지난 임시저장 게시글 {affected_rows}개가 삭제되었습니다 \n',
                   f' tangopick_{server_name}_temp')
    else:
        send_slack(f' 삭제한 임시저장 글이 없습니다 \n',
                   f' tangopick_{server_name}_temp')


if __name__ == '__main__':
    conn = pymysql.connect(**con_DB.tangopick_db_config(read=False)[0])

    now = datetime.now()
    now_str = now.strftime('%Y-%m-%d %H:%M:00')
    now = datetime.strptime(now_str, '%Y-%m-%d %H:%M:00')

    delete_temp(conn, now)
    conn.commit()
    conn.close()