[ 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