Python MySQLdb.escape_string函数代码示例

	def train(self, clas, docs):
		"""
		Trains the Bayesian Classifier with the given input data.

		:param clas: string representing the class
		:param docs: list of docs, each of which is a list of words (w/ repeats)
		"""
		cur = self.conn.cursor()
		clas = escape_string(clas)

		self._setup_db(clas)

		#Adds documents to database.
		for doc in docs:
			counts = Counter(doc)
			for term, count in counts.iteritems():
				cur.execute("SELECT count from {} WHERE term = ?;".format(clas), (escape_string(term),))
				currCount = cur.fetchone()
				if currCount != None:
					count = currCount[0] + count
					self.conn.execute("UPDATE {} SET count = ? WHERE term = ?;".format(clas), (count, escape_string(term)))
				else:
					self.conn.execute("INSERT INTO {} VALUES(?, ?);".format(clas), (escape_string(term), count))

			#Update doc nums.
			cur.execute("SELECT count FROM doc_nums WHERE class = ?", (clas,))
			num = cur.fetchone()[0]
			self.conn.execute("UPDATE doc_nums SET count = ? WHERE class = ?;", (num+1, clas))
			self.conn.commit()

    def transactions(self,**args):
        self.common()
        begin_date=args.get('begin_date','')
        end_date=args.get('end_date','')
        what=args.get('what','')
        action=args.get('action','SALE')
        deleteid=args.get('deleteid','0')
        if int(deleteid) >0:
            Transaction.delete(deleteid)
        
        
        self._transactionstemplate.begin_date=begin_date
        self._transactionstemplate.end_date=end_date
        self._transactionstemplate.what=what
        self._transactionstemplate.action=action

        self._transactionstemplate.transactions=[]
        if begin_date and end_date:
            self._transactionstemplate.transactions=list(Transaction.select("""
        transactionLog.date >= '%s' AND
        transactionLog.date <= ADDDATE('%s',INTERVAL 1 DAY) AND
        transactionLog.info LIKE '%%%s%%' AND
        transactionLog.action LIKE '%%%s%%'
                """ % (escape_string(begin_date),escape_string(end_date),escape_string(what),escape_string(action))))
            
        
                                                                        
        return  self._transactionstemplate.respond()

def sanitize_json(json):
    if isinstance(json, basestring):
        # Escape all strings
        return escape_string(json)
    elif isinstance(json, list):
        return [sanitize_json(item) for item in json]
    elif isinstance(json, dict):
        return {escape_string(key):sanitize_json(value) for key,value in json.items()}
    else: # Int, float, True, False, None don't need to be sanitized
        return json

    def updateVariableDescriptionTable(self):
        self.memoryCode = self.fastLookupTableIfNecessary()
        code = (
            """DELETE FROM masterVariableTable WHERE dbname="%(field)s";
        INSERT INTO masterVariableTable
        (dbname,     name,      type,       tablename,     anchor,      alias,     status,description)
           VALUES
        ('%(field)s','%(field)s','%(type)s','%(finalTable)s','%(anchor)s','%(alias)s','%(status)s','') """
            % self.__dict__
        )
        self.dbToPutIn.query(code)
        if not self.unique:
            code = self.fastSQLTable()
            try:
                parentTab = self.dbToPutIn.query(
                    """
                SELECT tablename FROM masterVariableTable
                WHERE dbname='%s'"""
                    % self.fastAnchor
                ).fetchall()[0][0]
            except:
                parentTab = "fastcat"
            self.dbToPutIn.query(
                'DELETE FROM masterTableTable WHERE masterTableTable.tablename="%s";' % (self.field + "heap")
            )
            self.dbToPutIn.query(
                "INSERT INTO masterTableTable VALUES ('%s','%s','%s')"
                % (self.field + "heap", parentTab, escape_string(code))
            )
        if self.datatype == "categorical":
            # Variable Info

            code = (
                """
            DELETE FROM masterVariableTable WHERE dbname='%(field)s__id';
            INSERT IGNORE INTO masterVariableTable
                    (dbname,     name,      type,       tablename,
                      anchor,      alias,     status,description)
           VALUES
                    ('%(field)s__id','%(field)s','lookup','%(fasttab)s',
                    '%(anchor)s','%(alias)s','hidden','') """
                % self.__dict__
            )
            self.dbToPutIn.query(code)
            # Separate Table Info
            code = self.fastLookupTableIfNecessary()
            self.dbToPutIn.query(
                'DELETE FROM masterTableTable WHERE masterTableTable.tablename="%s";' % (self.field + "Lookup")
            )
            self.dbToPutIn.query(
                "INSERT INTO masterTableTable VALUES ('%s','%s','%s')"
                % (self.field + "Lookup", self.fasttab, escape_string(code))
            )

 def escape(self, value):
     """ 转义MySQL """
     if isinstance(value, (tuple, list)):
         return [self.escape(v) for v in value]
     elif isinstance(value, str):
         return escape_string(value)
     elif isinstance(value, unicode):
         return escape_string(value.encode('utf-8'))
     elif isinstance(value, (int, long, float)):
         return str(value)
     else:
         return value

    def search(self,author="",title=""):
        searchform = widgets.TableForm(fields=SearchFields(), submit_text="Search!")
        if author == "" and title=="":
            the_titles=False
        else:
            the_titles=Title.select("""
	    book.title_id=title.id AND
            book.status ='STOCK' AND
            author.id=author_title.author_id AND
            author_title.title_id=title.id AND author.author_name LIKE '%%%s%%' AND title.booktitle LIKE '%%%s%%'
            """ % (escape_string(author),escape_string(title)),orderBy="booktitle",clauseTables=['book','author','author_title'],distinct=True)
	return dict(the_titles=the_titles,authorswidget=AuthorsWidget(),titlelistwidget=TitleListWidget(),searchform=searchform,values=dict(author=author,title=title))

def insert_table(datas):
    sql = "INSERT INTO %s (Hash, item_name, item_price, item_link, item_category) \
values('%s', '%s', '%s', '%s', '%s')" % (SQL_TABLE,
    hashlib.sha224(datas['item_name']).hexdigest(),
    escape_string(datas['item_name']),
    escape_string(datas['item_price']),
    escape_string(datas['item_link']),
    escape_string(datas['item_category'])
    )
#    print sql
    if cursor.execute(sql):
        print "Inserted"
    else:
        print "Something wrong"

 def query(self, sql, *params):
     cur = self.db.cursor()
     sql= escape_string(sql)
     cur.execute(sql, *params)
     r = ResultSet(cur.fetchall())
     cur.close()
     return r

def insertInDatabase(table, **args):
    """
    returns valid SQL Code for insertion 
    """
    fields = ','.join(args.keys())
    values = ','.join(['"%s"' % escape_string(i) for i in args.values()])
    return "INSERT INTO %s (%s) VALUES (%s)" % (table, fields, values)

    def get_old_message_from_user(cls, open_id, page, count_of_page, last_message_id=None):
        """
        获取一页资源小助手信息
        :param open_id: 用户openid
        :param page: 页数
        :param count_of_page: 每页记录数
        :param last_message_id:  在那条记录之前
        :return:
        """

        cache_key = 'sign_resource_helper_' + open_id
        # 记录下当前阅读时间
        redis_client.set(cache_key, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))

        able_look_time = model_manager['BusinessCardModel'].get_user_create_time(open_id)
        if not able_look_time:
            able_look_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        open_id = escape_string(open_id)
        where_str = "(((target_open_id = '%s' OR target_open_id = '') AND send_or_receive = 0) OR (source_open_id = '%s') ) AND create_time >= '%s' AND status = 0" % (open_id, open_id, able_look_time)

        if last_message_id is not None:
            where_str += (" AND id < %d" % int(last_message_id))

        return cls.get_one_page_of_table(cls.table_name, where_str, page, count_of_page, order_by="create_time desc")

def insert(db,table, v, step=1000, update=False,debug=False):
    if not v:   return
    v = list(v)
    keys = v[0].keys()
    update_keys = ','.join(( '`%s`=values(`%s`)'%(e,e) for e in keys ))
    
    sql_base ="insert into `%s`(`%s`) values ('%%s')"%(table,'`,`'.join(keys))
    for i in xrange(step):
        rs = v[i::step]
        if not rs:  continue
        
        vv =([escape_string('%s'%e.get(k, 'None')) for k in keys]  for e in rs)
        vv =( "','".join(e) for e in vv)
        vv = "'),('".join(vv)
        vv = vv.replace("'None'", "NULL")
        
        vv = sql_base%vv
        
        if bool(update):
            vv = '%s ON DUPLICATE KEY UPDATE %s' %(vv,update_keys)
        
        try:
            if  debug:
                log.msg( '>>>sql_insert:%s'%(vv,), log.DEBUG)
            db.execute( vv )
        except:
            db.reset()

def insert_table(datas):
    """
    Just MySQL Insert function
    """
    sql = "INSERT INTO %s (name, link, categories, price, time_capt) \
values('%s', '%s', '%s', '%s', NOW())" % (SQL_TABLE,
    escape_string(datas['item_name']),
    escape_string(datas['item_link']),
    escape_string(datas['item_category']),
    escape_string(datas['item_price'])
    )
    # print sql
    if cursor.execute(sql):
        return True
    else:
        print "Something wrong"

def gen_solution(cur, td, num, p_id):
#	import pdb
#	pdb.set_trace()
	global testcase_id
	global testcase_crawled

	if num == 0:
		column_name = 'java'
	elif num == 1:
		column_name = 'cpp'
	elif num == 2:
		column_name = 'csharp'
	else:
		column_name = 'VB'
	cur.execute('select %s from problem where id = %d' % (column_name, p_id))
	if cur.fetchall()[0][0] != None:
		return
	p = compile('"/stat\?c=problem_solution.*?"')
	l = p.findall(td)
	if len(l) == 1:
		url = topcoder_site_url + unescape(l[0][1:-1])
		try:
			page = topcoder.get_page(url)
		except Exception, e:
			print url, e
			return
		p = compile('<TD CLASS="problemText" COLSPAN="8" VALIGN="middle" ALIGN="left">[\d\D]*?</TD>')
		try:
			code = escape_string(p.findall(page)[0])
		except Exception, e:
			print 'No code found:',url,e
			return

def setBucket(bucket, userid):
	'''creates a new empty bucket'''
	MAX_BUCKETS_PER_USER = 100

	conn = Connection()
	#Validate the bucket
	try:
		_verifyBucket(conn, bucket, False, userid)

		#Check if user has too many buckets
		query = "SELECT bucket FROM bucket WHERE userid = %s"
		result = conn.executeStatement(query, (int(userid)))
		if len(result) >= MAX_BUCKETS_PER_USER:
				raise BadRequestException.TooManyBucketsException()

		#Write bucket to database and filesystem
		query = "INSERT INTO bucket (bucket, userid, bucket_creation_time) VALUES (%s, %s, NOW())"
		conn.executeStatement(query, (escape_string(str(bucket)), int(userid)))
		path = Config.get('common','filesystem_path')
		path += str(bucket)
		os.mkdir(path)
	except:
		conn.cancelAndClose()
		raise
	else:
		conn.close()

    def update_index_sql(self, entity):
        """Only generates SQL for changed index values"""

        if not entity._has_changed:
            # immediately return if this entity hasn't even changed.
            return None

        attrs, values = self._attrs_and_values(entity)

        if not attrs:
            return None

        def values_have_changed(entity):
            def _mapper(attr):
                ent_value = getattr(entity, attr)
                orig_value = entity._original_attrs.get(attr)
                # True if changed, False if equal
                return ent_value != orig_value
            return _mapper

        bools = map(values_have_changed(entity), attrs)
        res = reduce(lambda x, y: x or y, bools)
        if not res: return

        updates = ['%s=%s' % (attr, value)
                   for attr, value in zip(attrs, values)]

        sql = "UPDATE %s" % self.table
        sql+= " SET %s" % ', '.join(updates)
        sql+= " WHERE entity_id = '%s'" % escape_string(entity.id)

        return sql