Using SQL join query with pyodbc mssql django

Question

I am trying to perform some join statements but they are not working.

All the examples I see are with php admin. I am using SQL Server. I don't want do do the joins using the the django models, instead I want to use queries to perform the join and render it to my html page.

Please can someone tell me how to do this?

Here is my code:

models.py

from django.db import models


class Company(models.Model):
    name = models.CharField(max_length=100)
    contact_name = models.CharField(max_length=100)
    address = models.TextField(max_length=255)
    ph_no = models.CharField(max_length=17)
    tele = models.CharField(max_length=17)
    mail = models.EmailField(max_length=150)
    is_active = models.BinaryField(default=0)


class Client(models.Model):
    company_id = models.IntegerField()
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    ph_no = models.CharField(max_length=17)
    tele = models.CharField(max_length=17)
    mail = models.CharField(max_length=100)
    is_active = models.BinaryField(default=1)```



views.py


from django.shortcuts import render
from .models import Company, Client
import pyodbc
import datetime
import pytz

con = pyodbc.connect('Driver={SQL server};'
                     'Server=;'
                     'Database=Shipping;'
                     'Trusted_Connection=True;')
cursor = con.cursor()
con.autocommit = False

sql_join_client = '''
select Client.Client_id,Company.name,Client.first_name,Client.last_name,
Client.ph_no,Client.tele,Client.mail,Client.IsActive,Client.last_update
from Client
join Company on Client.company_id=Company.company_id
'''
#some names in model differ from the actual names in the db tables
def showclient(request):
    cursor.execute(sql_join_client)  # join
    result = cursor.fetchall()
    return render(request, 'client.html', {'Client': result})

client.html

{% extends 'base.html' %}
{% block table_name %}Client{% endblock %}
{% block table_view %}
<thead>
    <tr>
        <th>Client Id</th>
        <th>Company</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Ph. No.</th>
        <th>Telephone</th>
        <th>E-mail</th>
        <th>Is Active</th>
        <th>Last Updated</th>
        <th>Actions</th>
    </tr>
</thead>
<tfoot>
    <tr>
        <th>Client Id</th>
        <th>Company</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Ph. No.</th>
        <th>Telephone</th>
        <th>E-mail</th>
        <th>Is Active</th>
        <th>Last Updated</th>
        <th>Actions</th>
    </tr>
</tfoot>
<tbody>
{% for datadisplay in Client%}
    <tr>
        <td>{{datadisplay.client_id}}</td>
        <td>{{datadisplay.company_name}}</td>
        <td>{{datadisplay.first_name}}</td>
        <td>{{datadisplay.last_name}}</td>
        <td>{{datadisplay.ph_no}}</td>
        <td>{{datadisplay.tele}}</td>
        <td>{{datadisplay.mail}}</td>
        <td>{{datadisplay.IsActive}}</td>
        <td>{{datadisplay.last_update}}</td>
        <td>
            <div class="d-grid gap-2">
                <a class="btn btn-warning btn-sm" href="/editclient/{{datadisplay.client_id}}">Edit</a>
                <a class="btn btn-danger btn-sm" href="/deleteclient/{{datadisplay.client_id}}">Delete</a>
            </div>
        </td>
    </tr>
{% endfor %}
</tbody>
<!--<center><a class="btn btn-primary" href="/addcompany">Add Record</a></center>-->
{% endblock %}
{% block add_record_btn %}
<a class="btn btn-primary" href="/addclient" style="margin-left:900px">Add Record</a>
{% endblock %}

so this is the out put i get.i m missing the client_id and company_name.

JuConte · Accepted Answer · 2021-03-18 12:54:18Z

1

use raw

def showclient(request):
    Client.objects.raw('ursqlcommand')

answered Mar 18, 2021 at 12:54

JuConte

5423 gold badges9 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Indrajeet Singh Yadav Over a year ago

i got the error which i have posted below

Indrajeet Singh Yadav Over a year ago

nevermind i found a work around. but if you guys have a different way i could get the company name from the client object i am all ears. for now i solved it by making the following change:- In client.html change datadisplay.company_name to datadisplay.0

JuConte · Accepted Answer · 2021-03-19 06:47:14Z

1

Frist problem you pass "result" in html but iterate "Client", second you can use django query to get the same result in view

def showclient(request, pk): 
    company=get_object_or_404(Company, pk=pk)
    Client.objects.filter(company_id=company)
    return render(request, 'client.html', {'Client': result})

and in html

{% extends 'base.html' %}
{% block table_name %}Client{% endblock %}
{% block table_view %}
<thead>
    <tr>
        <th>Client Id</th>
        <th>Company</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Ph. No.</th>
        <th>Telephone</th>
        <th>E-mail</th>
        <th>Is Active</th>
        <th>Last Updated</th>
        <th>Actions</th>
    </tr>
</thead>
<tfoot>
    <tr>
        <th>Client Id</th>
        <th>Company</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Ph. No.</th>
        <th>Telephone</th>
        <th>E-mail</th>
        <th>Is Active</th>
        <th>Last Updated</th>
        <th>Actions</th>
    </tr>
</tfoot>
<tbody>
{% for datadisplay in result %}
    <tr>
        <td>{{datadisplay.client_id}}</td>
        <td>{{datadisplay.company_name}}</td>
        <td>{{datadisplay.first_name}}</td>
        <td>{{datadisplay.last_name}}</td>
        <td>{{datadisplay.ph_no}}</td>
        <td>{{datadisplay.tele}}</td>
        <td>{{datadisplay.mail}}</td>
        <td>{{datadisplay.IsActive}}</td>
        <td>{{datadisplay.last_update}}</td>
        <td>
            <div class="d-grid gap-2">
                <a class="btn btn-warning btn-sm" href="/editclient/{{datadisplay.client_id}}">Edit</a>
                <a class="btn btn-danger btn-sm" href="/deleteclient/{{datadisplay.client_id}}">Delete</a>
            </div>
        </td>
    </tr>
{% endfor %}
</tbody>
<!--<center><a class="btn btn-primary" href="/addcompany">Add Record</a></center>-->
{% endblock %}
{% block add_record_btn %}
<a class="btn btn-primary" href="/addclient" style="margin-left:900px">Add Record</a>
{% endblock %}

check if useful

answered Mar 19, 2021 at 6:47

JuConte

5423 gold badges9 silver badges21 bronze badges

3 Comments

Indrajeet Singh Yadav Over a year ago

this thing is i m rendering Client to html which is equal to the result. check the dictionary set in return statement, which works everywhere. and about the querry set i cam to know about it recently and my project in nearly 60-70% complete making changes for that will be time consuming which i dont have. next time on i m gonna use the querryset methods and in the html file when u call result it will give error.because we didnt put result in the dictionary key

Indrajeet Singh Yadav Over a year ago

over that i made it work with some weird combinations which is ok for now. If you want to know plz check your answer for with "raw" there i have explained what i did to make it work

Indrajeet Singh Yadav Over a year ago

Thanks for all your help Now i know about the raw method

Indrajeet Singh Yadav · Accepted Answer · 2021-03-18 13:20:29Z

0

hey @JuConte I tried what u said and it gave me this error

answered Mar 18, 2021 at 13:20

Indrajeet Singh Yadav

479 bronze badges

1 Comment

Indrajeet Singh Yadav Over a year ago

i have update my question with all the relevant codes and the output for it. please check the same and guide me where i went wrong

Collectives™ on Stack Overflow

Using SQL join query with pyodbc mssql django

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related