[ Why am I receiving this error: _mysql_exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)') ]

I'm receiving the above error, which I believe implies I'm passing in the wrong type as I add rows to my table. However, I'm creating the new table using columns from two other tables and I'm using the same types as specified in the original tables. What am I missing?

First I create the table:

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)

Next I create a new table by looping over queries and adding the row results to the new table (lens_items). I INSERT at "######>>>>>" below:

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

You do not follow mysql syntax for INSERT, since you try to mix the values clause with a select. Use INSERT ... SELECT ... instead:

    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

However, you could simplify the data transfer if you used join in the select part leaving python completely out of the picture:

    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