import pymysql.cursors
import os
import configparser as cparser
'''
base_dir= str(os.path.dirname(os.path.dirname(__file__)))
base_dir=base_dir.replace('\\','/')
file_path=base_dir+"/db_config.ini"

cf=cparser.ConfigParser()

cf.read(file_path)
host=cf.get("mysqlconf","host")
port=cf.get("mysqlconf","port")
db=cf.get("mysqlconf","db_name")
user=cf.get("mysqlconf","user")
password=cf.get("mysqlconf","password")
'''
class DB:
    def __int__(self):
        try:
            #connect to the database
            self.connection=pymysql.connect(host='127.0.0.1',
                                            user='root',
                                            password='123456',
                                            db='mysql',
                                            port=3306,
                                            charset='utf8mb4',
                                            cursorclass=pymysql.cursors.DictCursor)
        except pymysql.err.OperationalError as e:
            print("Mysql Error %d:%s" % (e.args[0],e.args[1]))

#clear table data
    def clear(self,table_name):
        #real_sql="truncate table"+table_name+";"
        real_sql="delete from" + table_name+";"
        with self.connection.cursor() as cursor:
            cursor.exectute("SET FOREIGN_KEY_CHECKS=0;")
            cursor.exectute(real_sql)
        self.connection.commit()

 #insert sql statement
    def insert(self,table_name,table_data):
        for key in table_data:
            table_data[key]="'" + str(table_data[key])+"'"
        key = ','.join(table_data.keys())
        value=','.join(table_data.values())
        real_sql="INSERT TO" + table_name + "("+key+") VALUES (" + value + ")"

        with self.connection.cursor() as cursor:
            cursor.exectute(real_sql)

        self.connection.commit()

    def close(self):
        self.connection.close()


if __name__=='__main__':

    db=DB()
    table_name = "sign_event"
    data={'id':1,'name':'红米','limit':2000,'status':1,
               'address':'北京会展中心','start_time':'2016-08-20 00:24:42' }
    table_name2="sign_guest"
    data2={'realname':'alen','phone':12312341234,'email':'alen@mail.com','sign':0,'event_id':1}


    db.clear(table_name)
    db.insert(table_name,data)
    db.close()