Allow sqlfluff to be installed in a conda (or other) virtual environment #23

GClunies · 2021-03-12T19:45:26Z

Issue:
As alluded to by @pwildenhain in this slack thread, it seems this extension only works if sqlfluff is installed globally. It does not seem to recognize sqlfluff when it is installed in a virtual environment.

I have sqlfluff installed in a conda virtual environment and I don’t get ANY feedback in VScode that the sqlfluff extension is working even though the extension is installed and enabled. I see no linting hints like in the demo shown in this repo. I am able to run sqlfluff from the terminal so I do know it is installed and works.

▶ sqlfluff lint models/analytics/editorial/sl_web_editorial_time_on_page.sql                                                                                           
== [models/analytics/editorial/sl_web_editorial_time_on_page.sql] FAIL
L:  35 | P:   7 | L003 | Indentation not hanging or a multiple of 4 spaces
L:  37 | P:   5 | L003 | Indent expected and not found compared to line #34
L:  39 | P:   7 | L003 | Indentation not hanging or a multiple of 4 spaces
L:  39 | P: 113 | L016 | Line is too long

I have python, dbt, and sqlfluff installed via a conda virtual environment (and virtual env is activated in VScode). The conda env is defined as:

name: surfline-dbt-dev
channels:
  - conda-forge
  - defaults
dependencies:
  - pip=20.1.1
  - python=3.7.3
  - pip:
    - dbt==0.19.0
    - sqlfluff[dbt]

Path to sqlfluff is

▶ which sqlfluff                                                                                                                                                    
/opt/anaconda3/envs/surfline-dbt-dev/bin/sqlfluff

I have the path to sqlfluff specified in my VScode settings.json like:

    "sql.linter.executablePath": "/opt/anaconda3/envs/surfline-dbt-dev/bin/sqlfluff",
    "sql.linter.run": "onType",

Expected Behavior:
sqlfluff extension should be able to use sqlfluff from the conda environment, or any other virtual environment that is active in VScode.

Possible Solutions:
While I am not familiar with the code structure for VScode extensions, I do know that the dbt Power user VSCode extension is able to recognize dbt when it is installed in a conda environment. Maybe a similar approach could be used here? It looks like the important files for recognizing the virtual environment are listed here: https://github.com/innoverio/vscode-dbt-power-user/search?q=environment

The text was updated successfully, but these errors were encountered:

RoyalTS · 2021-05-17T09:47:58Z

Running up against the same issue. Any ongoing work on this?

b-per · 2021-06-23T03:47:09Z

The latest version (0.0.5) fixed the definition of the executable in "sql.linter.executablePath". It doesn't recognise the venv directly but the extension works when the path has been added manually.

GClunies · 2021-06-23T18:43:11Z

@b-per, what do you mean by "when the path has been added manually"?

Are you referring to...

because this still doesn't work for me. Would be very interested to know your venv setup and VScode settings.

GClunies · 2021-06-23T19:51:35Z

So it seems this is working for me with this configuration.

vscode-sqlfluff extension settings:

Not sure why onSave works but onType doesn't?

VScode settings found in .vscode/setting.json located in my dbt project repo, they override any settings in my global vscode settings.json.

{
    "python.defaultInterpreterPath": "/opt/anaconda3/envs/surfline-dbt/bin/python",
}

Hope this helps others. Thank you @dorzey and others for updates to the plugin 🙏🏽 .

dorzey · 2021-06-23T20:25:28Z

@GClunies It would be great if you could raise a PR with the anaconda setup so that I can link it from the README. I'm unfamiliar with anaconda myself.

GClunies · 2021-06-23T20:41:11Z

@dorzey I am still playing around with this a bit still, so excuse the "stream of consciousness" comments. But in general it seems like recent fixes are headed in the right direction!

My conda + dbt + sqlfluff + vscode + vscode-sqlfluff + dbt-power-user setup is as follows:

My conda env (named surfline-dbt) is defined as:

name: surfline-dbt
channels:
  - conda-forge
  - defaults
dependencies:
  - agate=1.6.1
  - pip=20.1.1
  - python=3.8
  - pip:
    - dbt==0.19.1
    - sqlfluff==0.5.3  # I plan to update this

Nothing special about dbt except that I source it from the surfline-dbt conda env defined above.

My .sqlfluff is as follows. I use templater = dbt.

.sqlfluff config file (click dropdown to view)

# For SQLFluff Rules reference, see:
# https://docs.sqlfluff.com/en/stable/rules.html#rules-reference
[sqlfluff]
verbose = 0
nocolor = False
dialect = postgres
templater = dbt
rules = None
exclude_rules = L032,L033,L034,L037,L044
recurse = 0
output_line_length = 80
runaway_limit = 10
ignore = parsing,templating
ignore_templated_areas = True

[sqlfluff:indentation]
indented_joins = False
template_blocks_indent = True

# Some rules can be configured directly from the config common to other rules.
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 120
indent_unit = space
comma_style = trailing
allow_scalar = True
single_table_references = consistent
only_aliases = True

# Some rules have their own specific config.
# All SQLFluff rules can be found at: https://docs.sqlfluff.com/en/stable/rules.html#rules-reference
# When a rule in not listed below, we inherit the default behavior from above.
[sqlfluff:rules:L003]
lint_templated_tokens = False

[sqlfluff:rules:L010]  # Keywords
capitalisation_policy = lower

[sqlfluff:rules:L014]  # Unquoted identifiers
# Note that sqlfluff uses the 'postgres' dialect for Redshift.
# The postgres keywords used to check against 'unquoted identifiers' can be at:
#    https://www.postgresql.org/docs/8.1/sql-keywords-appendix.html
extended_capitalisation_policy = lower
unquoted_identifiers_policy = all

[sqlfluff:rules:L016]
# Setting to True allows us to copy/paste long URLs as comments
ignore_comment_lines = True

[sqlfluff:rules:L030]  # Function names
capitalisation_policy = lower

[sqlfluff:rules:L038]
select_clause_trailing_comma = forbid

[sqlfluff:rules:L040]  # Null & Boolean Literals
capitalisation_policy = lower

[sqlfluff:rules:L042]
# By default, allow subqueries in from clauses, but not join clauses.
forbid_subquery_in = join

In my GLOBAL VScode settings, I have some configs for dbt, SQL, Jinja SQL, vscode-sqlfluff extension, and dbt-power-user extension:

{
    "dbt.listModelsDisabled": true,

    "files.associations": {
        "*.sql": "jinja-sql",
        "*.rmd": "markdown"
    },

    "[sql]": {
        "breadcrumbs.showArrays": true,
        "editor.insertSpaces": true,
        "editor.tabSize": 4
    },

    "[jinja-sql]": {
        "breadcrumbs.showArrays": true,
        "editor.insertSpaces": true,
        "editor.tabSize": 4,
    },

    "sql.linter.executablePath": "/opt/anaconda3/envs/surfline-dbt/bin/sqlfluff",
    
}

In my repo containing my dbt project, I have the following .vscode/settings.json. This just makes sure I always use the surfline-dbt conda env when working in this repo so that dbt-power-user always finds dbt.

{
    "python.defaultInterpreterPath": "/opt/anaconda3/envs/surfline-dbt/bin/python",
}

I think that's the whole setup right now.

I am still testing functionality still. It's a bit slow at returning linting errors when I save a file, but I suspect thats from the dbt-compiler having to run due to templater = dbt in my .sqlfluff. Not sure.

dorzey · 2021-06-30T14:49:52Z

Thanks for this. I will try and find time to replicate your setup.

SpikyClip · 2021-10-01T04:41:56Z

I have the same issue where lint warnings are not showing up in the problems pane, but the formatting on save works??? I've tried clean installing, removing all my sqlfluff user settings, toggling them on/off. Here are my sql related settings:

    "[sql]": {
        "editor.defaultFormatter": "dorzey.vscode-sqlfluff"
    },
    "sql.linter.executablePath": "C:\\Users\\Vikesh\\miniconda3\\Scripts\\sqlfluff.exe",
    "files.associations": {
        "*.sql": "sql"
    },

Whats strange is the linting was working yesterday, and I had not changed any settings. So I have no clue why its not working today. I have the sqltools extension too but uninstalling that makes no difference either.

I'm on vscode-sqlfluff v0.0.5 and sqlfluff 0.6.6.

dbrtly-ofx · 2021-11-23T11:53:20Z

Somehow, I eventually had this working on a previous machine. New machine and I can't get the vscode extension to work.
I can successfully lint files from the cli.

in the virtual environment:

(env) % which sqlfluff
/Users/danielbartley/my_project/env/bin/
(env) % sqlfluff --version
sqlfluff, version 0.8.2
(env) % cd '/Users/danielbartley/my_project/env/bin/'
(env) % ls | grep sqlfluff
sqlfluff

.vscode/settings.json
{
"sql.linter.executablePath": "/Users/danielbartley/my_project/env/bin/",
"sql.linter.run": "onType"
}

right click on a file, select 'format document' and I get:
"spawn "/Users/danielbartley/my_project/env/bin/" EACCES"
I don't know what that means.

I wrote a note to myself last time that alternating between onType and onSave was enough to get it unstuck. Doesnt work this time.

I would really appreciate more detailed docs for the setup. Willing to help.

lorcan17 · 2023-01-04T19:59:03Z

I'm having a similar issue with using vscode-sqlfluff in an conda virtual environment. The below error message shows up:

Error Message

sqlfluff fix sql_code.sql --dialect snowflake works using command line so it's installed correctly.

I've added the below to .vscode/settings.json within project:
{ "python.defaultInterpreterPath": "c:/Users/user/Anaconda3/envs/sql_development/python", "sqlfluff.executablePath": "c:/Users/user/Anaconda3/envs/sql_development/Scripts/sqlfluff" }

And I've also added the below to user settings:
"sqlfluff.executablePath": "c:/Users/user/Anaconda3/envs/sql_development/Scripts/sqlfluff"
"sqlfluff.linter.run": "OnSave"

Conda Environment

channels:
  - conda-forge
  - defaults
dependencies:
  - appdirs=1.4.4
  - attrs=22.2.0
  - backports.cached-property=1.0.2
  - bzip2=1.0.8
  - ca-certificates=2022.12.7
  - certifi=2022.12.7
  - chardet=5.1.0
  - click=8.1.3
  - colorama=0.4.6
  - diff-cover=7.3.0
  - exceptiongroup=1.1.0
  - importlib-metadata=6.0.0
  - importlib_metadata=6.0.0
  - iniconfig=1.1.1
  - jinja2=3.1.2
  - libffi=3.4.2
  - markupsafe=2.1.1
  - openssl=1.1.1s
  - packaging=22.0
  - pathspec=0.10.3
  - pip=22.3.1
  - pluggy=1.0.0
  - pygments=2.14.0
  - pytest=7.2.0
  - python=3.10.8
  - python_abi=3.10
  - pyyaml=6.0
  - regex=2022.7.9
  - setuptools=65.5.0
  - sqlfluff=1.4.5
  - sqlite=3.40.0
  - tblib=1.7.0
  - tk=8.6.12
  - toml=0.10.2
  - tomli=2.0.1
  - tqdm=4.64.1
  - typing=3.10.0.0
  - typing_extensions=4.4.0
  - tzdata=2022g
  - vc=14.2
  - vs2015_runtime=14.27.29016
  - wheel=0.37.1
  - wincertstore=0.2
  - xz=5.2.8
  - yaml=0.2.5
  - zipp=3.11.0
  - zlib=1.2.13

GClunies changed the title ~~Allow sqlfluff to be installed in a virtualenv~~ Allow sqlfluff to be installed in a conda (or other) virtual environment Mar 12, 2021

jice-lavocat mentioned this issue Aug 24, 2022

Cannot find executable - Windows 10 #36

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Allow sqlfluff to be installed in a conda (or other) virtual environment #23

Allow sqlfluff to be installed in a conda (or other) virtual environment #23

GClunies commented Mar 12, 2021 •

edited

Loading

RoyalTS commented May 17, 2021

b-per commented Jun 23, 2021

GClunies commented Jun 23, 2021 •

edited

Loading

GClunies commented Jun 23, 2021 •

edited

Loading

dorzey commented Jun 23, 2021

GClunies commented Jun 23, 2021 •

edited

Loading

dorzey commented Jun 30, 2021

SpikyClip commented Oct 1, 2021

dbrtly-ofx commented Nov 23, 2021

lorcan17 commented Jan 4, 2023 •

edited

Loading

Allow sqlfluff to be installed in a conda (or other) virtual environment #23

Allow sqlfluff to be installed in a conda (or other) virtual environment #23

Comments

GClunies commented Mar 12, 2021 • edited Loading

RoyalTS commented May 17, 2021

b-per commented Jun 23, 2021

GClunies commented Jun 23, 2021 • edited Loading

GClunies commented Jun 23, 2021 • edited Loading

dorzey commented Jun 23, 2021

GClunies commented Jun 23, 2021 • edited Loading

dorzey commented Jun 30, 2021

SpikyClip commented Oct 1, 2021

dbrtly-ofx commented Nov 23, 2021

lorcan17 commented Jan 4, 2023 • edited Loading

GClunies commented Mar 12, 2021 •

edited

Loading

GClunies commented Jun 23, 2021 •

edited

Loading

GClunies commented Jun 23, 2021 •

edited

Loading

GClunies commented Jun 23, 2021 •

edited

Loading

lorcan17 commented Jan 4, 2023 •

edited

Loading