Django 内部結合 SQL views.py

from django.shortcuts import render,get_object_or_404, redirect

from django.http import HttpResponse

from django.views.generic import ListView

from django.db import connection

import psycopg2

# Create your views here.

#list

def index(request):

sqltext="""SELECT

a.id

, a.empid

, a.empname

, a.deptid

, a.mailaddress

, b.deptname

, b.upperdeptid

FROM

joinquery_employee a

INNER JOIN

joinquery_department b

on a.deptid=b.deptid

ORDER BY

a.id

; """

emplist=exec_query(sqltext)

return render(request, 'joinquery/index.html',{'emplist':emplist})

def exec_query(sqltext):

with connection.cursor() as cursor:

cursor.execute(sqltext)

 

""" 一行ずつ取得

get_arr =

for row in cursor:

get_arr.append(row)

print(get_arr[0])

"""

results = cursor.fetchall()

# print(results)

# resultsにフィールド名を付加

 

############ カラム名取得 ###############

columns =

for fiels in cursor.description:

# print(fiels[0])

columns.append(fiels[0])

print(columns) # DB カラム名取得

########### 辞書型で入れる

values =

for result in results : # ===> 値の数

val_dic = {}

for index, field in enumerate(columns): # enumerate(columns) => カラムの値でループ

val_dic[field] = result[index]

values.append(val_dic)

########### 辞書　重複削除

"""

seen = []

re_dic = {}

for key, val in values.items():

 

if val not in seen:

seen.append(val)

re_dic[key] = val

 

print(re_dic)

"""

 

return values