configuring read only connection still uses default connection

[pablobuenaposada]

Hi, I am using sql-explorer in a project, at first decided to use the default django connection and everything worked as expected, later on I decided to create a new database user with read only permissions for sql-explorer as your documentation suggests. Unfortunately, after this change each of the queries I had in there was throwing me a 500 error, in detail, it was throwing:

InvalidExplorerConnectionException: Attempted to access connection default, but that is not a registered Explorer connection.

After getting really mad and check the code I found what was the problem, the connection used for every query is somehow stored in the Query django model you guys have (which I don't know why) which of course it was still pointing to the default connection and not the new one, had to manually update all the connection fields of this Query model to point to the new connection like this:

Query.objects.all().update(connection="sql_explorer")

after this everything is working fine again with the new connection, not sure why you guys want to store the connection but I think we are missing a mechanism to update those if you change EXPLORER_CONNECTIONS in settings.py at least.

[marksweb]

@chrisclark I think this might need your input as I suspect this is something you'd decided on.

[lawson89]

@marksweb @pablobuenaposada you are correct the connection is stored with the query. Maybe this was the start of being able to support queries to multiple backend connections?

[RahulSwami01]

@pablobuenaposada Do you know the file/folder name to update the settings? I am also facing the same issue.

[lawson89]

@RahulSwami01 it's actually in the explorer_query table- look at the connection column

[RahulSwami01]

Getting following error

InvalidExplorerConnectionException at /explorer/1/

Attempted to access connection other, but that is not a registered Explorer connection.

Request Method:	GET
http://127.0.0.1:8000/explorer/1/
4.1.7
InvalidExplorerConnectionException
Attempted to access connection other, but that is not a registered Explorer connection.
C:\Users\RASWAMI\AppData\Local\Programs\Python\Python310\lib\site-packages\explorer\utils.py, line 181, in get_valid_connection
explorer.views.query.QueryView
C:\Users\RASWAMI\AppData\Local\Programs\Python\Python310\python.exe
3.10.5
['C:\name\py\oneclick', 'C:\Users\RASWAMI\AppData\Local\Programs\Python\Python310\python310.zip', 'C:\Users\RASWAMI\AppData\Local\Programs\Python\Python310\DLLs', 'C:\Users\RASWAMI\AppData\Local\Programs\Python\Python310\lib', 'C:\Users\RASWAMI\AppData\Local\Programs\Python\Python310', 'C:\Users\RASWAMI\AppData\Local\Programs\Python\Python310\lib\site-packages', 'C:\Users\RASWAMI\AppData\Local\Programs\Python\Python310\lib\site-packages\win32', 'C:\Users\RASWAMI\AppData\Local\Programs\Python\Python310\lib\site-packages\win32\lib', 'C:\Users\RASWAMI\AppData\Local\Programs\Python\Python310\lib\site-packages\Pythonwin']
Wed, 15 Feb 2023 19:06:58 +0000

[lawson89]

@RahulSwami01 Did you also change EXPLORER_DEFAULT_CONNECTION in settings.py?

[RahulSwami01]

DATABASES = { 'other': { 'ENGINE': 'django.db.backends.mysql', 'HOST': 'somehostname', 'PORT': '1433', 'NAME': 'DBName', 'USER': 'Username', 'PASSWORD': 'Password'

	    },
                    'default': {
                    'ENGINE': 'django.db.backends.sqlite3',
                    'NAME': BASE_DIR / 'db.sqlite3'
                },
	}

[lawson89]

Yeah I think you should find this setting (which is probably set to default and change it to other) EXPLORER_DEFAULT_CONNECTION='other'

[RahulSwami01]

I have the following connection strings: and i am trying to connect to the new database "other" mentioned above. it has only read access.

EXPLORER_CONNECTIONS = { 'Default': 'default'} EXPLORER_DEFAULT_CONNECTION = 'default'

[RahulSwami01]

I made it, it shows me loading......loading...... and not showing anything.

EXPLORER_CONNECTIONS = { 'Default': 'default', 'other':'other'} EXPLORER_DEFAULT_CONNECTION = 'other'

[RahulSwami01]

The following query I am using to test it: Select DATE('now') AS 'CurrentDATETime'

When I am running in Microsoft SQL server management studio, it gives me the output. but here i just got following error. (2013, "Lost connection to server at 'handshake: reading initial communication packet', system error: 0")

[lawson89]

Ok and you updated all the queries in the explorer_query table, connection column to point to 'other'? I'd also make sure that you can connect using another tool to the mysql db using the credentials in your settings.py One more thing - I'd restart the django test server as well (you probably already did this)

if this still doesn't work let me know and I'll test a similar setup this evening after work

[lawson89]

Oh - looks like you are using mysql django backend ('django.db.backends.mysql') but connecting to a microsoft sql server instance?

[RahulSwami01]

do you think that is the issue?

[lawson89]

I haven't ever used Django with MS SQL but yeah it requires a 3rd party lib to be installed and configured

https://docs.djangoproject.com/en/4.1/ref/databases/#third-party-notes

[RahulSwami01]

Thank you very much for the link. Unfortunately, it's not working for me.

I have an Idea, Let me know if it is possible.

I have a code that works on the package pyodbc(for MS SQL) let's say "fileA.py".

Can we integrate this code with SQL explorer?

user needs to pass the query from the front hand to fileA.py.

the query will run using the code "FileA.py"

results have been saved in a python data frame, and that data frame result has been sent back to the front-hand user.

[lawson89]

@RahulSwami01 I think your best bet is to figure out how to get your Django app connected up to MS SQL server if that's the db you need to use. Then everything else will just work.

[RahulSwami01]

It’s been 4 days, I found out that it only works with the old version (Django). For sql explorer we need new version (Django). So it’s deadlock for me.

On Fri, Feb 17, 2023 at 8:43 AM Richard Lawson @.***> wrote:

@RahulSwami01 https://github.com/RahulSwami01 I think your best bet is to figure out how to get your Django app connected up to MS SQL server if that's the db you need to use. Then everything else will just work.

— Reply to this email directly, view it on GitHub https://github.com/groveco/django-sql-explorer/issues/453#issuecomment-1434668667, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB4UOWZKL73CHHZAPZL34DTWX56AHANCNFSM5BREXZHA . You are receiving this because you were mentioned.Message ID: @.***>

[lawson89]

@RahulSwami01 What version do you need to use? Looking at the tox seyup - looks like sql explorer is tested on 3.2 and above. It may work on earlier versions (@marksweb ?)

[marksweb]

Yeah version 3 dropped support for <3.2 of django.