vim: higlight SQL inside python triple-quote string

Question

I'm trying to highlight SQL queries inside python triple-quote strings in Vim. Using this question I have a pysql.vim file containing:

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

syntax region sqlSnippet start=/\zs\v(SELECT|FROM|AND|WHERE|OR|ON|GROUP BY|ORDER BY)/ end=/\ze'''/ contains=@SQL containedin=pythonString

let b:current_syntax = 'pysql'

However when I run set syntax=pysql in the following foo.py file

def get_first_events_after_install(application_id, os, event_id,
                                   year_start, month_start, day_start,
                                   year_end, month_end, day_end,
                                   perform=False):

    query = \
        '''SELECT event_id, counter, name FROM
        (
        -- This selects event_ids and counts number of occurrences for a
        -- specific in a specific time frame
        SELECT event_id, COUNT(1) as counter FROM fault.all_events_monthly
        WHERE month_partition
            BETWEEN '201511' AND '201601'
            AND app_partition IN (434)
            AND ref_type_partition IN ('apple_ifa')
        GROUP by event_id
        ) t_counter
    INNER JOIN
        (
            --  This selects events names and along event_id
            SELECT id, name from mysql.ruby.events
        ) t_name
    ON t_counter.event_id = t_name.id
    ORDER BY counter DESC'''.\
        format(month_part=month_partitions, os_part=os_partitions,
               ys=year_start, ms=month_start, ds=day_start,
               ye=year_end, me=month_end, de=day_end, app_id=application_id,
               ev_id=event_id)

The code before and after the string query is also highlighted as SQl (you can see this in the image below). So my question is: how do I higlight SQL code only inside a triple (or triple-double) quote string? Thanks in advance.

Your chances to get a answer might be better at Vi and Vim Stack Exchange. — DreyFax
– DreyFax, Commented Mar 8, 2016 at 13:59

Martin Tournoij · Accepted Answer · 2017-01-02 00:45:06Z

2

You'll need to modify and redefine the pythonString syntax. From /usr/share/vim/vim74/syntax/python.vim:

" Triple-quoted strings can contain doctests.
syn region  pythonString
    \ start=+[uU]\=\z(['"]\)+ end="\z1" skip="\\\\\|\\\z1"
    \ contains=pythonEscape,@Spell
syn region  pythonString
    \ start=+[uU]\=\z('''\|"""\)+ end="\z1" keepend
    \ contains=pythonEscape,pythonSpaceError,pythonDoctest,@Spell
syn region  pythonRawString
    \ start=+[uU]\=[rR]\z(['"]\)+ end="\z1" skip="\\\\\|\\\z1"
    \ contains=@Spell
syn region  pythonRawString
    \ start=+[uU]\=[rR]\z('''\|"""\)+ end="\z1" keepend
    \ contains=pythonSpaceError,pythonDoctest,@Spell

With syn clear pythonString you can clear the existing syntax highlighting for it, and then we can redefine with with @SQL added in the contains. Putting this in ~/.vim/after/syntax/python.vim seems to work well:

" SQL syntax file won't load if this is set
unlet b:current_syntax

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

" We don't need these (a string inside a string!)
syn cluster SQL remove=sqlString,sqlComment

" Clear existing syntax
syntax clear pythonString

" Triple-quoted strings can contain SQL. This is the same as the original
" except with @SQL added in contains=
syn region  pythonString
    \ start=+[uU]\=\z(['"]\)+ end="\z1" skip="\\\\\|\\\z1"
    \ contains=pythonEscape,@Spell
syn region  pythonString
    \ start=+[uU]\=\z('''\|"""\)+ end="\z1" keepend
    \ contains=pythonEscape,pythonSpaceError,pythonDoctest,@Spell,@SQL
syn region  pythonRawString
    \ start=+[uU]\=[rR]\z(['"]\)+ end="\z1" skip="\\\\\|\\\z1"
    \ contains=@Spell
syn region  pythonRawString
    \ start=+[uU]\=[rR]\z('''\|"""\)+ end="\z1" keepend
    \ contains=pythonSpaceError,pythonDoctest,@Spell,@SQL

" Re-set current syntax
let b:current_syntax = 'python'

edited Jan 2, 2017 at 0:45

answered Mar 9, 2016 at 4:09

Martin Tournoij

28k24 gold badges110 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

petobens Over a year ago

Thank you! How can I add some keywords that are not highlighted by the standard sql.vim syntax file?. I tried syn keyword sqlKeyword INNER RIGHT LEFT OUTER JOIN just before let b:current_syntax = 'pysql' but it doesn't work.

petobens Over a year ago

Placing it above the syntax clear pythonString line seems to work.

Martin Tournoij Over a year ago

@petobens You can set the SQL flavour. See :help ft_sql.txt and the syntax/sql.vim file. The default is rather basic but it supports much more.

Collectives™ on Stack Overflow

vim: higlight SQL inside python triple-quote string

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related