energy-data-trans/utils/db/trans_mysql.py

# -*- coding: utf-8 -*-
# @Time    : 2024/5/15
# @Author  : 魏志亮

# 建立数据库连接
import pymysql
from sqlalchemy import create_engine

from utils.log.trans_log import trans_print

user = 'admin'
password = 'admin123456'
host = '192.168.50.233'
port = 3306
database = 'energy_data'
plt_conn = pymysql.connect(
    host=host,
    port=3306,
    user=user,
    password=password,
    database=database,
    charset='utf8mb4'
)

engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:{port}/{database}', echo=True)

susscess_sql = "update batch_status set status = 'success' where batch_no = '{batch_no}' and trans_type = '{trans_type}'";

error_sql = "update batch_status set status = 'error',message='{message}' where batch_no = '{batch_no}'  and trans_type = '{trans_type}'"


# def __query(sql):
#     trans_print('开始执行SQL:',sql)
#     plt_conn.ping(reconnect=True)
#     with plt_conn.cursor() as cursor:
#         df = pd.read_sql(sql, cursor)
#     return df
#
#
# def __ddl_sql(sql):
#     trans_print('开始执行SQL:',sql)
#     plt_conn.ping(reconnect=True)
#     with plt_conn.cursor() as cursor:
#         cursor.execute(sql)
#     plt_conn.commit()


def update_transe_status(batch_no, trans_type, status, message):
    exec_sql = susscess_sql if status == 'success' else error_sql
    exec_sql = exec_sql.format(batch_no=batch_no, status=status, message=message, trans_type=trans_type)
    #
    # plt_conn.ping(reconnect=True)
    # with plt_conn.cursor() as cursor:
    #     cursor.execute(exec_sql)
    # plt_conn.commit()
    trans_print(exec_sql)


# def insert_data(batch_no, type, status, message):
#     exec_sql = insert_sql.format(batch_no=batch_no, type=type, status=status, message=message)
#     plt_conn.ping(reconnect=True)
#     with plt_conn.cursor() as cursor:
#         cursor.execute(exec_sql)
#
#     plt_conn.commit()


def create_table(batch_no, date_list=list(), fengji_list=list()):
    pass


def get_exec_data():
    query_running_sql = "selecgt 1 from table where status = 'running"
    query_next_exdc_sql = "selecgt 1 from table where status = 'waiting' order by id "
    trans_print(query_next_exdc_sql)
    # df = __query(query_running_sql)
    # if df.empty:
    #     df = __query(query_next_exdc_sql)
    #     if df.empty:
    #         return None
    #     else:
    #         return df.iloc[0]