如何从原始SQL文件中清除注释 [英] Howto clean comments from raw sql file
本文介绍了如何从原始SQL文件中清除注释的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在从已有的sql文件中清除注释和空行时遇到问题。
该文件有1万多行,因此无法手动清理。
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.
我有一个小python脚本,但是我不知道如何处理注释
I have a little python script, but I have no idea how to handle comments inside multi line inserts.
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
这应该保持不变:
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
\'
[...]
');
推荐答案
尝试 sqlparse 模块。
更新示例:在其中保留注释在CREATE FUNCTION块中插入值和注释。您可以进一步调整以调整行为:
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)])
输出:
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'
);
这篇关于如何从原始SQL文件中清除注释的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文