Python sqlparse.split函数代码示例

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 test_split_quotes_with_new_line():
    stmts = sqlparse.split('select "foo\nbar"')
    assert len(stmts) == 1
    assert stmts[0] == 'select "foo\nbar"'

    stmts = sqlparse.split("select 'foo\n\bar'")
    assert len(stmts) == 1
    assert stmts[0] == "select 'foo\n\bar'"

def sqlRstToBlockSequence(sqlText):
    def __tryMatchCommentBlock(text):
        """
        :param text: the text to parse
        :return: (CommentBlock|None,str). The block matched and the rest
            of the string or None and the text.
        """
        m = re.match(BLOCK_COMMENT_BLOCK, text, re.MULTILINE | re.DOTALL)
        if m:
            return (
                structure.BlockCommentBlock(m.group("comment"), m.group("before"), m.group("after")),
                m.group("rest"),
            )
        m = re.match(LINES_COMMENT_BLOCK, text, re.MULTILINE | re.DOTALL)
        if m:
            return (
                structure.LinesCommentBlock(m.group("comment"), m.group("before"), m.group("after")),
                m.group("rest"),
            )
        return (None, text)

    def __doMatchSQLStatement(text):
        m = re.match(CREATE_TABLE_RE + ".*", text, re.IGNORECASE)
        if m:
            return structure.CreateTableStatementBlock(text, name=m.group("name"))
        m = re.match(CREATE_VIEW_RE + ".*", text, re.IGNORECASE)
        if m:
            return structure.CreateViewStatementBlock(text, name=m.group("name"))
        m = re.match(SELECT_RE + ".*", text, re.IGNORECASE)
        if m:
            return structure.SelectStatementBlock(text)
        return structure.UnknownStatementBlock(text)

    blocks = []
    # Split the text in (comments* +sqlstatement restofline) segments.
    # Spaces+lines before comments are removed, spaces after blocks
    # are removed. some blank lines may disappear in the process.
    segments = sqlparse.split(sqlText)

    # Process each segment as they still may contains some comments
    # additionaly to one (or more?) sql statements
    for segment in segments:

        rest = segment

        # try to get all comments before the sql statement(s?)
        (comment_block, rest) = __tryMatchCommentBlock(rest)
        while comment_block is not None:  # and not re.match('\s*',rest, re.MULTILINE):
            blocks.append(comment_block)
            (comment_block, rest) = __tryMatchCommentBlock(rest)

        # a priori there is just one remaining sql block,
        # but just to be sure...
        stmt_texts = sqlparse.split(rest)
        for stmt_text in stmt_texts:
            blocks.append(__doMatchSQLStatement(stmt_text))

    return blocks

def exec_impala_query_from_file(file_name):
  """Execute each query in an Impala query file individually"""
  if not os.path.exists(file_name):
    LOG.info("Error: File {0} not found".format(file_name))
    return False

  LOG.info("Beginning execution of impala SQL: {0}".format(file_name))
  is_success = True
  impala_client = ImpalaBeeswaxClient(options.impalad, use_kerberos=options.use_kerberos)
  output_file = file_name + ".log"
  with open(output_file, 'w') as out_file:
    try:
      impala_client.connect()
      with open(file_name, 'r+') as query_file:
        queries = sqlparse.split(query_file.read())
        for query in queries:
          query = sqlparse.format(query.rstrip(';'), strip_comments=True)
          if query.strip() != "":
            result = impala_client.execute(query)
            out_file.write("{0}\n{1}\n".format(query, result))
    except Exception as e:
      out_file.write("ERROR: {0}\n".format(query))
      traceback.print_exc(file=out_file)
      is_success = False

  if is_success:
    LOG.info("Finished execution of impala SQL: {0}".format(file_name))
  else:
    LOG.info("Error executing impala SQL: {0} See: {1}".format(file_name, \
             output_file))

  return is_success

def executeSQL(connection,command,parent_conn = None):
    ''' command is a sequence of SQL commands
        separated by ";" and possibly "\n"
        connection is a MySQLdb connection
        returns the output from the last command
        in the sequence
    '''
    #split commands by \n
    commands = command.split("\n")
    #remove comments and whitespace"
    commands = [x for x in commands if x.lstrip()[0:2] != '--']
    commands = [re.sub('\r','',x) for x in commands if x.lstrip() != '\r']
    command = ' '.join(commands)

    statements = sqlparse.split(command)
    count = 0
    for statement in statements:
        cur = connection.cursor()
        #make sure actually does something
        if sqlparse.parse(statement):
            cur.execute(statement)
        cur.close()
    connection.commit()
    if parent_conn:
        parent_conn.send(True)
    return True

 def explain(self):
     self.results.assure_visible()
     buf = self.textview.get_buffer()
     bounds = buf.get_selection_bounds()
     if not bounds:
         bounds = buf.get_bounds()
     statement = buf.get_text(*bounds)
     if len(sqlparse.split(statement)) > 1:
         dialogs.error(_(u"Select a single statement to explain."))
         return
     if not self.connection:
         return
     queries = [Query(stmt, self.connection)
                for stmt in self.connection.explain_statements(statement)]
     def _execute_next(last, queries):
         if last is not None and last.failed:
             self.results.set_explain_results(last)
             return
         q = queries.pop(0)
         if len(queries) == 0:
             q.connect('finished',
                       lambda x: self.results.set_explain_results(x))
         else:
             q.connect('finished',
                       lambda x: _execute_next(x, queries))
         q.execute()
     _execute_next(None, queries)

 def get_schema(self, name):
     fp = self.load_file(name, 'schemas')
     query = fp.read()
     # queries = query.split(';')
     queries = sqlparse.split(query)
     ret_queries = [q.strip() for q in queries if q.strip()]
     return ret_queries

    def loadDatabaseSchema(dumpFileName, ignoreSlonyTriggers = False):
        database = PgDatabase()
        logging.debug('Loading %s file' % dumpFileName)

        statements = sqlparse.split(open(dumpFileName,'r'))
        logging.debug('Parsed %d statements' % len(statements))
        for statement in statements:
            statement = PgDumpLoader.strip_comment(statement).strip()
            if PgDumpLoader.PATTERN_CREATE_SCHEMA.match(statement):
                CreateSchemaParser.parse(database, statement)
                continue

            match = PgDumpLoader.PATTERN_DEFAULT_SCHEMA.match(statement)
            if match:
                database.setDefaultSchema(match.group(1))
                continue

            if PgDumpLoader.PATTERN_CREATE_TABLE.match(statement):
                CreateTableParser.parse(database, statement)
                continue

            if PgDumpLoader.PATTERN_ALTER_TABLE.match(statement):
                AlterTableParser.parse(database, statement)
                continue

            if PgDumpLoader.PATTERN_CREATE_SEQUENCE.match(statement):
                CreateSequenceParser.parse(database, statement)
                continue

            if PgDumpLoader.PATTERN_ALTER_SEQUENCE.match(statement):
                AlterSequenceParser.parse(database, statement)
                continue

            if PgDumpLoader.PATTERN_CREATE_INDEX.match(statement):
                CreateIndexParser.parse(database, statement)
                continue

            if PgDumpLoader.PATTERN_CREATE_VIEW.match(statement):
                CreateViewParser.parse(database, statement)
                continue

            if PgDumpLoader.PATTERN_ALTER_VIEW.match(statement):
                AlterViewParser.parse(database, statement)
                continue

            if PgDumpLoader.PATTERN_CREATE_TRIGGER.match(statement):
                CreateTriggerParser.parse(database, statement, ignoreSlonyTriggers)
                continue

            if PgDumpLoader.PATTERN_CREATE_FUNCTION.match(statement):
                CreateFunctionParser.parse(database, statement)
                continue

            if PgDumpLoader.PATTERN_COMMENT.match(statement):
                CommentParser.parse(database, statement)
                continue

            logging.info('Ignored statement: %s' % statement)

        return database

 def test_if_function(self):  # see issue 33
     # don't let IF as a function confuse the splitter
     sql = ('CREATE TEMPORARY TABLE tmp '
            'SELECT IF(a=1, a, b) AS o FROM one; '
            'SELECT t FROM two')
     stmts = sqlparse.split(sql)
     self.assertEqual(len(stmts), 2)

    def run(self, statement):
        """Execute the sql in the database and return the results. The results
        are a list of tuples. Each tuple has 4 values
        (title, rows, headers, status).
        """

        # Remove spaces and EOL
        statement = statement.strip()
        if not statement:  # Empty string
            yield (None, None, None, None)

        # Split the sql into separate queries and run each one.
        for sql in sqlparse.split(statement):
            # Remove spaces, eol and semi-colons.
            sql = sql.rstrip(';')

            # \G is treated specially since we have to set the expanded output
            # and then proceed to execute the sql as normal.
            if sql.endswith('\\G'):
                special.set_expanded_output(True)
                yield self.execute_normal_sql(sql.rsplit('\\G', 1)[0])
            else:
                try:   # Special command
                    _logger.debug('Trying a dbspecial command. sql: %r', sql)
                    cur = self.conn.cursor()
                    for result in special.execute(cur, sql):
                        yield result
                except special.CommandNotFound:  # Regular SQL
                    yield self.execute_normal_sql(sql)

def execute_statements(cursor, statements):
    """Executes statements."""

    statements = statements.strip(u'%s%s' % (string.whitespace, ';'))
    statement_list = None
    if statements:
        statement_list = sqlparse.split(statements)

    if not statements:
        return

    try:
        for statement in statement_list:
            statement = statement.rstrip(u'%s%s' % (string.whitespace, ';'))

            if not statement:
                continue

            cursor.execute(statement)

            while cursor.nextset() is not None:
                pass

    finally:
        while cursor.nextset() is not None:
            pass

def execute_favorite_query(cur, arg, **_):
    """Returns (title, rows, headers, status)"""
    if arg == '':
        for result in list_favorite_queries():
            yield result

    """Parse out favorite name and optional substitution parameters"""
    name, _, arg_str = arg.partition(' ')
    args = shlex.split(arg_str)

    query = favoritequeries.get(name)
    if query is None:
        message = "No favorite query: %s" % (name)
        yield (None, None, None, message)
    else:
        query, arg_error = subst_favorite_query_args(query, args)
        if arg_error:
            yield (None, None, None, arg_error)
        else:
            for sql in sqlparse.split(query):
                sql = sql.rstrip(';')
                title = '> %s' % (sql)
                cur.execute(sql)
                if cur.description:
                    headers = [x[0] for x in cur.description]
                    yield (title, cur, headers, None)
                else:
                    yield (title, None, None, None)

    def find_statements(self):
        """Finds statements in current buffer.

        Returns:
            List of 2-tuples (start iter, end iter).
        """
        buffer_ = self.get_buffer()
        buffer_start, buffer_end = buffer_.get_bounds()
        content = buffer_.get_text(buffer_start, buffer_end)
        iter_ = buffer_.get_start_iter()
        for stmt in sqlparse.split(content):
            if not stmt.strip():
                continue
            # FIXME: Does not work if linebreaks in buffers are not '\n'.
            #    sqlparse unifies them!
            bounds = iter_.forward_search(stmt.strip(),
                                          gtk.TEXT_SEARCH_TEXT_ONLY)
            if bounds is None:
                continue
            start, end = bounds
            yield start, end
            iter_ = end
            if not iter_:
                raise StopIteration
        raise StopIteration

def run(conn, sql, config, user_namespace):
    if sql.strip():
        for statement in sqlparse.split(sql):
            first_word = sql.strip().split()[0].lower()
            if first_word == 'begin':
                raise Exception("ipython_sql does not support transactions")
            if first_word.startswith('\\') and 'postgres' in str(conn.dialect):
                pgspecial = PGSpecial()
                _, cur, headers, _ = pgspecial.execute(
                                              conn.session.connection.cursor(),
                                              statement)[0]
                result = FakeResultProxy(cur, headers)
            else:
                txt = sqlalchemy.sql.text(statement)
                result = conn.session.execute(txt, user_namespace)
            try:
                # mssql has autocommit
                if config.autocommit and ('mssql' not in str(conn.dialect)):
                    conn.session.execute('commit')
            except sqlalchemy.exc.OperationalError:
                pass # not all engines can commit
            if result and config.feedback:
                print(interpret_rowcount(result.rowcount))
        resultset = ResultSet(result, statement, config)
        if config.autopandas:
            return resultset.DataFrame()
        else:
            return resultset
        #returning only last result, intentionally
    else:
        return 'Connected: %s' % conn.name

    def run(self, statement):
        """Execute the sql in the database and return the results. The results
        are a list of tuples. Each tuple has 4 values
        (title, rows, headers, status).
        """

        # Remove spaces and EOL
        statement = statement.strip()
        if not statement:  # Empty string
            yield (None, None, None, None)

        # Split the sql into separate queries and run each one.
        # Unless it's saving a favorite query, in which case we
        # want to save them all together.
        if statement.startswith('\\fs'):
            components = [statement]

        else:
            components = sqlparse.split(statement)

        for sql in components:
            # Remove spaces, eol and semi-colons.
            sql = sql.rstrip(';')

            # \G is treated specially since we have to set the expanded output.
            if sql.endswith('\\G'):
                special.set_expanded_output(True)
                sql = sql[:-2].strip()
            try:   # Special command
                _logger.debug('Trying a dbspecial command. sql: %r', sql)
                cur = self.conn.cursor()
                for result in special.execute(cur, sql):
                    yield result
            except special.CommandNotFound:  # Regular SQL
                yield self.execute_normal_sql(sql)