def main(): # Set filenames conffilename = "/home/adam/ElGrupo/conf.txt" # Load it gs = ElGrupo.Session(conffilename) gs.db.connect() cookie_string = os.environ.get("HTTP_COOKIE") if not cookie_string: return None cookie = Cookie.SimpleCookie() cookie.load(cookie_string) try: secret = cookie["secret"].value except: return None gs.db.sql("delete from sessions where secret = %s;" % MySQLdb.string_literal(secret)) # Delete the cookie from user's browsing cookie["secret"]["expires"] = "Sun, 18-Oct-2009 10:00:00 GMT" gs.accountCheck(force_cookie=cookie)
def db_escape(val): """ Escapes all the necessary characters, or if they arent found returns NULL """ val = str(val).strip() if val == None or val == "None": val = None if val: return MySQLdb.string_literal(val) else: return "NULL"
def main(): # Set filenames conffilename = '/home/adam/ElGrupo/conf.txt' # Load it gs = ElGrupo.Session(conffilename) gs.db.connect() gs.accountCheck() form = cgi.FieldStorage() name = form.getvalue('p') rows = gs.db.sqlReturn('select p_id from persons where u_id=%d and name=%s;'\ % (gs.u_id, MySQLdb.string_literal(name))) if len(rows) == 0: gs.printHeader('Failure') print '<h1 class="login">You have noone of that name.</h1>' gs.printFooter('Failure') gs.db.disconnect() return for row in rows: gs.db.sql('delete from stats where p_id=%d;' % row[0]) gs.db.sql('delete from persons where u_id=%d and name=%s;'\ % (gs.u_id, MySQLdb.string_literal(name))) gs.printHeader('Deleted') print '<h1 class="login">%s Deleted.</h1>' % cgi.escape(name) gs.printFooter('Deleted') gs.db.disconnect()
def main(): # Set filenames conffilename = '/home/adam/ElGrupo/conf.txt' # Load it gs = ElGrupo.Session(conffilename) gs.db.connect() gs.accountCheck() form = cgi.FieldStorage() name = form.getvalue('name') rows = gs.db.sqlReturn('select p_id from persons where u_id=%d and name=%s'\ % (gs.u_id, MySQLdb.string_literal(name))) if len(rows) != 0: gs.printHeader('Failure') print '<h1 class="login">You already have a person of that name.</h1>' gs.printFooter('Failure') gs.db.disconnect() return gs.db.sql('insert into persons (u_id, name, birthday) values (%d,%s,date(now()));' \ % (gs.u_id, MySQLdb.string_literal(name))) rows = gs.db.sqlReturn('select p_id from persons where u_id=%d and name=%s;'\ % (gs.u_id, MySQLdb.string_literal(name))) p_id = rows[0][0] for stat in ['Food', 'Drink', 'Happiness', 'Exercise']: gs.db.sql('insert into stats (p_id, stat, value) values (%d, %s, 0)' \ % (p_id, MySQLdb.string_literal(stat))) gs.printHeader('Thanks') print '<h1 class="login">Thank you!</h1>' print '<p>A person has been created. You can manipulate it on the left.</p>' gs.printFooter('Thanks') gs.db.disconnect()
def main(): # Set filenames conffilename = '/home/adam/ElGrupo/conf.txt' # Load it gs = ElGrupo.Session(conffilename) gs.db.connect() gs.accountCheck() # Get values form = cgi.FieldStorage() cur_passwd = form.getvalue('cur_passwd') email = form.getvalue('email') new_passwd = form.getvalue('new_passwd') new_confirm = form.getvalue('new_confirm') wants_email = form.getvalue('wantmail') u_id = gs.loggedInAs() # Check current passwd if not cur_passwd: fail(gs, 'Must enter current password.') rows = gs.db.sqlReturn( \ 'select name from users where u_id = %d and passwd_hash = "%s";' % \ (u_id, hashlib.sha256(cur_passwd).hexdigest())) if len(rows) != 1: fail(gs, 'Current password does not match.') # Make the changes if email: gs.db.sql('update users set email=%s where u_id=%d;' % \ (MySQLdb.string_literal(email), u_id)) if new_passwd: if new_passwd == new_confirm: gs.db.sql('update users set passwd_hash=%s where u_id=%d;' % \ (MySQLdb.string_literal(hashlib.sha256(new_passwd).hexdigest()), u_id)) else: fail(gs, 'Passwords do not match.') if wants_email: gs.db.sql('update users set wants_email=1 where u_id=%d;' % gs.u_id) else: gs.db.sql('update users set wants_email=0 where u_id=%d;' % gs.u_id) gs.printHeader('Thanks') print '<h1 class="login">Update succeeded.</h1>' gs.printFooter('Thanks') gs.db.disconnect()
def add_task_price(item_id, image_link): """ """ item_id = int(item_id) image = MySQLdb.string_literal(image_link) price_task_template = "insert into price_360_task (`image`, `item_id`) values (%s, %d)"%(image, item_id) return DB.update(price_task_template)
def loggedInAs(self): cookie_string = os.environ.get('HTTP_COOKIE') if not cookie_string: return None cookie = Cookie.SimpleCookie() cookie.load(cookie_string) try: secret = cookie['secret'].value except: return None rows = self.db.sqlReturn('select u_id, s_id from sessions where secret = %s;' % \ (MySQLdb.string_literal(secret))) if len(rows) != 1: return None self.s_id = rows[0][1] return rows[0][0]
def data_record(data): """ """ store_item_id = MySQLdb.string_literal(data['item_id']) item_brand = data['brand'] item_model = data['model'] store_title = MySQLdb.string_literal(data['title']) store_desc = MySQLdb.string_literal(data['desc']) store_adv = MySQLdb.string_literal(data['adv']) store_price = float(data['current_price']) item_url = data['item_url'] item_thumb = MySQLdb.string_literal(data['item_thumb']) item_category = MySQLdb.string_literal(data['category']) item_property = MySQLdb.string_literal(data['property']) rec_sql_template = "insert into item_from_suning (`store_item_id`,`item_brand`,`item_model`,`store_title`,`store_desc`, \ `store_adv`, `store_price`, `item_url`, `item_thumb`, `item_category`, `item_property`, `_created`, `_updated`) values (%s, '%s', '%s', %s, %s, %s, \ %f, '%s', %s, %s, %s, now(), now())"%(store_item_id, item_brand, item_model, store_title, store_desc,store_adv,store_price,item_url,item_thumb,item_category,item_property) return DB.update(rec_sql_template)
def upload(self, uploadobj, outputfunction): output = outputfunction if not self.connect(): output("Unable to connect to database") return for item in uploadobj: table = item[0] column = item[1] dtype = item[3] value = item[4] if dtype == 'text_file' or dtype == 'binary': filename = value if dtype == 'string': sqlstr = "UPDATE %s set %s = %s where Project_ID = %s"\ ";" % (table, column, value, GB.P.ID) res = self.sendQuery(sqlstr) if res != "": output(res) elif dtype == 'text_file' or dtype == 'binary': if os.path.exists(filename): try: if dtype == 'binary': fp = open(filename, 'rb') else: fp = open(filename) F = fp.read() # read in entire file as a string fp.close() except: output("unable to open %s for reading" % filename) continue else: output("unable to find %s" % filename) continue sqlstr = "UPDATE %s set %s = %s where Project_ID = %s"\ ";" % (table, column, MySQLdb.string_literal(F), GB.P.ID) res = self.sendQuery(sqlstr) if res != "": output(res)
def main(): # Set filenames conffilename = '/home/adam/ElGrupo/conf.txt' # Load it gs = ElGrupo.Session(conffilename) gs.db.connect() form = cgi.FieldStorage() name = form.getfirst('name', '') gs.name = name passwd = form.getfirst('passwd', '') rows = gs.db.sqlReturn( \ 'select u_id from users where name = %s and passwd_hash = "%s";' % \ (MySQLdb.string_literal(name), hashlib.sha256(passwd).hexdigest())) if len(rows) != 1: gs.name = None gs.printHeader('Login failed.') print '<h1 class="login">Login failed.</h1>' print '<p><a href="index.py">Try again</a></p>' gs.printFooter('Login failed.') sys.exit(0) u_id = rows[0][0] gs.u_id = u_id secret = hashlib.sha256(str(random.random())).hexdigest() gs.db.sql('insert into sessions (secret, u_id) values ("%s", %d);' % \ (secret, u_id)) c = Cookie.SimpleCookie() c['secret'] = secret gs.printHeader('Thanks', cookie=c) print '<h1 class="login">Login succeeded.</h1>' gs.printFooter('Thanks') gs.db.disconnect()
def handle(self): """ Handles established connection self.request is the socket """ global server_running global MYSQL_DB log = logging.getLogger('req_handler') # Init parser #parser = re.compile('^(("(?:[^"]|"")*"|[^,]*)(,("(?:[^"]|"")*"|[^,]*))*)$') parser = re.compile(',') fieldlist = ( ("call_start", 'datetime', '%Y/%m/%d %H:%M:%S'), ("call_duration", 'time', '%H:%M:%S'), ("ring_duration", 'timeint'), # In seconds, max 9999 ("caller", 'str', 255), ("direction", 'enum', ['I', 'O']), #Inbound, Outbound ("called_number", 'str', 255), ("dialled_number", 'str', 255), ("account", 'str', 255), ("is_internal", 'bool'), #0 or 1 ("call_id", 'int'), #Internal avaya call ID ("continuation", 'bool'), #Tells if there is a further record for this callID ("party1device", 'str', 5), #(E|T|V)xxx E=Extension, T=Trunk, V=voicemail ("party1name", 'str', 255), ("party2device", 'str', 5), #Like above ("party2name", 'str', 255), ("hold_time", 'timeint'), #Seconds ("park_time", 'timeint'), #Seconds ("authvalid", 'str', 255), #Undocumented from here ("authcode", 'str', 255), ("user_charged", 'str', 255), ("call_charge", 'str', 255), ("currency", 'str', 255), ("amount_change", 'str', 255), ("call_units", 'str', 255), ("units_change", 'str', 255), ("cost_per_unit", 'str', 255), ("markup", 'str', 255), ) peerinfo = self.request.getpeername() log.info(u'Got connection from ' + unicode(peerinfo[0]) + ' (' + unicode(peerinfo[1]) + ')') #Init database conn = MySQLdb.connect(host=MYSQL_DB['host'], user=MYSQL_DB['user'], passwd=MYSQL_DB['passwd'], db=MYSQL_DB['db'], port=MYSQL_DB['port']) conn.autocommit(True) #Receive data loop dbuffer = "" while server_running: data = self.request.recv(1024) if not data: break # Append data to LOGFILE lgf = open(LOGFILE, 'ab') lgf.write(data) lgf.close() # Process data line = data.strip(" \n\r\t") vals = parser.split(line) if len(vals) >= len(fieldlist): # Received a good line # Build a dictionary dictv = {} i = 0 try: for v in fieldlist: if v[1] == 'datetime': dictv[v[0]] = datetime.strptime(vals[i], v[2]) elif v[1] == 'time': dictv[v[0]] = datetime.strptime(vals[i], v[2]).time() elif v[1] == 'timeint': z = int(vals[i]) h = int(math.floor(z / (60**2))) m = int(math.floor((z - (h * 60**2)) / 60**1)) s = z - (h * 60**2) - (m * 60**1) dictv[v[0]] = time(h, m, s) elif v[1] == 'int': dictv[v[0]] = int(vals[i]) elif v[1] == 'str': if len(vals[i]) > v[2]: raise ParserError(v[0] + ': String too long') dictv[v[0]] = str(vals[i]) elif v[1] == 'bool': if vals[i] != '0' and vals[i] != '1': raise ParserError(v[0] + ': Unvalid boolean') dictv[v[0]] = bool(vals[i]) elif v[1] == 'enum': if not vals[i] in v[2]: raise ParserError(v[0] + ': Value out of range') dictv[v[0]] = str(vals[i]) else: raise ParserError(v[0] + ': Unknown field type ' + v[1]) i += 1 except Exception, e: # Unable to parse line log.error(u"Parse error on line (" + str(v[0]) + str(vals[i]) + "): got exception " + unicode(e) + " (" + str(line) + ")") else: # Line parsed correctly log.debug(u"Correctly parsed 1 line: " + unicode(dictv)) #Prepare dictv for query map(lambda v: MySQLdb.string_literal(v), dictv) dictv['table'] = MYSQL_DB['table'] # Put the data into the DB cursor = conn.cursor() q = """ INSERT INTO `%(table)s` SET `call_start` = '%(call_start)s', `call_duration` = '%(call_duration)s', `ring_duration` = '%(ring_duration)s', `caller` = '%(caller)s', `direction` = '%(direction)s', `called_number` = '%(called_number)s', `dialled_number` = '%(dialled_number)s', `account` = '%(account)s', `is_internal` = %(is_internal)d, `call_id` = %(call_id)d, `continuation` = %(continuation)d, `party1device` = '%(party1device)s', `party1name` = '%(party1name)s', `party2device` = '%(party2device)s', `party2name` = '%(party2name)s', `hold_time` = '%(hold_time)s', `park_time` = '%(park_time)s', `authvalid` = '%(authvalid)s', `authcode` = '%(authcode)s', `user_charged` = '%(user_charged)s', `call_charge` = '%(call_charge)s', `currency` = '%(currency)s', `amount_change` = '%(amount_change)s', `call_units` = '%(call_units)s', `units_change` = '%(units_change)s', `cost_per_unit` = '%(cost_per_unit)s', `markup` = '%(markup)s'; """ % dictv log.debug(u"Query: " + unicode(q)) cursor.execute(q) cursor.close() else: log.error(u"Parse error on line (len " + str(len(vals)) + " vs " + str(len(fieldlist)) + "): " + unicode(line))
def main(): # Set filenames conffilename = '/home/adam/ElGrupo/conf.txt' # Load it gs = ElGrupo.Session(conffilename) gs.db.connect() gs.accountCheck() p = ElGrupo.Person() form = cgi.FieldStorage() p.name = form.getvalue('p') # Find person info rows = gs.db.sqlReturn('select p_id, birthday from persons where ' + 'name = %s and u_id = %d' % (MySQLdb.string_literal(p.name), gs.u_id)) if len(rows) == 0: gs.printHeader() print '<h1 class="login">You have no person named %s.</h1>' % (cgi.escape(p.name)) gs.printFooter() gs.db.disconnect() return p.p_id, p.birthday = rows[0] # Find stat info spend = form.getvalue('spend') spend_spot = None rows = gs.db.sqlReturn('select stat, value from stats where p_id=%d' % p.p_id) i = 0 sum = 0 for row in rows: s = ElGrupo.Stat() s.string, s.value = row sum += float(s.value) if spend == s.string: spend_spot = i p.stats.append(s) i += 1 # Do we need to up a stat? if spend: rows = gs.db.sqlReturn( 'select stat_id from stats where p_id=%d and stat=%s and value<100;'\ % (p.p_id, MySQLdb.string_literal(spend))) if len(rows) > 0 and spend_spot is not None and p.stats[spend_spot].value < 100: stat_id = rows[0][0] rows = gs.db.sqlReturn('select points from users where u_id=%d'\ % gs.u_id) if rows[0][0] >= 100: gs.db.sql('update users set points=points-100 where u_id=%d'\ % gs.u_id) p.stats[spend_spot].value = min(p.stats[spend_spot].value+20, 100) gs.db.sql('update stats set value=%f where stat_id=%d'\ % (p.stats[spend_spot].value, stat_id)) gs.printHeader(p.name) print '<h1 class="login">%s, born on %s.</h1>' % (p.name, str(p.birthday)) print '<table width="300px">' for stat in p.stats: print '<tr>' print '<td>%s:</td><td align="right">%2.1f</td>' % (stat.string, stat.value) print '<td align="right">' print '<a href="person.py?p=%s&spend=%s">Spend' % (p.name, cgi.escape(stat.string)) print '</a></td>' print '</tr>' print '<tr>' print '<td><strong>Total:</td>' print '<td align="right"><strong>%2.1f</strong></td><td />' % (sum/len(p.stats)) print '</table>' print '<p><a href="delete.py?p=%s">Delete</a></p>' % p.name gs.printFooter(p.name) gs.db.disconnect()
def load_table(db, table, data): " Put some test data into the database table" #B = data2list(data) # data can be read from a text file B = data # B is a list of [column_name, value] pairs # INSERT INTO table (column1, column2,..) VALUES (val1, val2, ..) curs = db.cursor() columns = "(" values = "(" projid = None #textFiles = {} # dictionary of textfiles for item in B: col = item[0] val = item[1] if col in valueColumns: columns += '%s,' % col values += '"%s",' % val if col == 'project_id': projid = val elif col in textColumns: columns += '%s,' % col values += '"%s",' % val if col == 'project_id': projid = val elif col in textFileColumns: filename = val if not os.path.exists(filename): print filename + " not found" continue fp = open(filename) F = fp.read() # read in entire file as a string fp.close() #textFiles[col] = F # put in dictionary, accessed by column name columns += '%s,' % col values += '"%s",' % MySQLdb.string_literal(F) elif col in binaryFileColumns: filename = val if not os.path.exists(filename): print filename + " not found" continue fp = open(filename, 'rb') F = fp.read() # read in entire file as a string fp.close() #textFiles[col] = F # put in dictionary, accessed by column name columns += '%s,' % col values += '"%s",' % MySQLdb.string_literal(F) # insert the values into the table columns = columns[:-1] + ")" values = values[:-1] + ")" sqlstr = 'INSERT INTO %s %s VALUES %s' % (table, columns, values) #print sqlstr curs.execute("%s" % (sqlstr, )) curs.close() # show data in table showdata(db, table)
def load_table(db, table, data): " Put some test data into the database table" #B = data2list(data) # data can be read from a text file B = data # B is a list of [column_name, value] pairs # INSERT INTO table (column1, column2,..) VALUES (val1, val2, ..) curs=db.cursor() columns = "(" values = "(" projid = None #textFiles = {} # dictionary of textfiles for item in B: col = item[0] val = item[1] if col in valueColumns: columns += '%s,' % col values += '"%s",' % val if col == 'project_id': projid = val elif col in textColumns: columns += '%s,' % col values += '"%s",' % val if col == 'project_id': projid = val elif col in textFileColumns: filename = val if not os.path.exists(filename): print filename + " not found" continue fp = open(filename) F = fp.read() # read in entire file as a string fp.close() #textFiles[col] = F # put in dictionary, accessed by column name columns += '%s,' % col values += '"%s",' % MySQLdb.string_literal(F) elif col in binaryFileColumns: filename = val if not os.path.exists(filename): print filename + " not found" continue fp = open(filename, 'rb') F = fp.read() # read in entire file as a string fp.close() #textFiles[col] = F # put in dictionary, accessed by column name columns += '%s,' % col values += '"%s",' % MySQLdb.string_literal(F) # insert the values into the table columns = columns[:-1] + ")" values = values[:-1] + ")" sqlstr = 'INSERT INTO %s %s VALUES %s' % (table, columns, values) #print sqlstr curs.execute("%s" % (sqlstr,) ) curs.close() # show data in table showdata(db, table)
def marathon(gs, form, filename): q = gs.getRandQuestion(gs.game_name) # Is a game running? ms_rows = gs.db.sqlReturn( 'select ms_id, n_correct, t_left from marathon_scores where u_id = %d and g_id = %d and t_prev + interval t_left second > now() and lost=0;' % (gs.u_id, gs.g_id)) if len(ms_rows) == 0: # Is it time to do the action now? startone = form.getvalue('startone') if startone == 'yes': # Get a game going gs.db.sql('insert into marathon_scores (u_id, g_id, t_left) ' + 'values (%d, %d, %d);' % (gs.u_id, gs.g_id, 100)) marathon(gs, form, filename) # Now, there's a row, so no deep recursion else: # Need to review a finished game? rows = gs.db.sqlReturn( 'select n_correct, ms_id from marathon_scores where ' + 'reviewed = 0 and u_id = %d and g_id = %d;' % (gs.u_id, gs.g_id)) if len(rows) == 0: # Give dialog gs.printHeader(gs.game_name + '-Marathon (New Game)') print '<form action="%s" method="post">' % (filename) print '<input type="hidden" name="startone" value="yes" />' print '<input type="hidden" name="m" value="m" />' print '<input type="submit" value="New game" />' print '</form>' gs.printFooter(gs.game_name + '-Marathon (New Game)') else: gs.db.sql('update users set points=points+%d where u_id=%d'\ % (round((10*rows[0][0])**1.2), gs.u_id)) # Print summary gs.printHeader(gs.game_name + '-Marathon (Review)') print '<h1 class="login">Good job. Your score is %d.</h1>' % \ (rows[0][0]) gs.printFooter(gs.game_name + '-Marathon (Review)') # Now it is reviewed gs.db.sql('update marathon_scores set reviewed=1 where ms_id=%d;' % rows[0][1]) else: # Present a question ms_id = ms_rows[0][0] n_correct = ms_rows[0][1] t_left = ms_rows[0][2] # Time to quit? quit = form.getvalue('quit') if quit == 'yes': gs.db.sql('update marathon_scores set lost=1 where ms_id=%d' % ms_id) marathon(gs, form, filename) return # Figure out correctness last_answer = form.getvalue('answer') correct = None if last_answer: sep = last_answer.find('-') last_q_id = int(last_answer[:sep]) last_string = last_answer[sep+1:] rows = gs.db.sqlReturn( 'select correct from answers where q_id=%d and answer=%s;' % \ (last_q_id, MySQLdb.string_literal(last_string))) if len(rows) == 1: if rows[0][0] == 1: correct = True n_correct += 1 if 2 < t_left <= 6: t_left -= 1 elif 6 < t_left: t_left = math.ceil(0.85 * t_left) gs.db.sql( 'update marathon_scores set n_correct=n_correct+1, t_prev=now(), ' 't_left=%d where ms_id=%d;' % (t_left, ms_id) ) else: # Lose the game correct = False gs.db.sql('update marathon_scores set lost=1 where ms_id=%d;' % ms_id) marathon(gs, form, filename) return # Print info gs.printHeader(gs.game_name+'-Marathon') print '<h1 class="question">%s</h1><br />' % (q.string) print '<div class="question_bar"> </div><br />' print '<div class="question_form">' print '<table width="520px">' print '<form action="%s" method="post">' % filename spot = int((len(q.answers) + 1) / 2) i = 0 for a in q.answers: print '<tr>' print '<td>' print '<input type="radio" name="answer" value="%d-%s">%s</input><br />' \ % (q.q_id, a.string, a.string) print '</td>' print '<td align="right">' if i == 0: print '<span style="color:red">' + str(t_left) + 's round</span>' if i == spot-1: print n_correct elif i == spot and not correct is None: if correct: print 'Correct!' else: print 'Incorrect!' print '</td>' print '</tr>' i += 1 print '<tr><td><input type="submit" value="Choose" />' print '</td><td align="right"><a href="%s?m=m&quit=yes">Quit</a></td></tr>'\ % filename print '<input type="hidden" name="m" value="m">' print '</form></table></div><br />' print '<div class="question_bar"> </div><br />' gs.printFooter(gs.game_name + '-Marathon') gs.db.disconnect()
def readQuestions(self, filename): # Can't do this if not connected print 'foo' if not self.db.connected: print "Error: can't reinitialize the questions without connecting first." sys.exit(1) # Open the file try: fp = open(filename, 'r') except: print 'Error opening %s. Exiting.' % (filename) sys.exit(1) # Clear the tables #self.db.sql('delete from answers;') #self.db.sql('delete from questions;') #self.db.sql('delete from games;') # Read the info in games = [] for line in fp: # Remove comments, newlines comment_start = line.find('#') if comment_start >= 0: line = line[:comment_start] while line[-1] == '\r' or line[-1] == '\n': line = line[:-1] # Do the command toks = line.split() if len(toks) == 0: continue if toks[0] == 'game': if len(toks) != 3: print 'Error: game takes exactly 2 arguments.' sys.exit(1) g = Game() g.string = toks[1] g.filename = toks[2] games.append(g) elif toks[0] == 'question': if len(games) == 0: print 'Error: question before a game was initialized.' sys.exit(1) q = Question() q.string = line[line.find('n')+2:] # Remove the "question ". games[-1].questions.append(q) elif toks[0] == 'answer': if len(games) == 0: print 'Error: answer before a game was initialized.' sys.exit(1) if len(games[-1].questions) == 0: print 'Error: answer before a question was initialized.' sys.exit(1) a = Answer() if toks[1] == 'correct': a.correct = True elif toks[1] == 'incorrect': a.correct = False else: print 'Error: answer needs either "correct" or "incorrect".' sys.exit(1) a.string = line[line.find('t')+2:] # Remove the "...rrect ". games[-1].questions[-1].answers.append(a) # Make sql command to insert values g_id = 0 q_id = 0 g_stmt = 'insert into games (name, filename) values ' q_stmt = 'insert into questions (g_id, question) values ' a_stmt = 'insert into answers (q_id, answer, correct) values ' for g in games: g_id += 1 g_stmt += '(%s, %s), ' % (MySQLdb.string_literal(g.string), MySQLdb.string_literal(g.filename)) for q in g.questions: q_id += 1 q_stmt += '(%d, %s), ' % (g_id, MySQLdb.string_literal(q.string)) for a in q.answers: a_stmt += '(%d, %s, ' % (q_id, MySQLdb.string_literal(a.string)) if a.correct: a_stmt += 'true), ' else: a_stmt += 'false), ' g_stmt = g_stmt[:-2] + ';' q_stmt = q_stmt[:-2] + ';' a_stmt = a_stmt[:-2] + ';' # Run them print g_stmt self.db.sql(g_stmt) print a_stmt self.db.sql(a_stmt) print q_stmt self.db.sql(q_stmt)
def insert_notes(self, text): """Inserts arbitrary notes into the hits table""" generator = Fortunate('/usr/share/games/fortune/startrek') cur = self.db.cursor() cur.execute('insert into hits (notes) values (%s)' % MySQLdb.string_literal(generator())) cur.close()
def _escape(self, value): return mysql.string_literal(value).decode('utf-8')
def dbescape(val): if val: return MySQLdb.string_literal(val) else: return "NULL"
if category == '': category = 'Others' sql = """INSERT INTO pycookbook (title,submitter,lastupdate,version,category,description,source,filename) VALUES (%s,%s,%s,%s,%s,%s,%s,%s);""" % tuple(map(MySQLdb.string_literal,[title,submitter,lastupdate,version,category,description,source,filename])) dbc.execute(sql) else: print "parsing fail: %s" % filename newfile = tmpl % todict(title=title, submitter=submitter,lastupdate=lastupdate,version=version,category=category,description=description,source=source,filename=filename) file("%s/%s"%(newdir,filename),'w').write(newfile) sql = "select distinct category from pycookbook order by category" dbc.execute(sql) cats = dbc.fetchall() toc = '<UL>\n' toc += '<LI><OBJECT type="text/sitemap"><param name="Name" value="Main page"><param name="Local" value="main.html"></OBJECT>\n' for cat, in cats: toc += '<LI><OBJECT type="text/sitemap"><param name="Name" value="%s"><param name="Local" value="%s.html"></OBJECT>\n' % (cat,cat) sql = "select title,filename,lastupdate from pycookbook where category=%s order by lastupdate desc" % MySQLdb.string_literal(cat) dbc.execute(sql) rows = dbc.fetchall() sources_link = ''.join(["<br><a href='%s'>%s</a> (%s)" % (filename,title,lastupdate) for title,filename,lastupdate in rows]) toc += '<UL>\n' + ''.join(['<LI><OBJECT type="text/sitemap"><param name="Name" value="%s"><param name="Local" value="%s"></OBJECT>\n' % (title,filename) for title,filename,lastupdate in rows]) + '</UL>\n' newfile = tmpl_cat % todict(title=title, category=cat,sources_link=sources_link) file("%s/%s.html"%(newdir,cat),'w').write(newfile) toc += '</UL>\n' sources_link = ''.join(["<br><a href='%s.html'>%s</a>" % (cat[0],cat[0]) for cat in cats]) newfile = tmpl_main % todict(sources_link=sources_link) file("%s/main.html"%newdir,'w').write(newfile) newfile = tmpl_toc % todict(toc=toc) file("%s/toc.hhc"%newdir,'w').write(newfile)
def sprint(gs, form, filename): q = gs.getRandQuestion(gs.game_name) # Is a game running? ss_rows = gs.db.sqlReturn( 'select ss_id, n_correct, n_total, time_to_sec(timediff(started + interval 3 minute, now())) from sprint_scores where ' + 'u_id = %d and g_id = %d and started + interval 3 minute > now() and lost=0;' % (gs.u_id, gs.g_id)) if len(ss_rows) == 0: # Is it time to do the action now? startone = form.getvalue('startone') if startone == 'yes': # Get a game going gs.db.sql('insert into sprint_scores (u_id, g_id) values (%d, %d);' % (gs.u_id, gs.g_id)) sprint(gs, form, filename) # Now, there's a row, so no deep recursion else: # Need to review a finished game? rows = gs.db.sqlReturn( 'select n_correct, n_total, ss_id from sprint_scores where ' + 'reviewed = 0 and u_id = %d and g_id = %d;' % (gs.u_id, gs.g_id)) if len(rows) == 0: # Give dialog gs.printHeader(gs.game_name + '-Sprint (New Game)') print '<form action="%s" method="post">' % (filename) print '<input type="hidden" name="startone" value="yes" />' print '<input type="hidden" name="m" value="s" />' print '<input type="submit" value="New game" />' print '</form>' gs.printFooter(gs.game_name + '-Sprint (New Game)') else: gs.db.sql('update users set points=points+%d where u_id=%d'\ % (round((10*rows[0][0])**1.1), gs.u_id)) # Print summary gs.printHeader(gs.game_name + '-Sprint (Review)') print '<h1 class="login">Good job. Your score is %d/%d.</h1>' % \ (rows[0][0], rows[0][1]) gs.printFooter(gs.game_name + '-Sprint (Review)') # Now it is reviewed gs.db.sql('update sprint_scores set reviewed=1 where ss_id=%d;' % rows[0][2]) else: # Present a question ss_id = ss_rows[0][0] n_correct = ss_rows[0][1] n_total = ss_rows[0][2] t_left = ss_rows[0][3] # Time to quit? quit = form.getvalue('quit') if quit == 'yes': gs.db.sql('update sprint_scores set lost=1 where ss_id=%d' % ss_id) sprint(gs, form, filename) return # Figure out correctness last_answer = form.getvalue('answer') correct = None if last_answer: sep = last_answer.find('-') last_q_id = int(last_answer[:sep]) last_string = last_answer[sep+1:] rows = gs.db.sqlReturn( 'select correct from answers where q_id=%d and answer=%s;' % \ (last_q_id, MySQLdb.string_literal(last_string))) if len(rows) == 1: n_total += 1 if rows[0][0] == 1: correct = True n_correct += 1 gs.db.sql('''\ update sprint_scores set n_correct=n_correct+1, n_total=n_total+1 where ss_id=%d;''' % ss_id) else: correct = False gs.db.sql('''\ update sprint_scores set n_total=n_total+1 where ss_id=%d;''' % ss_id) # Print info gs.printHeader(gs.game_name + '-Sprint') print '<h1 class="question">%s</h1><br />' % (q.string) print '<div class="question_bar"> </div><br />' print '<div class="question_form">' print '<table width="520px">' print '<form action="%s" method="post">' % filename spot = int((len(q.answers) + 1) / 2) i = 0 for a in q.answers: print '<tr>' print '<td>' print '<input type="radio" name="answer" value="%d-%s">%s</input><br />' \ % (q.q_id, a.string, a.string) print '</td>' print '<td align="right">' if i == 0: print '<span style="color:red">' + str(t_left) + 's remaining</span>' if i == spot-1: print n_correct, '/', n_total elif i == spot and not correct is None: if correct: print 'Correct!' else: print 'Incorrect!' print '</td>' print '</tr>' i += 1 print '<tr><td><input type="submit" value="Choose" />' print '</td><td align="right"><a href="%s?m=s&quit=yes">Quit</a></td></tr>'\ % filename print '<input type="hidden" name="m" value="s">' print '</form></table></div><br />' print '<div class="question_bar"> </div><br />' gs.printFooter(gs.game_name+'-Sprint') gs.db.disconnect()
def test_string_literal(self): assert MySQLdb.string_literal(2) == "'2'"
#encoding: utf-8 import toolkit import ConfigParser import MySQLdb conf = ConfigParser.ConfigParser() conf.read('task.conf') HOST = conf.get('mysql', 'host') USER = conf.get('mysql', 'user') PSWD = conf.get('mysql', 'pswd') DB = conf.get('mysql', 'base') DB = toolkit.MySQLClient(HOST, USER, PSWD, DB, _isdict = True) FROM = DB.getRows("select * from item_from_suning") for i in FROM: f = lambda x:MySQLdb.string_literal(x) #print f(i['store_adv']) DB.update("insert into items (`store_item_id`,`item_brand`,`item_model`,`store_title`,`item_store`, \ `store_price`,`item_url`,`item_thumb`,`item_category`,`item_property`) values ('%s','%s','%s',\"%s\",'%s',%f,'%s','%s','%s','%s') "%(i['store_item_id'],i['item_brand'],i['item_model'],i['store_title'],'"suning"',i['store_price'],i['item_url'],i['item_thumb'],i['item_category'],i['item_property']))
def handle(self): """ Handles established connection self.request is the socket """ global server_running global MYSQL_DB log = logging.getLogger('req_handler') # Init parser #parser = re.compile('^(("(?:[^"]|"")*"|[^,]*)(,("(?:[^"]|"")*"|[^,]*))*)$') parser = re.compile(',') fieldlist = ( ( "call_start", 'datetime', '%Y/%m/%d %H:%M:%S' ), ( "call_duration", 'time', '%H:%M:%S' ), ( "ring_duration", 'timeint' ), # In seconds, max 9999 ( "caller", 'str', 255 ), ( "direction", 'enum', ['I','O'] ), #Inbound, Outbound ( "called_number", 'str', 255 ), ( "dialled_number", 'str', 255 ), ( "account", 'str', 255 ), ( "is_internal", 'bool' ), #0 or 1 ( "call_id", 'int' ), #Internal avaya call ID ( "continuation", 'bool' ), #Tells if there is a further record for this callID ( "party1device", 'str', 5 ), #(E|T|V)xxx E=Extension, T=Trunk, V=voicemail ( "party1name", 'str', 255 ), ( "party2device", 'str', 5 ), #Like above ( "party2name", 'str', 255 ), ( "hold_time", 'timeint' ), #Seconds ( "park_time", 'timeint' ), #Seconds ( "authvalid", 'str', 255 ), #Undocumented from here ( "authcode", 'str', 255 ), ( "user_charged", 'str', 255 ), ( "call_charge", 'str', 255 ), ( "currency", 'str', 255 ), ( "amount_change", 'str', 255 ), ( "call_units", 'str', 255 ), ( "units_change", 'str', 255 ), ( "cost_per_unit", 'str', 255 ), ( "markup", 'str', 255 ), ); peerinfo = self.request.getpeername() log.info(u'Got connection from ' + unicode(peerinfo[0]) + ' (' + unicode(peerinfo[1]) + ')') #Init database conn = MySQLdb.connect( host = MYSQL_DB['host'], user = MYSQL_DB['user'], passwd = MYSQL_DB['passwd'], db = MYSQL_DB['db'], ) conn.autocommit(True) #Receive data loop dbuffer = "" while server_running: data = self.request.recv(1024) if not data: break # Append data to LOGFILE lgf = open(LOGFILE, 'ab') lgf.write(data) lgf.close() # Process data line = data.strip(" \n\r\t") vals = parser.split(line) if len(vals) >= len(fieldlist): # Received a good line # Build a dictionary dictv = {} i = 0 try: for v in fieldlist: if v[1] == 'datetime': dictv[v[0]] = datetime.strptime(vals[i], v[2]) elif v[1] == 'time': dictv[v[0]] = datetime.strptime(vals[i], v[2]).time() elif v[1] == 'timeint': z = int(vals[i]) h = int(math.floor( z / ( 60 ** 2 ) )) m = int(math.floor( ( z - ( h * 60 ** 2 ) ) / 60 ** 1 )) s = z - ( h * 60 ** 2 ) - ( m * 60 ** 1 ) dictv[v[0]] = time(h, m, s) elif v[1] == 'int': dictv[v[0]] = int(vals[i]) elif v[1] == 'str': if len(vals[i]) > v[2]: raise ParserError(v[0] + ': String too long') dictv[v[0]] = str(vals[i]) elif v[1] == 'bool': if vals[i] != '0' and vals[i] != '1': raise ParserError(v[0] + ': Unvalid boolean') dictv[v[0]] = bool(vals[i]) elif v[1] == 'enum': if not vals[i] in v[2]: raise ParserError(v[0] + ': Value out of range') dictv[v[0]] = str(vals[i]) else: raise ParserError(v[0] + ': Unknown field type ' + v[1]) i += 1 except Exception, e: # Unable to parse line log.error(u"Parse error on line (" + str(v[0]) + str(vals[i]) + "): got exception " + unicode(e) + " (" + str(line) + ")") else: # Line parsed correctly log.debug(u"Correctly persed 1 line: " + unicode(dictv)) #Prepare dictv for query map(lambda v: MySQLdb.string_literal(v), dictv) dictv['table'] = MYSQL_DB['table'] # Put the data into the DB cursor = conn.cursor() q = """ INSERT INTO `%(table)s` SET `call_start` = '%(call_start)s', `call_duration` = '%(call_duration)s', `ring_duration` = '%(ring_duration)s', `caller` = '%(caller)s', `direction` = '%(direction)s', `called_number` = '%(called_number)s', `dialled_number` = '%(dialled_number)s', `account` = '%(account)s', `is_internal` = %(is_internal)d, `call_id` = %(call_id)d, `continuation` = %(continuation)d, `paty1device` = '%(party1device)s', `party1name` = '%(party1name)s', `party2device` = '%(party2device)s', `party2name` = '%(party2name)s', `hold_time` = '%(hold_time)s', `park_time` = '%(park_time)s', `authvalid` = '%(authvalid)s', `authcode` = '%(authcode)s', `user_charged` = '%(user_charged)s', `call_charge` = '%(call_charge)s', `currency` = '%(currency)s', `amount_change` = '%(amount_change)s', `call_units` = '%(call_units)s', `units_change` = '%(units_change)s', `cost_per_unit` = '%(cost_per_unit)s', `markup` = '%(markup)s'; """ % dictv log.debug(u"Query: " + unicode(q)) cursor.execute(q) cursor.close() else: log.error(u"Parse error on line (len " + str(len(vals)) + " vs " + str(len(fieldlist)) + "): " + unicode(line))
#encoding: utf-8 import toolkit import ConfigParser import MySQLdb conf = ConfigParser.ConfigParser() conf.read('task.conf') HOST = conf.get('mysql', 'host') USER = conf.get('mysql', 'user') PSWD = conf.get('mysql', 'pswd') DB = conf.get('mysql', 'base') DB = toolkit.MySQLClient(HOST, USER, PSWD, DB, _isdict=True) FROM = DB.getRows("select * from item_from_suning") for i in FROM: f = lambda x: MySQLdb.string_literal(x) #print f(i['store_adv']) DB.update( "insert into items (`store_item_id`,`item_brand`,`item_model`,`store_title`,`item_store`, \ `store_price`,`item_url`,`item_thumb`,`item_category`,`item_property`) values ('%s','%s','%s',\"%s\",'%s',%f,'%s','%s','%s','%s') " % (i['store_item_id'], i['item_brand'], i['item_model'], i['store_title'], '"suning"', i['store_price'], i['item_url'], i['item_thumb'], i['item_category'], i['item_property']))
def normal(gs, form, filename): q = gs.getRandQuestion(gs.game_name) # Make sure table has a row for me rows = gs.db.sqlReturn( \ 'select n_correct, n_total from normal_scores where u_id = %d and g_id = %d;' \ % (gs.u_id, gs.g_id)) if len(rows) == 0: gs.db.sql('insert into normal_scores (u_id, g_id, n_correct, n_total) values (%d,%d,%d,%d);' % (gs.u_id, gs.g_id, 0, 0)) n_correct = 0 n_total = 0 else: n_correct = rows[0][0] n_total = rows[0][1] # Figure out correctness last_answer = form.getvalue('answer') correct = None if last_answer: sep = last_answer.find('-') last_q_id = int(last_answer[:sep]) last_string = last_answer[sep+1:] rows = gs.db.sqlReturn( 'select correct from answers where q_id=%d and answer=%s;' % \ (last_q_id, MySQLdb.string_literal(last_string))) if len(rows) == 1: if rows[0][0] == 1: correct = True gs.db.sql('''\ update normal_scores set n_correct=n_correct+1, n_total=n_total+1 where u_id=%d and g_id=%d;''' % (gs.u_id, gs.g_id)) gs.db.sql('update users set points=points+10 where u_id=%d;'%gs.u_id) else: correct = False gs.db.sql('''\ update normal_scores set n_total=n_total+1 where u_id=%d and g_id=%d;''' % (gs.u_id, gs.g_id)) rows = gs.db.sqlReturn( \ 'select n_correct, n_total from normal_scores where u_id = %d and g_id = %d;' \ % (gs.u_id, gs.g_id)) n_correct = rows[0][0] n_total = rows[0][1] # Print info gs.printHeader(gs.game_name + '-Normal') print '<h1 class="question">%s</h1><br />' % (q.string) print '<div class="question_bar"> </div><br />' print '<div class="question_form">' print '<table width="520px">' print '<form action="%s" method="get">' % filename spot = int((len(q.answers) + 1) / 2) i = 0 for a in q.answers: print '<tr>' print '<td>' print '<input type="radio" name="answer" value="%d-%s">%s</input><br />' \ % (q.q_id, a.string, a.string) print '</td>' print '<td align="right">' if i == spot-1: print n_correct, '/', n_total elif i == spot and not correct is None: if correct: print 'Correct!' else: print 'Incorrect!' print '</td>' print '</tr>' i += 1