# import MySQLdb
import os

import pymysql.cursors

_HOSTNAME="127.0.0.1"
_DATABASE="listing_td_all"
_USERNAME="root"
_PASSWORD="root"

def sql_execute(sql_cmd):
    # Connect to the database
    connection = pymysql.connect(host=_HOSTNAME,
                                 user=_USERNAME,
                                 password=_PASSWORD,
                                 db=_DATABASE,
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql_cmd)
            connection.commit()
            if (sql_cmd.startswith(('CREATE','SELECT', 'SHOW'))):
                return cursor.fetchall()
    except Exception as err:
        connection.rollback()
        print(err)
    connection.close()

def sql_insert(sql_cmd, params):
    # Connect to the database
    connection = pymysql.connect(host=_HOSTNAME,
                                 user=_USERNAME,
                                 password=_PASSWORD,
                                 db=_DATABASE,
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql_cmd,params)
            connection.commit()
    except Exception as err:
        connection.rollback()
        print(err)
    # if not (sql_cmd.startswith(('INSERT','UPDATE','DELETE'))):
    #     raise MySQLdb.Error('Query error! It must be insert or update query.')
    last_row_id = connection.insert_id()
    connection.close()
    return last_row_id

def sql_execute_with_param(sql_cmd, params):
    # Connect to the database
    connection = pymysql.connect(host=_HOSTNAME,
                                 user=_USERNAME,
                                 password=_PASSWORD,
                                 db=_DATABASE,
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql_cmd,params)
            connection.commit()
            if (sql_cmd.startswith(('CREATE','SELECT', 'SHOW'))):
                return cursor.fetchall()
    except Exception as err:
        connection.rollback()
        print(err)
    connection.close()