python - Django:如何根据 user_id 显示名称?
问题描述
class Order_ListAPIView(APIView):
def get(self,request,format=None):
totalData=[]
if request.method == 'GET':
cur,conn = connection()
order_query = ''' SELECT * FROM orders '''
order_detail_query = ''' SELECT * FROM order_details '''
user_query = ''' SELECT * FROM users '''
with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
cursor.execute(order_detail_query)
order_detail_result = cursor.fetchall()
order_detail_data = list(order_detail_result)
# print(order_detail_data)
cursor.execute(order_query)
order_result = cursor.fetchall()
order_data = list(order_result)
cursor.execute(user_query)
user_result = cursor.fetchall()
user_data = list(user_result)
dic = {}
def merge_order_data_and_detail(order_data, order_detail_data):
for d in order_detail_data:
if d['order_id'] not in dic:
dic[d['order_id']] = []
dic[d['order_id']].append(d)
for o in order_data:
if o['order_id'] in dic:
o['order_detail_data'] = dic[o['order_id']]
merge_order_data_and_detail(order_data, order_detail_data)
# totalData.append({"order_data":order_data, })
return Response({"order_data":order_data, "user_data":user_data},status=status.HTTP_200_OK)
else:
return Response(status=status.HTTP_400_BAD_REQUEST)
输出:
{
"order_data": [
{
"order_id": 1,
"payment_method_id": 1,
"delivery_id": 2,
"user_id": 3,
"txnid": "584ffb7fd622eca10a6d",
"order_no": "1-1583152683-0005",
"order_total": 1.0,
"payment_status": "Paid",
"payuMoneyId": "306043618",
"mihpayid": "9956109007",
"order_detail_data": [
{
"order_detail_id": 1,
"order_id": 1,
"user_id": 3,
"qty": 1,
"product_price": 1.0,
"order_item_status": "Placed",
"feedback": ""
}
]
},
{
"order_id": 2,
"payment_method_id": 2,
"delivery_id": 2,
"user_id": 2,
"txnid": "",
"order_no": "1-1583152785-0010",
"order_total": 1.0,
"payment_status": "Unpaid",
"payuMoneyId": "",
"mihpayid": "",
"order_detail_data": [
{
"order_detail_id": 2,
"order_id": 2,
"user_id": 2,
"qty": 1,
"product_price": 1.0,
"order_item_status": "Cancelled",
"feedback": "0"
}
]
},
{
"order_id": 3,
"payment_method_id": 1,
"delivery_id": 2,
"user_id": 1,
"txnid": "83e066ca75437f433343d05b0",
"order_no": "2-1583152964-0019",
"order_total": 2.0,
"payment_status": "Paid",
"payuMoneyId": "306044826",
"mihpayid": "9956136979",
"order_detail_data": [
{
"order_detail_id": 3,
"order_id": 3,
"user_id": 1,
"qty": 2,
"product_price": 1.0,
"order_item_status": "Placed",
"feedback": ""
}
]
},
],
"user_data": [
{
"user_id": 1,
"role": 0,
"created_by": 0,
"group_in": 0,
"name": "Deebaco",
"email": "singh.deeba@gmail.com",
"mobile": "",
"password": "",
"image": "",
"last_login": null,
"rand": "",
"opened": 0,
"auth_id": "114025425174039",
"auth_platform": "Google",
"created_datetime": "2020-03-02T17:54:28",
"updated_datetime": "2020-03-02T17:54:28",
"status": "Active"
},
{
"user_id": 2,
"role": 0,
"created_by": 0,
"group_in": 0,
"name": "Kriti Mathur",
"email": "mathurkriti@gmail.com",
"mobile": "",
"password": "",
"image": "",
"last_login": null,
"rand": "",
"opened": 0,
"auth_id": "111445960345755",
"auth_platform": "Google",
"created_datetime": "2020-03-02T18:02:24",
"updated_datetime": "2020-03-02T18:02:24",
"status": "Active"
},
{
"user_id": 3,
"role": 0,
"created_by": 0,
"group_in": 0,
"name": "Sakshi Swarnkar",
"email": "sakshi@gmail.com",
"mobile": "",
"password": "",
"image": "",
"last_login": null,
"rand": "",
"opened": 0,
"auth_id": "117543975857",
"auth_platform": "Google",
"created_datetime": "2020-03-02T18:02:24",
"updated_datetime": "2020-03-02T18:02:24",
"status": "Active"
},
]
}
我想要的是如下所示:获取user_id的名称替换。
{
"order_data": [
{
"order_id": 1,
"payment_method_id": 1,
"delivery_id": 2,
"name": "Sakshi Swarnkar",
"txnid": "584ffb7fd622eca10a6d",
"order_no": "1-1583152683-0005",
"order_total": 1.0,
"payment_status": "Paid",
"payuMoneyId": "306043618",
"mihpayid": "9956109007",
"order_detail_data": [
{
"order_detail_id": 1,
"order_id": 1,
"name": "Sakshi Swarnkar",
"qty": 1,
"product_price": 1.0,
"order_item_status": "Placed",
"feedback": ""
}
]
},
{
"order_id": 2,
"payment_method_id": 2,
"delivery_id": 2,
"name": "Kriti Mathur",
"txnid": "",
"order_no": "1-1583152785-0010",
"order_total": 1.0,
"payment_status": "Unpaid",
"payuMoneyId": "",
"mihpayid": "",
"order_detail_data": [
{
"order_detail_id": 2,
"order_id": 2,
"name": "Kriti Mathur",
"qty": 1,
"product_price": 1.0,
"order_item_status": "Cancelled",
"feedback": "0"
}
]
},
{
"order_id": 3,
"payment_method_id": 1,
"delivery_id": 2,
"name": "Deebaco",
"txnid": "83e066ca75437f433343d05b0",
"order_no": "2-1583152964-0019",
"order_total": 2.0,
"payment_status": "Paid",
"payuMoneyId": "306044826",
"mihpayid": "9956136979",
"order_detail_data": [
{
"order_detail_id": 3,
"order_id": 3,
"name": "Deebaco",
"qty": 2,
"product_price": 1.0,
"order_item_status": "Placed",
"feedback": ""
}
]
},
],
我直接使用来自数据库的原始查询而不是 ORM 来获取数据。我想获取 user_id 的用户名替换。
我正在尝试解决这个问题,但我没有得到任何可以解决这个问题的可能答案。如果有人能帮助我找到我正在寻找的东西,那就太好了。提前非常感谢!
解决方案
您将需要更改以下 sql 查询:order_query = ''' SELECT * FROM orders '''
您的 sql 查询将需要一个连接
SELECT users.name,order.* from order left join users on order.user_id = users.user_id
它不会完全符合您的期望,因为我没有所有信息并且我正在对您的数据库做出假设,但是在您稍微修改它之后这应该可以工作
推荐阅读
- c++ - 使用 Winsock C++ 的蓝牙连接
- operating-system - 使用 Semaphore 确定三个进程的执行顺序并打印“AABC”
- bash - 如何递归地grep多个文件夹中的文件?我无法管道递归猫输出
- word-embedding - 从 BERT 模型中提取单词特征
- android - 对于 Android Gradle 插件 4.1.0 中的库项目,版本属性不起作用
- jquery - 如何获取 JQuery 插件验证以检查数据库中的重复值
- post - 无法使用 Kafka Rest Proxy 创建 Kafka 主题 - HTTP 415 Unsupported Media Type javax.ws.rs.NotSupportedException
- office-addins - AADSTS50020:租户中不存在用户帐户
- python - 使用 Python 和 SQLAlchemy 连接到 Heroku Postgres
- python - Python Selenium:如何在 Network > XHR 中访问响应对象