import pymysql

ip_ad="106.15.1.178"

db = pymysql.connect(host=ip_ad,
                     user="root",
                     password="root",
                     port=3306,  # 端口
                     charset='utf8')

cursor = db.cursor()

def create_db():
    db = pymysql.connect(host=ip_ad,
                         user="root",
                         password="root",
                         #port=3306,  # 端口
                         #charset='utf8')
                         )

    cursor = db.cursor()
    # 使用 execute() 方法执行 SQL，如果表存在则删除
    #cursor.execute("DROP TABLE IF EXISTS pose")

    # 使用预处理语句创建表
    cursor.execute("create database if not exists pose")
    db.commit()
    db.close()
    cursor.close()
def create_table():
    db = pymysql.connect(host=ip_ad,
                         user="root",
                         password="root",
                         port=3306,  # 端口
                         charset='utf8',
                         database="pose")

    cursor = db.cursor()


    sql = """CREATE TABLE pose(
             id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
             status INT)
             """
    insert_table_sql = """INSERT INTO `pose` (`status`) VALUES ('0')"""

    cursor.execute(sql)
    db.commit()
    cursor.execute(insert_table_sql)
    version = cursor.fetchone()
    print(version)
    db.commit()
    db.close()
def create_table_dynamic():
    db = pymysql.connect(host=ip_ad,
                         user="root",
                         password="root",
                         port=3306,  # 端口
                         charset='utf8',
                         database="pose")

    cursor = db.cursor()

    sql = """CREATE TABLE video(
             id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
             status INT,
             mode VARCHAR(20),
             photo1 INT,
             photo2 INT,
             photo3 INT)
             """
    insert_table_sql = """INSERT INTO `video` (`status`,`mode`,`photo1`,`photo2`,`photo3`) VALUES ('0','jump','-1','-1','-1')"""

    cursor.execute(sql)
    db.commit()
    cursor.execute(insert_table_sql)
    version = cursor.fetchone()
    print(version)
    db.commit()
    db.close()
def add_new_data():
    db = pymysql.connect(host=ip_ad,
                         user="root",
                         password="root",
                         port=3306,  # 端口
                         database="pose",
                         charset='utf8')

    cursor = db.cursor()

    insert_table_sql="""INSERT INTO `pose` (`status`) VALUES ( '0')"""
    cursor.execute(insert_table_sql)
    db.commit()
    db.close()
    cursor.close()
    return_id=cursor.lastrowid
    return return_id

def add_new_data_dyna(modes):
    db = pymysql.connect(host=ip_ad,
                         user="root",
                         password="root",
                         port=3306,  # 端口
                         database="pose",
                         charset='utf8')

    cursor = db.cursor()

    insert_table_sql="""INSERT INTO `video` (`status`,`mode`) VALUES ( '%d','%s')""" %(0,modes)
    cursor.execute(insert_table_sql)
    db.commit()
    db.close()
    cursor.close()
    return_id=cursor.lastrowid
    return return_id

def lookup_data(id):
    db = pymysql.connect(host=ip_ad,
                         user="root",
                         password="root",
                         port=3306,  # 端口
                         database="pose",
                         charset='utf8')
    cursor = db.cursor()
    sql="select mode from video where id=%d"  %id
    cursor.execute(sql)
    result=cursor.fetchone()
    db.commit()
    cursor.close()
    db.close()
    return result[0]
def process_db(id):
    db = pymysql.connect(host=ip_ad,
                         user="root",
                         password="root",
                         port=3306,  # 端口
                         database="pose",
                         charset='utf8')
    sql = "update video set status = 1 where id = %d " %id

    cursor = db.cursor()
    cursor.execute(sql)

    db.commit()
    cursor.close()
    db.close()

def get_status(id):
    db = pymysql.connect(host=ip_ad,
                         user="root",
                         password="root",
                         port=3306,  # 端口
                         database="pose",
                         charset='utf8')
    sql="select status from video where id =%d"%id
    cursor=db.cursor()
    cursor.execute(sql)
    result=cursor.fetchone()
    cursor.close()
    db.close()

    return result
if __name__ == '__main__':
    print(add_new_data_dyna("jump"))