提问人:Oleksii 提问时间:5/18/2023 最后编辑:khelwoodOleksii 更新时间:5/19/2023 访问量:37
mysql-python,请求变量?
Mysql-python, Variable for request?
问:
表组:
id_group | 名字 | create_time | update_time |
---|---|---|---|
1 | 萨尔萨 | 2023-03-18 | 零 |
2 | 嘻哈 | 2023-03-19 | 零 |
3 | 华尔兹 | 2023-03-22 | 零 |
4 | 狐步 | 2023-04-01 | 零 |
5 | 快步 | 2023-04-01 | 零 |
6 | 狐步舞专业版 | 2023-04-04 | 零 |
表格日历:
id_c | fulldate | 全职 | dayOfWeek | 1 | 2 | 3 | 4 | 5 | 6 |
---|---|---|---|---|---|---|---|---|---|
1232 | 记录 | 记录 | 记录 | 零 | 2 | 零 | 4 | 零 | 零 |
2233 | 记录 | 记录 | 记录 | 零 | 2 | 零 | 零 | 零 | 零 |
3342 | 记录 | 记录 | 记录 | 零 | 零 | 3 | 零 | 零 | 6 |
4422 | 记录 | 记录 | 记录 | 零 | 零 | 3 | 零 | 零 | 零 |
5442 | 记录 | 记录 | 记录 | 零 | 零 | 零 | 零 | 零 | 6 |
6324 | 记录 | 记录 | 记录 | 零 | 2 | 零 | 4 | 零 | 零 |
import mysql.connector
try:
connection = mysql.connector.connect(host='localhost',
database='ck4',
user='user',
password='user')
Query = "SELECT `id_group` FROM groups"
cursor = connection.cursor()
cursor.execute(Query)
records = cursor.fetchall()
for rows in records:
row = (rows[0])
print(row)
>>>>
1
2
3
4
5
6
>>>>
sql_select_Query = """SELECT * FROM `calendar` WHERE `%s` = '%s'""" % (row, row)
except mysql.connector.Error as e:
print("Error reading data from MySQL table", e)
finally:
if connection.is_connected():
connection.close()
cursor.close()
print("MySQL connection is closed")
请求工作正常。
sql_select_Query = """SELECT * FROM `calendar` WHERE `%s` = '%s'""" % (row, row)
请求在每个 id 的循环中处理:
SELECT * FROM `calendar` WHERE `1` = '1';
SELECT * FROM `calendar` WHERE `2` = '2';
SELECT * FROM `calendar` WHERE `3` = '3';
SELECT * FROM `calendar` WHERE `4` = '4';
SELECT * FROM `calendar` WHERE `5` = '5';
SELECT * FROM `calendar` WHERE `6` = '6';
我怎样才能提出另一个这样的请求:
SELECT * FROM `calendar` WHERE `1` = '1' OR `2` = '2' OR `3` = '3' OR `4` = '4' OR `5` = '5' OR `6` = '6';
答:
0赞
Barmar
5/19/2023
#1
您可以使用分隔符连接所有列名。join()
OR
Query = "SELECT `id_group` FROM groups"
cursor = connection.cursor()
cursor.execute(Query)
groups = [row[0] for row in cursor.fetchall()]
condition = ' OR '.join(f"`{group}` = '{group}'" for group in groups
sql_select_Query = f"SELECT * FROM `calendar` WHERE {condition}"
请注意,像这样具有动态列名称的表设计通常是一个糟糕的主意,这意味着您大部分时间都必须编写动态 SQL。动态信息应位于列值中,而不是列名中。
评论
0赞
Oleksii
5/19/2023
- 请求已成功完成。谢谢 ) - 通过动态信息的结构,我完全同意你的看法。但事实就是如此。
评论
WHERE `1` OR `2` OR `3` OR `4` OR `5` OR `6`