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?

Answer 1

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"

Answer 2

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).