Python sqlparse.parse() Examples

def parse_partial_identifier(word):
    """Attempt to parse a (partially typed) word as an identifier

    word may include a schema qualification, like `schema_name.partial_name`
    or `schema_name.` There may also be unclosed quotation marks, like
    `"schema`, or `schema."partial_name`

    :param word: string representing a (partially complete) identifier
    :return: sqlparse.sql.Identifier, or None
    """

    p = sqlparse.parse(word)[0]
    n_tok = len(p.tokens)
    if n_tok == 1 and isinstance(p.tokens[0], Identifier):
        return p.tokens[0]
    elif p.token_next_by(m=(Error, '"'))[1]:
        # An unmatched double quote, e.g. '"foo', 'foo."', or 'foo."bar'
        # Close the double quote, then reparse
        return parse_partial_identifier(word + '"')
    else:
        return None 

def tableFromCreateStatement(schema, stmt):
    """
    Add a table from a CREATE TABLE sqlparse statement object.

    @param schema: The schema to add the table statement to.
    @type schema: L{Schema}

    @param stmt: The C{CREATE TABLE} statement object.
    @type stmt: L{Statement}
    """
    i = iterSignificant(stmt)
    expect(i, ttype=Keyword.DDL, value="CREATE")
    expect(i, ttype=Keyword, value="TABLE")
    function = expect(i, cls=Function)
    i = iterSignificant(function)
    name = expect(i, cls=Identifier).get_name().encode("utf-8")
    self = Table(schema, name)
    parens = expect(i, cls=Parenthesis)
    cp = _ColumnParser(self, iterSignificant(parens), parens)
    cp.parse()
    return self 

def extract_tables(sql):
    """Extract the table names from an SQL statment.

    Returns a list of (schema, table, alias) tuples

    """
    parsed = sqlparse.parse(sql)
    if not parsed:
        return []

    # INSERT statements must stop looking for tables at the sign of first
    # Punctuation. eg: INSERT INTO abc (col1, col2) VALUES (1, 2)
    # abc is the table name, but if we don't stop at the first lparen, then
    # we'll identify abc, col1 and col2 as table names.
    insert_stmt = parsed[0].token_first().value.lower() == "insert"
    stream = extract_from_part(parsed[0], stop_at_punctuation=insert_stmt)
    return list(extract_table_identifiers(stream)) 

def test_placeholder(self):
        def _get_tokens(sql):
            return sqlparse.parse(sql)[0].tokens[-1].tokens
        t = _get_tokens('select * from foo where user = ?')
        self.assert_(t[-1].ttype is sqlparse.tokens.Name.Placeholder)
        self.assertEqual(t[-1].value, '?')
        t = _get_tokens('select * from foo where user = :1')
        self.assert_(t[-1].ttype is sqlparse.tokens.Name.Placeholder)
        self.assertEqual(t[-1].value, ':1')
        t = _get_tokens('select * from foo where user = :name')
        self.assert_(t[-1].ttype is sqlparse.tokens.Name.Placeholder)
        self.assertEqual(t[-1].value, ':name')
        t = _get_tokens('select * from foo where user = %s')
        self.assert_(t[-1].ttype is sqlparse.tokens.Name.Placeholder)
        self.assertEqual(t[-1].value, '%s')
        t = _get_tokens('select * from foo where user = $a')
        self.assert_(t[-1].ttype is sqlparse.tokens.Name.Placeholder)
        self.assertEqual(t[-1].value, '$a') 

def extract_column_names(sql):
    p = parse(sql)[0]
    return _extract_column_names(p) 

def sql_tokenize(string):
    """ Tokenizes a SQL statement into tokens.

    Inputs:
       string: string to tokenize.

    Outputs:
       a list of tokens.
    """
    tokens = []
    statements = sqlparse.parse(string)

    # SQLparse gives you a list of statements.
    for statement in statements:
        # Flatten the tokens in each statement and add to the tokens list.
        flat_tokens = sqlparse.sql.TokenList(statement.tokens).flatten()
        for token in flat_tokens:
            strip_token = str(token).strip()
            if len(strip_token) > 0:
                tokens.append(strip_token)

    newtokens = []
    keep = True
    for i, token in enumerate(tokens):
        if token == ".":
            newtoken = newtokens[-1] + "." + tokens[i + 1]
            newtokens = newtokens[:-1] + [newtoken]
            keep = False
        elif keep:
            newtokens.append(token)
        else:
            keep = True

    return newtokens 

def test_issue13(self):
        parsed = sqlparse.parse(("select 'one';\n"
                                 "select 'two\\'';\n"
                                 "select 'three';"))
        self.assertEqual(len(parsed), 3)
        self.assertEqual(str(parsed[1]).strip(), "select 'two\\'';") 

def test_issue78():
    # the bug author provided this nice examples, let's use them!
    def _get_identifier(sql):
        p = sqlparse.parse(sql)[0]
        return p.tokens[2]
    results = (('get_name', 'z'),
               ('get_real_name', 'y'),
               ('get_parent_name', 'x'),
               ('get_alias', 'z'),
               ('get_typecast', 'text'))
    variants = (
        'select x.y::text as z from foo',
        'select x.y::text as "z" from foo',
        'select x."y"::text as z from foo',
        'select x."y"::text as "z" from foo',
        'select "x".y::text as z from foo',
        'select "x".y::text as "z" from foo',
        'select "x"."y"::text as z from foo',
        'select "x"."y"::text as "z" from foo',
    )
    for variant in variants:
        i = _get_identifier(variant)
        assert isinstance(i, sql.Identifier)
        for func_name, result in results:
            func = getattr(i, func_name)
            assert func() == result 

def test_function_param_single_literal(self):
        t = sqlparse.parse('foo(5)')[0].tokens[0].get_parameters()
        self.assertEqual(len(t), 1)
        self.assert_(t[0].ttype is T.Number.Integer) 

def test_single_quotes_are_strings():
    p = sqlparse.parse("'foo'")[0].tokens
    assert len(p) == 1
    assert p[0].ttype is T.String.Single 

def test_issue26(self):
        # parse stand-alone comments
        p = sqlparse.parse('--hello')[0]
        self.assertEqual(len(p.tokens), 1)
        self.assert_(p.tokens[0].ttype is T.Comment.Single)
        p = sqlparse.parse('-- hello')[0]
        self.assertEqual(len(p.tokens), 1)
        self.assert_(p.tokens[0].ttype is T.Comment.Single)
        p = sqlparse.parse('--hello\n')[0]
        self.assertEqual(len(p.tokens), 1)
        self.assert_(p.tokens[0].ttype is T.Comment.Single)
        p = sqlparse.parse('--')[0]
        self.assertEqual(len(p.tokens), 1)
        self.assert_(p.tokens[0].ttype is T.Comment.Single)
        p = sqlparse.parse('--\n')[0]
        self.assertEqual(len(p.tokens), 1)
        self.assert_(p.tokens[0].ttype is T.Comment.Single) 

def test_issue34(self):
        t = sqlparse.parse("create")[0].token_first()
        self.assertEqual(t.match(T.Keyword.DDL, "create"), True)
        self.assertEqual(t.match(T.Keyword.DDL, "CREATE"), True) 

def test_scientific_numbers(num):
    p = sqlparse.parse(num)[0].tokens
    assert len(p) == 1
    assert p[0].ttype is T.Number.Float 

def test_issue40(self):
        # make sure identifier lists in subselects are grouped
        p = sqlparse.parse(('SELECT id, name FROM '
                            '(SELECT id, name FROM bar) as foo'))[0]
        self.assertEqual(len(p.tokens), 7)
        self.assertEqual(p.tokens[2].__class__, sql.IdentifierList)
        self.assertEqual(p.tokens[-1].__class__, sql.Identifier)
        self.assertEqual(p.tokens[-1].get_name(), u'foo')
        sp = p.tokens[-1].tokens[0]
        self.assertEqual(sp.tokens[3].__class__, sql.IdentifierList)
        # make sure that formatting works as expected
        self.ndiffAssertEqual(
            sqlparse.format(('SELECT id, name FROM '
                             '(SELECT id, name FROM bar)'),
                            reindent=True),
            ('SELECT id,\n'
             '       name\n'
             'FROM\n'
             '  (SELECT id,\n'
             '          name\n'
             '   FROM bar)'))
        self.ndiffAssertEqual(
            sqlparse.format(('SELECT id, name FROM '
                             '(SELECT id, name FROM bar) as foo'),
                            reindent=True),
            ('SELECT id,\n'
             '       name\n'
             'FROM\n'
             '  (SELECT id,\n'
             '          name\n'
             '   FROM bar) as foo')) 

def test_double_precision_is_builtin():
    sql = 'DOUBLE PRECISION'
    t = sqlparse.parse(sql)[0].tokens
    assert (len(t) == 1
            and t[0].ttype == sqlparse.tokens.Name.Builtin
            and t[0].value == 'DOUBLE PRECISION') 

def test_valid_identifier_names(name):  # issue175
    t = sqlparse.parse(name)[0].tokens
    assert isinstance(t[0], sqlparse.sql.Identifier) 

def test_parenthesis(self):
        s = 'select (select (x3) x2) and (y2) bar'
        parsed = sqlparse.parse(s)[0]
        self.ndiffAssertEqual(s, str(parsed))
        self.assertEqual(len(parsed.tokens), 7)
        self.assert_(isinstance(parsed.tokens[2], sql.Parenthesis))
        self.assert_(isinstance(parsed.tokens[-1], sql.Identifier))
        self.assertEqual(len(parsed.tokens[2].tokens), 5)
        self.assert_(isinstance(parsed.tokens[2].tokens[3], sql.Identifier))
        self.assert_(isinstance(parsed.tokens[2].tokens[3].tokens[0], sql.Parenthesis))
        self.assertEqual(len(parsed.tokens[2].tokens[3].tokens), 3) 

def test_quoted_identifier():
    t = sqlparse.parse('select x.y as "z" from foo')[0].tokens
    assert isinstance(t[2], sqlparse.sql.Identifier)
    assert t[2].get_name() == 'z'
    assert t[2].get_real_name() == 'y' 

def test_nested_function(self):
        t = sqlparse.parse('foo(bar(5))')[0].tokens[0].get_parameters()
        self.assertEqual(len(t), 1)
        self.assert_(type(t[0]) is sqlparse.sql.Function) 

def test_parse_join(expr):
    p = sqlparse.parse('%s foo' % expr)[0]
    assert len(p.tokens) == 3
    assert p.tokens[0].ttype is Keyword 

def test_keyword_like_identifier(self):  # see issue47
        t = sqlparse.parse('foo.key')[0].tokens
        self.assertEqual(len(t), 1)
        self.assert_(isinstance(t[0], sqlparse.sql.Identifier)) 

def test_square_brackets_notation_isnt_too_greedy(self):  # see issue153
        t = sqlparse.parse('[foo], [bar]')[0].tokens
        self.assert_(isinstance(t[0], sqlparse.sql.IdentifierList))
        self.assertEqual(len(t[0].tokens), 4)
        self.assertEqual(t[0].tokens[0].get_real_name(), '[foo]')
        self.assertEqual(t[0].tokens[-1].get_real_name(), '[bar]') 

def test_access_symbol(self):  # see issue27
        t = sqlparse.parse('select a.[foo bar] as foo')[0].tokens
        self.assert_(isinstance(t[-1], sqlparse.sql.Identifier))
        self.assertEqual(t[-1].get_name(), 'foo')
        self.assertEqual(t[-1].get_real_name(), '[foo bar]')
        self.assertEqual(t[-1].get_parent_name(), 'a') 

def test_float(self):
        t = sqlparse.parse('.5')[0].tokens
        self.assertEqual(len(t), 1)
        self.assert_(t[0].ttype is sqlparse.tokens.Number.Float)
        t = sqlparse.parse('.51')[0].tokens
        self.assertEqual(len(t), 1)
        self.assert_(t[0].ttype is sqlparse.tokens.Number.Float)
        t = sqlparse.parse('1.5')[0].tokens
        self.assertEqual(len(t), 1)
        self.assert_(t[0].ttype is sqlparse.tokens.Number.Float)
        t = sqlparse.parse('12.5')[0].tokens
        self.assertEqual(len(t), 1)
        self.assert_(t[0].ttype is sqlparse.tokens.Number.Float) 

def test_child_of(self):
        sql = '(col1, col2)'
        p = sqlparse.parse(sql)[0]
        self.assert_(p.tokens[0].tokens[1].is_child_of(p.tokens[0]))
        sql = 'select foo'
        p = sqlparse.parse(sql)[0]
        self.assert_(not p.tokens[2].is_child_of(p.tokens[0]))
        self.assert_(p.tokens[2].is_child_of(p)) 

def test_within(self):
        sql = 'foo(col1, col2)'
        p = sqlparse.parse(sql)[0]
        col1 = p.tokens[0].tokens[1].tokens[1].tokens[0]
        self.assert_(col1.within(sqlparse.sql.Function)) 

def test_newlines(self):
        sql = u'select\n*from foo;'
        p = sqlparse.parse(sql)[0]
        self.assertEqual(unicode(p), sql)
        sql = u'select\r\n*from foo'
        p = sqlparse.parse(sql)[0]
        self.assertEqual(unicode(p), sql)
        sql = u'select\r*from foo'
        p = sqlparse.parse(sql)[0]
        self.assertEqual(unicode(p), sql)
        sql = u'select\r\n*from foo\n'
        p = sqlparse.parse(sql)[0]
        self.assertEqual(unicode(p), sql) 

def test_multistatement(self):
        sql1 = 'select * from foo;'
        sql2 = 'select * from bar;'
        stmts = sqlparse.parse(sql1 + sql2)
        self.assertEqual(len(stmts), 2)
        self.assertEqual(str(stmts[0]), sql1)
        self.assertEqual(str(stmts[1]), sql2) 

def test_tokenize(self):
        sql = 'select * from foo;'
        stmts = sqlparse.parse(sql)
        self.assertEqual(len(stmts), 1)
        self.assertEqual(str(stmts[0]), sql) 

def test_comment_end_of_line(self):
        sql = ('select * from foo; -- foo\n'
               'select * from bar;')
        stmts = sqlparse.parse(sql)
        self.assertEqual(len(stmts), 2)
        self.ndiffAssertEqual(''.join(unicode(q) for q in stmts), sql)
        # make sure the comment belongs to first query
        self.ndiffAssertEqual(unicode(stmts[0]), 'select * from foo; -- foo\n')