[Python] Mysql DB 연결

---

from sqlalchemy import create_engine
import pymysql
import pandas as pd

pymysql.install_as_MySQLdb()

class DBConn_APIs:
    # DataFrame으로 데이터 Insert
    def InsertDB_DF(self, data_df, table_name, if_exists):
        try:
            user_id = '[DBMS 계정]'
            user_pw = '[DBMS 패스워드]'
            host = '[DBMS 호스트]'
            db = '[데이터베이스]'

            engine = create_engine("mysql+mysqldb://" + user_id + ":" + user_pw + "@" + host + "/" + db, encoding='utf-8')
            conn = engine.connect()

            data_df.to_sql(name=table_name, con=engine, if_exists=if_exists, index=False)

            conn.close()
        except Exception as e:
            print(e)

from sqlalchemy import create_engine
import pymysql
import pandas as pd

pymysql.install_as_MySQLdb()

class DBConn_APIs:
    # pymysql로 DB 연결
    def Conn_DB(self):
        try:
            db_conn = pymysql.connect(
                host='[DBMS 호스트]',
                port=[DB포트],
                user='[DBMS 계정]',
                passwd='[DBMS 패스워드]',
                db='[데이터베이스]',
                charset='utf8',
                cursorclass=pymysql.cursors.DictCursor
            )

            return db_conn

        except Exception as e:
            print(e)

    def Select_DB_DF(self, sql=''):
        db_con = self.Conn_DB()

        df = pd.read_sql(sql, db_con)

        db_con.close()

        return df



    def Select_DB(self, sql):
        try:
            db_con = self.Conn_DB()

            with db_con.cursor() as cursor:
                cursor.execute(sql)
                result = cursor.fetchall()

            db_con.close()

            return result

        except Exception as e:
            print(e)

    def Insert_DB(self, sql, data_tuple):
        try:
            db_con = self.Conn_DB()

            with db_con.cursor() as cursor:
                # sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
                cursor.execute(sql, data_tuple)

            db_con.commit()

            db_con.close()

        except Exception as e:
            print(e)

    def Update_DB(self, sql, data_tuple):
        try:
            db_con = self.Conn_DB()

            with db_con.cursor() as cursor:
                # sql = "UPDATE `users` SET `password` = %s WHERE `email` = %s"
                cursor.execute(sql, data_tuple)

            db_con.commit()

            db_con.close()

        except Exception as e:
            print(e)
            
    def Execute_SQL(self, sql):
        try:
            db_con = self.Conn_DB()

            with db_con.cursor() as cursor:
                # sql = "truncate table firewall_mgmt_qa.vpn_user_account_temp;"
                cursor.execute(sql)

            db_con.commit()

            db_con.close()

        except Exception as e:
            print(e)

---