Howto clean comments from raw sql file

Question

I have problem with cleaning comments and empty lines from already existing sql file. The file has over 10k lines so cleaning it manually is not an option.

I have a little python script, but I have no idea how to handle comments inside multi line inserts.

Code:

f = file( 'file.sql', 'r' )
t = filter( lambda x: not x.startswith('--') \
            and not x.isspace() 
  , f.readlines() )
f.close()
t #<- here the cleaned data should be

How it should work:

This should be cleaned:

-- normal sql comment

This should stay as it is:

CREATE FUNCTION func1(a integer) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
        -- comment
       [...]
END;
$$;

INSERT INTO public.texts (multilinetext) VALUES ('
and more lines here \'
-- part of text 
\'
[...]

');

Can you keep track of how many non-escaped quotes you've passed? An odd number means the comment is part of a string and thus should not be removed. — chrisaycock, May 03 '11 at 15:30
I'm slightly off topic, but maybe your real problem is that your SQL file is 10K lines. Or that you're not using version control. Or both. Something like our process might help you. See my answer for http://stackoverflow.com/questions/5330065/sql-where-should-the-primary-key-be-defined — Mike Sherrill 'Cat Recall', May 03 '11 at 15:59
@Catcall I have both, the file is produced from several smaller files, but for production environment deployment the comments are not needed (they take around 60% of file) — Szymon Lukaszczyk, May 03 '11 at 16:24
It might be easier to change the makefile. It's certainly easier to skip removing comments from functions and stored procedures--just don't add the "remove comments" code to the part of the make that builds the functions (or the file full of functions). — Mike Sherrill 'Cat Recall', May 03 '11 at 17:23
@Catcall but I still need the "remove comments" function, or I`m missing the meaning ? — Szymon Lukaszczyk, May 03 '11 at 17:34
If each function is in a separate file under version control, it's easy to leave the comments in. If you have a 10K line file of SQL, it's harder to strip comments from everything *except* functions. That's what I was talking about. — Mike Sherrill 'Cat Recall', May 03 '11 at 17:48
sqlparse's strip_comments is worth looking at: sqlparse.format(sql, strip_comments=True) — Tim Smith, Dec 15 '17 at 05:51

score 14 · Answer 1 · edited Sep 24 '19 at 16:34

14

Adding an updated answer :)

import sqlparse

sql_example = """--comment
SELECT * from test;
INSERT INTO test VALUES ('
-- test
a
');
 """
print sqlparse.format(sql_example, strip_comments=True).strip()

Output:

SELECT * from test;
INSERT INTO test VALUES ('
-- test
a
');

It achieves the same result but also covers all other corner cases and more concise

edited Sep 24 '19 at 16:34

Sunny Patel

7,830
2
31
46

answered Jul 31 '18 at 13:32

Hussein Negm

551
5
17

samplebias · Accepted Answer · 2011-05-03T18:21:09.217

10

Try the sqlparse module.

Updated example: leaving comments inside insert values, and comments within CREATE FUNCTION blocks. You can tweak further to tune the behavior:

import sqlparse
from sqlparse import tokens

queries = '''
CREATE FUNCTION func1(a integer) RETURNS void
    LANGUAGE plpgsql
        AS $$
        BEGIN
                -- comment
       END;
       $$;
SELECT -- comment
* FROM -- comment
TABLE foo;
-- comment
INSERT INTO foo VALUES ('a -- foo bar');
INSERT INTO foo
VALUES ('
a 
-- foo bar'
);

'''

IGNORE = set(['CREATE FUNCTION',])  # extend this

def _filter(stmt, allow=0):
    ddl = [t for t in stmt.tokens if t.ttype in (tokens.DDL, tokens.Keyword)]
    start = ' '.join(d.value for d in ddl[:2])
    if ddl and start in IGNORE:
        allow = 1
    for tok in stmt.tokens:
        if allow or not isinstance(tok, sqlparse.sql.Comment):
            yield tok

for stmt in sqlparse.split(queries):
    sql = sqlparse.parse(stmt)[0]
    print sqlparse.sql.TokenList([t for t in _filter(sql)])

Output:

CREATE FUNCTION func1(a integer) RETURNS void
    LANGUAGE plpgsql
        AS $$
        BEGIN
                -- comment
       END;
       $$;

SELECT * FROM TABLE foo;

INSERT INTO foo VALUES ('a -- foo bar');

INSERT INTO foo
VALUES ('
a
-- foo bar'
);

edited May 03 '11 at 18:21

answered May 03 '11 at 15:40

samplebias

37,113
6
107
103

1

thanks for a tip, but this is also stripping comments from inside functions, is there a way to prevent it ? – Szymon Lukaszczyk May 03 '11 at 15:53
@Szymon Lukaszczyk : i was just living the same comment for @samplebias :) , look at my answer if you want something that work with your example. – mouad May 03 '11 at 15:54
@Szymon updated to show the raw tokens, which you can filter by iterating over them. – samplebias May 03 '11 at 15:57
1

almost, still fails on the INSERT from my example – Szymon Lukaszczyk May 03 '11 at 17:58
@Szymon I updated it to handle that case. This should provide enough of a baseline which you can extend and modify. – samplebias May 03 '11 at 18:29

Joe Heffer · Answer 3 · 2018-02-19T14:08:20.523

1

# Remove comments i.e. lines beginning with whitespace and '--' (using multi-line flag)
re.sub('^\s*--.*\n?', '', query, flags=re.MULTILINE)

Regex string explained:

^ start of line
\s whitespace
\s* zero or more whitespace characters
-- two hypens (static string pattern)
.* zero or more of any characters (i.e. the rest of the line)
\n newline character
? end of string
flags = re.M is the multiline modifier

"When specified, the pattern character '^' matches at the beginning of the string and at the beginning of each line (immediately following each newline)"

See the Python regular expressions documentation for more details:

https://docs.python.org/3/library/re.html

edited Feb 19 '18 at 14:08

answered Feb 15 '18 at 10:15

Joe Heffer

755
7
9

add some explanation please – Yahya Hussein Feb 15 '18 at 10:40
It'd be nice to have this include /* */ comments too, though I'm guessing this would be tricky to accomodate with the -- ones potentially overlapping or lines starting with -- and ending with */ and you missing the closing part. – Tim Edwards Oct 23 '18 at 09:13

mouad · Answer 4 · 2011-05-03T16:13:01.890

1

This is an extend of samplebias answer that work with your example :

import sqlparse

sql_example = """--comment
SELECT * from test;
INSERT INTO test VALUES ('
-- test
a
');
"""

new_sql = []

for statement in sqlparse.parse(sql_example):
    new_tockens = [stm for stm in statement.tokens 
                   if not isinstance(stm, sqlparse.sql.Comment)]

    new_statement = sqlparse.sql.TokenList(new_tockens)
    new_sql.append(new_statement.to_unicode())

print sqlparse.format("\n".join(new_sql))

Output:

SELECT * from test;

INSERT INTO test VALUES ('
-- test
a
');

edited May 03 '11 at 16:13

answered May 03 '11 at 15:52

mouad

67,571
18
114
106

`print(sqlparse.format("\n".join(new_sql)))` returns `u'\n'` after coming the example to python – Szymon Lukaszczyk May 03 '11 at 16:00
@Szymon Lukaszczyk: i pasted the code above directly from a script that produced the same output as in my answer weird ??!! :) – mouad May 03 '11 at 16:04
got (, , ) after parse instead of proper objects. Don`t know why. – Szymon Lukaszczyk May 03 '11 at 16:06
I have installed sqlparse from debian sid repo, (ver 0.1.2-2) – Szymon Lukaszczyk May 03 '11 at 16:09
@Szymon Lukaszczyk: i have the same version in my ubuntu machine, and the output that you said that you are getting is the return value of `sqlparse.parse(sql_example)` weird !!?? – mouad May 03 '11 at 16:11

score 0 · Answer 5 · answered May 03 '11 at 16:04

It is possible to do it with regular expressions. First you have to split the file by strings and after this you can split the file by comments. The following Perl program does it:

#! /usr/bin/perl -w

# Read hole file.
my $file = join ('', <>);

# Split by strings including the strings.
my @major_parts = split (/('(?:[^'\\]++|\\.)*+')/, $file);

foreach my $part (@major_parts) {
    if ($part =~ /^'/) {
        # Print the part if it is a string.
        print $part; 
    }
    else {
        # Split by comments removing the comments
        my @minor_parts = split (/^--.*$/m, $part);
        # Print the remaining parts.
        print join ('', @minor_parts);
    }
}

he's looking to leave comments inside functions/procedures intact, filtering out the rest. — samplebias, May 03 '11 at 16:12

Howto clean comments from raw sql file

Code:

How it should work:

5 Answers5

Linked