Python sqlparse.split() Examples

def prepare_sql_script(self, sql):
        """
        Takes a SQL script that may contain multiple lines and returns a list
        of statements to feed to successive cursor.execute() calls.

        Since few databases are able to process raw SQL scripts in a single
        cursor.execute() call and PEP 249 doesn't talk about this use case,
        the default implementation is conservative.
        """
        try:
            import sqlparse
        except ImportError:
            raise ImproperlyConfigured(
                "sqlparse is required if you don't split your SQL "
                "statements manually."
            )
        else:
            return [sqlparse.format(statement, strip_comments=True)
                    for statement in sqlparse.split(sql) if statement] 

def prepare_sql_script(self, sql):
        """
        Take an SQL script that may contain multiple lines and return a list
        of statements to feed to successive cursor.execute() calls.

        Since few databases are able to process raw SQL scripts in a single
        cursor.execute() call and PEP 249 doesn't talk about this use case,
        the default implementation is conservative.
        """
        try:
            import sqlparse
        except ImportError:
            raise ImproperlyConfigured(
                "The sqlparse package is required if you don't split your SQL "
                "statements manually."
            )
        else:
            return [sqlparse.format(statement, strip_comments=True)
                    for statement in sqlparse.split(sql) if statement] 

def query_check(self, db_name=None, sql=''):
        # 查询语句的检查、注释去除、切分
        result = {'msg': '', 'bad_query': False, 'filtered_sql': sql, 'has_star': False}
        # 删除注释语句，进行语法判断，执行第一条有效sql
        try:
            sql = sqlparse.format(sql, strip_comments=True)
            sql = sqlparse.split(sql)[0]
            result['filtered_sql'] = sql.strip()
        except IndexError:
            result['bad_query'] = True
            result['msg'] = '没有有效的SQL语句'
        if re.match(r"^select|^show|^explain", sql, re.I) is None:
            result['bad_query'] = True
            result['msg'] = '不支持的查询语法类型!'
        if '*' in sql:
            result['has_star'] = True
            result['msg'] = 'SQL语句中含有 * '
        # select语句先使用Explain判断语法是否正确
        if re.match(r"^select", sql, re.I):
            explain_result = self.query(db_name=db_name, sql=f"explain {sql}")
            if explain_result.error:
                result['bad_query'] = True
                result['msg'] = explain_result.error
        return result 

def execute(self, db_name=None, sql='', close_conn=True):
        """原生执行语句"""
        result = ResultSet(full_sql=sql)
        conn = self.get_connection(db_name=db_name)
        try:
            cursor = conn.cursor()
            for statement in sqlparse.split(sql):
                cursor.execute(statement)
            conn.commit()
            cursor.close()
        except Exception as e:
            logger.warning(f"MySQL语句执行报错，语句：{sql}，错误信息{traceback.format_exc()}")
            result.error = str(e)
        if close_conn:
            self.close()
        return result 

def explainPlan(self, queries, callback):
        queryName = 'explain plan'
        explainQuery = self.getNamedQuery(queryName)
        if not explainQuery:
            return

        strippedQueries = [
            explainQuery.format(query.strip().strip(";"))
            for rawQuery in queries
            for query in filter(None, sqlparse.split(rawQuery))
        ]
        queryToRun = self.buildNamedQuery(queryName, strippedQueries)
        args = self.buildArgs(queryName)
        env = self.buildEnv()
        self.Command.createAndRun(args=args,
                                  env=env,
                                  callback=callback,
                                  query=queryToRun,
                                  encoding=self.encoding,
                                  timeout=self.timeout,
                                  silenceErrors=False,
                                  stream=self.useStreams) 

def execute_check(self, db_name=None, sql=''):
        """上线单执行前的检查, 返回Review set"""
        check_result = ReviewSet(full_sql=sql)
        # 切分语句，追加到检测结果中，默认全部检测通过
        rowid = 1
        split_sql = sqlparse.split(sql)
        for statement in split_sql:
            check_result.rows.append(ReviewResult(
                id=rowid,
                errlevel=0,
                stagestatus='Audit completed',
                errormessage='None',
                sql=statement,
                affected_rows=0,
                execute_time=0, )
            )
            rowid += 1
        return check_result 

def execute_check(self, db_name=None, sql=''):
        """上线单执行前的检查, 返回Review set"""
        check_result = ReviewSet(full_sql=sql)
        # 切分语句，追加到检测结果中，默认全部检测通过
        split_reg = re.compile('^GO$', re.I | re.M)
        sql = re.split(split_reg, sql, 0)
        sql = filter(None, sql)
        split_sql = [f"""use [{db_name}]"""]
        for i in sql:
            split_sql = split_sql + [i]
        rowid = 1
        for statement in split_sql:
            check_result.rows.append(ReviewResult(
                id=rowid,
                errlevel=0,
                stagestatus='Audit completed',
                errormessage='None',
                sql=statement,
                affected_rows=0,
                execute_time=0, ))
            rowid += 1
        return check_result 

def is_mutating(status):
    """Determines if the statement is mutating based on the status."""
    if not status:
        return False

    mutating = set(
        [
            "insert",
            "update",
            "delete",
            "alter",
            "create",
            "drop",
            "replace",
            "truncate",
            "load",
        ]
    )
    return status.split(None, 1)[0].lower() in mutating 

def query_check(self, db_name=None, sql=''):
        # 查询语句的检查、注释去除、切分
        result = {'msg': '', 'bad_query': False, 'filtered_sql': sql, 'has_star': False}
        # 删除注释语句，进行语法判断，执行第一条有效sql
        try:
            sql = sqlparse.format(sql, strip_comments=True)
            sql = sqlparse.split(sql)[0]
            result['filtered_sql'] = sql.strip()
        except IndexError:
            result['bad_query'] = True
            result['msg'] = '没有有效的SQL语句'
        if re.match(r"^select", sql, re.I) is None:
            result['bad_query'] = True
            result['msg'] = '不支持的查询语法类型!'
        if '*' in sql:
            result['has_star'] = True
            result['msg'] = 'SQL语句中含有 * '
        return result 

def need_completion_refresh(queries):
    """Determines if the completion needs a refresh by checking if the sql
    statement is an alter, create, drop or change db."""
    for query in sqlparse.split(queries):
        try:
            first_token = query.split()[0]
            if first_token.lower() in (
                "alter",
                "create",
                "use",
                "\\r",
                "\\u",
                "connect",
                "drop",
            ):
                return True
        except Exception:
            return False 

def test_psql_quotation_marks():  # issue83
    # regression: make sure plain $$ work
    t = sqlparse.split("""
    CREATE OR REPLACE FUNCTION testfunc1(integer) RETURNS integer AS $$
          ....
    $$ LANGUAGE plpgsql;
    CREATE OR REPLACE FUNCTION testfunc2(integer) RETURNS integer AS $$
          ....
    $$ LANGUAGE plpgsql;""")
    assert len(t) == 2
    # make sure $SOMETHING$ works too
    t = sqlparse.split("""
    CREATE OR REPLACE FUNCTION testfunc1(integer) RETURNS integer AS $PROC_1$
          ....
    $PROC_1$ LANGUAGE plpgsql;
    CREATE OR REPLACE FUNCTION testfunc2(integer) RETURNS integer AS $PROC_2$
          ....
    $PROC_2$ LANGUAGE plpgsql;""")
    assert len(t) == 2 

def query_check(self, db_name=None, sql=''):
        # 查询语句的检查、注释去除、切分
        result = {'msg': '', 'bad_query': False, 'filtered_sql': sql, 'has_star': False}
        keyword_warning = ''
        sql_whitelist = ['select', 'explain']
        # 根据白名单list拼接pattern语句
        whitelist_pattern = "^" + "|^".join(sql_whitelist)
        # 删除注释语句，进行语法判断，执行第一条有效sql
        try:
            sql = sql.format(sql, strip_comments=True)
            sql = sqlparse.split(sql)[0]
            result['filtered_sql'] = sql.strip()
            # sql_lower = sql.lower()
        except IndexError:
            result['bad_query'] = True
            result['msg'] = '没有有效的SQL语句'
            return result
        if re.match(whitelist_pattern, sql) is None:
            result['bad_query'] = True
            result['msg'] = '仅支持{}语法!'.format(','.join(sql_whitelist))
            return result
        if result.get('bad_query'):
            result['msg'] = keyword_warning
        return result 

def execute_query(self, query):
        # Try to run first as special command
        try:
            for rows, columns, status, statement, is_error in special.execute(self, query):
                yield rows, columns, status, statement, is_error
        except special.CommandNotFound:
            # Execute as normal sql
            # Remove spaces, EOL and semi-colons from end
            query = query.strip()
            if not query:
                yield None, None, None, query, False
            else:
                for single_query in sqlparse.split(query):
                    # Remove spaces, EOL and semi-colons from end
                    single_query = single_query.strip()
                    if single_query:
                        for rows, columns, status, statement, is_error \
                            in self._execute_query(single_query):
                            yield rows, columns, status, statement, is_error
                    else:
                        yield None, None, None, None, False
                        continue 

def prepare_sql_script(self, sql):
        """
        Takes an SQL script that may contain multiple lines and returns a list
        of statements to feed to successive cursor.execute() calls.

        Since few databases are able to process raw SQL scripts in a single
        cursor.execute() call and PEP 249 doesn't talk about this use case,
        the default implementation is conservative.
        """
        try:
            import sqlparse
        except ImportError:
            raise ImproperlyConfigured(
                "sqlparse is required if you don't split your SQL "
                "statements manually."
            )
        else:
            return [sqlparse.format(statement, strip_comments=True)
                    for statement in sqlparse.split(sql) if statement] 

def prepare_sql_script(self, sql):
        """
        Take an SQL script that may contain multiple lines and return a list
        of statements to feed to successive cursor.execute() calls.

        Since few databases are able to process raw SQL scripts in a single
        cursor.execute() call and PEP 249 doesn't talk about this use case,
        the default implementation is conservative.
        """
        try:
            import sqlparse
        except ImportError:
            raise ImproperlyConfigured(
                "sqlparse is required if you don't split your SQL "
                "statements manually."
            )
        else:
            return [sqlparse.format(statement, strip_comments=True)
                    for statement in sqlparse.split(sql) if statement] 

def dev_work_detail(id):
    """
    Show worker order status detail.
    :param id:
    :return:
    """
    work = Work.query.get(id)

    if work.status == 0 or work.status == 4:
        list_content = json.loads(work.execute_result)
    else:
        list_content = json.loads(work.auto_review)

    for content in list_content:
        content[4] = content[4].split('\n')
        content[5] = content[5].split('\r\n')

    return render_template('dev/work_detail.html', work=work, list_content=list_content) 

def prepare_sql_script(self, sql):
        """
        Takes an SQL script that may contain multiple lines and returns a list
        of statements to feed to successive cursor.execute() calls.

        Since few databases are able to process raw SQL scripts in a single
        cursor.execute() call and PEP 249 doesn't talk about this use case,
        the default implementation is conservative.
        """
        try:
            import sqlparse
        except ImportError:
            raise ImproperlyConfigured(
                "sqlparse is required if you don't split your SQL "
                "statements manually."
            )
        else:
            return [sqlparse.format(statement, strip_comments=True)
                    for statement in sqlparse.split(sql) if statement] 

def _statement_split(self, statements):
        """
        Split a statement into separate SQL statements.

        This method converts a single string containing one or more SQL
        statements into an iterator of strings, each corresponding to one SQL
        statement. If the statement's language is not to be SQL, this method
        should be overloaded appropriately.

        Args:
            statements (str): A string containing one or more SQL statements.

        Returns:
            iterator<str>: An iterator of SQL statements.
        """
        for statement in sqlparse.split(statements):
            statement = statement.strip()
            if statement.endswith(';'):
                statement = statement[:-1].strip()
            if statement:  # remove empty statements
                yield statement 

def server_version(self):
        def numeric_part(s):
            """Returns the leading numeric part of a string.
            """
            re_numeric_part = re.compile(r"^(\d+)")
            m = re_numeric_part.match(s)
            if m:
                return int(m.group(1))
            return None

        self.get_connection()
        version = self.conn.get_server_info()
        return tuple([numeric_part(n) for n in version.split('.')[:3]]) 

def optimize_sqltuning(request):
    instance_name = request.POST.get('instance_name')
    db_name = request.POST.get('db_name')
    sqltext = request.POST.get('sql_content')
    option = request.POST.getlist('option[]')
    sqltext = sqlparse.format(sqltext, strip_comments=True)
    sqltext = sqlparse.split(sqltext)[0]
    if re.match(r"^select|^show|^explain", sqltext, re.I) is None:
        result = {'status': 1, 'msg': '只支持查询SQL！', 'data': []}
        return HttpResponse(json.dumps(result),content_type='application/json')
    try:
        user_instances(request.user).get(instance_name=instance_name)
    except Instance.DoesNotExist:
        result = {'status': 1, 'msg': '你所在组未关联该实例！', 'data': []}
        return HttpResponse(json.dumps(result), content_type='application/json')

    sql_tunning = SqlTuning(instance_name=instance_name, db_name=db_name, sqltext=sqltext)
    result = {'status': 0, 'msg': 'ok', 'data': {}}
    if 'sys_parm' in option:
        basic_information = sql_tunning.basic_information()
        sys_parameter = sql_tunning.sys_parameter()
        optimizer_switch = sql_tunning.optimizer_switch()
        result['data']['basic_information'] = basic_information
        result['data']['sys_parameter'] = sys_parameter
        result['data']['optimizer_switch'] = optimizer_switch
    if 'sql_plan' in option:
        plan, optimizer_rewrite_sql = sql_tunning.sqlplan()
        result['data']['optimizer_rewrite_sql'] = optimizer_rewrite_sql
        result['data']['plan'] = plan
    if 'obj_stat' in option:
        result['data']['object_statistics'] = sql_tunning.object_statistics()
    if 'sql_profile' in option:
        session_status = sql_tunning.exec_sql()
        result['data']['session_status'] = session_status
    # 关闭连接
    sql_tunning.engine.close()
    result['data']['sqltext'] = sqltext
    return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
                        content_type='application/json') 

def is_auto_review(workflow_id):
    """
    判断SQL上线是否无需审批，无需审批的提交会自动审核通过
    :param workflow_id:
    :return:
    """

    workflow = SqlWorkflow.objects.get(id=workflow_id)
    auto_review_tags = SysConfig().get('auto_review_tag', '').split(',')
    # TODO 这里也可以放到engine中实现，但是配置项可能会相对复杂
    if workflow.instance.db_type == 'mysql' and workflow.instance.instance_tag.filter(
            tag_code__in=auto_review_tags).exists():
        # 获取正则表达式
        auto_review_regex = SysConfig().get('auto_review_regex',
                                            '^alter|^create|^drop|^truncate|^rename|^delete')
        p = re.compile(auto_review_regex, re.I)

        # 判断是否匹配到需要手动审核的语句
        auto_review = True
        sql_content = workflow.sqlworkflowcontent.sql_content
        for statement in sqlparse.split(sql_content):
            # 删除注释语句
            statement = sqlparse.format(statement, strip_comments=True)
            if p.match(statement.strip()):
                auto_review = False
                break
            if auto_review:
                # 更新影响行数加测,总语句影响行数超过指定数量则需要人工审核
                review_engine = get_engine(instance=workflow.instance)
                inception_review = review_engine.execute_check(db_name=workflow.db_name, sql=sql_content).to_dict()
                all_affected_rows = 0
                for review_result in inception_review:
                    sql = review_result.get('sql', '')
                    affected_rows = review_result.get('affected_rows', 0)
                    if re.match(r"^update", sql.strip().lower()):
                        all_affected_rows = all_affected_rows + int(affected_rows)
                if int(all_affected_rows) > int(SysConfig().get('auto_review_max_update_rows', 50)):
                    auto_review = False
    else:
        auto_review = False
    return auto_review 

def _simplify_whitespace(s):
    return b' '.join(s.split()) 

def _sqlfile_to_statements(sql):
    """
    Takes a SQL string containing 0 or more statements and returns a 
    list of individual statements as strings. Comments and
    empty statements are ignored.
    """
    statements = (sqlparse.format(stmt, strip_comments=True).strip() for stmt in sqlparse.split(sql))
    return [stmt for stmt in statements if stmt]

#### Migrations repositories 

def get_migrations(self, exclude=None):
        filenames = self._get_all_filenames()
        filenames = [os.path.split(fn)[1] for fn in filenames]
        if exclude:
            filenames = set(filenames) - set(exclude)
            filenames = sorted(filenames)
        return filenames


#### Database-independent interface for migration-related operations 

def convert_to_sqlalchemy_statement(raw_sql_script):
    """Convert raw SQL into SQLAlchemy statement."""
    # remove comment and tail spaces
    formated_sql_script = sqlparse.format(
        raw_sql_script.strip(), strip_comments=True)
    return sqlparse.split(formated_sql_script) 

def get_base_sqlitem_list(full_sql):
    ''' 把参数 full_sql 转变为 SqlItem列表
    :param full_sql: 完整sql字符串, 每个SQL以分号;间隔, 不包含plsql执行块和plsql对象定义块
    :return: SqlItem对象列表
    '''
    list = []
    for statement in sqlparse.split(full_sql):
        statement = sqlparse.format(statement, strip_comments=True, reindent=True, keyword_case='lower')
        if len(statement) <= 0:
            continue
        item = SqlItem(statement=statement)
        list.append(item)
    return list 

def server_version(self):
        conn = self.get_connection()
        version = conn.version
        return tuple([n for n in version.split('.')[:3]]) 

def query_check(self, db_name=None, sql=''):
        # 查询语句的检查、注释去除、切分
        result = {'msg': '', 'bad_query': False, 'filtered_sql': sql, 'has_star': False}
        banned_keywords = ["ascii", "char", "charindex", "concat", "concat_ws", "difference", "format",
                           "len", "nchar", "patindex", "quotename", "replace", "replicate",
                           "reverse", "right", "soundex", "space", "str", "string_agg",
                           "string_escape", "string_split", "stuff", "substring", "trim", "unicode"]
        keyword_warning = ''
        star_patter = r"(^|,|\s)\*(\s|\(|$)"
        sql_whitelist = ['select', 'sp_helptext']
        # 根据白名单list拼接pattern语句
        whitelist_pattern = "^" + "|^".join(sql_whitelist)
        # 删除注释语句，进行语法判断，执行第一条有效sql
        try:
            sql = sql.format(sql, strip_comments=True)
            sql = sqlparse.split(sql)[0]
            result['filtered_sql'] = sql.strip()
            sql_lower = sql.lower()
        except IndexError:
            result['bad_query'] = True
            result['msg'] = '没有有效的SQL语句'
            return result
        if re.match(whitelist_pattern, sql_lower) is None:
            result['bad_query'] = True
            result['msg'] = '仅支持{}语法!'.format(','.join(sql_whitelist))
            return result
        if re.search(star_patter, sql_lower) is not None:
            keyword_warning += '禁止使用 * 关键词\n'
            result['has_star'] = True
        if '+' in sql_lower:
            keyword_warning += '禁止使用 + 关键词\n'
            result['bad_query'] = True
        for keyword in banned_keywords:
            pattern = r"(^|,| |=){}( |\(|$)".format(keyword)
            if re.search(pattern, sql_lower) is not None:
                keyword_warning += '禁止使用 {} 关键词\n'.format(keyword)
                result['bad_query'] = True
        if result.get('bad_query') or result.get('has_star'):
            result['msg'] = keyword_warning
        return result 

def run(self, engine, step=None):
        """Runs SQL script through raw dbapi execute call"""
        text = self.source()
        # Don't rely on SA's autocommit here
        # (SA uses .startswith to check if a commit is needed. What if script
        # starts with a comment?)
        conn = engine.connect()
        try:
            trans = conn.begin()
            try:
                # ignore transaction management statements that are
                # redundant in SQL script context and result in
                # operational error being returned.
                #
                # Note: we don't ignore ROLLBACK in migration scripts
                # since its usage would be insane anyway, and we're
                # better to fail on its occurance instead of ignoring it
                # (and committing transaction, which is contradictory to
                # the whole idea of ROLLBACK)
                ignored_statements = ('BEGIN', 'END', 'COMMIT')
                ignored_regex = re.compile('^\s*(%s).*;?$' % '|'.join(ignored_statements),
                                           re.IGNORECASE)

                # NOTE(ihrachys): script may contain multiple statements, and
                # not all drivers reliably handle multistatement queries or
                # commands passed to .execute(), so split them and execute one
                # by one
                text = sqlparse.format(text, strip_comments=True, strip_whitespace=True)
                for statement in sqlparse.split(text):
                    if statement:
                        if re.match(ignored_regex, statement):
                            log.warning('"%s" found in SQL script; ignoring' % statement)
                        else:
                            conn.execute(statement)
                trans.commit()
            except Exception as e:
                log.error("SQL script %s failed: %s", self.path, e)
                trans.rollback()
                raise
        finally:
            conn.close() 

def __init__(self, mssqlcli_options, sql_tools_client, owner_uri=None, **kwargs):

        self.server_name = mssqlcli_options.server
        if ',' in mssqlcli_options.server:
            self.prompt_host, self.prompt_port = self.server_name.split(',')
        else:
            self.prompt_host = mssqlcli_options.server
            self.prompt_port = 1433

        self.user_name = mssqlcli_options.username
        self.password = mssqlcli_options.password
        self.authentication_type = u'Integrated' if mssqlcli_options.integrated_auth \
                                                 else u'SqlLogin'
        self.database = mssqlcli_options.database
        self.connected_database = None
        self.encrypt = mssqlcli_options.encrypt
        self.trust_server_certificate = mssqlcli_options.trust_server_certificate
        self.connection_timeout = mssqlcli_options.connection_timeout
        self.application_intent = mssqlcli_options.application_intent
        self.multi_subnet_failover = mssqlcli_options.multi_subnet_failover
        self.packet_size = mssqlcli_options.packet_size

        self.owner_uri = owner_uri if owner_uri else generate_owner_uri()
        self.sql_tools_client = sql_tools_client
        self.is_connected = False
        self.server_version = None
        self.server_edition = None
        self.is_cloud = False

        self.extra_params = kwargs

        logger.info(u'Initialized MssqlCliClient with owner Uri %s', self.owner_uri)