mysqlhostold="119.45.95.223"
mysqlhostnewold="60.204.129.214"
mysqlhostnew="101.35.219.220"
#mysqlhost="119.45.95.223"
mysqlhost=mysqlhostnew
import pymysql 
from 	sqlalchemy import create_engine
import  sqlalchemy  #初始化数据库连接，使用pymysql模块
import pandas as pd
class Onedb:
    def __init__(self):
        pass
    def del_table(self, tablename):
        # 打开数据库连接
        global mysqlhost
        db = pymysql.connect(host=mysqlhost, user="one", password="Cfintttt.3321", database="one",charset="utf8")
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 删除语句
        #sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
        sql = "DELETE FROM "+tablename
        try:
            cursor.execute(sql)# 执行SQL语句
            db.commit()# 提交修改
            print(f"delete {tablename} from  mysql OK!!!")
        except:
            print(f"delete {tablename} from  mysql ERROR!!!")
            db.rollback()# 发生错误时回滚
        db.close()    # 关闭连接
        return 0
    def partitional_deletion(delstring):
        # 打开数据库连接
        global mysqlhost
        db = pymysql.connect(host=mysqlhost, user="one", password="Cfintttt.3321", database="one",charset="utf8")
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 删除语句
        try:
            # 执行SQL语句
            cursor.execute(delstring)
            # 提交修改
            db.commit()
            print("strfordeletion OK")
        except:
            # 发生错误时回滚
            print("strfordeletion ERROR!")
            db.rollback()
        # 关闭连接
        db.close()
        return 0
    def into_table(self, pdname,tablename,dtypestr,varcharnb):
        global mysqlhost
        ksql = create_engine(
            'mysql+pymysql://one:Cfintttt.3321@%s:3306/one?charset=utf8'%(mysqlhost))
        pd.io.sql.to_sql(pdname, tablename, ksql, schema='one', if_exists='append',
                         dtype={dtypestr: sqlalchemy.VARCHAR(varcharnb)})
        print(f"put {tablename} data into mysql is OK!")
    def From_table(self, sql="select username,loged from oneuser"):
        global mysqlhost
        db = pymysql.connect(host=mysqlhost, user="one", password="Cfintttt.3321", database="one", charset="utf8")
        df = pd.read_sql(sql, db) #, index_col=index_column)
        return df
    def update_table(self, stockcode, time, ntype=0):
        #UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
        global mysqlhost
        db = pymysql.connect(host=mysqlhost, user="one", password="Cfintttt.3321", database="one",charset="utf8")
        cursor = db.cursor()
        if ntype==1:
            sql =f"UPDATE autotradelist SET applyed='yes' WHERE stockcode='{stockcode}' and time='{time}'"
            print("sql",sql)
        if ntype==0: 
            sql =f"UPDATE autotradelist SET applyed='yes' WHERE stockcode='{stockcode}'"
            print("sql",sql)
        try:
            cursor.execute(sql)# 执行SQL语句
            db.commit()# 提交修改
            print(f"UPdate OK")
        except:
            print(f"UPdate ERROR!!!")
            db.rollback()# 发生错误时回滚
        db.close()    # 关闭连接
        return 0
    def update_applyed(self):
        global mysqlhost
        db = pymysql.connect(host=mysqlhost, user="one", password="Cfintttt.3321", database="one",charset="utf8")
        cursor = db.cursor()
        sql ="UPDATE autotradelist SET applyed='no' WHERE loopornot<>'no'"
        print("sql",sql)
        try:
            cursor.execute(sql)# 执行SQL语句
            db.commit()# 提交修改
            print(f"UPdate OK")
        except:
            print(f"UPdate ERROR!!!")
            db.rollback()# 发生错误时回滚
        db.close()    # 关闭连接
        return 0
    def update_datestring(self,datestring):
        global mysqlhost
        db = pymysql.connect(host=mysqlhost, user="one", password="Cfintttt.3321", database="one",charset="utf8")
        cursor = db.cursor()
        sql =f"UPDATE autotradelist SET reason='{datestring}' WHERE stockcode='update'"
        print("sql",sql)
        try:
            cursor.execute(sql)# 执行SQL语句
            db.commit()# 提交修改
            print(f"UPdate OK")
        except:
            print(f"UPdate ERROR!!!")
            db.rollback()# 发生错误时回滚
        db.close()    # 关闭连接
        return 0
    def execute_sql(self,sqlstring):
        # 打开数据库连接
        global mysqlhost
        db = pymysql.connect(host=mysqlhost, user="one", password="Cfintttt.3321", database="one",charset="utf8")
        cursor = db.cursor()
        try:
            # 执行SQL语句
            cursor.execute(sqlstring)
            #tmpdb=cursor.call_procedure(sqlstring)
            #print("tmpdb inside class",tmpdb)
            # 提交修改
            db.commit()
            print("Execute SQL OK")
        except:
            # 发生错误时回滚
            print("Execute SQL ERROR!")
            db.rollback()
        # 关闭连接
        db.close()
        return 0 #tmpdb
    def execute_sqlnew(self,sqlstring):
        # 打开数据库连接
        global mysqlhost
        df=pd.DataFrame()
        db = pymysql.connect(host=mysqlhost, user="one", password="Cfintttt.3321", database="one",charset="utf8")
        cursor = db.cursor()
        try:
            # 执行SQL语句
            cursor.execute(sqlstring)
            try:
                data = cursor.fetchall()
                columnDes = cursor.description #获取连接对象的描述信息
                columnNames = [columnDes[i][0] for i in range(len(columnDes))] #获取列名
                df = pd.DataFrame([list(i) for i in data],columns=columnNames) #得到的data为二维元组，逐行取出，转化为列表，再转化为df
                df=df.drop(labels='index',axis=1)
                ###print("XXX df in sqlnew")
                ###print(df)
            except:
                pass
            '''
            tmpdb=cursor.fetchall()
            datalist=list(tmpdb)
            df=pd.DataFrame(datalist)
            '''
            #tmpdb=cursor.call_procedure(sqlstring)
            #print("tmpdb inside class",tmpdb)
            # 提交修改
            db.commit()
            print("Execute SQL OK")
        except:
            # 发生错误时回滚
            print("Execute SQL ERROR!")
            db.rollback()
        # 关闭连接
        db.close()
        try:
            return df
        except:
            pass

    def call_mysql_procedure(self,sqlstring):
        global mysqlhost
        db = pymysql.connect(host=mysqlhost, user="one", password="Cfintttt.3321", database="one",charset="utf8")
        cursor = db.cursor()
        try:
            # 执行SQL语句
            #cursor.execute(sqlstring)
            cursor.callproc(sqlstring)
            data = cursor.fetchall()
            columnDes = cursor.description #获取连接对象的描述信息
            columnNames = [columnDes[i][0] for i in range(len(columnDes))] #获取列名
            df = pd.DataFrame([list(i) for i in data],columns=columnNames) #得到的data为二维元组，逐行取出，转化为列表，再转化为df
            print("XXX df in call procedure")
            print(df)
            '''
            tmpdb=cursor.fetchall()
            print("tmpdb XXX inside call procedure",tmpdb)
            datalist=list(tmpdb)
            df=pd.DataFrame(datalist)
            '''
            # 提交修改
            db.commit()
            print("Execute SQL OK")
        except:
            # 发生错误时回滚
            print("Execute SQL ERROR!")
            db.rollback()
        # 关闭连接
        db.close()
        return df