Query sql in views.py

Using Django Using the ORM
1

I have the following database structure in settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': 'mydatabase',
    },
    'users_db': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'users',
        'USER': '',
        'PASSWORD': '',
        'HOST': '',
        'PORT': '3306' 
    },
    'intranet_db': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'intranet',
        'USER': '',
        'PASSWORD': '',
        'HOST': '',
        'PORT': '3306' 
    },
     'nav_db': {
        'ENGINE': 'django.db.backends.postgresql',
        'OPTIONS': {
            'service': 'my_service',
            'passfile': '.my_pgpass',
        },
    }
}

I simply want the last database called nav_db to query data.

How could I indicate the table / field to the query?

What I think is correct is

query.objects.using('nav_db').all()

But I don’t know how to tell it to make a query as such sql…

select name from customers

Thank you.

2

Note that in:

That query at the beginning is the name of the Model on which you’re writing your query.

The same way that you would write any other query in Django. The only change here is to add the using method to the queryset.

3

I don’t understand your phrase very well, I don’t have a model for this database since it will only be for queries.
So how can I?

This is not correct because I don’t have a model called query, right?

query.objects.using('nav_db').all()

4

You can still create models to allow you to write queries using the ORM. (That’s something we do frequently here.)

Otherwise, see Performing raw SQL queries | Django documentation | Django

5

Hello,

After reading I have understood the following, I can do the query as follows, and using a specific connection (sql)

def NoConformidad(request):
    with connections['nav_db'].cursor() as cursor:
        cursor.execute("SELECT Name FROM Customer")
        row = cursor.fetchone()
    return row

But I get the following error:

image
image1040×404 45 KB

does this have to do with my sql server where i query?

6

Possibly? I don’t recognize that port number (7048).

The error is being thrown in the psycopg2 module, so it’s one of the databases you’ve got defined as a PostgreSQL server. The full traceback would help identify which line caused the error.

Note: Please don’t paste an image of a traceback. Copy/paste the traceback from the console log.

7

Sorry, I think the problem already starts with the wrong connection.

Can you confirm that the connection to a sql database is correct?

Now I have this configuration

'nav_db': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'locahost',
        'PORT': '',
        'NAME': 'PRUEBA',
        'USER': 'test',
        'PASSWORD': ''        
    },

The truth is that I have seen several options and none of them suit me…

In the ENGINE section I am not very clear what to put.

8

There’s no one-size-fits-all answer here.

I can’t tell you what you should put without knowing what database you’re trying to connect to, what server it’s running on, and what type of database it is.

Review the docs at Settings | Django documentation | Django and Databases | Django documentation | Django

9

Do not really understand.
The real server is windows server 2012 although I can test with windows 10 and the database is sql

From what it puts in the documentation, it would not be any of these

The database backend to use. The built-in database backends are:

  • 'django.db.backends.postgresql'
  • 'django.db.backends.mysql'
  • 'django.db.backends.sqlite3'
  • 'django.db.backends.oracle'
10

Are you saying that the database is Microsoft’s SQL server?

11

Yes correct, it is from microsoft sql

12

See the docs at Using a 3rd party database backend

You will need to use mssql-django · PyPI

Side note, in the future, you will want to refer to your database as “SQL Server” or “MS SQL Server”, not just as “sql”. The acronym “sql” is a generic term that applies to a number of different things.

13

Ok, now I think that it is already correctly connected to SQL Server but I don’t know how to check it since the query tells me the following.

Internal Server Error: /noConformidad/
Traceback (most recent call last):
  File "C:\Users\apenaranda\Desktop\Python_Intranet\env\Lib\site-packages\django\core\handlers\exception.py", line 55, in inner
    response = get_response(request)
               ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\apenaranda\Desktop\Python_Intranet\env\Lib\site-packages\django\core\handlers\base.py", line 197, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\apenaranda\Desktop\Python_Intranet\reclamaciones\views.py", line 151, in NoConformidad
    cursor.execute("SELECT Golmar$Customer.Name FROM Golmar$Customer WHERE Golmar$Customer.Name  = 'DISTRIBUIDORES-7' ", [self.Name])
                                                                                                                          ^^^^^^^^^
AttributeError: 'WSGIRequest' object has no attribute 'Name'

Query in views.py

def NoConformidad(self):
    with connections['nav_db'].cursor() as cursor:
        cursor.execute("SELECT Name FROM Golmar$Customer WHERE Name  = DISTRIBUIDORES-7", [self.Name])
        row = cursor.fetchone()
    return row
14

Well I already managed to get the information with

with connections['nav_db'].cursor() as cursor:
        cursor.execute("SELECT * FROM Golmar$Customer WHERE Name  = 'DISTRIBUIDORES-7';") # Here

        for row in cursor.fetchall(): # Here
            print(row)

        print(cursor.fetchall()) # []

    return HttpResponse("NoConformidad")

What if I want to show it on the page and not on the console?

15

I already got it. Many thanks for everything.