Python pymysql.escape_string() Examples

def backtracking_id(site):
    cookies = cookies_raw2jar(site['cookies'])
    for _tid in range(site['start_torrent'], site['end_torrent'] + 2):
        t0 = time.time()

        _link = site['torrent_url'].format(_tid)
        torrent_page = requests.get(_link, cookies=cookies, headers=headers)
        title_search = re.search(site['search_ptn'], torrent_page.text)

        if title_search:
            _title = pymysql.escape_string(unescape(title_search.group("title")))
            pubDate = re.search("发布于(.+?)<", torrent_page.text).group(1)
            _timestamp = time.mktime(time.strptime(pubDate, "%Y-%m-%d %H:%M:%S"))

            wrap_insert(site=site['name'], sid=_tid, title=_title, link=_link, pubdate=_timestamp, t=t0)
        else:
            print("ID: {}, Cost: {:.5f} s, No torrent.".format(_tid, time.time() - t0))

        time.sleep(2) 

def select_sql_params(self, pk=0, fields=[], search_fields=[]):
        where, limit, order = '', '', ''
        if pk:
            where = 'WHERE id="%d"' % pk
        elif self.request.arguments:
            if not self.get_argument('search', None):
                where_fields = [field for field in fields if self.get_argument(field, None) != None]
                if where_fields:
                    where = ' WHERE %s' % ' and '.join(
                        ['%s in (%s)' % (field, ','.join(
                            ['"%s"' % pymysql.escape_string(v) for v in self.get_arguments(field)]))
                         for field in where_fields])
            else:
                where = 'WHERE concat(%s) like "%%%s%%"' % (','.join(search_fields),
                                                            pymysql.escape_string(self.get_argument('search')))

            if self.get_argument('offset', None) and self.get_argument('limit', None):
                limit = 'LIMIT %s, %s' % (pymysql.escape_string(self.get_argument('offset')),
                                          pymysql.escape_string(self.get_argument('limit')))

            if self.get_argument('order', None) and self.get_argument('sort', None):
                order = 'ORDER BY %s %s' % (pymysql.escape_string(self.get_argument('sort')),
                                            pymysql.escape_string(self.get_argument('order')))
        return where, order, limit 

def auditlog(self):
        if self.reqdata.get('password'):
            self.reqdata['password'] = '*' * 6

        insert_sql = '''
            INSERT INTO 
              auditlog (
                user_id, 
                uri,
                method, 
                reqdata,
                record_time) 
            VALUES ("%s", "%s", "%s", "%s", "%s")
        ''' % (self.requser['id'],
               self.request.uri,
               self.request.method,
               pymysql.escape_string(json.dumps(self.reqdata)),
               datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        try:
            with self.transaction():
                self.cursor.execute(insert_sql)
        except Exception as e:
            logger.error('Add auditlog failed: %s' % str(e)) 

def select_sql_params(self, pk=0, fields=[], search_fields=[]):
    where, limit, order = '', '', ''
    if pk:
        where = 'WHERE id="%d"' % pk
    elif self.request.arguments:
        if not self.get_argument('search', None):
            where_fields = [field for field in fields if self.get_argument(field, None) != None]
            if where_fields:
                where = ' WHERE %s' % ' and '.join(
                    ['%s in (%s)' % (field, ','.join(
                        ['"%s"' % pymysql.escape_string(v) for v in self.get_arguments(field)]))
                     for field in where_fields])
        else:
            where = 'WHERE concat(%s) like "%%%s%%"' % (','.join(search_fields),
                                                        pymysql.escape_string(self.get_argument('search')))

        if self.get_argument('offset', None) and self.get_argument('limit', None):
            limit = 'LIMIT %s, %s' % (pymysql.escape_string(self.get_argument('offset')),
                                      pymysql.escape_string(self.get_argument('limit')))

        if self.get_argument('order', None) and self.get_argument('sort', None):
            order = 'ORDER BY %s %s' % (pymysql.escape_string(self.get_argument('sort')),
                                        pymysql.escape_string(self.get_argument('order')))
    return where, order, limit 

def get_valid(func):
    def _wrapper(self):
        error = {}
        logfile = self.get_argument('logfile', '')
        match = self.get_argument('match', '')

        if not logfile:
            error['logfile'] = 'Required'
        else:
            if logfile.isnumeric():
                select_sql = 'SELECT * FROM logfile WHERE id="%s"' % (int(logfile))
            else:
                select_sql = 'SELECT * FROM logfile WHERE name="%s"' % pymysql.escape_string(logfile)
            self.cursor.execute(select_sql)
            logfile = self.cursor.dictfetchone()
            if not logfile:
                error['logfile'] = 'Not exist'

        if error:
            self._write(dict(code=400, msg='Bad GET param', error=error))
            return

        self.reqdata = dict(
            logfile=logfile,
            match=match,
        )

        return func(self)
    return _wrapper 

def string_sort(string):
    string = re.sub("[\n\r]", " ", string)
    return pymysql.escape_string(string) 

def init_session(self):
        self.session_id = self.get_secure_cookie('session_id')
        self.session = None
        if self.session_id:
            session_id = self.session_id.decode('utf-8')
            select_sql = '''
                        SELECT * FROM session WHERE session_id="%s" and expire_time>="%s"
                    ''' % (pymysql.escape_string(session_id), time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))
            self.cursor.execute(select_sql)
            self.session = self.cursor.dictfetchone() 

def argements_valid(handler, pk=None):
    error = dict()
    name = handler.get_argument('name', '')
    path = handler.get_argument('path', '')
    comment = handler.get_argument('comment', '')
    host = handler.get_argument('host', '')
    monitor_choice = handler.get_argument('monitor_choice', '0')

    if not path:
        error['path'] = 'Required'
    else:
        select_sql = 'SELECT id FROM logfile WHERE name="%s" %s'
        select_arg = (pymysql.escape_string(name), 'and id!="%d"' % pk if pk else '')
        count = handler.cursor.execute(select_sql % select_arg)
        if count:
            error['path'] = 'Already existed'

    for i, j in ((name, 'name'), (host, 'host'), (comment, 'comment')):
        if not i:
            error[j] = 'Required'

    if monitor_choice not in ('0', '-1'):
        error['monitor_choice'] = 'Invalid'

    data = dict(name=name,
                path=path,
                comment=comment,
                host=host,
                hosts=host.split(','),
                monitor_choice=int(monitor_choice))
    return error, data 

def test_escape_fallback_encoder(self):
        con = self.connections[0]
        cur = con.cursor()

        class Custom(str):
            pass

        mapping = {text_type: pymysql.escape_string}
        self.assertEqual(con.escape(Custom('foobar'), mapping), "'foobar'") 

def test_escape_fallback_encoder(self):
        con = self.connect()
        cur = con.cursor()

        class Custom(str):
            pass

        mapping = {text_type: pymysql.escape_string}
        self.assertEqual(con.escape(Custom('foobar'), mapping), "'foobar'") 

def get_data_clone_id(self, key, site) -> None or int:
        clone_id = None

        key = pymysql.escape_string(re.sub(r"[_\-. ]", "%", key))
        sql = "SELECT `{site}` FROM `info_list` WHERE `search_name` LIKE '{key}'".format(site=site, key=key)
        try:  # Get clone id info from database
            clone_id = int(self.exec(sql=sql)[0])
        except TypeError:  # The database doesn't have the search data, Return dict only with raw key.
            logging.warning(
                "No record for key: \"{key}\" in \"{site}\". Or may set as `None`".format(key=key, site=site)
            )

        return clone_id 

def test_escape_fallback_encoder(self):
        con = self.connections[0]
        cur = con.cursor()

        class Custom(str):
            pass

        mapping = {text_type: pymysql.escape_string}
        self.assertEqual(con.escape(Custom('foobar'), mapping), "'foobar'") 

def rotate(record, newpassword):
    user = record.login
    oldpassword = record.password

    try:
        host = record.get('cmdr:host')
        port = record.get('cmdr:port') or '3306'
        user_host = record.get('cmdr:user_host') or '%'

        with pymysql.connect(host=host, port=int(port), user=user, password=oldpassword) as cursor:
            is_old_version = True
            affected = cursor.execute('select @@version')
            if affected == 1:
                rs = cursor.fetchone()
                version = rs[0]     # type: str
                vc = version.split('.')
                vn = 0
                if len(vc) == 3:
                    for n in vc:
                        vn *= 1000
                        vn += int(n)
                    is_old_version = vn < 5007006

            if is_old_version:
                sql = f'set password for \'{user}\'@\'{user_host}\' = password(\'{pymysql.escape_string(newpassword)}\')'
            else:
                sql = f'alter user \'{user}\'@\'{user_host}\' identified by \'{pymysql.escape_string(newpassword)}\''
            cursor.execute(sql)
            record.password = newpassword
            return True
    except pymysql.err.OperationalError as e:
        logging.error("MySQL Plugin Error: Unable to establish connection: %s", e)
    except pymysql.err.ProgrammingError as e:
        logging.error("MySQL Plugin Syntax Error: %s", e)
    except Exception as e:
        logging.error("MySQL password rotation error: %s", e)

    return False 

def get_keyword_sentence(cur):
    cur.execute("select * from KeywordsLinks")
    results = cur.fetchall()
    for result in results:
        try:
            print(result)
            link=result[1]
            LinkID=result[0]
            headers = {
                'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 '
                              '(KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36'
            }

            cur.execute("select Word from KeyWords,KeywordsLinks where KeywordsLinks.KeyWordID= KeyWords.KeyWordID and KeywordsLinks.LinkID=(%d)" % int(LinkID))
            keyword=(cur.fetchone())[0]
            pattern=re.compile(r'.{5,20}'+keyword+r'.{5,20}',re.S)
            replace=re.compile(r'<.*?>')
            page = requests.get(link, headers=headers,timeout=1)
            page=replace.sub('',page.text)
            items=re.findall(pattern,page)
            con=""
            for item in items:
                con+=item

            print(LinkID)
            print(len(con))
            cur.execute("""UPDATE KeywordsLinks SET Content="%s" WHERE LinkID=%d""" % (pymysql.escape_string(con),LinkID))#escape_string将用户的输入进行转义，防止SQL注入
            cur.connection.commit()
            time.sleep(random.random())
        except Exception:
            pass
#删除表中的空值 

def write_to_file(link,cur):
    for keyID,Other in link.items():# KeywordID , Other(Link,Keyword,Word)
        for k,pages_v in Other.items(): # Keyword , other(Word , Link)
            for w,links in pages_v.items(): # Word , Link
                for link in links:
                    cur.execute('INSERT INTO KeywordsLinks(Link,KeyWordID) VALUES ("%s","%d")' % (pymysql.escape_string(link),keyID))
                    cur.connection.commit() 

def test_escape_fallback_encoder(self):
        con = self.connections[0]
        cur = con.cursor()

        class Custom(str):
            pass

        mapping = {text_type: pymysql.escape_string}
        self.assertEqual(con.escape(Custom('foobar'), mapping), "'foobar'") 

def test_escape_fallback_encoder(self):
        con = self.connections[0]
        cur = con.cursor()

        class Custom(str):
            pass

        mapping = {pymysql.text_type: pymysql.escape_string}
        self.assertEqual(con.escape(Custom('foobar'), mapping), "'foobar'") 

def test_escape_fallback_encoder(self):
        con = self.connections[0]
        cur = con.cursor()

        class Custom(str):
            pass

        mapping = {text_type: pymysql.escape_string}
        self.assertEqual(con.escape(Custom('foobar'), mapping), "'foobar'") 

def getmain(res,uid,table,conn,url,user_agents, cookies,conf,use_proxies=False):
    dynamic = re.compile(r'.*?><span class="ctt">(.*?)<a href', re.S)#匹配动态
    times = re.compile(r'.*?<span class="ct">(.*?)&nbsp',re.S)#匹配动态发布时间
    page_number = re.compile(r'.*/(\d*?)页</div>',re.S)#匹配动态页数
    re_nbsp = re.compile(r'&nbsp',re.S) #去除$nbsp
    re_html = re.compile(r'</?\w+[^>]*>',re.S) #去除html标签
    re_200b = re.compile(r'\u200b',re.S) #去除分隔符
    re_quot = re.compile(r'&quot',re.S)
    dys = re.findall(dynamic,res.text)
    ts = re.findall(times,res.text)
    pages = re.findall(page_number,res.text)
    if(len(pages) <= 0):
        print('\033[1;31mERROR!!! uid:'+str(uid)+' does not have page_number tags. Skip this uid...\033[0m')
        return
    pagenums=pages[0]
    print(pagenums)

    mainurl=url
    label = 0 #标签用于计数，每十次延时10S
    for pagenum in range(int(pagenums))[1:]:
        if(label ==10 ):
            time.sleep(10)
            label = 0
        # 随机选择，防止被ban
        cookie = random.choice(cookies)
        cookie = getcookies(cookie)
        headers = {
            'User_Agent': random.choice(user_agents)
        }
        pagenum+=1
        label += 1
        url=mainurl+'?page='+str(pagenum)
        page=gethtml(url,headers,cookie,conf,use_proxies)
        dys += re.findall(dynamic,page.text)
        ts += re.findall(times,page.text)
    dys = dys[1:]
    print(len(dys))
    print(len(ts))
    for i in range(len(ts)):
        dys[i] = re_nbsp.sub('', dys[i])
        dys[i] = re_html.sub('', dys[i])
        dys[i] = re_200b.sub('', dys[i])
        dys[i] = re_quot.sub('', dys[i])
        ins = insert(table).values(uid=uid,weibo_cont=pymysql.escape_string(dys[i]),create_time=ts[i])
        ins = ins.on_duplicate_key_update(weibo_cont=pymysql.escape_string(dys[i]))
        conn.execute(ins)

#默认不使用代理ip 

def open_valid(func):
    def _wrapper(self):
        error = {}
        logfile = self.get_argument('logfile', '')
        match = self.get_argument('match', '')
        path = self.get_argument('path', '')
        host = self.get_argument('host', '')

        if not logfile:
            error['logfile'] = 'Required'
        else:
            if logfile.isnumeric():
                select_sql = 'SELECT * FROM logfile WHERE id="%s"' % (int(logfile))
            else:
                select_sql = 'SELECT * FROM logfile WHERE name="%s"' % pymysql.escape_string(logfile)

            self.cursor.execute(select_sql)
            logfile_row = self.cursor.dictfetchone()
            if not logfile_row:
                error['logfile'] = 'Not exist'

        if match:
            try:
                re.search(r'%s' % match, '')
            except:
                error['match'] = 'Incorrect format'

        if not path:
            error['path'] = 'Required'
        elif logfile_row and not re.search(logfile_row['path'], path):
            error['path'] = 'Invalid path'

        if not host:
            error['host'] = 'Required'
        elif logfile_row and host not in logfile_row['host'].split(','):
            error['host'] = 'Invalid host'

        if error:
            message = dict(code=400, msg='Bad Param', error=error)
            self.write_message(message)
            self.close()
        else:
            for callback in self.registers:
                if callback.requser.get('username') == self.requser.get('username'):
                    message = dict(code=403,
                                   msg='New connection has been opened, and this connection needs to be closed')
                    callback.write_message(message)
                    callback.close()
            self.registers.append(self)
            self.match = match
            self.path = path
            self.host = host
            self.logfile = logfile
            return func(self)

    return _wrapper 

def handler(meta, boardid, id, result, big):
    """
    将得到的数据插入数据库，本函数全局只会运行一份
    :param meta: 见mpms文档
    :param boardid: 板块id
    :param id: 帖子id
    :param result: 爬取的帖子内容 list类型 [楼层lc, 用户名user, 发帖内容content, 发帖时间posttime, 最后编辑时间lastedittime]
    :param big: 是否大表 ""或"big"
    :return: 无返回值
    """
    if len(result) == 0:
        return
    if len(result) > 1000:  # avoid too long sql
        handler(meta, boardid, id, result[1000:], big)
        result = result[:1000]
    if result[0][0] == 0:  # 由于避免太长sql的特性，result[0]可能不是帖子标题，判断不是标题就不要显示了
        try:
            showline = [boardid, id, result[0][2], len(result)]
            if myip != "":
                showline.insert(0, myip)  # if enables multiple ip, print IP first
            print(" ".join(str(i) for i in (showline)))
        except:
            try:
                print(" ".join(str(i) for i in (boardid, id, pformat(result[0][2]), len(result))))
            except:
                print("Something cannot print")
    global conn
    sql = "insert ignore into {}bbs_{}(id,lc,user,content,posttime,edittime,gettime) values ".format(big, boardid)
    for i in result:
        sql += "({},{},\"{}\",\"{}\",\"{}\",\"{}\",now()),".format(id, i[0],
                                                                   pymysql.escape_string(i[1]),
                                                                   pymysql.escape_string(i[2]), i[3], i[4])
    # print(sql)
    sql = sql[:-1]
    # 将数据库改为utf8mb4编码后，现在不再替换emoji表情
    cur = conn.cursor()
    try:
        cur.execute(
            "SET NAMES utf8mb4;SET CHARACTER SET utf8mb4; SET character_set_connection=utf8mb4;")  # 相应的这里要处理好编码问题
    except:
        conn = db()
        cur.execute("SET NAMES utf8mb4;SET CHARACTER SET utf8mb4; SET character_set_connection=utf8mb4;")
    try:
        cur.execute(sql)
        conn.commit()
    except pymysql.err.ProgrammingError as e:  # 这种错误就是还没有建表，先调用建表函数再插入
        createTable(boardid, big=big)
        cur.execute(sql)
        conn.commit()
    except Exception as e:
        print(e)