pyodbc query response take too long

I have a function that connect database and gets sales from sql server for every book.

import website.queries as query

def book_list(request, user_id):
    user = User.objects.get(id=user_id)
    books =[::-1]

    sales = {}
    for book in books:
        if book.is_active:
            sales[book.isbn] = query.get_total_sales(book.isbn)

    return render(
        'book-list.html', {
            'user': user,
            'books': books,
            'sales': sales

and query

def get_total_sales(code):
    with connections['logo'].cursor() as cursor:

        column_names = [column[0] for column in cursor.description]
        query_result = [
            dict(zip(column_names, row)) for row in cursor.fetchall()

    total_sales = 0
    for column in query_result:
        if 'TOPLAM' in column:
            total_sales += int(query_result[column])

    return total_sales

Sometimes book_list function response, took too long

Also this is how I connect to the database in functions, is this a correct method?

You should create a model for your URUNLER_TOPLAM table, and then use the ORM to perform your query, including the total_sales calculation.

Beside that, what is your purpose for this?:

If someone can edit the isbn field for a book, then this is a SQL injection vulnerability:

(Technically, it’s a vulnerability regardless - and a bad practice in general.)

With all this:

You’re doing a lot of manual manipulations that you don’t need to do.

At worst, those calculations should be done in your query. At best, you do it using the ORM.

Books are in no particular order, so i just reversed the order of addition.

I make this application only to show the sales amount of the books, i get the sales volumes from a remote server

How can I create a suitable model for this?

Please ignore my stupidity :slightly_smiling_face:

Btw, books are stored in a database on the server, along with their trivial information.
Important information is on the remote server that I connect to

Books are linked to specific users and retrieve important information from the remote server when the user logs in

It’s a lot better to use the order_by query clause. You can order_by the id field if you want to sort it by insertion order. See order_by and ordering.

You can use the inspectdb command as a starting point. (You’ll still need to read/evaluate what it produces to validate it.)
See How to integrate Django with a legacy database | Django documentation | Django and inspectdb.

Lack of knowledge != “stupidity”

And if you’re here to learn, we’re here to help reduce that “lack of knowledge”.

But, information from the remote server is constantly changing and updating (not by this application)

Also, remote database is used by a large application on windows server and and that’s a place where i don’t want to interfere.

I don’t see how it’s right to create a model, since just want to get certain information. (such as total sales or last year sales…)

Are you saying that the table schema is being modified constantly?

You’re not. A Django model is a Python representation of a table. Creating such an object has no effect on the database.

That’s precisely a reason to do it.

I’ve posted a number of times here describing where I’ve got projects that retrieve data from legacy databases owned by other projects. It’s a very routine operation.

Everything from online, wholesale or retail book sales is done through the program on the remote server. I get the information of these sales in my application.

Yep. So that’s an excellent reason to create a model for those tables. Using the ORM for your queries will help facilitate that - far more than generating raw SQL and post-processing resultsets yourself.

Honestly, I’ve never tried anything like this before (a model that holds data from a remote server). I need to do some research

Start with the documentation links I provided earlier.

Side note: It may be a language or translation issue, but your phrasing:

possibly indicates an incomplete understanding of what the ORM is and what Models do.

A Model is a Python object - that’s all. It’s a container for a set of attributes that map to the columns of a database table. (It also allows for the definition of some related behavior that may be created or assigned to it)

The ORM issues queries to a database, gets the responses, and then creates instances of the Model based upon the data returned from the database by the query. Each row returned creates a new instance of the model. The data for each column of the result set is assigned to the corresponding field for that instance of the model.

If you use it as a “query-only” (no updates) object, that’s all it does. There’s no “magic” here. There’s nothing going on that is going to make this appear to the database as anything different from any other query being issued.

As an example, you have the query:

This is going to cause the ORM to execute something like this SQL statement:
select * from auth_user where id = user_id
It’s then going to create an instance of the User object named user, and use the output of that query to populate the fields in user. That is really all that is happening here.

As i understand it, following this page may lead me to the right path.

I don’t want to use inspectdb for remote server,

Book information on the remote server contains a lot of information that is not necessary for my application

The URUNLER_TOPLAM table I used at the very beginning of the topic is a view operation set on Microsoft SQL Server

Then remove the fields you don’t need. Don’t inspect models you don’t need.

You can create models for views.

I’m sorry, but I’m not understanding your reluctance to use the “Django way” of handling this. If you’re not going to use Django’s facilities, then why are you using Django?

The very first note of that page that you’re referencing:

Explore the ORM before using raw SQL!

So take its advice and use the ORM.

Because there is a more complex query under URUNLER_TOPLAM on the remote server and other queries inside my application are also complex

Where I need Django is to present the results of these queries to users through a website

Ok, so going back to your original question, you were looking for a way to improve the performance of your queries.

I have given you a direction to take to do so. Whether you choose to use this information is up to you. But if you choose not to use it, I’m not sure how I can help you.

Thank you very much,

This problem has taught me that I still have a lot to learn