getOptionsForSgdbCli -- AttributeError: 'NoneType' object has no attribute 'get'

[Demetrio92]

I cannot execute an sql statement. The connections selector seems to be working though.
Traceback:

  File "C:\Users\user\Software\Portable\Sublime_Text_3\sublime_plugin.py", line 797, in run_
    return self.run()
  File "C:\Users\user\Software\Portable\Sublime_Text_3\Data\Packages\SQLTools\SQLTools.py", line 354, in run
    ST.conn.execute(getSelection(), output)
  File "C:\Users\user\Software\Portable\Sublime_Text_3\Data\Packages\SQLTools\SQLToolsAPI\Connection.py", line 104, in execute
    for query in self.getOptionsForSgdbCli()['before']:
  File "C:\Users\user\Software\Portable\Sublime_Text_3\Data\Packages\SQLTools\SQLToolsAPI\Connection.py", line 142, in getOptionsForSgdbCli
    return self.settings.get('cli_options', {}).get(self.type)
AttributeError: 'NoneType' object has no attribute 'get'

Just a reminder: we had something similar in the issue #45.

P.s.
I am on:
e7470ee ("Bump version: 0.6.5 to 0.6.6", 2016-10-29)

My config is already listed in issue #41

[Demetrio92]

Ach, good, it is a connection issue. I was wondering why do I see DB:None, Connection: none@none (under the name of connection which by the way is displayed properly) in the selection menu. Even though it allows me to chose the database connection it writes into the console almost the same error:

  File "C:\Users\dero\Software\Portable\Sublime_Text_3\Data\Packages\SQLTools\SQLTools.py", line 213, in <lambda>
    Window().show_quick_panel(menu, lambda index: ST.setConnection(index, tablesCallback, columnsCallback, functionsCallback))
  File "C:\Users\dero\Software\Portable\Sublime_Text_3\Data\Packages\SQLTools\SQLTools.py", line 198, in setConnection
    ST.loadConnectionData(tablesCallback, columnsCallback, functionsCallback)
  File "C:\Users\dero\Software\Portable\Sublime_Text_3\Data\Packages\SQLTools\SQLTools.py", line 185, in loadConnectionData
    ST.conn.getTables(tbCallback)
  File "C:\Users\dero\Software\Portable\Sublime_Text_3\Data\Packages\SQLTools\SQLToolsAPI\Connection.py", line 58, in getTables
    query = self.getOptionsForSgdbCli()['queries']['desc']['query']
  File "C:\Users\dero\Software\Portable\Sublime_Text_3\Data\Packages\SQLTools\SQLToolsAPI\Connection.py", line 142, in getOptionsForSgdbCli
    return self.settings.get('cli_options', {}).get(self.type)
AttributeError: 'NoneType' object has no attribute 'get'

[mtxr]

@Demetrio92 does it still happening? Couldn't reproduce this error here, Mac, windows and Linux were ok.

[Demetrio92]

@mtxr well, last commit is 29th of October, this issue was created on 8th novermber.

user@my_pc MINGW64 ~/Software/Portable/Sublime_Text_3/Data/Packages/SQLTools (master)
$ git pull
Current branch master is up to date.

should I test some other branch?

[Demetrio92]

My \Sublime_Text_3\Data\Packages\User\SQLToolsConnections.sublime-settings:

{
    "connections": {
        "Connection 2": {
            "type"    : "pgsql",
            "host"    : "127.0.0.1",
            "port"    :  "5432",
            "username": "anotheruser",
            "database": "dbname",
            "encoding": "utf-8"
        },
        "my_pgsql": {
            "type"    : "pgsql",
            "host"    : "my_server",
            "port"    :  "5432",
            "database": "our_db",
            "username": "my_username",
            "password": "pass,word20",
            "encoding": "utf-8"
        }
    },
    "default": "my_pgsql"
}

to sum it up, even the basic functionality, like establishing connection and executing a query doesn't work. I am quite sure that a critical bug :)

[optio]

postgresql requires pgpass file. Your connection file uses a password. See https://github.com/mtxr/SQLTools/blob/master/SQLToolsConnections.sublime-settings

[mvaneggermond]

Just spent some time on this issue for Mac (OS X 10.11.6 El Capitain). SQLTools is reading the settings from the defaults and not from the user folder due to a path error. This is because in the SQLTools.py script the user path (USER_FOLDER) reverts to null. Installing the package Fix Mac Path resolved this issue for me: https://github.com/int3h/SublimeFixMacPath.

[Setyll]

I have the same issue with mysql.

OS : Win 10 Pro
Path to SublimeText : External SSD (portable version)
DB : MySQL on Workbench (installed on local drive C:) (I must use it at School)

My \Sublime_Text_3\Data\Packages\User\SQLToolsConnections.sublime-settings :

{
    "connections": {
        
        "Name1": {         // Connection name, used in menu (Display name)
            "database" : "dbvideo",
            "host"     : "127.0.0.1",
            "port"     : "3306",
            "type"     : "mysql",    // SGDB to use: (mysql, pgsql, oracle, vertica)
            "username" : "root",
            "password" : "password",        // (if you are using postgresql, you must setup a pgpass file and set it to null)
            "encoding" : "utf-8"        // Your DB encodings. Default: utf-8
        },

        "Name2": {         // Connection name, used in menu (Display name)
            "database" : "dbvideo",
            "host"     : "127.0.0.1",
            "port"     : "3306",
            "type"     : "mysql",    // SGDB to use: (mysql, pgsql, oracle, vertica)
            "username" : "root",
            "password" : "password",        // (if you are using postgresql, you must setup a pgpass file and set it to null)
            "encoding" : "utf-8"        // Your DB encodings. Default: utf-8
        }
         
    },
    "default": "Name1"
}

And the console result :

Traceback (most recent call last):
  File "N:\Sublime Text Build 3126 x64\Data\Packages\SQLTools\SQLTools.py", line 213, in <lambda>
    Window().show_quick_panel(menu, lambda index: ST.setConnection(index, tablesCallback, columnsCallback, functionsCallback))
  File "N:\Sublime Text Build 3126 x64\Data\Packages\SQLTools\SQLTools.py", line 198, in setConnection
    ST.loadConnectionData(tablesCallback, columnsCallback, functionsCallback)
  File "N:\Sublime Text Build 3126 x64\Data\Packages\SQLTools\SQLTools.py", line 185, in loadConnectionData
    ST.conn.getTables(tbCallback)
  File "N:\Sublime Text Build 3126 x64\Data\Packages\SQLTools\SQLToolsAPI\Connection.py", line 58, in getTables
    query = self.getOptionsForSgdbCli()['queries']['desc']['query']
  File "N:\Sublime Text Build 3126 x64\Data\Packages\SQLTools\SQLToolsAPI\Connection.py", line 142, in getOptionsForSgdbCli
    return self.settings.get('cli_options', {}).get(self.type)
AttributeError: 'NoneType' object has no attribute 'get'

[Demetrio92]

@optio

Sure thing, I totally forgot about the authentification method postgres uses, in fact, password parameter is completely immaterial since SQLTools.sublime-settings does not use it:

 "cli_options": {
        "pgsql": {
            "options": [],
            "before": [],
            "args": "-h {host} -p {port} -U {username} -d {database}",
            "queries": {
                "desc" : {
                    "query": "SELECT

Also, on my windows machine I should substitute pgsql with program_files/pgadmin-III/1.22/psql

All these things would've popped up, if SQLTools actually made it to the step "send the query into psql console". I would've fixed them myself without any external help.

As you can see from the traceback I provided it simply fails to load options, but I am a bit confused on the cause of this error.
@mvaneggermond led me to an intersting idea. I installed SQLTools on my Mac and got the same error, which I fixed with SublimeFixMacPath.
Now, from the Mac, with the same SQLToolsConnections.sublime-settings file I am able to get results for queries from our database.
My guess is that the portable version I am using on Win7 somehow misguides SQL tools and prevents it from reading some of the config files. But which one? I mean, it somehow knows how many connections I have in the SQLToolsConnections.sublime-settings .
I will probably have to add debug-prints into each of the functions to figure out where it loses track. Meanwhile, any ideas?

[Demetrio92]

A day of debugging beatufully written, modular, but completely uncommented python code and I found it.
But first, I really wanna know what is this supposed to represent?

class Settings(Storage):
    pass

?

--

Now to the error.

        self.cli = settings.get('cli')[options['type']]
        cli_path = shutil.which(self.cli)

(from Connection.py __init__)
self.cli is correctly assigned in all cases (tested), but then shutil.which(self.cli) sometimes simply does not work.
In certain cases it returns nothing, for which there is a piece of "handling code"

        if cli_path is None:
            Log((
                "'{0}' could not be found.\n\n" +
                "Please set the '{0}' path in your SQLTools settings " +
                "before continue.").format(self.cli))
            return

which

1. is does not solve the problem
2. does not show up anywhere. Neither in the sublime console, nor as a message to the user, whose experience is "select connection does nothing". The return after the Log() is the reason me, @Kaelys and @mvaneggermond saw the traceback we saw without without any clue of what might have led to this behaviour.

Don't ask me whyLog() doesn't work, this complicated feature is implemented as following:

def Log(message):
    return Logger.debug(message)

Maybe it's b/c of

class Logger:
    logging = False

?
But I deviate.

--

Why does the problem occur?
In my case, I am on a windows machine where my psql client is pg_admin, so I go to settings and write

    "cli" : {
        "mysql"   : "mysql",
        "pgsql"   : "C:/<complicated_folder_structure>/pgadmin-III/1.22/psql",
        "oracle"  : "sqlplus",

As you might guess,

>>> print(self.cli)
C:/<complicated_folder_structure>/pgadmin-III/1.22/psql

Interestingly

>>> type(shutil.which('C:/<complicated_folder_structure>/pgadmin-III/1.22/psql'))
<class 'NoneType'>
>>> type(shutil.which('C:\\<complicated_folder_structure>\\pgadmin-III\\1.22\\psql'))
<class 'NoneType'>
>>> type(shutil.which('psql'))
<class 'NoneType'>
>>> shutil.which('python')
'C:\\<complicated_folder_structure>\\python\\python.EXE'

even though from the MINGW64 console

user@PC MINGW64 /
$ which C:/<complicated_folder_structure>/pgadmin-III/1.22/psql
C:/<complicated_folder_structure>/pgadmin-III/1.22/psql

so, there is a simple bug in shutil or in the way it is being used here (my python3.4 console on the same machine returns the same result: None, but I am sure it's not the expected behavior)

--

WORKAROUND

1. Do things properly and add your psql to PATH
2. If you nonetheless wanna use manual settings from SQLToolsConnections.sublime-settings (e.g. you switch between two engines back and forth 20 times a day), change the code this way:

        self.cli = settings.get('cli')[options['type']]
        # cli_path = shutil.which(self.cli)
        cli_path = self.cli

--

A Long-Run Solution

@mtxr, @optio

import this
...
Errors should never pass silently.
Unless explicitly silenced.
...

This also applies to

        try:
            query = self.getOptionsForSgdbCli()['queries']['columns']['query']
            self.Command.createAndRun(self.builArgs('columns'), query, cb)
        except Exception:
            pass

        try:
            query = self.getOptionsForSgdbCli()['queries']['functions']['query']
            self.Command.createAndRun(self.builArgs(
                'functions'), query, cb)
        except Exception:
            pass

and many others.
Combined with an amazing sublime console, it's a pleasure to debug such code.

[mtxr]

@Demetrio92 great job!

I'll investigate it as soon as a have some time. But your comment already helped me a lot but it will take some time, ok?

Thanks!

[mtxr]

@Demetrio92 Also, I should start commenting the code 🤒 , sorry :P

[thoughtchad]

@mtxr You should probably drop something into the readme for OSX users.

@mvaneggermond nice find.

Just spent some time on this issue for Mac (OS X 10.11.6 El Capitain). SQLTools is reading the settings from the defaults and not from the user folder due to a path error. This is because in the SQLTools.py script the user path (USER_FOLDER) reverts to null. Installing the package Fix Mac Path resolved this issue for me: https://github.com/int3h/SublimeFixMacPath.

[mtxr]

@mvaneggermond could you make a pull request updating the README with your findings?
Helps a lot :D

[Demetrio92]

The most safe way is still to add cli-tools (psql, mysql, whatever) to your path.

[mvaneggermond]

Sure, but you need to add me as a collaborator I believe? Otherwise, feel free to add this to the readme ### Using SQLTools with Mac OS X Sublime Text has its PATH set from launchctl, not by your shell. Binaries installed by packages such as homebrew, for instance `Postgresql`, cannot be found by Sublime Text and results in a null pointer exception thrown by `SQLTools`. Installing the package `Fix Mac Path` resolves this issue. This package can be downloaded [here](https://github.com/int3h/SublimeFixMacPath). On 3 Mar 2017, at 9:59 AM, Matheus Teixeira <notifications@github.com<mailto:notifications@github.com>> wrote: @mvaneggermond<https://github.com/mvaneggermond> could you make a pull request updating the README with your findings? Helps a lot :D — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#51 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AHCgdhtuc7qmDP2yudvhq1UsG4sAC8oYks5rh3QHgaJpZM4Ksv0C>.

[mtxr]

All done! Thanks mate :D