import pymysql.cursors
from entity.shop import Shop


def getConnection():
    # Connect to the database
    connection = pymysql.connect(host='111.231.232.212',
                                 user='root',
                                 password='mysqlroot',
                                 db='rushticket',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    return connection


# query All Data
def queryAll(querySql):
    querySql = "select * from shop"
    return execute(querySql)


# query data by condition
def queryByCondition(condition):
    if not condition:
        return None

    queryByConditionSql = "select * from shop where id = " + str(condition)
    return execute(queryByConditionSql)


# insert data by object
def insert(obj):

    if not obj:
        return None

    try:
        connect = getConnection()
        with connect.cursor() as cursor:
            sql = "insert into ticket(secrtkey, `number`, trainCode, isMore, secondClass, firstClass, zeroClass, `time`) values (%s,%s,%s,%s,%s,%s,%s,%s)"
            res = cursor.execute(sql, (obj.secrtkey, obj.number, obj.trainCode, obj.isMore, obj.secondClass, obj.firstClass, obj.zeroClass, obj.time))
            connect.commit()
            return res
    except Exception as err:
        print(err)
        connect.rollback()
    finally:
        connect.close()


def execute(sql):
    if not sql:
        return None
    try:
        connection = getConnection()
        with connection.cursor() as cursor:
            cursor.execute(sql)
            # connection.commit()
            # fetch 是抽取数据的
            return cursor.fetchall()

    finally:
        connection.close()


if __name__ == '__main__':
    shop = Shop("songyinghui2","ssss", 2.9, "python测试", 1.98, "/img/song.jpg", "1")
    n = insert(shop)
    # result = queryByCondition(1)
    print("execute end")
    print(n)