python - 为什么我会收到此错误 : _mysql_exceptions. OperationalError : (1241, 'Operand should contain 1 column(s)' )

Question

我收到上述错误，我认为这意味着我在向表中添加行时传递了错误的类型。但是，我正在使用其他两个表中的列创建新表，并且我使用的是与原始表中指定的相同类型。我错过了什么？

首先我创建表:

cur = db.cursor()
cur.execute("DROP TABLE IF EXISTS lens_items;")
query = '''
CREATE TABLE lens_items (
    solution_id INTEGER(9),
    task_id BIGINT(20),
    item_oid CHAR(48),
    influence DOUBLE
);
'''
cur.execute(query)

接下来，我通过循环查询并将行结果添加到新表 (lens_items) 来创建一个新表。我在下面的“######>>>>>”处插入:

cursor1 = db.cursor(MySQLdb.cursors.DictCursor)  # a dictcursor enables a named hash
cursor2 = db.cursor(MySQLdb.cursors.DictCursor)  # a dictcursor enables a named hash

query = """
    Select user_id, solution_id 
    From user_concepts
    Where user_id IN (Select user_id FROM fields);
"""  

cursor1.execute(query)

rows1 = cursor1.fetchall()
######>>>>>

for row in rows1:
    user_id = row["user_id"]
    solution_id = row["solution_id"]

    cursor2.execute("""
        INSERT INTO lens_items (solution_id, task_id, item_oid, influence)
        VALUES (solution_id, (SELECT task_id, item_oid, influence
        FROM solution_oids 
        WHERE task_id = %s
        ORDER BY influence DESC));
    """, (solution_id,))

Answer 1

您不遵循 INSERT 的 mysql 语法，因为您试图将 values 子句与 select 混合。使用 INSERT ... SELECT ...相反:

    INSERT INTO lens_items (solution_id, task_id, item_oid, influence)
    SELECT solution_id, task_id, item_oid, influence
    FROM solution_oids 
    WHERE task_id = %s
    ORDER BY influence DESC

但是，如果您在选择部分使用 join 而完全不考虑 python，则可以简化数据传输:

    INSERT INTO lens_items (solution_id, task_id, item_oid, influence)
    SELECT s.solution_id, s.task_id, s.item_oid, s.influence
    FROM solution_oids s
    INNER JOIN user_concepts u ON s.solution_id=u.solution_id
    INNER JOIN fields f ON f.user_id=u.user_id
    ORDER BY influence DESC