forked from TheJunky/PyCore
-
Notifications
You must be signed in to change notification settings - Fork 2
/
Amysql.py
380 lines (329 loc) · 11.7 KB
/
Amysql.py
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
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
'''
@author: The Junky
'''
import ConfigParser
import threading
import logging
from collections import deque
import MySQLdb
from subspace_bot.utilities.messaging import SSmessenger
class AElement:
"""
Base Class For All items passed back and forth from the worker thread
you should always getType on all elements returned from the queue and use
the element accordingly
"""
TYPE_NONE = 0
TYPE_QUERY = 1
TYPE_MESSAGE = 2
TYPE_RESULT = 3
def getType(self):
return AElement.TYPE_NONE
class AMessage(AElement):
"""
This is used to send commands to the worker thread (ping,terminate)
and recieve messages from the core such as connected/notconnected
"""
DB_NOT_CONNECTED = 0
DB_CONNECTED = 1
PING = 2
TERMINATE = 3
def __init__(self):
self.id = None
self.message = None
def getType(self):
return AElement.TYPE_MESSAGE
class AQuery(AElement):
"""
Actual Queries
"""
def __init__(self, query, query_tuple, extra_data):
self.text = query
self.tuple = query_tuple
self.data = extra_data
def getType(self):
return AElement.TYPE_QUERY
class AResult(AElement):
"""
Results of queries
"""
def __init__(self):
self.messages = None
self.error_no = None
self.error_msg = None
self.last_row_id = None
self.description = None
self.rows = None # the actual results
self.rows_affected = None # inserts/deletes/updates will set this
self.query = None # Original Aquery
self.info = None
# eg. can do things like
# (Query_type, name_of_person who issued it, extradata)
# or a class whatever u want
def getType(self):
return AElement.TYPE_RESULT
def executeQueryAndStoreResults(self, conn, cursor, q):
self.query = q
try:
cursor.execute(q.text, q.tuple)
self.last_row_id = conn.insert_id()
self.info = conn.info()
self.description = cursor.description
self.rows = cursor.fetchall()
self.messages = cursor.messages
self.rows_affected = cursor.rowcount
except MySQLdb.Error, e:
self.error_no = e.args[0]
self.error_msg = e.args[1]
def GenericResultPrettyPrinter(self, ssbot, mtype, target,
fail_only=False):
"""
This function will print any result nicely on screen with proper
formatting
"""
ss = SSmessenger(ssbot, mtype, target)
if fail_only:
if self.error_msg:
ss.send_message("Error: " + str(self.query.text))
ss.send_message("Error: " + str(self.error_msg))
elif self.rows is None or len(self.rows) == 0:
if self.rows_affected:
ss.send_message("RowsAffected: " + str(self.rows_affected))
if self.last_row_id:
ss.send_message("InsertId: " + str(self.last_row_id))
if self.error_msg:
ss.send_message("Error: " + str(self.error_msg))
else:
ss.send_message("Query Successful: No Results")
if self.messages:
for m in self.messages:
ss.send_message("Messages: " + str(m))
else:
if not self.description:
ss.send_message("#### NO RESULTS ###")
else:
names = []
lengths = []
for dd in self.description: # iterate over description
names.append(dd[0])
# in case name is bigger then max len of data
lengths.append(len(dd[0]))
for row in self.rows: # get the max length of each column
for i in range(len(row)):
lengths[i] = max(lengths[i], len(str(row[i])))
tb = "-" * (sum(map(int, lengths)) + (len(lengths) * 3) + 1)
fm = "|"
for col in lengths: # make the format string
fm += " %" + str(col) + "s |"
ss.send_message(tb)
ss.send_message((fm % tuple(names)))
ss.send_message(tb)
for row in self.rows: # output the rows
ss.send_message((fm % row))
ss.send_message(tb)
class Amysql(threading.Thread):
"""Example of usage
initialize:
db = Amysql(logger)
db.db.SetDbCredentialsFromFile(os.getcwd()+R"/db.conf","db")
db.start()
use:
db.Query("Select * from info",None,None)
results:
in event tick or timer:
r = db.GetResults()
check if None , check if message if not
use....
see mysqltest.py for working example
"""
def __init__(self, logger):
self.level = logging.DEBUG
self.default_file = None
self.__Host = None
self.__Port = None
self.__User = None
self.__Password = None
self.__DB = None
threading.Thread.__init__(self)
self.__query_queue = deque()
self.__query_cond = threading.Condition()
self.__results_queue = deque()
self.__results_lock = threading.Lock()
self.do_it = 1
self.conn = None
self.cursor = None
self.logger = logger
def setDBCredentials(self, host, port, user, password, db):
"""
function sets the information needed to login to the database
this function or setDbCredentialsFromFile must be used before
you start() the worker thread
"""
self.__host = host
self.__port = port
self.__user = user
self.__pass = password
self.__db = db
def setDbCredentialsFromFile(self, filename, section):
"""
function sets the information needed to login to the database
this function or setDbCredentialsFromFile must be used before
you start() the worker thread
"""
config = ConfigParser.RawConfigParser()
config.read(filename)
self.__host = config.get(section, "host")
self.__port = config.getint(section, "port")
self.__user = config.get(section, "user")
self.__pass = config.get(section, "pass")
self.__db = config.get(section, "db")
def query(self, query, query_tuple, extra_data):
"""
this function will queue a query to be executed on the worker
thread. query can be the actual query or the format string
querytuple can be none, or it can be a tuple of arges to be
inserted into the query format string, extra data can be anything
you want. for example if the query is concerning a player or has
to be privd to a specific player on return u can pass a player name
"""
self.__enqueue_query(AQuery(query, query_tuple, extra_data))
def ping(self):
"""
this function will request that a mysql_ping takes place on the worker
thread sometimes if a mysql connection didnt happen, or it has been
idle too long it will drop, ping will be used periodicly to keep the
connection alive
"""
m = AMessage()
m.id = AMessage.PING
self.__enqueue_query(m)
def isConnected(self):
if self.conn:
return True
else:
return False
def __enqueue_query(self, q):
self.__query_cond.acquire()
self.__query_queue.append(q)
self.__query_cond.notify()
self.__query_cond.release()
def __connect_to_db(self):
"""
function connects to the db, used in the worker thread
"""
m = AMessage()
try:
self.conn = MySQLdb.connect(
host=self.__host,
port=self.__port,
user=self.__user,
passwd=self.__pass,
db=self.__db
)
self.cursor = self.conn.cursor()
m.id = m.DB_CONNECTED
m.message = "DBCONNECTED"
# self.logger.info("DB CONNECTED")
except MySQLdb.Error, e:
m.id = m.DB_NOT_CONNECTED
m.message = e.args[1]
m.e = e
self.logger.info("MysqlConnect:%d: %s" % (e.args[0], e.args[1]))
# BotUtilities.log_exception(self.logger)
finally:
return m
def __execute_query(self, q):
"""
actually executed the query on the worker thread
"""
if q is None:
return None
if self.cursor:
r = AResult()
r.executeQueryAndStoreResults(self.conn, self.cursor, q)
else:
self.logger.error("DBNotConnected cant execute:" + q.text)
return r
def __dequeue_query(self):
q = None
# this seems like it would deadlock but the wait will release the lock
self.__query_cond.acquire()
# releases the lock and waits for main thread to notify it something
# is waiting in the queue
self.__query_cond.wait()
# might notify when i want to kill the thread with out adding stuff
# to the queue
if len(self.__query_queue) > 0:
q = self.__query_queue.pop()
self.__query_cond.release()
return q
def __enqueue_results(self, r):
"""
queues back the results so it can be retrieved on bot thread
"""
if r is None:
return
# return results to results queue
# in case of the notify that makes this thread join main thread
self.__results_lock.acquire()
self.__results_queue.append(r)
self.__results_lock.release()
def run(self):
"""
worker thread function
"""
m = self.__connect_to_db()
self.__enqueue_results(m)
while self.do_it:
q = self.__dequeue_query()
if q.getType() == q.TYPE_QUERY:
r = self.__execute_query(q)
self.__enqueue_results(r)
elif q.getType() == AElement.TYPE_MESSAGE and \
q.id == AMessage.PING:
if self.conn: # has connected in the past
self.conn.ping()
else: # never connected try again
m = self.__connect_to_db()
self.__enqueue_results(m)
elif q.getType() == AElement.TYPE_MESSAGE and \
q.id == AMessage.TERMINATE:
break
if self.conn is not None:
self.conn.close()
def queryEscape(self, string):
"""
any string that is gathered from the user or an ennviroment that
isn't trusted must be escaped before it is put into a query
this is to prevent sql injection. if you use the query,
querytuple it should auto escape any string for you
"""
if self.conn is not None:
return MySQLdb.escape_string(string)
return None
def requestStop(self):
"""
request workerthread die
"""
self.do_it = 0 # wont iterate again
m = AMessage()
m.id = AMessage.TERMINATE
m.message = "DIE"
self.__enqueue_query(m)
def cleanUp(self):
"""
this will hang til worker thread ends you should do this in cleanup
"""
self.requestStop()
self.join(5)
def getResults(self):
"""
grab results if any from the result queue
do this in timer/tick
"""
result = None
self.__results_lock.acquire()
if len(self.__results_queue) > 0:
result = self.__results_queue.pop()
self.__results_lock.release()
return result