Highlight SQL inside Python string literals

Question

I have some Python files in which I want to highlight the SQL queries in string literals. Assume that all string literals in those files contain SQL queries.

I saved the following syntax file as pysql.vim:

if exists("b:current_syntax")
  finish
endif

" Include Python syntax
runtime! syntax/python.vim
unlet b:current_syntax

syn include @SQL syntax/sql.vim
syn region SQLEmbedded start=+'+ end=+'+ contains=@SQL
syn region SQLEmbedded start=+%+ end=+%+ contains=@SQL

let b:current_syntax = "pysql"

I added syntax for percent signs to check if it works.

Load the following python file (ignoring the fact the first line is not valid in Python):

a = %select * from mytab%
b = 'select * from mytab'

And run vim command set syntax=pysql. It works for SQL queries inside percent signs, but not work for quotes. Strangely, any words following the string literal is highlighted as SQL. For instance, select * from mytab in '1' select * from mytab '2' is highlighted.

Could you find the error in my syntax file?

I'm not sure how to fix but select * from mytab in '1' select * from mytab '2' is ignoring the 1 in '1' and the 2 as they aren't SQL matching the select ... using the ' from after the 1 and before the 2 — Steve
– Steve, Commented Apr 22, 2015 at 8:04
But with 'select * from mytab' select * from mytab 'select * from mytab', the middle SQL query (outside of string literals) is highlighted. — Chungmin Lee
– Chungmin Lee, Commented Apr 22, 2015 at 8:17

Chungmin Lee · Accepted Answer · 2015-04-22 08:50:57Z

I found a hint at https://github.com/krisajenkins/vim-java-sql/blob/master/after/syntax/java.vim. The reason why my syntax didn't work was because it interfered with sqlString group in sql.vim. I changed my syntax as follows and now it works great:

if exists("b:current_syntax")
  finish
endif

" Load Python syntax at the top level
runtime! syntax/python.vim
unlet b:current_syntax

" Load SQL syntax
syn include @SQL syntax/sql.vim

" Reference: https://github.com/krisajenkins/vim-java-sql/blob/master/after/syntax/java.vim
" Take care not to consume the double-quotes (\zs & \ze)
" Case-insensitive (no \C)
syn region SQLEmbedded start=+\z(['"]\)\zs[\s\n]*\v(ALTER|CALL|COMMENT|COMMIT|CONNECT|CREATE|DELETE|DROP|EXPLAIN|EXPORT|GRANT|IMPORT|INSERT|LOAD|LOCK|MERGE|REFRESH|RENAME|REPLACE|REVOKE|ROLLBACK|SELECT|SET|TRUNCATE|UNLOAD|UNSET|UPDATE|UPSERT)+ skip=+\\\z1+ end=+\ze\z1+ contains=@SQL containedin=pythonString

let b:current_syntax = "pysql"

ibizaman · Accepted Answer · 2016-04-20 20:30:23Z

1

Your answer did not work for me with the following version of vim:

VIM - Vi IMproved 7.4 (2013 Aug 10, compiled Dec 18 2015 21:31:31)
Included patches: 1-882
Modified by [email protected]

I modified a bit you answer.

if exists("b:current_syntax")
  finish
endif

" Load Python syntax at the top level
runtime! syntax/python.vim

" Needed to make syntax/sql.vim do something
unlet b:current_syntax

" Load SQL syntax
syntax include @SQL syntax/sql.vim

" Copied from syntax/python.vim to add the keepend
syn region pythonString matchgroup=pythonQuotes
      \ start=+[uU]\=\z(['"]\)+ end="\z1" skip="\\\\\|\\\z1"
      \ contains=pythonEscape,@Spell keepend
syn region  pythonRawString matchgroup=pythonQuotes
      \ start=+[uU]\=[rR]\z(['"]\)+ end="\z1" skip="\\\\\|\\\z1"
      \ contains=@Spell keepend

syn region SQLEmbedded contains=@SQL containedin=pythonString,pythonRawString contained
    \ start=+\v(ALTER|BEGIN|CALL|COMMENT|COMMIT|CONNECT|CREATE|DELETE|DROP|END|EXPLAIN|EXPORT|GRANT|IMPORT|INSERT|LOAD|LOCK|MERGE|REFRESH|RENAME|REPLACE|REVOKE|ROLLBACK|SELECT|SET|TRUNCATE|UNLOAD|UNSET|UPDATE|UPSERT)+
    \ end=+;+

let b:current_syntax = "pysql"

With that, highlighting starts at one of the given SQL keywords and stops either at the first ; and can restart on next SQL keyword, or stops at the end of the python string (see keepend).

edited Apr 20, 2016 at 20:30

answered Apr 20, 2016 at 20:24

ibizaman

3,4132 gold badges26 silver badges35 bronze badges

1 Comment

Tshirtman Over a year ago

This is cool, but had a tendency to match in the middle of any docstring that contain any of the sql valid keywords, and it was missing "with", so i modified the region definition start to

start=+^\w*\v(ALTER|BEGIN|CALL|COMMENT|COMMIT|CONNECT|CREATE|DELETE|DROP|END|EXPLAIN|EXPORT|GRANT|IMPORT|INSERT|LOAD|LOCK|MERGE|REFRESH|RENAME|REPLACE|REVOKE|ROLLBACK|SELECT|SET|TRUNCATE|UNLOAD|UNSET|UPDATE|UPSERT|WITH)+

So it only matches if the line starts with one of the SQL keywords.

Collectives™ on Stack Overflow

Highlight SQL inside Python string literals

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related