SQLDataBase_test/ops.py at main · BanYiMeng/SQLDataBase_test · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
import pandas as pd
import numpy as np

PATH=""
Users=""

pd.set_option('display.unicode.ambiguous_as_wide',True)
pd.set_option('display.unicode.east_asian_width',True)
pd.set_option('display.max_columns',None)
pd.set_option('expand_frame_repr',False)
pd.set_option('display.max_rows',None)
pd.set_option('display.width',None)

def op_Insert(dbname,tname,obj_name,value):
    (df,const)=mop_load(dbname,tname,Users)
    if (isinstance(df,int)):
        return
    if (obj_name==[]):
        obj_name=const.columns.tolist()
    if (not mop_changer(df,const,obj_name,value,True)):
        return
    df=df.append(dict(zip(obj_name,value)),ignore_index=True)
    mop_save(df,dbname,tname)

def op_Delete(dbname,tname,op_OBJ,op_B):
    (df,const)=mop_load(dbname,tname,Users)
    if (isinstance(df,int)):
        return
    tmp=mop_muti_Where(df,op_OBJ,op_B)
    df=df.drop(tmp.index)
    mop_save(df,dbname,tname)

def op_Update(dbname,tname,value,op_OBJ,op_B):
    (df,const)=mop_load(dbname,tname,Users)
    if (isinstance(df,int)):
        return
    tmp=np.array(value)
    obj_name=tmp[:,0].tolist()
    obj_value=tmp[:,1].tolist()
    if (not mop_changer(df,const,obj_name,obj_value,False)):
        return
    t=mop_muti_Where(df,op_OBJ,op_B)
    df=df.drop(t.index)
    t=t.copy()
    for i,j in zip(obj_name,obj_value):
        t[i]=j  #t.loc[:,i]=j
    df=pd.merge(df,t,how='outer')
    mop_save(df,dbname,tname)

def op_Select(dbname,tname,value,op_OBJ,op_B):
    (df,const)=mop_load(dbname,tname)
    if (isinstance(df,int)):
        return
    if (value[0]=="*"):
        tmp=mop_muti_Where(df,op_OBJ,op_B)
        if (isinstance(tmp,int)):
            return
        print(tmp)
    else:
        for y in value:
            if (y not in const.columns):
                print("columns \033[36m"+y+"\033[0m is not exist")
                return
        tmp=mop_muti_Where(df,op_OBJ,op_B)
        if (isinstance(tmp,int)):
            return
        print(tmp[value])

def mop_muti_Where(df,op_OBJ,op_B):
    i=0
    if (not op_OBJ==[]):
        tmp=mop_Where(df,i,op_OBJ)
        if (isinstance(tmp,int)):
            return tmp
        for x in op_B:
            i+=1
            if (i<len(op_OBJ)):
                tmp1=mop_Where(df,i,op_OBJ)
                if (isinstance(tmp1,int)):
                    return tmp1
            else:
                return tmp
            if (x=="And"):
                tmp=pd.merge(tmp,tmp1)
            else:
                tmp=pd.merge(tmp,tmp1,how='outer')
        return tmp
    else:
        return df


def mop_Where(df,i,op_OBJ):
    if (op_OBJ[i][0] in df.columns.tolist()):
        try:
            if (op_OBJ[i][1]=="!=" or op_OBJ[i][1]=="<>"):
                tmp=df.loc[df[op_OBJ[i][0]]!=op_OBJ[i][2]]
            elif (op_OBJ[i][1]==">"):
                tmp=df.loc[df[op_OBJ[i][0]].astype(int)>int(op_OBJ[i][2])]
            elif (op_OBJ[i][1]=="<"):
                tmp=df.loc[df[op_OBJ[i][0]].astype(int)<int(op_OBJ[i][2])]
            elif (op_OBJ[i][1]==">="):
                tmp=df.loc[df[op_OBJ[i][0]].astype(int)>=int(op_OBJ[i][2])]
            elif (op_OBJ[i][1]=="<="):
                tmp=df.loc[df[op_OBJ[i][0]].astype(int)<=int(op_OBJ[i][2])]
            elif (op_OBJ[i][1]=="="):
                tmp=df.loc[df[op_OBJ[i][0]]==op_OBJ[i][2]]
            else:
                tmp=-1
            return tmp
        except ValueError as e:
            print(e)
            return -1
    else:
        print("columns \033[36m"+op_OBJ[i][0]+"\033[0m is not exist")
        return -1

def mop_changer(df,const,obj_name,value,iu):
    if (len(obj_name)!=len(value)):
        print("Values cannot be matched")
        return False
    for x in obj_name:
        if (x not in const.columns):
            print("Something not in columns")
            return False
    if (iu):
        for x in const.columns.tolist():
            if (const[x][0] in ["Primary Key","Not Null"] and x not in obj_name):
                print("\033[35m"+x+"\033[0m require Value for \033[36m"+const[x][0]+"\033[0m")
                return False
    for i,j in zip(obj_name,range(0,len(value))):
        if (i in const.columns and (const[i][0]=="Primary Key" or const[i][0]=="Unique")):
            if (i in obj_name):
                if (not df.loc[df[i]==value[j]].empty):
                    print("\033[36m"+i+"\033[0m should be \033[34munique\033[0m,you cannot insert \033[33m"+value[j]+"\033[0m into it")
                    return False
    return True

def mop_load(dbname,tname,User=""):
    global writer
    global filer
    (df,const,filer,writer)=TB.connecter(dbname,tname,User)
    return (df,const)

def mop_save(df,dbname,tname):
    df.to_excel(writer,sheet_name=tname,index=0)
    TB.saver(filer,writer,dbname,tname)
    print(df)
    writer.save()

def mop_init(tb0,path0,users):
    global TB
    global PATH
    global Users
    Users=users
    PATH=path0
    TB=tb0