FilesExpand file tree

 master

/

mysqlmanage.py

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

164 lines (149 loc) · 4.74 KB

 master

/

mysqlmanage.py

Copy path

Top

File metadata and controls

• Code
• Blame

164 lines (149 loc) · 4.74 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

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

157

158

159

160

161

162

163

164

#!/usr/bin/python

# -*- coding:utf-8 -*-

#mysqlmanage.py

import MySQLdb

import sys

import md5

import getpass

def mydb(dbcmdlist):

try:

conn=MySQLdb.connect(host='localhost',user='root',passwd='123456',db='fortress',port=3306)

cur=conn.cursor()

result=[]

for dbcmd in dbcmdlist:

cur.execute(dbcmd)

sqlresult = cur.fetchall()

result.append(sqlresult)

conn.commit()

cur.close()

conn.close()

return result

except MySQLdb.Error,e:

print 'mysql error msg: ',e

while True:

print '''

1.创建用户

2.删除用户

3.添加主机

4.添加用户权限主机

5.查看用户权限主机

6.查看用户日志

7.查看所有日志

8.初始化表

0.退出

'''

i = raw_input('select:')

sqlcmd = []

if i == '':

continue

elif i == '1':

while True:

adduser = raw_input('adduser:').strip()

if adduser.isalnum():break

while True:

pwd = getpass.getpass('passwd:').strip()

if pwd != '':break

md5pwd=md5.new(pwd).hexdigest()

sqlcmd.append("insert into user (userName,password)values('%s','%s');" %(adduser,md5pwd))

mydb(sqlcmd)

elif i == '2':

while True:

deluser = raw_input('deluser:').strip()

if deluser.isalnum():break

sqlcmd.append("DELETE FROM user WHERE userName = '%s';" %(deluser))

mydb(sqlcmd)

elif i == '3':

while True:

hostIp = raw_input('hostIp:').strip()

if hostIp != '':break

while True:

host_port = int(raw_input("host_port:").strip())

if host_port < 65500:break

while True:

host_user = raw_input('host_user:').strip()

if host_user.isalnum():break

while True:

host_pw = raw_input('host_pw:').strip()

if host_pw != '':break

sqlcmd.append("insert into host (hostIp,host_port,host_user,host_pw)values('%s','%s','%s','%s');" %(hostIp,host_port,host_user,host_pw))

mydb(sqlcmd)

elif i == '4':

while True:

sqlcmd=[]

while True:

user = raw_input('user:').strip()

if user.isalnum():break

sqlcmd.append("select * from user where userName = '%s';" %(user))

result = mydb(sqlcmd)

if len(result[0]) >= 1:break

while True:

sqlcmd=[]

while True:

hostIP = raw_input('hostIP:').strip()

if hostIP != '':break

sqlcmd.append("select * from host where hostIp = '%s';" %(hostIP))

result = mydb(sqlcmd)

if len(result[0]) >= 1:break

sqlcmd=[]

sqlcmd.append("insert into user_host (user_id,host_id)values('%s','%s');" %(user,hostIP))

mydb(sqlcmd)

elif i == '5':

while True:

sqlcmd=[]

while True:

user = raw_input('user:').strip()

if user.isalnum():break

sqlcmd.append("select * from user where userName = '%s';" %(user))

result = mydb(sqlcmd)

if len(result[0]) >= 1:break

sqlcmd=[]

sqlcmd.append("select host_id from user_host where user_id = '%s';" %(user))

result = mydb(sqlcmd)

for i in result[0]:

print i[0]

elif i == '6':

while True:

sqlcmd=[]

while True:

user = raw_input('user:').strip()

if user.isalnum():break

sqlcmd.append("select * from user where userName = '%s';" %(user))

result = mydb(sqlcmd)

if len(result[0]) >= 1:break

sqlcmd=[]

sqlcmd.append("select * from login_log where loginuser = '%s';" %(user))

result = mydb(sqlcmd)

print result

for i in result[0]:

print i

elif i == '7':

sqlcmd=[]

sqlcmd.append("select * from login_log;")

result = mydb(sqlcmd)

print result

for i in result[0]:

print i

elif i == '8':

try:

conn=MySQLdb.connect(host='localhost',user='root',passwd='123456',port=3306)

cur=conn.cursor()

cur.execute('create database if not exists fortress;')

conn.select_db('fortress')

cur.execute('drop table if exists user;')

cur.execute('drop table if exists host;')

cur.execute('drop table if exists user_host;')

cur.execute('drop table if exists login_log;')

cur.execute('CREATE TABLE user ( id BIGINT(20) NOT NULL AUTO_INCREMENT, userName VARCHAR(50) DEFAULT NULL, password VARCHAR(50) DEFAULT NULL, PRIMARY KEY (id) );')

cur.execute('CREATE TABLE host ( id BIGINT(20) NOT NULL AUTO_INCREMENT, hostIp VARCHAR(50) DEFAULT NULL, host_port VARCHAR(50) DEFAULT NULL, host_user VARCHAR(50) DEFAULT NULL, host_pw VARCHAR(50) DEFAULT NULL, PRIMARY KEY (id) );')

cur.execute('CREATE TABLE user_host ( id BIGINT(20) NOT NULL AUTO_INCREMENT, user_id VARCHAR(50) NOT NULL , host_id VARCHAR(50) NOT NULL , PRIMARY KEY (id) );')

cur.execute('CREATE TABLE login_log ( id BIGINT(20) NOT NULL AUTO_INCREMENT, loginuser VARCHAR(50) DEFAULT NULL , remoteip VARCHAR(50) DEFAULT NULL , date_time VARCHAR(50) DEFAULT NULL , hostname VARCHAR(50) DEFAULT NULL ,username VARCHAR(50) DEFAULT NULL , cmd VARCHAR(256) DEFAULT NULL , PRIMARY KEY (id) ) ;')

conn.commit()

cur.close()

conn.close()

except MySQLdb.Error,e:

print "mysql error %d:%s" %(e.args[0],e.args[1])

elif i == '0':

sys.exit()

else:

print 'reselect'

continue