我的内部连接有一些问题。
请查看此代码。
@app.route('/products', defaults={'page':1})
@app.route('/products/<int:store_id>', methods=['GET'])
def productcheck(store_id):
if 'loggedin' in session:
cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT * FROM products WHERE store_id=%s", [store_id])
data = cursor.fetchall()
sql = "SELECT categories.category_title AS product_category FROM products INNER JOIN categories ON products.product_category = categories.id"
cursor.execute(sql)
myresult = cursor.fetchall()
if data:
return render_template('productstore.html', data=data, myresult=myresult)
else:
return 'Error loading #{id}'.format(id=id)
else:
return redirect(url_for('login'))
<table class="table">
<thead>
<tr>
<th scope="col">product name</th>
<th scope="col">product price</th>
<th scope="col">category</th>
<th scope="col">delete</th>
</tr>
</thead>
{% for item in data %}
<tbody>
<tr style="font-size: 22px;">
<th scope="row">{{ item.product_title }}</th>
<td>{{ item.product_price }}</td>
<td>{{ "product category should be here" }}</td>
<td><a href="/delete/{{ item.slug }}" class="fa fa-edit" style="font-size: 34px;"></a></td>
</tr>
{% endfor %}
在产品表中有类别ID,在类别表中有类别名称。
我想匹配这两个值并显示类别名称而不是类别 ID
这是产品表
+---------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| store_id | int(11) | YES | | NULL | |
| product_title | varchar(255) | YES | | NULL | |
| product_price | varchar(30) | YES | | NULL | |
| product_count | int(11) | YES | | NULL | |
| product_description | text | YES | | NULL | |
| product_category | int(11) | YES | | NULL | |
| image | varchar(100) | YES | | NULL | |
| date | varchar(30) | YES | | NULL | |
| slug | varchar(100) | YES | | NULL | |
| product_link | varchar(255) | YES | | NULL | |
| product_ignore | int(14) | NO | | NULL | |
+---------------------+------------------+------+-----+---------+----------------+
12 rows in set (0.01 sec)
这是类别
+----------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| store_id | int(11) | YES | | NULL | |
| category_title | varchar(200) | YES | | NULL | |
| category_description | text | YES | | NULL | |
| date | varchar(30) | YES | | NULL | |
| slug | varchar(100) | YES | | | |
+----------------------+------------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
我只是自己修好了,谢谢大家。 更正后的代码如下。
@app.route('/products/<int:store_id>', methods=['GET'])
def productcheck(store_id):
if 'loggedin' in session:
cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT * FROM products LEFT JOIN categories ON products.product_category = categories.id WHERE products.store_id=%s", [store_id])
data = cursor.fetchall()
if data:
return render_template('productstore.html', data=data)
else:
return 'Error loading #{id}'.format(id=id)
else:
return redirect(url_for('login'))
尝试使用内部连接来反转您,除此之外似乎还可以:
sql = "SELECT categories.category_title AS product_category"
"FROM categories"
"INNER JOIN products"
"ON products.product_category = categories.id"