Exemple #1
0
def registration_page():
    """Page where new user accounts are made."""
    try:
        message = None

        if request.method == 'POST':
            username = request.form['username']

            password = sha256_crypt.encrypt(
                (str(request.form['password'])))   # taking password and hashing it for protection
            c, conn = connection()

            if is_username_exist(escape_string(username)) > 0:   # checking if user exists
                message = 'That username is already taken, please choose anot\
                her'
                return render_template('UserAccount/register.html',
                                       message=message)

            else:
                c.execute("INSERT INTO users (username, password) VALUES \
                (%s, %s)", (escape_string(username), escape_string
                            (password)))    # creating new user
                conn.commit()
                c.close()
                conn.close()
                session['logged_in'] = True
                session['username'] = username
                return redirect(url_for('logged_in'))
        return render_template('UserAccount/register.html')

    except Exception as e:
        return(str(e))
Exemple #2
0
def logged_in():
    """Logged in page that appears afte succesful authentication and shows bookmarks."""
    try:
        c, conn = connection()

        if request.method == 'POST':
            title = request.form['name']   # taking input for storing bookmark
            url = request.form['url']

            c.execute("SELECT uid FROM users WHERE username = (%s)",
                      session['username'])   # storing bookmark under the user who created it
            c.execute("INSERT INTO bookmarks (title, url, uid) VALUES \
                (%s, %s, %s)", (escape_string(title), escape_string
                                (url), c.fetchone()[0]))
            conn.commit()   # database commiting changes and closing of the connection
            c.close()
            conn.close()
            return redirect(url_for('logged_in', message="message"))

        else:
            c.execute("SELECT * FROM users WHERE username = (%s)",
                      session['username'])
            c.execute("SELECT * FROM bookmarks WHERE uid = (%s)",
                      c.fetchone()[0])
            a = list(c.fetchall())  # if get request then query the list of bookmarks and display them

            return render_template('UserAccount/logged_in.html',
                                   data=a)
    except Exception, e:
        return render_template('UserAccount/login.html', message=e)
    def title(self, id):
        ratingsform = widgets.RemoteForm(fields=RatingFields(),
                                         submit_text="Rate it!")
        #        Title.sqlmeta.addJoin(MultipleJoin('Rating',joinMethodName='ratings'))
        thetitle = Title.get(id)
        thetitle.ratings = []
        same_author = []
        for author in thetitle.author:
            titles = Title.select(
                """
            title.id!=%s AND
            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='%s'
            """ % (escape_string(
                    "%s" % thetitle.id), escape_string(author.author_name)),
                orderBy="booktitle",
                clauseTables=['book', 'author', 'author_title'],
                distinct=True)
            for title in titles:
                same_author.append(title)
        if thetitle.copies_in_status("STOCK") == 0:
            raise HTTPError(404)

        return dict(thetitle=thetitle,
                    authorswidget=AuthorsWidget(),
                    ratingsform=ratingsform,
                    titlelistwidget=TitleListWidget(),
                    same_author=same_author)
Exemple #4
0
    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 reinventory(self, isbn="", author="", title=""):
        searchform = widgets.TableForm(fields=PrivateSearchFields(),
                                       submit_text="Search!")
        if author == "" and title == "" and isbn == "":
            the_titles = False
        else:
            the_titles = Title.select("""
            title.isbn LIKE '%%%s%%' 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(isbn), escape_string(author),
                   escape_string(title)),
                                      orderBy="booktitle",
                                      clauseTables=['author', 'author_title'],
                                      distinct=True)

        title_count = 0
        try:
            title_count = the_titles.count()
        except:
            pass
        if title_count > 0:
            if the_titles.count() == 1:
                return self.title(the_titles[0].id,
                                  searchvalues=dict(author=author,
                                                    title=title,
                                                    isbn=isbn))

        return dict(the_titles=the_titles,
                    authorswidget=PrivateAuthorsWidget(),
                    titlelistwidget=PrivateTitleListWidget(),
                    searchform=searchform,
                    values=dict(author=author, title=title, isbn=isbn))
Exemple #6
0
def edit_profile():
    username = session['username']
    c, conn = connection()
    c.execute('SELECT * FROM users WHERE user_username = %s;',
              (escape_string(username), ))
    acct = c.fetchone()
    print(acct)

    if not acct:
        return redirect(url_for("profile"))

    id = acct[0]
    name = acct[1]
    abt = acct[5]

    if request.method == 'POST' and 'name' in request.form and 'about' in request.form:
        newName = request.form['name']
        newAbout = request.form['about']

        if not newName or not newAbout:
            return redirect(url_for("profile"))
        else:
            c.execute(
                'UPDATE users SET user_name = %s, user_about = %s WHERE user_id = %s AND user_username = %s;',
                (newName, newAbout, id, escape_string(username)))
            conn.commit()
            return redirect(url_for("profile"))

    return render_template('edit_profile.html', name=name, about=abt)
	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()
Exemple #8
0
def update_user_tracking():
    try:
        completed = str(request.args["completed"])
        if completed in str(DASHBOARD_CONTENTS.values()):
            client_name, settings, tracking, rank = user_info()

            if tracking == None:
                tracking = completed
            else:
                if completed not in tracking:
                    tracking = tracking + "," + completed

            c, conn = connection()
            c.execute("update users set tracking = %s where username = %s",
                      (escape_string(tracking), escape_string(client_name)))
            conn.commit()
            c.close()
            conn.close()

            #gc.collect()
            client_name, settings, tracking, ranking = user_info()

        else:
            pass

    except Exception as e:
        pass
Exemple #9
0
def insert_book(name, author, translator, publish, pub_date, instrution, price,
                pages, isbn, douban_id):
    """
    """

    time = _time_stamp()
    sql = """ SELECT id FROM books_book WHERE isbn='%s'""" % isbn
    name = escape_string(name)
    author = escape_string(author)
    translator = escape_string(translator)
    publish = escape_string(publish)
    instrution = escape_string(instrution)

    if db.query_one(sql):
        sql = """ UPDATE books_book SET name='%s',author='%s',translator='%s',publish='%s',pub_date='%s',instrution='%s',price='%s',pages=%d,douban_id=%d WHERE isbn='%s'""" % (
            name, author, translator, publish, pub_date, instrution, price,
            pages, douban_id, isbn)

    else:
        sql = """ INSERT INTO books_book (name,author,translator,publish,pub_date,instrution,price,pages,isbn,douban_id,category_id)
    VALUES ('%s','%s','%s','%s','%s','%s','%s',%d,%d,%d,%d)""" % (
            name, author, translator, publish, pub_date, instrution, price,
            int(pages), int(isbn), douban_id, 1)

    #print sql

    return db.execute(sql)
Exemple #10
0
    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
Exemple #12
0
def page(thread_id):
    message = ''
    thread_id = thread_id
    c, conn = connection()
    c.execute("SELECT * FROM posts WHERE post_id = %s", (thread_id, ))
    thread = c.fetchone()

    c.execute(
        "SELECT * FROM comments WHERE comment_thread_id = %s ORDER BY comment_posted DESC;",
        (thread_id, ))
    comments = c.fetchall()
    users = [c[2] for c in comments]
    dates = [c[4].strftime("%m/%d/%y %H:%M:%S") for c in comments]
    comments = [c[3] for c in comments]
    data = zip(users, comments, dates)

    c.execute("SELECT * FROM users WHERE user_username = %s;", (thread[1], ))
    user_id = c.fetchone()[0]

    if request.method == "GET":
        return render_template('thread_detail.html',
                               id=thread[0],
                               username=thread[1],
                               bodytext=thread[2],
                               date_posted=thread[3],
                               data=data,
                               message=message,
                               user_id=user_id)
    elif request.method == "POST":
        username = session['username']
        post = request.form['post']
        if not post:
            message = 'Please enter a comment before submit!'
            return render_template('thread_detail.html',
                                   id=thread[0],
                                   username=thread[1],
                                   bodytext=thread[2],
                                   date_posted=thread[3],
                                   data=data,
                                   message=message,
                                   user_id=user_id)
        c.execute(
            "INSERT INTO comments (comment_thread_id, comment_username, comment_bodytext) VALUES (%s, %s, %s)",
            (
                thread_id,
                escape_string(username),
                escape_string(post),
            ))
        conn.commit()
        return redirect(f"/threads/{thread_id}")
    elif request.method == 'POST':
        message = 'Missing information'
        return redirect(f"/threads/{thread_id}")
    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))
            )
Exemple #14
0
def profile():
    username = session['username']
    c, conn = connection()
    c.execute("SELECT * FROM users WHERE user_username = % s",
              (escape_string(username), ))
    acct = c.fetchone()
    name = acct[1]
    if name is None:
        name = username
        c.execute("UPDATE users SET user_name = %s WHERE user_username = %s",
                  (escape_string(name), escape_string(username)))
    return render_template('profile.html', name=name, username=username)
Exemple #15
0
 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
Exemple #16
0
    def search_cards(cls, own_open_id, name, company, resources_label,
                     resources_key, page, count_of_page):
        """
        搜索标签
        :param own_open_id: 自己的openid
        :param name: 搜索的名字
        :param company: 搜索的公司名
        :param resources_label: 搜索的资源标签
        :param resources_key: 搜索的自定义标签
        :param page: 页数
        :param count_of_page: 每页记录数
        :return: 数据列表(list),总记录数(int),页数(int)
        """

        if type(page) != int or type(count_of_page) != int:
            raise Exception('argv type error!')

        # 处理异常数据
        if page <= 0:
            page = 1
        # 计算偏移量
        index = (page - 1) * count_of_page

        sql = """
            select * from %s
            where status = 0 AND open_id != '%s' AND name like '%%%s%%'
            AND company like '%%%s%%' AND resources_label like '%%%s%%'
            AND resources_key like '%%%s%%'
            order by update_time desc
            limit %d, %d;
        """ % (cls.table_name, escape_string(own_open_id), escape_string(name),
               escape_string(company), escape_string(resources_label),
               escape_string(resources_key), index, count_of_page)
        objs = cls.get_db_lib().query_for_list(sql)
        objs = map(cls.handle_data_use_in_json, objs)

        sql = """
            select count(*) as nums from %s
            where status = 0 AND open_id != '%s' AND name like '%%%s%%'
            AND company like '%%%s%%' AND resources_label like '%%%s%%'
            AND resources_key like '%%%s%%'
            order by update_time desc
            limit 1;
        """ % (cls.table_name, escape_string(own_open_id), escape_string(name),
               escape_string(company), escape_string(resources_label),
               escape_string(resources_key))
        obj = cls.get_db_lib().query_for_dict(sql)

        max_page = obj['nums'] / count_of_page
        if obj['nums'] % count_of_page:
            max_page += 1

        return objs, obj['nums'], max_page
    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))
Exemple #18
0
    def get_all_card_in_a_group(cls, group_token, page, count_of_page,
                                search_key, industry, role):
        """
        获取一个群的所有名片
        :param group_token: 群凭证
        :return:
        """

        if type(page) != int or type(count_of_page) != int:
            raise Exception("args type error!")

        if page <= 0:
            page = 1
        index = (page - 1) * count_of_page
        limit_str = " limit %d, %d" % (index, count_of_page)

        search_str = """
            AND (card_business_card.name like '%%%s%%' OR card_business_card.company like '%%%s%%'
            OR card_business_card.redundancy_labels like '%%%s%%') AND card_business_card.industry like '%%%s%%'
            AND card_business_card.role like '%%%s%%'
        """ % (escape_string(search_key), escape_string(search_key),
               escape_string(search_key), escape_string(industry),
               escape_string(role))
        search_str = search_str.replace('%', '%%')
        sql = """
            select card_business_card.* from card_group_record
            left join card_business_card on card_group_record.open_id = card_business_card.open_id
            WHERE card_group_record.group_token = %s AND card_business_card.status = 0
        """ + search_str + """
            order by card_business_card.name_pinyin ASC
        """ + limit_str
        objs = cls.db.query_for_list(sql, [group_token])
        objs = map(cls.handle_data_use_in_json, objs)

        sql = """
            select count(*) as nums from card_group_record
            left join card_business_card on card_group_record.open_id = card_business_card.open_id
        """ + search_str + """
            WHERE card_group_record.group_token = %s AND card_business_card.status = 0
            limit 1
        """
        obj = cls.db.query_for_dict(sql, [group_token])

        max_page = obj['nums'] / count_of_page
        if obj['nums'] % count_of_page:
            max_page += 1

        return {
            "objs": objs,
            "total_nums": obj['nums'],
            "total_page": max_page
        }
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 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"
Exemple #21
0
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"
Exemple #22
0
def user_info():
    try:
        client_name = session["username"]
        guest = False
    except:
        guest = True
        client_name = "Guest"
    if not guest:
        try:
            #user = User()
            #user_one = user.query.filter_by(username=session["username"]).first()

            c, conn = connection()
            c.execute("select * from users where username = (%s)",
                      [(escape_string(client_name))])
            data = c.fetchone()
            print(data)  # for debugging purposes.
            settings = data[4]
            tracking = data[5]
            rank = data[6]
        except Exception as e:
            pass
    else:
        settings = [0, 0]
        tracking = [0, 0]
        rank = [0, 0]

    return client_name, settings, tracking, rank
Exemple #23
0
def login():

    try:
        c, conn = connection()
        error = None
        if request.method == "POST":
            data = c.execute("select * from users where username = (%s)",
                             [escape_string(request.form["username"])])
            data = c.fetchone()[2]

            if pbkdf2_sha256.verify(request.form["password"], data):
                flash("You are Logged in.", "success")
                session["logged_in"] = True
                session["username"] = request.form["username"]
                # for keeping track of the users and admins.
                # session["rank"] = 2
                return redirect(url_for("dashboard"))
            else:
                error = "Invalid credentials. Try again."
        gc.collect()
        return render_template("login.html", error=error)

    except Exception as e:
        error = "Invalid credentials. Try again."
        return render_template("login.html", error=error)

    return render_template("login.html", error=error)
Exemple #24
0
def login_page():
    """Home page of the application where the user can log in."""
    if 'logged_in' and 'username' in session:    # redirect user if already logged in
        return redirect(url_for('logged_in'))

    try:
        message = None
        c, conn = connection()
        if request.method == 'POST':
            data = c.execute("SELECT * FROM users WHERE username = (%s)",
                             escape_string(request.form['username']))  # extracting user
            data = c.fetchone()[2]

            if sha256_crypt.verify(request.form['password'], data):  # verifying hash password
                session['logged_in'] = True       # setting session values helps keep logged in
                session['username'] = request.form['username']
                return redirect(url_for('logged_in', message=message))

            else:
                message = 'Invalid credentials. Try again'

        return render_template('UserAccount/login.html', message=message)  # for get method

    except Exception:        # catching any errors that occured in above code
        message = 'Invalid credentials. Try again'
        return redirect(url_for('/', message=message))
Exemple #25
0
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 unconfirmed(self, letter=""):
        all_titles = []
        if letter == "":
            all_titles = Title.select("kind_id=1", orderBy='booktitle')
        else:
            all_titles = Title.select(
                """kind_id=1 and booktitle LIKE '%s%%'""" %
                (escape_string(letter)),
                orderBy='booktitle')
        unconfirmed_titles = []
        for t in all_titles:
            if t.copies_in_status(
                    'STOCK') > 0:  # or t.copies_in_status('UNKNOWN')>0:
                t.unconfirmed_books = []
                for b in [
                        b for b in t.books
                        if (b.status == 'STOCK' or b.status == 'UNKNOWN')
                ]:
                    tags = b.get_tags(
                        'inventory',
                        'confirmation11')  #needs a date eventually!
                    if len(tags) == 0:
                        t.unconfirmed_books.append(b)
                if len(t.unconfirmed_books) > 0:
                    unconfirmed_titles.append(t)

        return dict(titles=unconfirmed_titles)
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"
Exemple #28
0
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()
Exemple #29
0
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()
Exemple #30
0
def user_list(user_id):
    c, conn = connection()
    c.execute("SELECT * FROM users WHERE user_id = %s", (user_id, ))
    acct = c.fetchone()

    if not acct:
        return redirect(url_for("profile"))
    else:
        priv = acct[7]
        if priv == 1:
            username = acct[2]
            c, conn = connection()
            c.execute(
                "SELECT * FROM betlists WHERE username = %s ORDER BY bet_id DESC;",
                (escape_string(username), ))
            data = c.fetchall()
            bet_ids = [d[0] for d in data]
            bets = []
            for bet_id in bet_ids:
                c.execute(
                    "SELECT team1, team2, odd FROM allbets WHERE bet_id = %s;",
                    (bet_id, ))
                team1, team2, odd = c.fetchone()
                odd = odd.replace('[[', '').replace(']]', '')
                odd = odd.split('], [')
                bets.append([team1, team2, odd])
            return render_template('user_list.html',
                                   username=username,
                                   bets=bets)
        else:
            return render_template('private.html')
Exemple #31
0
    def term(self, field_name, operator, data):
        model, name = self.model.get_class_from_field_name(field_name)
        target_type, target_name = model.get_target_from_name(name)
        target_model, field = model.get_field_from_target_name(target_name)
        db_column = target_model._meta.db_table + '.' + field.column

        # Handle case-insensitive queries.
        if target_type == 'LCSTR':
            data = data.lower()
            name = 'LOWER(' + db_column + ')'

        # Generate the LIKE or "=" statement.
        if operator == 'equals':
            result = db_column + "='{}'"
        elif operator == 'startswith':
            result = db_column + " LIKE '{}%%'"
        elif operator == 'endswith':
            result = db_column + " LIKE '%%{}'"
        elif operator == 'contains':
            result = db_column + " LIKE '%%{}%%'"
        elif operator == 'regex':
            result = db_column + " REGEXP '%%{}%%'"
        elif operator == 'any':
            result = '1'
        else:
            raise Exception('unsupported operator:' + str(operator))

        # Yes, I would prefer to use a prepared statement, but collecting arguments
        # and passing them back along the string everywhere would be awful design,
        # and if you prefer that, you are a bad software developer.
        # (Also, I didn't find any API from Django to generate a prepared statement
        # without already executing it, e.g. django.db.connection.execute())
        return result.format(escape_string(data))
Exemple #32
0
def register_page():
    form = RegistrationForm(request.form)
    try:
        if request.method == 'POST' and form.validate():
            username = form.username.data
            email = form.email.data
            password = form.password.data

            c = mysql.connection.cursor()
            x = c.execute('SELECT * FROM users WHERE username = ("%s");' %
                          escape_string(username))

            if int(x) > 0:
                flash('That username is already taken, please choose another')
                return render_template('register.html', form=form)
            else:
                cur = mysql.connection.cursor()
                print(username)
                cur.execute(
                    "INSERT INTO users VALUES (username, email, password)")
                mysql.connection.commit()
                cur.close()
                flash('Thanks for registering!')
                session['logged_in'] = True
                session['username'] = username
                return redirect(url_for('favourites_page'))
        return render_template('register.html', form=form)
    except Exception as e:
        return render_template('register.html', form=form)
Exemple #33
0
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 titles(self, letter=None):
     alphabet = [
         "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m",
         "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"
     ]
     the_titles = False
     if letter != None:
         if letter in alphabet:
             the_titles = Title.select("""
                title.booktitle LIKE "%s%%" AND
                book.title_id=title.id AND
                book.status ='STOCK' """ % (escape_string(letter)),
                                       orderBy="booktitle",
                                       clauseTables=['book'],
                                       distinct=True)
         else:
             the_titles = Title.select("""
                title.booktitle RLIKE "^[0-9]" AND
                book.title_id=title.id AND
                book.status ='STOCK' """,
                                       orderBy="booktitle",
                                       clauseTables=['book'],
                                       distinct=True)
         return dict(authorswidget=AuthorsWidget(),
                     titlelistwidget=TitleListWidget(),
                     the_titles=the_titles,
                     the_letter=letter,
                     alphabet=alphabet)
     else:
         return dict(authorswidget=AuthorsWidget(),
                     titlelistwidget=TitleListWidget(),
                     the_titles=False,
                     the_letter=letter,
                     alphabet=alphabet)
Exemple #35
0
 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
Exemple #36
0
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 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 updateMasterVariableTable(self):
        """
        All the categorical variables get a lookup table;
        we store the create code in the databse;
        """
        for variable in self.variables:
            variable.updateVariableDescriptionTable()

        inCatalog = self.uniques()
        if len(inCatalog) > 0 and self.tableName != "catalog":
            #catalog has separate rules handled in CreateDatabase.py; so this builds
            #the big rectangular table otherwise.
            #It will fail if masterTableTable doesn't exister.
            fileCommand = self.uniqueVariableFastSetup()
            try:
                parentTab = self.db.query("""
                SELECT tablename FROM masterVariableTable
                WHERE dbname='%s'""" % self.fastAnchor).fetchall()[0][0]
            except:
                if self.fastAnchor == "bookid":
                    parentTab = "fastcat"
                else:
                    print(
                        "Unable to find a table to join the anchor (%s) against"
                        % self.fastAnchor)
                    raise
            self.db.query(
                'DELETE FROM masterTableTable WHERE masterTableTable.tablename="%s";'
                % self.fastName)
            self.db.query(
                "INSERT IGNORE INTO masterTableTable VALUES ('%s','%s','%s')" %
                (self.fastName, parentTab, escape_string(fileCommand)))
    def updateMasterVariableTable(self):
        """
        All the categorical variables get a lookup table;
        we store the create code in the databse;
        """
        for variable in self.variables:
            # Make sure the variables know who their parent is
            variable.fastAnchor = self.fastAnchor
            # Update the referents for everything
            variable.updateVariableDescriptionTable()

        inCatalog = self.uniques()
        if len(inCatalog) > 0 and self.tableName != "catalog":
            # catalog has separate rules handled in CreateDatabase.py; so this builds
            # the big rectangular table otherwise.
            # It will fail if masterTableTable doesn't exister.
            fileCommand = self.uniqueVariableFastSetup()
            try:
                parentTab = self.db.query(
                    """
                SELECT tablename FROM masterVariableTable
                WHERE dbname='%s'"""
                    % self.fastAnchor
                ).fetchall()[0][0]
            except:
                if self.fastAnchor == "bookid":
                    parentTab = "fastcat"
                else:
                    logging.error("Unable to find a table to join the anchor (%s) against" % self.fastAnchor)
                    raise
            self.db.query('DELETE FROM masterTableTable WHERE masterTableTable.tablename="%s";' % self.fastName)
            self.db.query(
                "INSERT INTO masterTableTable VALUES ('%s','%s','%s')"
                % (self.fastName, parentTab, escape_string(fileCommand))
            )
Exemple #40
0
    def _attrs_and_values(self, entity):
        values = []
        attrs = []

        # map/reduce bool changes first, then if there are changes,
        # continue stringifying values.
        for attr in self.properties:
            value = getattr(entity, attr)

            # generally if a value is None it isn't required
            # by the index, but this is a bad assumption...
            if value is None:
                continue

            if isinstance(value, bool):
                value = str(int(value))

            if isinstance(value, datetime.datetime) or \
               isinstance(value, datetime.date):
                value = str(value)

            if isinstance(value, basestring):
                value = "'%s'" % escape_string(value)

            values.append(value)
            attrs.append(attr)

        return (attrs, values)
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
Exemple #42
0
    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
Exemple #43
0
    def index_sql(self, entity):
        """
        Indexes are strict about only indexing entities that have
        all the properties required for indexing.

        Example:
            (Just an example, the actual indexer doesn't index dict objs)

            user_id_index = Index(table="index_user_id",
                                  properties=["user_id")
            ent = Entity()
            ent['id'] = 'foo'
            ent['user_id'] = 'bar'

            sql = user_id_index.index_sql(ent)
            sql:
                INSERT INTO index_user_id (entity_id, user_id) VALUES (
                    'foo', 'bar'
                )
        """
        attrs, values = self._attrs_and_values(entity)
        if not attrs:
            return None

        values = ["'%s'" % escape_string(entity.id)] + values

        sql = "INSERT INTO %s (entity_id, %s) VALUES (%s)" % (
            self.table,
            ', '.join(attrs),
            ', '.join(['%s' % str(v) for v in values])
        )

        return sql
Exemple #44
0
def test(url):
    import pdb
    pdb.set_trace()
    page = topcoder.get_page(url)
    p = compile('<a name="problem_stats"></a>[\d\D]*?</table>')
    divs = p.findall(page)
    for i in xrange(len(divs)):
        div = divs[i]
        p = compile('<tr>[\d\D]*?</tr>')
        trs = p.findall(div)
        for tr in trs:
            if tr.find('/stat?c=problem_statement&pm=') == -1:
                continue
            p = compile('/stat\?c=problem_statement&pm=[0-9]*&rd=[0-9]*')
            url = topcoder_site_url + p.findall(tr)[0]
            p = compile('pm=[0-9]*')
            p_id = p.findall(url)[0][3:]

            p = compile('>.*?<')
            name = p.findall(tr)[2][1:-1]
            print p_id, url, name
            page = topcoder.get_page(url)
            p = compile('<\!-- BEGIN BODY -->[\d\D]*?<\!-- END BODY -->')
            description = p.findall(page)[0]

            try:
                cur.execute(insert_template %
                            (p_id, name, escape_string(description), url))
            except IntegrityError:
                print p_id, 'Duplicate!'
    print
Exemple #45
0
def index():
    error = ''
    c, conn = connection()
    try:
        if request.method == "POST":
            input_username = request.form['username']
            input_password = str(request.form['pswrd'])
            c.execute("SELECT * FROM users WHERE user_username = % s",
                      (escape_string(input_username), ))
            data = c.fetchone()

            if not data:
                error = 'Invalid credential! Please try again.'
                return render_template("login.html", error=error)

            if input_password == data[3]:
                session['loggedin'] = True
                session['username'] = input_username
                return redirect(url_for("threads"))
            else:
                error = "Invalid credential! Please try again."

        gc.collect()
        return render_template("login.html", error=error)
    except Exception as e:
        error = e
        return render_template("login.html")
Exemple #46
0
def _save_hashtbl(tbl_name, data, dbi=None):
    """
  將 data 存入列表,接受 body, [body...] 或 [{'body':'body'}...] 的格式
  數量可能很大,因此硬寫不快取
  """
    from MySQLdb import escape_string
    from lib.util.text import md5

    if (type(data) is str):
        _data = {'md5': md5(data), 'body': data}
    elif (type(data) is dict):
        if 'md5' not in data:
            data['md5'] = md5(data['body'])
        _data = data
    elif (type(data[0]) is str):
        _data = [{'md5': md5(x), 'body': x} for x in data]
    else:
        _data = [{
            'md5': x['md5'] if 'md5' in x else md5(x['body']),
            'body': x['body']
        } for x in data]

    sql = "INSERT IGNORE INTO `%s` (`body`, `hash`) VALUES " % escape_string(
        tbl_name)
    sql += "(%(body)s, UNHEX(%(md5)s))"

    DB.execute(sql, _data, dbi=dbi)
Exemple #47
0
    def search_cards(cls, own_open_id, industry, role, search_key, page, count_of_page):
        """
        搜索标签
        :param own_open_id: 自己的openid
        :param industry: 搜索的行业
        :param role: 搜索的职能
        :param page: 页数
        :param count_of_page: 每页记录数
        :return: 数据列表(list),总记录数(int),页数(int)
        """

        if type(page) != int or type(count_of_page) != int:
            raise Exception('argv type error!')

        # 处理异常数据
        if page <= 0:
            page = 1
        # 计算偏移量
        index = (page - 1) * count_of_page

        sql = """
            select * from %s
            where status = 0 AND open_id != '%s' AND (redundancy_labels like '%%%s%%'
            OR name like '%%%s%%' OR company like '%%%s%%')
            AND industry like '%%%s%%' AND role like '%%%s%%'
            order by update_time desc
            limit %d, %d;
        """ % (cls.table_name, escape_string(own_open_id), escape_string(search_key), escape_string(search_key), escape_string(search_key), escape_string(industry), escape_string(role), index, count_of_page)

        objs = cls.get_db_lib().query_for_list(sql)
        objs = map(cls.handle_data_use_in_json, objs)

        sql = """
            select count(*) as nums from %s
            where status = 0 AND open_id != '%s' AND (redundancy_labels like '%%%s%%'
            OR name like '%%%s%%' OR company like '%%%s%%')
            AND industry like '%%%s%%' AND role like '%%%s%%'
            order by update_time desc
            limit 1;
        """ % (cls.table_name, escape_string(own_open_id), escape_string(search_key), escape_string(search_key), escape_string(search_key), escape_string(industry), escape_string(role),)
        obj = cls.get_db_lib().query_for_dict(sql)

        max_page = obj['nums'] / count_of_page
        if obj['nums'] % count_of_page:
            max_page += 1

        return objs, obj['nums'], max_page
Exemple #48
0
    def __build_wheres(self, conditions):

        if len(conditions) == 0:
            return ''
        else:
            i = 0
            string = ' WHERE'
            for field, condition in conditions.items():
                if i > 0:
                    field, pre_op = self.__get_precondition_tuple(field)
                else:
                    pre_op = ''

                field, operator = self.__get_condition_tuple(field)
                made_pre = False
                made_pst = False

                for letter_map, tbl_name in self.table_map.items():

                    tbl_name_len = len(tbl_name)

                    if not made_pre:

                        if field[:tbl_name_len] == tbl_name:
                            field = self.__protect_input(letter_map)+'.'+self.__protect_input(field[tbl_name_len:])
                            made_pre = True
                        elif field[:2] == letter_map+'.':
                            field = self.__protect_input(letter_map)+'.'+self.__protect_input(field[2:])
                            made_pre = True

                    if not made_pst:

                        if condition[:tbl_name_len] == tbl_name:
                            condition = self.__protect_input(letter_map)+'.'+self.__protect_input(condition[tbl_name_len:])
                            made_pst = True
                        elif condition[:2] == letter_map+'.':
                            condition = self.__protect_input(letter_map)+'.'+self.__protect_input(condition[2:])
                            made_pst = True

                    if made_pre and made_pst:
                        break

                else:
                    if not made_pre:

                        if self.append_letter:
                            field = '`a`.'+self.__protect_input(field)
                        else:
                            field = self.__protect_input(field)

                    if not made_pst:
                        condition = '\''+escape_string(condition)+'\''

                string = string+' '+pre_op+field
                string = string+operator+condition

                i = i + 1

        return string
 def extract_log(self, raw):
   log = {}
   try:
     fields = raw.split('\t')
     # convert to time and back to make time valid
     logtime = time.strptime(fields[0] + ' ' + fields[1].split(',')[0], TIME_FORMAT)
     log['logtime'] = time.strftime( TIME_FORMAT, logtime)
     if fields[2] in LOG_LEVELS:
       log['level'] = escape_string(fields[2])
     else:
       raise ValueError()
     log['class'] = escape_string(fields[3])
     log['msg'] = escape_string(' '.join([m for m in fields[4:] if m != "NULL"]))
   except Exception as e:
     # print(e.message)
     return None
   return log
Exemple #50
0
 def get_uid_by_username_or_email(username_email):
     result = yield model.MatrixDB.query(
         "select user_id from user where username='******' or email = '{0}'".
         format(escape_string(username_email)))
     if len(result) == 0:
         raise tornado.gen.Return(None)
     else:
         raise tornado.gen.Return(result[0])
Exemple #51
0
 def escape(self, value):
     """ 转义MySQL """
     if isinstance(value, (tuple, list)):
         return [self.escape(v) for v in value]
     elif isinstance(value, (str, unicode)):
         return escape_string(value)
     else:
         return value
 def uncategorized(self,letter=""):
     all_titles=[]
     if letter=="":
         all_titles=Title.select("kind_id=1",orderBy='booktitle')
     else:
         all_titles=Title.select("""kind_id=1 and booktitle LIKE '%s%%'""" % (escape_string(letter)),orderBy='booktitle')
                 
     return dict(titles=[t for t in all_titles if len(list(t.sections))==0])
 def outofstock(self,letter=""):
     all_titles=[]
     if letter=="":
         all_titles=Title.select("kind_id=1",orderBy='booktitle')
     else:
         all_titles=Title.select("""kind_id=1 and booktitle LIKE '%s%%'""" % (escape_string(letter)),orderBy='booktitle')
                 
     return dict(titles=[t for t in all_titles if t.copies_in_status("STOCK")==0])
Exemple #54
0
    def get_all_card_in_a_group(cls, group_token, page, count_of_page, search_key, industry, role):
        """
        获取一个群的所有名片
        :param group_token: 群凭证
        :return:
        """

        if type(page) != int or type(count_of_page) != int:
            raise Exception("args type error!")

        if page <= 0:
            page = 1
        index = (page - 1) * count_of_page
        limit_str = " limit %d, %d" % (index, count_of_page)

        search_str = """
            AND (card_business_card.name like '%%%s%%' OR card_business_card.company like '%%%s%%'
            OR card_business_card.redundancy_labels like '%%%s%%') AND card_business_card.industry like '%%%s%%'
            AND card_business_card.role like '%%%s%%'
        """ % (escape_string(search_key), escape_string(search_key), escape_string(search_key), escape_string(industry),
               escape_string(role))
        search_str = search_str.replace('%', '%%')
        sql = """
            select card_business_card.* from card_group_record
            left join card_business_card on card_group_record.open_id = card_business_card.open_id
            WHERE card_group_record.group_token = %s AND card_business_card.status = 0
        """ + search_str + """
            order by card_business_card.name_pinyin ASC
        """ + limit_str
        objs = cls.db.query_for_list(sql, [group_token])
        objs = map(cls.handle_data_use_in_json, objs)

        sql = """
            select count(*) as nums from card_group_record
            left join card_business_card on card_group_record.open_id = card_business_card.open_id
        """ + search_str + """
            WHERE card_group_record.group_token = %s AND card_business_card.status = 0
            limit 1
        """
        obj = cls.db.query_for_dict(sql, [group_token])

        max_page = obj['nums'] / count_of_page
        if obj['nums'] % count_of_page:
            max_page += 1

        return {"objs": objs, "total_nums": obj['nums'], "total_page": max_page}
Exemple #55
0
    def get_all(self, datastore, **kwargs):
        offset = kwargs.pop('offset', None)
        limit = kwargs.pop('limit', None)
        order_by = kwargs.pop('order', None)

        # unquote_plus because inserting url-encoded strings is a bad
        # idea... python treats them as format strings. the reason
        # to do it here instead of all escape_string calls is because
        # get_all is generally used with arguments passed from the url
        # or forms. general rule: if you're inserting user input in your
        # sql, unquote and escape.

        values = []

        shard_on_value = kwargs.pop(self.shard_on, None)
        if not shard_on_value:
            raise Exception("Index doesn't have a value to shard on")

        shard_on_value = escape_string(urllib.unquote_plus(shard_on_value))
        values.append(shard_on_value)

        where_clause = []
        s = "%s=" % self.shard_on
        s+= "%s"
        where_clause.append(s)

        for k, v in kwargs.iteritems():
            if k in self.properties:
                if isinstance(v, basestring):
                    s = "%s=" % k
                    s+= "%s"
                    where_clause.append(s)
                    values.append(v)

        sql = "SELECT entity_id FROM %s" % self.table

        if values:
            sql += " WHERE "
            sql += " AND ".join(where_clause)

        if order_by:
            sql += " ORDER BY %s " % order_by

        if limit:
            sql += " LIMIT %d " % limit

        if offset:
            sql += " OFFSET %d " % offset

        shard = datastore.choose_shard(shard_on_value)
        conn = datastore.get_connection(shard)

        index_objs = conn.query(sql, *values)
        if not index_objs:
            return []

        ids = [obj['entity_id'] for obj in index_objs]
        return datastore.get_list(ids)
def do_str(_in):
	'''deals with stringlike things'''
	if type(_in) is type(None):
		_in = 'NULL'
		return _in
	if type(_in) is not basestring:
		_in = '%s' % _in
	gg = escape_string(_in.decode('ascii',errors='replace'))
	return ' \' %s \' ' % gg
Exemple #57
0
def safe_string(value):
    """
    Alias to :py:data:`MySQLdb.escape_string` to match with `safe_int`: Escapes the given string to be safe
    to replace in SQL query

    :param str value: SQL string to escape
    :returns: Escaped string
    """
    return escape_string(value)
    def author(self,id):
    	the_author=Author.get(id)
	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='%s'
            """ % (escape_string(the_author.author_name)),orderBy="booktitle",clauseTables=['book','author','author_title'],distinct=True)
	return dict(the_titles=the_titles,the_author=the_author,authorswidget=AuthorsWidget(),titlelistwidget=TitleListWidget())
Exemple #59
0
    def __extract_insert_data(self, insert_dict):

        fields = []
        values = []

        for field, value in insert_dict.items():
            fields.append(self.__protect_input(field))
            values.append(escape_string(value))

        return (fields, values)