def checkWordfilters(message, ip, board): fixed_ip = inet_aton(ip) wordfilters = FetchAll("SELECT * FROM `filters` WHERE `type` = '0' ORDER BY `id` ASC") for wordfilter in wordfilters: if wordfilter["boards"] != "": boards = pickle.loads(wordfilter["boards"]) if wordfilter["boards"] == "" or board in boards: if wordfilter['action'] == '0': if not re.search(wordfilter['from'], message, re.DOTALL | re.IGNORECASE) is None: raise UserError, wordfilter['reason'] elif wordfilter['action'] == '1': message = re.compile(wordfilter['from'], re.DOTALL | re.IGNORECASE).sub(wordfilter['to'], message) elif wordfilter['action'] == '2': # Ban if not re.search(wordfilter['from'], message, re.DOTALL | re.IGNORECASE) is None: if wordfilter['seconds'] != '0': until = str(timestamp() + int(wordfilter['seconds'])) else: until = '0' InsertDb("INSERT INTO `bans` (`ip`, `boards`, `added`, `until`, `staff`, `reason`, `note`, `blind`) VALUES (" + \ "'" + str(fixed_ip) + "', '" + _mysql.escape_string(wordfilter['boards']) + \ "', " + str(timestamp()) + ", " + until + ", 'System', '" + _mysql.escape_string(wordfilter['reason']) + \ "', 'Word Auto-ban', '"+_mysql.escape_string(wordfilter['blind'])+"')") regenerateAccess() raise UserError, wordfilter['reason'] elif wordfilter['action'] == '3': if not re.search(wordfilter['from'], message, re.DOTALL | re.IGNORECASE) is None: raise UserError, '<meta http-equiv="refresh" content="%s;url=%s" />%s' % (wordfilter['redirect_time'], wordfilter['redirect_url'], wordfilter['reason']) return message
def trackLyrics(self, mbid): query = "select Lyrics from TrackLyrics where MBID='" + _mysql.escape_string(mbid) + "';" dbcfg = self.mc.get('database') dl = _mysql.connect(host=dbcfg['host'],user=dbcfg['username'],passwd=dbcfg['password'],db=dbcfg['database']) dl.query(query) ret = "" r = dl.store_result() if r.num_rows() > 0: dd = r.fetch_row(1,1)[0] ret = dd['Lyrics'] if len(ret) == 0: info = self.track(mbid) artist = info['Artist']['Name'] track = info['Title'] url = "http://lyrics.wikia.com/" + artist.replace(' ', '_') + ':' + track.replace(' ', '_') fp = urllib.urlopen(url) soup = BeautifulSoup(fp) for div in soup.findAll('div', { 'class' : 'lyricbox' }): for elem in div.findAll(text=True): if elem.parent.name == 'script': continue s = elem.encode('utf8', 'ignore') if s.find('<p>') >= 0: continue ret = ret + s + "\n" fp.close() if(len(ret) > 0): query = "insert ignore into TrackLyrics (MBID, Lyrics) values ('" + _mysql.escape_string(mbid) + "', '" + _mysql.escape_string(ret) + "');" dl.query(query) return ret
def update_class(class_name, class_type, urls, strings, uploaduid): con = None class_name = _mysql.escape_string(str(class_name)) urls = _mysql.escape_string(str(urls)) strings = _mysql.escape_string(str(strings)) class_type = _mysql.escape_string(str(class_type)) #jar_type jar_main_class try: con = _mysql.connect('localhost', 'root', 'password', 'sandyfiles') #just for fun assigning it here #Escape urls _mysql.escape_string(str(classmd5)) #UPDATE Table SET Field=CONCAT(Field,'your extra html'); con.query("UPDATE `sandyfiles`.`uploads` SET urls=CONCAT(urls,'" + str(urls) + "'),strings=CONCAT(strings,'" + str(strings) + "'), jar_type='" + str(class_type) + "', jar_main_class='" + str(class_name) + "' WHERE `uploads`.`uploadid` =" + str(uploaduid)) result = con.use_result() print "Update done" print result.fetch_row()[0] except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) pass return
def mssqlmeta(meta, uid, fileinfo): try: con = None fileinfo = _mysql.escape_string(str(fileinfo)) meta = _mysql.escape_string(str(meta)) try: con = _mysql.connect('localhost', 'root', 'password', 'sandyfiles') uid = uid con.query("UPDATE `sandyfiles`.`uploads` SET `metascan` = '" + meta + "',`fileinfo` = '" + fileinfo + "' WHERE `uploads`.`uploadid` =" + str(uid)) result = con.use_result() ##print result.fetch_row()[0] except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) pass finally: if con: con.close()
def zipupdate(classmd5,name,jarfiltype,uploaduid): print "Am inside Zipupdate" try: con = None classmd5 = _mysql.escape_string(str(classmd5)) name = _mysql.escape_string(str(name)) jarfiltype = _mysql.escape_string(str(jarfiltype)) try: Con = MySQLdb.Connect(host="127.0.0.1", port=3306, user="******", passwd="password", db="sandyfiles") Cursor = Con.cursor() sql= "INSERT INTO sandyfiles.zipfiles (fname,md5,uid,filetype) VALUES ('"+str(name)+"','"+str(classmd5)+"','"+str(uploaduid)+"','"+str(jarfiltype)+"' )" print sql Cursor.execute(sql) attribid= Cursor.lastrowid print attribid Cursor.close() Con.commit() Con.close() except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) pass finally: if con: con.close()
def update_uploads(file_type, zipmetadata, binaryfound, uploaduid, urls): con = None file_type = _mysql.escape_string(str(file_type)) zipmetadata = _mysql.escape_string(str(zipmetadata)) urls = _mysql.escape_string(str(urls)) try: con = _mysql.connect('localhost', 'root', 'password', 'sandyfiles') #just for fun assigning it here con.query("UPDATE `sandyfiles`.`uploads` SET filetype='" + str(file_type) + "', metascan='" + str(zipmetadata) + "' ,binaryfound='" + str(binaryfound) + "' , urls='" + str(urls) + "' WHERE `uploads`.`uploadid` =" + str(uploaduid)) result = con.use_result() print "Update done" print result.fetch_row()[0] except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) pass return
def autoattribinsert(uid,author,title,credate,moddate,attribid,mime): print uid,author,title,credate,moddate,attribid,mime try: con = None uid =uid attribid= attribid author = _mysql.escape_string(str(author)) title = _mysql.escape_string(str(title)) credate = _mysql.escape_string(str(credate)) moddate = _mysql.escape_string(str(moddate)) try: con = _mysql.connect('localhost', 'root' ,'password', 'sandyfiles') uid =uid #filetype con.query("UPDATE `sandyfiles`.`uploads` SET `title` = '"+title+"',`author` = '"+author+"',`creadate` = '"+credate+"' ,`attribid` = '"+str(attribid)+"',`filetype` = '"+mime+"' WHERE `uploads`.`uploadid` ="+str(uid)) #print "UPDATE `sandyfiles`.`uploads` SET `title` = '"+title+"',`author` = '"+author+"',`creadate` = '"+credate+"',`moddate` = '"+moddate+"' WHERE `uploads`.`uploadid` ="+str(uid) result = con.use_result() print result.fetch_row()[0] except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) pass finally: if con: con.close()
def onCommand(self, source, args): arg = args.split() if len(arg) > 1: if arg[0].startswith("#"): user = arg[0][1:] if self.user(user): sender = self.auth(source) message = _mysql.escape_string(' '.join(arg[1:])) self.query( "insert into memo (`user`, `source`, `message`) values ('%s', '%s', '%s')" % (user, sender, message)) self.msg(source, "Done.") self.memo(user) else: self.msg(source, "Can't find user %s." % arg[0]) else: user = self.auth(arg[0]) if self.user(user): sender = self.auth(source) message = _mysql.escape_string(' '.join(arg[1:])) self.query( "insert into memo (`user`, `source`, `message`) values ('%s', '%s', '%s')" % (user, sender, message)) self.msg(source, "Done.") self.memo(user) else: self.msg(source, "Can't find user %s." % arg[0]) else: self.msg(source, "Syntax: MEMO <user> <message>")
def mssqljobdone(suid, md5hash, yara_results, strings, ctid): con = None strings = _mysql.escape_string(str(strings)) #scandata =scandata[300:] try: con = _mysql.connect('localhost', 'root', 'password', 'sandyfiles') #just for fun assigning it here suid = suid md5hash = md5hash ctid = ctid yara_results = _mysql.escape_string(str(yara_results)) con.query("UPDATE `sandyfiles`.`uploads` SET done='1', ctid='" + str(ctid) + "', sigscan2='" + str(yara_results) + "' , strings=CONCAT(strings,'" + str(strings) + "'),md5='" + str(md5hash) + "' WHERE `uploads`.`uploadid` =" + str(suid)) result = con.use_result() print "Update done" print result.fetch_row()[0] except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) pass return
def storyObject(pirEle,cursor,actionType): actionEle = pirEle.getElementsByTagName("action")[0] story_text = _mysql.escape_string(urllib.unquote(Common.checkXMLValue("story_text",actionEle))) title = _mysql.escape_string(urllib.unquote(Common.checkXMLValue("title",actionEle))) startsOn = Common.checkXMLValue("starts_on",actionEle) if (startsOn): month,day,year = startsOn.split('/') startsOn = "%s-%s-%s" % (year,month.rjust(2,'0'),day.rjust(2,'0')) endsOn = Common.checkXMLValue("ends_on",actionEle) if (endsOn): month,day,year = endsOn.split('/') endsOn = "%s-%s-%s" % (year,month.rjust(2,'0'),day.rjust(2,'0')) address,city,state,country,zipcode = getLocation(actionEle) if (actionType == "add"): cursor.execute("""insert into rs_story (story_text, title, starts_on, ends_on, address, city, state, country, zipcode, enterer_id) values ("%s","%s","%s","%s","%s","%s","%s","%s","%s",1)""" \ % (story_text,title,startsOn,endsOn,address,city,state,country,zipcode)) object_id = cursor.lastrowid Common.addXMLValue(actionEle,"object_id",str(object_id)) for objects in pirEle.getElementsByTagName("objects"): Common.addXMLValue(objects,"object_id",str(object_id)) if (Common.checkXMLValue("connect",actionEle)): connectObjects(actionEle,cursor) elif (actionType == "update"): storyId = Common.checkXMLValue("object_id",actionEle) cursor.execute("""update rs_story set story_text = "%s", title = "%s", starts_on = "%s", ends_on = "%s", address = "%s", city = "%s", state = "%s", country = "%s", zipcode = "%s" where id = %s""" % (story_text,title,startsOn,endsOn,address,city,state, \ country,zipcode,storyId))
def onCommand(self, source, args): arg = args.split() if len(arg) == 1: if arg[0].startswith("#"): exists = False for data in self.query("select channel from channels where channel = '%s'" % escape_string(arg[0])): exists = True if not exists: if not self.suspended(arg[0]): self.query("insert into channelinfo values ('%s', '', '', '', '', '10:5', '!')" % escape_string(arg[0])) self.query("insert into channels values ('%s','%s','n')" % (escape_string(arg[0]), self.auth(source))) self.join(arg[0]) self.mode(arg[0], "+q {0}".format(source)) self.msg(source, "Channel %s has been registered for you" % arg[0]) else: self.msg(source, "Channel " + arg[0] + " is suspended: " + self.suspended(arg[0])) else: self.msg(source, "Channel %s is already registered" % arg[0]) else: self.msg(source, "Invalid channel: {0}".format(arg[0])) else: self.msg(source, "Syntax: REQUEST <#channel>")
def new_user_route(): username = request.form['username'] password = request.form['password'] confirm_password = request.form['confirm_password'] if len(username) > 256: return json.dumps( {'error': 'Username cannot be more than 256 characters long\n'}), 409 if len(password) > 256: return json.dumps( {'error': 'Password cannot be more than 256 characters long\n'}), 409 if not len(username): return json.dumps({'error': 'Username cannot be left blank\n'}), 409 if not len(password): return json.dumps({'error': 'Password cannot be left blank\n'}), 409 if password != confirm_password: return json.dumps({'error': 'Passwords do not match\n'}), 409 with connect_to_database() as cur: cur.execute( "SELECT EXISTS(SELECT 1 FROM userInfo WHERE username = '******');" % escape_string(username)) cur_response = cur.fetchone() user_exists = cur_response.items()[0][1] if user_exists: user_conflict_json = json.dumps( {'error': 'Username is already taken\n'}) return user_conflict_json, 409 hash_handler = pbkdf2_sha512.using(rounds=123456) hashed_password = hash_handler.hash(password) cur.execute( "INSERT INTO userInfo (username, password) values ('%s', '%s');" % (escape_string(username), hashed_password)) return json.dumps({}), 200
def getCurrentChart(self): url = "http://www.bbc.co.uk/radio1/chart/singles.xml" http = urllib2.urlopen(url) xmldoc = minidom.parse(http) xmlobj = xmldoc.getElementsByTagName('record')[0] chart = [] ds = xmlobj.attributes['timestamp'].value do = iso8601.parse_date(ds) dt = time.mktime(do.timetuple()) ds = datetime.utcfromtimestamp(dt).strftime("%Y-%m-%d") dl = _mysql.connect(host=self.dbcfg['host'],user=self.dbcfg['username'],passwd=self.dbcfg['password'],db=self.dbcfg['database']) for entry in xmlobj.getElementsByTagName('entry'): item = {} item['title'] = str(entry.getElementsByTagName('title')[0].firstChild.data.encode('utf8', 'ignore')) item['artist'] = str(entry.getElementsByTagName('artist')[0].firstChild.data.encode('utf8', 'ignore')) item['chart_date'] = ds item['position'] = int(entry.getElementsByTagName('position')[0].firstChild.data) query = "insert ignore into UKChart (ChartDate, ChartPosition, SongTitle, ArtistName) values (" query = query + "'" + _mysql.escape_string(item['chart_date']) + "', " query = query + "'" + str(item['position']) + "', " query = query + "'" + _mysql.escape_string(item['title']) + "', " query = query + "'" + _mysql.escape_string(item['artist']) + "'" query = query + ");" dl.query(query) chart.append(item) return chart
def update_traffic(urlid,request,response,filetype): print "Am inside Traffic Update" try: con = None request = _mysql.escape_string(str(request)) response = _mysql.escape_string(str(response)) filetype = _mysql.escape_string(str(filetype)) try: Con = MySQLdb.Connect(host="127.0.0.1", port=3306, user="******", passwd="password", db="sandyfiles") Cursor = Con.cursor() sql= "INSERT IGNORE INTO sandyfiles.traffic (urlid,request,response,filetype) VALUES ('"+str(urlid)+"','"+str(request)+"','"+str(response)+"','"+str(filetype)+"' )" print sql Cursor.execute(sql) attribid= Cursor.lastrowid print attribid Cursor.close() Con.commit() Con.close() except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) pass finally: if con: con.close()
def insert_html(urlid,html,yara_results): print "Am inside update yara + counter" try: con = None html = html.encode('base64','strict') html = _mysql.escape_string(html) yara_results = _mysql.escape_string(yara_results) inf_status=4 #CONCAT( field, ' this is appended' ) try: Con = MySQLdb.Connect(host="127.0.0.1", port=3306, user="******", passwd="password", db="sandyfiles") Cursor = Con.cursor() sql= "UPDATE `sandyfiles`.`links` SET infection_status=infection_status+1 , sigscan=CONCAT(sigscan,'"+str(yara_results)+"') WHERE `links`.`id` ="+str(urlid) print sql Cursor.execute(sql) attribid= Cursor.lastrowid print attribid Cursor.close() Con.commit() Con.close() except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) pass finally: if con: con.close()
def storyObject(pirEle, cursor, actionType): actionEle = pirEle.getElementsByTagName("action")[0] story_text = _mysql.escape_string( urllib.unquote(Common.checkXMLValue("story_text", actionEle))) title = _mysql.escape_string( urllib.unquote(Common.checkXMLValue("title", actionEle))) startsOn = Common.checkXMLValue("starts_on", actionEle) if (startsOn): month, day, year = startsOn.split('/') startsOn = "%s-%s-%s" % (year, month.rjust(2, '0'), day.rjust(2, '0')) endsOn = Common.checkXMLValue("ends_on", actionEle) if (endsOn): month, day, year = endsOn.split('/') endsOn = "%s-%s-%s" % (year, month.rjust(2, '0'), day.rjust(2, '0')) address, city, state, country, zipcode = getLocation(actionEle) if (actionType == "add"): cursor.execute("""insert into rs_story (story_text, title, starts_on, ends_on, address, city, state, country, zipcode, enterer_id) values ("%s","%s","%s","%s","%s","%s","%s","%s","%s",1)""" \ % (story_text,title,startsOn,endsOn,address,city,state,country,zipcode)) object_id = cursor.lastrowid Common.addXMLValue(actionEle, "object_id", str(object_id)) for objects in pirEle.getElementsByTagName("objects"): Common.addXMLValue(objects, "object_id", str(object_id)) if (Common.checkXMLValue("connect", actionEle)): connectObjects(actionEle, cursor) elif (actionType == "update"): storyId = Common.checkXMLValue("object_id", actionEle) cursor.execute("""update rs_story set story_text = "%s", title = "%s", starts_on = "%s", ends_on = "%s", address = "%s", city = "%s", state = "%s", country = "%s", zipcode = "%s" where id = %s""" % (story_text,title,startsOn,endsOn,address,city,state, \ country,zipcode,storyId))
def onCommand(self, source, args): arg = args.split() if len(arg) > 1: if arg[0].startswith("#"): user = arg[0][1:] if self.user(user): sender = self.auth(source) message = _mysql.escape_string(' '.join(arg[1:])) self.query("insert into memo (`user`, `source`, `message`) values ('%s', '%s', '%s')" % (user, sender, message)) self.msg(source, "Done.") self.memo(user) else: self.msg(source, "Can't find user %s." % arg[0]) else: user = self.auth(arg[0]) if self.user(user): sender = self.auth(source) message = _mysql.escape_string(' '.join(arg[1:])) self.query("insert into memo (`user`, `source`, `message`) values ('%s', '%s', '%s')" % (user, sender, message)) self.msg(source, "Done.") self.memo(user) else: self.msg(source, "Can't find user %s." % arg[0]) else: self.msg(source, "Syntax: MEMO <user> <message>")
def escape_string(s): if isinstance(s, (int, long, float)): return str(s) elif isinstance(s, unicode): return "'%s'"%_mysql.escape_string(s.encode("UTF-8")) elif isinstance(s, basestring): return "'%s'"%_mysql.escape_string(s) return ''
def _insertIfNotExists(self, table, key, data): self.__cursor.execute("""SELECT count(`id`) FROM %s WHERE `id`='%s'""" % (table, _mysql.escape_string(key))) r = self.__cursor.fetchone() if not r or r[0] == 0: self.__cursor.execute( """INSERT INTO %s ( `id`, `data` ) VALUES ( '%s', '%s' );""" % (table, _mysql.escape_string(key), _mysql.escape_string(pickle.dumps(data))) )
def ip(name, nickname, description, img, rank): sql = u"""INSERT INTO `me2echelon`.`crazysearch_user` (`id` ,`name` ,`nickname` ,`description` ,`img` ,`rank` ,`last`) VALUES (NULL , '%s', '%s', '%s', '%s', '%s', NOW( )); """ % (name, _mysql.escape_string(nickname), _mysql.escape_string(description), img, rank) sql = sql.encode('utf-8') try: query(sql) except Exception: pass
def format_value(v): if isinstance(v,(int,long,float)): return str(v) elif isinstance(v,unicode): v = v.encode("UTF-8") return "'%s'" % _mysql.escape_string(v) elif isinstance(v,str): return "'%s'" % _mysql.escape_string(v) elif isinstance(v,MysqlExpr): return v elif v is None: return ''
def checkNamefilters(name, tripcode, ip, board): namefilters = FetchAll("SELECT * FROM `filters` WHERE `type` = '1'") for namefilter in namefilters: if namefilter["boards"] != "": boards = pickle.loads(namefilter["boards"]) if namefilter["boards"] == "" or board in boards: # check if this filter applies match = False if namefilter['from'] and namefilter['from_trip']: # both name and trip filter if re.search( namefilter['from'], name, re.DOTALL | re.IGNORECASE) and tripcode == namefilter['from_trip']: match = True elif namefilter['from'] and not namefilter['from_trip']: # name filter if re.search(namefilter['from'], name, re.DOTALL | re.IGNORECASE): match = True elif not namefilter['from'] and namefilter['from_trip']: # trip filter if tripcode == namefilter['from_trip']: match = True if match: # do action if namefilter['action'] == '0': raise UserError, namefilter['reason'] elif namefilter['action'] == '1': name = namefilter['to'] tripcode = '' return name, tripcode elif namefilter['action'] == '2': # Ban if namefilter['seconds'] != '0': until = str(timestamp() + int(namefilter['seconds'])) else: until = '0' InsertDb("INSERT INTO `bans` (`ip`, `boards`, `added`, `until`, `staff`, `reason`, `note`, `blind`) VALUES (" + \ "'" + _mysql.escape_string(ip) + "', '" + _mysql.escape_string(namefilter['boards']) + \ "', " + str(timestamp()) + ", " + until + ", 'System', '" + _mysql.escape_string(namefilter['reason']) + \ "', 'Name Auto-ban', '"+_mysql.escape_string(namefilter['blind'])+"')") regenerateAccess() raise UserError, namefilter['reason'] elif namefilter['action'] == '3': raise UserError, '<meta http-equiv="refresh" content="%s;url=%s" />%s' % ( namefilter['redirect_time'], namefilter['redirect_url'], namefilter['reason']) return name, tripcode
def onCommand(self, uid, args): import _mysql arg = args.split() if len(arg) > 1: channel = _mysql.escape_string(arg[1]) if len(arg) > 2: reason = _mysql.escape_string(' '.join(arg[2:])) if len(arg) == 2 and arg[0].lower() == "remove": if arg[1].startswith("#"): if self.suspended(channel): self.query("delete from suspended where channel = '%s'" % channel) self.msg(uid, "Unsuspended.") else: self.msg(uid, arg[1]+" is not suspended.") else: self.msg(uid, "Invalid channel: "+arg[1]) elif len(arg) > 2 and arg[0].lower() == "set": if arg[1].startswith("#"): if not self.suspended(channel): self.query("insert into suspended (`channel`, `reason`) values ('%s', '%s')" % (channel, reason)) if self.chanexist(channel): self.query("delete from channels where channel = '{0}'".format(channel)) self.query("delete from channelinfo where name = '{0}'".format(channel)) self.query("delete from banlist where channel = '{0}'".format(channel)) self.send(":{0} PART {1} :Channel {1} has been suspended.".format(self.bot, arg[1])) for user in self.userlist(channel): if not self.isoper(user): self.kick(arg[1], user, "Suspended: "+' '.join(arg[2:])) else: self.msg(arg[1], "This channel is suspended: "+' '.join(arg[2:])) else: self.query("update suspended set reason = '%s' where channel = '%s'" % (reason, channel)) for user in self.userlist(channel): if not self.isoper(user): self.kick(arg[1], user, "Suspended: "+' '.join(arg[2:])) else: self.msg(arg[1], "This channel is suspended: "+' '.join(arg[2:])) self.msg(uid, "Suspended.") else: self.msg(uid, "Invalid channel: "+arg[1]) elif len(arg) == 1 and arg[0].lower() == "list": for data in self.query("select * from suspended"): self.msg(uid, "Channel: {0} {1} Reason: {2}".format(data["channel"], " "*int(23-len(data["channel"])), data["reason"])) else: self.msg(uid, "Syntax: SUSPEND <list/set/remove> <#channel> [<reason>]")
def dict_to_sql(params, sep=', '): cols = [] for k, v in params.iteritems(): k2 = _mysql.escape_string(str(k)) if v is None: col = '`%s`=NULL' % k2 elif isinstance(v, (int, long, float)): col = '`%s`=%s' % (k2, v) elif isinstance(v, unicode): v2 = v.encode('utf-8') col = '`%s`="%s"' % (k2, smart_unicode(_mysql.escape_string(smart_str(v)))) else: col = '`%s`="%s"' % (k2, v) cols.append(col) return smart_unicode(sep.join(cols))
def insert(cls, _dic): """ 插入Something... :param _dic: 新增的字典 :return: """ key_value_lst = [] for key, value in _dic.items(): # 普通字符串 if type(value) == str or type(value) == unicode: value = _mysql.escape_string(value) item = "'%s'" % value else: item = "%s" % value key_value_lst.append(item) sql = "insert into {db}.{tbl}({column_list}) values ({value_list})". \ format(db=cls.db_name, tbl=cls.table_name, column_list=','.join(["`%s`" % v for v in _dic.keys()]), value_list=','.join(key_value_lst)) logger.info("base_insert===> %s" % sql) if cls.db_name == db_name_config.DOCTOR_DB: ret = doctor_conn.execute_with_exception(sql) elif cls.db_name == db_name_config.DOCTOR_USER_DB: ret = doctor_user_conn.execute_with_exception(sql) elif cls.db_name == db_name_config.DOCTOR_QUESTION_DB: ret = doctor_question_conn.execute_with_exception(sql) else: logger.error("error db...") ret = None return ret
def onCommand(self, source, args): from _mysql import escape_string arg = args.split() if len(arg) == 2 and arg[0] == "set": if arg[1].find(".") == -1: self.msg(source, "Invalid vhost. Where's the dot?") elif arg[1][-2] == "." or arg[1][-1] == ".": self.msg(source, "Domain ending is too short.") elif arg[1].find("@") != -1 and len(arg[1].split("@")[0]) < 3: self.msg(source, "vIdent too short.") elif arg[1].find("@") != -1 and len(arg[1].split("@")[1]) < 6: self.msg(source, "vHost too short.") elif arg[1].find("@") != -1 and arg[1].split("@")[0].find(".") != -1: self.msg(source, "No dots allowed in vIdent.") elif arg[1].find("@") != -1 and arg[1].split("@")[1].find(".") == -1: self.msg(source, "Thats no vHost, the DOT is missing.") elif len(arg[1]) < 6: self.msg(source, "Your vhost is too short.") else: entry = False vhost = arg[1] if vhost.find("@") != -1: vhost = vhost.split("@")[0] for data in self.query("select user from vhosts where vhost = '%s' and user != '%s'" % (escape_string(vhost), self.auth(source))): user = data["user"] entry = True if not entry: self.query("delete from vhosts where user = '******'" % self.auth(source)) self.query("insert into vhosts values ('%s','%s','0')" % (self.auth(source), escape_string(arg[1]))) self.msg(source, "Your new vhost %s has been requested" % arg[1]) for data in self.query("select host,username from online where uid = '%s'" % source): if not self.gateway(source): self.send(":%s CHGIDENT %s %s" % (self.bot, source, data["username"])) self.send(":%s CHGHOST %s %s" % (self.bot, source, data["host"])) else: self.send(":%s CHGIDENT %s %s" % (self.bot, source, data["username"])) crypthost = self.encode_md5(source + ":" + self.nick(source) + "!" + self.userhost(source)) self.send(":%s CHGHOST %s %s.gateway.%s" % (self.bot, source, crypthost, '.'.join(self.services_name.split(".")[-2:]))) for data in self.query("select uid from opers"): self.msg(data["uid"], "vHost request received from %s" % self.auth(source)) else: self.msg(source, "%s is already using this vHost." % user) elif len(arg) == 1 and arg[0].lower() == "info": vhost = self.getvhost(source) if vhost != "None": self.msg(source, "Your current vHost is: " + vhost) else: self.msg(source, "You did not set a vHost or userflag +x.") elif len(arg) == 1 and arg[0].lower() == "remove": self.query("delete from vhosts where user = '******'" % self.auth(source)) self.vhost(source) self.msg(source, "Done.") else: self.msg(source, "Syntax: VHOST <info/set/remove> [<vhost>]")
def batchlog(self, cursor, entries): logdir = os.path.join('/srv/logs', self.ident) if not os.path.exists(logdir): os.makedirs(logdir) logpath = os.path.join(logdir, (str(self.number) + (utils.format_attempt_path(self.attempt) + '_' + self.workname + '.log'))) with open(logpath, 'a+') as f: sql = ('insert into work_logs(id, number, workname, worker, log, ' 'timestamp, constraints, attempt) values ') values = [] for timestamp, log in entries: values.append('("%s", %s, "%s", "%s", "%s", %s, "%s", %s)' %(self.ident, self.number, self.workname, self.worker, _mysql.escape_string(log), utils.datetime_as_sql(timestamp), self.constraints, self.attempt)) f.write('%s %s\n' %(timestamp, log.rstrip())) sql += ', '.join(values) sql += ';' cursor.execute(sql) cursor.execute('commit;') if len(entries) > 1: print '%s Pushed %d log lines to server' %(datetime.datetime.now(), len(entries)) self.heartbeat(cursor)
def onCommand(self, source, args): from _mysql import escape_string arg = args.split() if len(arg) == 1: if arg[0].startswith("#"): if self.getflag(source, arg[0]) == "n" or self.getflag(source, arg[0]) == "q" or self.getflag(source, arg[0]) == "a": for channel in self.query("select name,modes from channelinfo where name = '{0}'".format(escape_string(arg[0]))): self.msg(source, "Current modes for {0}: {1}".format(channel["name"], channel["modes"])) else: self.msg(source, "Denied.") else: self.msg(source, "Invalid channel '{0}'".format(arg[0])) elif len(arg) == 2: modes = arg[1] if arg[0].startswith("#"): if self.getflag(source, arg[0]) == "n" or self.getflag(source, arg[0]) == "q" or self.getflag(source, arg[0]) == "a": for channel in self.query("select name,modes from channelinfo where name = '{0}'".format(escape_string(arg[0]))): modes = self.regexflag(channel["modes"], modes, True) self.query("update channelinfo set modes = '{0}' where name = '{1}'".format(escape_string(modes), channel["name"])) self.mode(channel["name"], modes) self.msg(source, "Done. New modes for {0}: {1}".format(channel["name"], modes)) else: self.msg(source, "Denied.") else: self.msg(source, "Invalid channel '{0}'".format(arg[0])) else: self.msg(source, "Syntax: CHANMODE <#channel> [<modes>]")
def write_vals(bt, inns, team, odi, summ): """ """ SQL = ("INSERT INTO `score`(odi, team, player, inns, runs, balls, mins, fours, sixes, pos," "`out`, out_over, out_score, out_wicket_no, out_by, out_assist) VALUES ") vals = [] for bb in bt: row = "(" row += '{},"{}","{}",{},{},{},'.format(odi, team, _mysql.escape_string(bb.player), inns, bb.runs, bb.balls) row += '{},{},{},{},'.format(bb.mins, bb.fours, bb.sixes, bb.pos) if bb.out_type not in WICKET_SET: row += '"notout",NULL,NULL,NULL,' row += 'NULL,NULL' else: if hasattr(bb, "out_over"): row += '"{}",{},{},{},'.format(bb.out_type, bb.out_over, bb.out_score, bb.out_wicket_no) else: row += '"{}",{},{},{},'.format(bb.out_type, summ[2], summ[0], summ[1]) if hasattr(bb, 'out_by'): row += '"{}",'.format(bb.out_by) else: row += 'NULL,' if hasattr(bb, 'out_assist'): row += '"{}"'.format(bb.out_assist) else: row += 'NULL' row = row.replace('"None"', 'NULL') row += ')' vals.append(row) mysql_execute(SQL + ",".join(vals))
def post_paste(pasteid, text): db = PasteConn() _db = db.db entry = ( "Insert into {0}.pastes (id, paste) values ('{1}', '{2}');" ).format(db.mysqldb, pasteid, _mysql.escape_string(text)) _db.query(entry)
def onCommand(self, uid, args): mode = list() desc = list() mode.append("n") desc.append("%s will answer with notices, instead of privmsgs." % self.bot_nick) mode.append("a") desc.append("Autojoin all channels where you have chanflag +v or higher.") mode.append("x") desc.append("Cloak your hostname.") arg = args.split() if len(arg) == 0: self.msg(uid, "Current user flags: +"+self.userflags(uid)) elif len(arg) == 1: if arg[0] == "?": i = 0 while i != len(mode): self.msg(uid, "+" + mode[i]+" = "+desc[i]) i += 1 else: userflags = self.regexflag("+" + self.userflags(uid), arg[0]) flags = ''.join([char for char in userflags if char in ''.join(mode)]) self.query("update users set flags = '%s' where name = '%s'" % (escape_string(flags), self.auth(uid))) self.msg(uid, "Done. Current user flags: +" + flags) if arg[0].find("x") != -1: self.vhost(uid) else: self.msg(uid, "Syntax: USERFLAGS [<flags>]")
def storyEle(storyEle, storyId, cursor): sql = """select id,title,story_text,date_format(starts_on,'%%m/%%d/%%Y'), date_format(ends_on,'%%m/%%d/%%Y'), address,city,state,country,zipcode, location_note from rs_story where id = %s""" % (storyId) cursor.execute(sql) row = cursor.fetchone() if (row): ele = pirDoc.createElement("story") storyEle.appendChild(ele) Common.addXMLValue(ele, "data", str(row[0])) Common.addXMLValue(ele, "label", str(row[1])) ele.setAttribute("object_id", str(row[0])) Common.addXMLValue(ele, "object_id", str(row[0])) Common.addXMLValue(ele, "title", str(row[1])) Common.addXMLValue(ele, "title_html", _mysql.escape_string(str(row[1]))) Common.addXMLValue(ele, "story_text", str(row[2])) if row[3]: starts_on = str(row[3]) else: starts_on = "00/00/0000" if row[4]: ends_on = str(row[4]) else: ends_on = "00/00/0000" Common.addXMLValue(ele, "starts_on", starts_on) Common.addXMLValue(ele, "ends_on", ends_on) printLocation(row[5], row[6], row[7], row[8], row[9], row[10], ele)
def onCommand(self, source, args): import _mysql arg = args.split() if len(arg) == 1: if arg[0].startswith("#"): entry = False for data in self.query("select name,welcome from channelinfo where name = '{0}'".format(arg[0])): self.msg(source, "[{0}] {1}".format(data["name"], data["welcome"])) entry = True if not entry: self.msg(source, "Channel {0} does not exist".format(arg[0])) else: self.msg(source, "Invalid channel") elif len(arg) > 1: if arg[0].startswith("#"): flag = self.getflag(source, arg[0]) welcome = _mysql.escape_string(' '.join(arg[1:])) if flag == "n" or flag == "q" or flag == "a": self.query("update channelinfo set welcome = '{0}' where name = '{1}'".format(welcome, arg[0])) self.msg(source, "Done.") else: self.msg(source, "Denied.") else: self.msg(source, "Invalid channel") else: self.msg(source, "Syntax: WELCOME <#channel> [<text>]")
def insertattribinsert(author): print "Am in attrib insert" try: con = None author = _mysql.escape_string(str(author)) try: Con = MySQLdb.Connect(host="127.0.0.1", port=3306, user="******", passwd="password", db="sandyfiles") Cursor = Con.cursor() sql = "INSERT IGNORE INTO sandyfiles.attribute (attrib_au,time) VALUES ('" + author + "', NOW()) ON DUPLICATE KEY UPDATE time = NOW()" print sql Cursor.execute(sql) attribid = Cursor.lastrowid print attribid Cursor.close() Con.commit() Con.close() return attribid except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) pass finally: if con: con.close()
def SQLExport( toFile, peeweeModel, records, batchSize=50 ): """ Create one or multiple insert queries for the specified records """ # Add a comment regarding the model name toFile.write("-- Model: %s (%s)\n" % (peeweeModel.__name__, peeweeModel._meta.name)) toFile.write("-- --------------------------------\n\n") # Collect rows rows = [] for rec in records: rows.append( rec._data ) print "Dumping %s : %i records" % (str(peeweeModel.__name__), len(rows)) # Insert in batches ofs = 0 toFile.write("BEGIN;\n") while ofs < len(rows): # Creqte the InsertQuery iq = peeweeModel.insert_many( rows[ofs:ofs+batchSize] ) ofs += batchSize # Insert sql = iq.sql() sqlStr = sql[0] % tuple(map(lambda x: "'%s'" % _mysql.escape_string(unicode(x).encode('utf-8')), sql[1])) toFile.write("REPLACE %s;\n" % sqlStr[7:]) toFile.write("COMMIT;\n")
def onCommand(self, source, args): import _mysql arg = args.split() if len(arg) > 1: if arg[0].startswith("#"): if self.getflag(source, arg[0]) == "n" or self.getflag( source, arg[0]) == "q" or self.getflag(source, arg[0]) == "a": self.query( "update channelinfo set topic = '{0}' where name = '{1}'" .format(_mysql.escape_string(' '.join(arg[1:])), arg[0])) self.send(":{0} TOPIC {1} :{2}".format( self.bot, arg[0], ' '.join(arg[1:]))) if self.chanflag("l", arg[0]): self.log("Q", "topic", arg[0], ":" + ' '.join(arg[1:])) self.msg(source, "Done.") else: self.msg(source, "No permission") else: self.msg(source, "Invalid channel '{0}'".format(arg[0])) else: self.msg(source, "Syntax: SETTOPIC <#channel> <topic>")
def escape(sql): sql = str(sql) if not sql: return "" if isinstance(sql, unicode): sql = sql.encode('utf8') return _mysql.escape_string(sql)
def insert(self): post_values = [_mysql.escape_string(str(value)) for key, value in self.post.iteritems()] return InsertDb("INSERT INTO `posts` (`%s`) VALUES ('%s')" % ( "`, `".join(self.post.keys()), "', '".join(post_values) ))
def onCommand(self, source, args): import _mysql if len(args) > 0: entry = False for data in self.query( "select text from feedback where user = '******'" % self.auth(source)): entry = True if not entry: self.query("insert into feedback values('" + self.auth(source) + "','" + _mysql.escape_string(args) + "')") self.msg(source, "Feedback added to queue.") for op in self.query("select uid from opers"): self.msg(str(op["uid"]), "New feedback from %s" % self.auth(source)) else: self.msg( source, "You already sent a feedback. Please wait until an operator read it." ) else: self.msg(source, "FEEDBACK <text>")
def __replaceSql(self, sql, key, kwargs, quote): for i in range(len(kwargs[key])): r = kwargs[key][i] if quote: if type(r) == type([]): joinChar = "%s,%s"%(self.quoteChar,self.quoteChar) ### #r could contain integers which will break join #make sure we cast to strings ### r = joinChar.join( map(lambda s: _mysql.escape_string(str(s)), r) ) sql = sql.replace("%s%i"%(self.replaceString, i), "%s%s%s"%(self.quoteChar, r, self.quoteChar)) else: if type(r) == type([]): ### #r could contain integers which will break join #make sure we cast to strings ### r = ",".join(map(str, r)) sql = sql.replace("%s%i"%(self.replaceString, i), r) #### #If any replace failed, make sure we get rid of all of #the REP strings #### sql = re.sub( '%s%s' % (self.replaceString, '\d+'), '', sql) return sql
def getLocation(pirEle): address_l1 = _mysql.escape_string(urllib.unquote(Common.checkXMLValue("address_l1",pirEle))) city = Common.checkXMLValue("city",pirEle) state = Common.checkXMLValue("state",pirEle) country = Common.checkXMLValue("country",pirEle) zipcode = Common.checkXMLValue("zipcode",pirEle) return address_l1,city,state,country,zipcode
def escape(sql): '''这里假定连接数据库都是使用utf8的。''' if sql is None: return '' if isinstance(sql, unicode): sql = sql.encode('utf8') return _mysql.escape_string(sql)
def storyEle(storyEle,storyId,cursor): sql = """select id,title,story_text,date_format(starts_on,'%%m/%%d/%%Y'), date_format(ends_on,'%%m/%%d/%%Y'), address,city,state,country,zipcode, location_note from rs_story where id = %s""" % (storyId) cursor.execute(sql) row = cursor.fetchone(); if (row): ele = pirDoc.createElement("story") storyEle.appendChild(ele) Common.addXMLValue(ele,"data",str(row[0])) Common.addXMLValue(ele,"label",str(row[1])) ele.setAttribute("object_id", str(row[0])) Common.addXMLValue(ele,"object_id",str(row[0])) Common.addXMLValue(ele,"title",str(row[1])) Common.addXMLValue(ele,"title_html",_mysql.escape_string(str(row[1]))) Common.addXMLValue(ele,"story_text",str(row[2])) if row[3]: starts_on = str(row[3]) else: starts_on = "00/00/0000" if row[4]: ends_on = str(row[4]) else: ends_on = "00/00/0000" Common.addXMLValue(ele,"starts_on",starts_on) Common.addXMLValue(ele,"ends_on",ends_on) printLocation(row[5],row[6],row[7],row[8],row[9], row[10],ele)
def onCommand(self, uid, args): arg = args.split() if len(arg) == 0: self.msg(uid, "Account Reason") for data in self.query("select * from users where suspended != '0'"): self.msg(uid, " {0} {1} {2}".format(data["name"], " "*int(20-len(data["name"])), data["suspended"])) self.msg(uid, "End of list.") elif len(arg) == 1: entry = False if arg[0][0] == "?": if self.user(arg[0][1:]): self.msg(uid, "Suspend status of account " + arg[0][1:] + ": " + str(self.banned(arg[0][1:]))) else: self.msg(uid, "Can't find user " + arg[0][1:]) else: if self.user(arg[0]): self.query("update users set suspended = '0' where name = '%s'" % arg[0]) self.msg(uid, "Done.") else: self.msg(uid, "Can't find user " + arg[0]) elif len(arg) > 1: if self.user(arg[0]): self.query("update users set suspended = '%s' where name = '%s'" % (escape_string(' '.join(arg[1:])), arg[0])) self.msg(uid, "Done.") else: self.msg(uid, "Can't find user " + arg[0])
def insertattribinsert(author): print "Am in attrib insert" try: con = None author = _mysql.escape_string(str(author)) try: Con = MySQLdb.Connect(host="127.0.0.1", port=3306, user="******", passwd="password", db="sandyfiles") Cursor = Con.cursor() sql= "INSERT IGNORE INTO sandyfiles.attribute (attrib_au,time) VALUES ('"+author+"', NOW()) ON DUPLICATE KEY UPDATE time = NOW()" print sql Cursor.execute(sql) attribid= Cursor.lastrowid print attribid Cursor.close() Con.commit() Con.close() return attribid except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) pass finally: if con: con.close()
def run(self): while self.running: query(self.db, "UPDATE media SET state = 'download queue' WHERE state LIKE 'downloading'") query(self.db, "SELECT m.videoid AS videoid, m.title AS title, m.length AS length, m.thumbnail AS thumbnail, SUM(v.vote) AS votes, m.state as status FROM media m, vote v WHERE m.videoid = v.videoid AND m.state LIKE 'download queue' GROUP BY v.videoid ORDER BY votes DESC, added ASC LIMIT 1") dbResult = self.db.store_result() for i in range(dbResult.num_rows()): res = dbResult.fetch_row()[0] print "fetching video info" print res[0] directUrl, videoInfo = getYoutubeVideoInfo(res[0], "http://chalmers.it") filename = os.path.join(MEDIA_DIR, res[0] + ".flv") if os.path.exists(filename): print "Target file already exists: " + filename + ". removing" os.remove(filename) query(self.db, "UPDATE media SET state = 'downloading' WHERE videoid = \"%s\"" % (_mysql.escape_string(res[0]),)) print "downloading video data" result = commands.getstatusoutput("wget --user-agent=\"Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3 (.NET CLR 3.5.30729)\" \"" + directUrl + "\" -O \"" + filename + "\" 1>&2") if result[0] != 0: print "wget exited with an error:" print result[1] else: print "Changing %s to idle" % (videoInfo['title'],) query(self.db, "UPDATE media SET state = 'idle' WHERE videoid = \"%s\"" % (_mysql.escape_string(res[0]),)) sys.stdout.write("d") sys.stdout.flush() time.sleep(1) print "Exiting downloading"
def save(self, data): ''' UPDATE Return : Success: return affected_rows if affected_rows > 0 Fail: return False Arguments: Dict data : {key1:value1, key2:value2, ...} ''' # Judge which data to use if data is None: if self._data is None: return False else: self._data = data # Check Type argsType = type(self._data) if argsType == types.DictType: # Convert Dict to String pass else: functionName = sys._getframe().f_code.co_name errmsg = "Type of argument 'data' in %s() should be %s" \ % (functionName, types.DictType) raise TypeError, errmsg # sql sets = '' for (key, val) in self._data.items(): tmpval = _mysql.escape_string(str(val)) if sets != '': sets = sets + ',' sets = sets + key + "='" + tmpval + "'" sql = "UPDATE %s SET %s" \ % (self._tableName,sets ) if self._condition is not None: sql = "%s WHERE %s" % (sql, self._condition) if self._limit is not None: sql = "%s LIMIT %s" % (sql, self._limit) result = self.execute(sql) if result: # Execute Succeed # Insert ID affected_rows = self._conn.affected_rows() if affected_rows > 0: # Primary key is AUTO_INCREMENT (or will be 0) return affected_rows else: # Execute Failed pass return result
def getLocation(pirEle): address_l1 = _mysql.escape_string( urllib.unquote(Common.checkXMLValue("address_l1", pirEle))) city = Common.checkXMLValue("city", pirEle) state = Common.checkXMLValue("state", pirEle) country = Common.checkXMLValue("country", pirEle) zipcode = Common.checkXMLValue("zipcode", pirEle) return address_l1, city, state, country, zipcode
def mysql_escape(args): if args is None: return '' if isinstance(args, unicode): args = args.encode('utf8') if not isinstance(args, str): args = str(args) return _mysql.escape_string(args)
def ensure_db_exists(self, db_name, user, host, password): try: self.connection.query( _mysql.escape_string('CREATE DATABASE {};'.format(db_name))) except _mysql_exceptions.ProgrammingError: pass self.connection.query( "GRANT ALL PRIVILEGES ON {}.* TO '{}'@'{}' IDENTIFIED BY '{}';". format(db_name, user, host, password))
def escape(var): '''这里假定连接数据库都是使用utf8的。''' if var is None: return '' if isinstance(var, unicode): var = var.encode('utf8') if not isinstance(var, str): var = str(var) return _mysql.escape_string(var)
def insert(self): post_values = [ _mysql.escape_string(str(value)) for key, value in self.post.iteritems() ] return InsertDb( "INSERT INTO `posts` (`%s`) VALUES ('%s')" % ("`, `".join(self.post.keys()), "', '".join(post_values)))
def onCommand(self, source, args): from _mysql import escape_string arg = args.split() if len(arg) == 1: if arg[0].startswith("#"): if self.getflag(source, arg[0]) == "n" or self.getflag( source, arg[0]) == "q" or self.getflag(source, arg[0]) == "a": for channel in self.query( "select name,modes from channelinfo where name = '{0}'" .format(escape_string(arg[0]))): self.msg( source, "Current modes for {0}: {1}".format( channel["name"], channel["modes"])) else: self.msg(source, "Denied.") else: self.msg(source, "Invalid channel '{0}'".format(arg[0])) elif len(arg) == 2: modes = arg[1] if arg[0].startswith("#"): if self.getflag(source, arg[0]) == "n" or self.getflag( source, arg[0]) == "q" or self.getflag(source, arg[0]) == "a": for channel in self.query( "select name,modes from channelinfo where name = '{0}'" .format(escape_string(arg[0]))): modes = self.regexflag(channel["modes"], modes, True) self.query( "update channelinfo set modes = '{0}' where name = '{1}'" .format(escape_string(modes), channel["name"])) self.mode(channel["name"], modes) self.msg( source, "Done. New modes for {0}: {1}".format( channel["name"], modes)) else: self.msg(source, "Denied.") else: self.msg(source, "Invalid channel '{0}'".format(arg[0])) else: self.msg(source, "Syntax: CHANMODE <#channel> [<modes>]")
def saveToSql(self): ''' save the article object to mysql ''' self.content = _mysql.escape_string(self.content) stat = "insert into posts(title, author, publish, isdeleted, content, visitcount) values('%s', '%s', '%s', %d, '%s', %d);" % ( self.title, self.author, self.date, self.isDeleted, self.content, self.visitCount) self.db.query(stat) self.db.commit()
def escape_string(*encode_args): encodes = list() for encode_arg in encode_args[0]: # print type(encode_args), encode_args, '1'*30 # print type(encode_arg), encode_arg, '2'*30 if encode_arg: encode = _mysql.escape_string(encode_arg) else: encode = '-' encodes.append(encode) return encodes
def clean_json(record_json): for k, v in record_json.items(): if k == "body": record_json[k] = escape_string(v).decode('utf-8') if k == "author": author_id = md5_string(v.encode('utf-8')) record_json[k] = v if v is None: record_json[k] = "null" record_json['author_id'] = author_id return record_json
def update(cls, dic, where_col='id', where_col_str=False): """ 更新Something... :param dic: 字典 :return: """ key_value_lst = [] for key, value in dic.items(): logger.info("%s=%s" % (key, value)) if key == where_col: continue # 普通字符串 if type(value) == str or type(value) == unicode: value = _mysql.escape_string(value) item = "%s='%s'" % (key, value) # 需要追加的int,比如 like_num: (1, True),那么是like_num = like_num + 1 elif type(value) == tuple and len(value) == 2: if value[1]: item = "%s=%s+%s" % (key, key, value[0]) else: item = "%s=%s" % (key, value[0]) # 普通int, 比如 del_flag: 1, 直接 def_flag = 1 else: item = "%s=%s" % (key, value) key_value_lst.append(item) sql = "update {db}.{tbl} set ".format(db=cls.db_name, tbl=cls.table_name) sql += ",".join(key_value_lst) # where 列默认是id where_value = dic[where_col] if where_col_str: sql += " where %s = '%s'" % (where_col, where_value) else: sql += ' where %s = %s' % (where_col, where_value) logger.info("base_update: %s" % sql) if cls.db_name == db_name_config.DOCTOR_DB: ret = doctor_conn.execute_with_exception(sql) elif cls.db_name == db_name_config.DOCTOR_USER_DB: ret = doctor_user_conn.execute_with_exception(sql) elif cls.db_name == db_name_config.DOCTOR_QUESTION_DB: ret = doctor_question_conn.execute_with_exception(sql) else: logger.error("error db...") ret = None return ret
def cleanup(connection, tables: set): assert len(tables) > 0, 'Empty table list' cursor = connection.cursor() cursor.execute('SET FOREIGN_KEY_CHECKS=0') for table in tables: if cursor.execute('SHOW TABLES LIKE %s', (_mysql.escape_string(table), )): cursor.execute('TRUNCATE TABLE `%s`' % (table, )) cursor.close()