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
| class MySqlDBHelper(object):
def __init__(self,host,user,password,db,port):
self.connecter = self.getConnect(host,user,password,db,port)
def __del__(self):
try:
self.connecter.close()
except:
print("connecter close ERROR")
def getConnect(self,host,user,password,db,port):
con = MySQLdb.connect(host = host,user = user, passwd = password, db = db, port = port,charset= 'utf8')
return con
def query(self,sql,arg = None):
""" Return the results(Tuples) after executing SQL statement """
cur = self.connecter.cursor()
cur.execute(sql,arg)
result = cur.fetchall()
cur.close()
return result
def insert(self,table,args):
""" insert a row into the table,please make sure that the location of the parameter in the line is correct"""
num = args.__len__()
sql = 'insert into ' + table + " values(";
i = 0
while i< num:
sql += '%s'
i += 1
if i == num:
sql += ')'
else:
sql += ','
cur = self.connecter.cursor()
r = cur.execute(sql,args)
self.connecter.commit()
cur.close()
return r
def batchInsert(self, table, values):
""" Batch insertions of row data into :table.Values must be a LIST of TUPLES """
num = values[0].__len__()
sql = 'insert into ' + table + " values(";
i = 0
while i< num:
sql += '%s'
i += 1
if i == num:
sql += ')'
else:
sql += ','
cur = self.connecter.cursor()
r = cur.executemany(sql,values)
self.connecter.commit()
cur.close()
return
def update(self,sql,args=None):
""" Update or delete, args must be a list"""
try:
cur = self.connecter.cursor()
r = cur.execute(sql,args)
self.connecter.commit()
cur.close()
return r
except:
print("Update/Delete Error:"+sql)
return 0
def createDatabase(self,name):
cursor = self.connecter.cursor()
sql = "create database " + name
cursor.execute(sql)
return True
def createTable(self,tableName):
cursor = self.connecter.cursor()
sql = "create database " + tableName
cursor.execute(sql)
return True
def getAllDatabase(self):
dbList = []
cursor = self.connecter.cursor()
cursor.execute("show databases")
for db in cursor.fetchall():
dbList.append(db[0])
return dbList
def getAllTables(self,database):
cursor = self.connecter.cursor()
cursor.execute("use "+database)
cursor.execute("show tables")
tables = []
for tab in cursor.fetchall():
tables.append(tab)
return tables
def createTable(self,db,tableName,fields,keys,types):
"""db 数据库名, fields: 字段列表，keys: 主键列表，types:字段对应的类型"""
if fields == None or fields.__len__() == 0 or fields.__len__() != types.__len__():
return False
if keys == None:
keys = []
self.connecter.cursor().execute("use "+db)
if(tableName not in self.getAllTables(db)):
sql = "create TABLE " + tableName;
fs = "("
i = 0
while i < fields.__len__():
if fs != "(":
fs +=","
fs +=( fields[i]+" " + types[i] + " ")
i += 1
i = 0
pk = ""
if keys.__len__() != 0:
while i < keys.__len__():
if pk == "":
pk += "primary key("+keys[i]
else:
pk += ","+keys[i]
i+=1
pk+=")"
if pk != "":
fs+=","+pk
fs += ")"
sql += fs
self.connecter.cursor().execute(sql)
print("create_table Success")
return True
else:
return False
|