I have this SQL query that I confirmed works in SQLite. It updates two columns in the Table. I have 144 columns that need to be updated using the same query. How can I, using Python, pass along variables so I can use the same query to update all of them?
Here is my query to update one column:
UPDATE GBPAUD_TA AS t1
SET _1m_L3_Time = COALESCE(
(
SELECT
MIN(
CASE t1.Action
WHEN 'Buy' THEN CASE WHEN (t2._1M_55 >= t2.Low AND t2._1M_55 < t2.Open) THEN t2.Date_Time END
WHEN 'Sell' THEN CASE WHEN (t2._1M_55 <= t2.High AND t2._1M_55 < t2.Open) THEN t2.Date_Time END
END
)
FROM GBPAUD_DATA t2
WHERE t2.Date_Time >= t1.Open_Date AND t2.Date_Time <= t1.New_Closing_Time
),
t1._1m_L3_Time
);
UPDATE GBPAUD_TA
SET _1m_L3_Price = (SELECT _1M_55
FROM GBPAUD_DATA
WHERE Date_Time = GBPAUD_TA._1m_L3_Time)
where EXISTS (SELECT _1M_55
FROM GBPAUD_DATA
WHERE Date_Time = GBPAUD_TA._1m_L3_Time)
Here is my query showing the variables that I would need to automatically insert:
UPDATE GBPAUD_TA AS t1
SET Variable1 = COALESCE(
(
SELECT
MIN(
CASE t1.Action
WHEN 'Buy' THEN CASE WHEN (t2.Variable2 >= t2.Low AND t2.Variable2< t2.Open) THEN t2.Date_Time END
WHEN 'Sell' THEN CASE WHEN (t2.Variable2 <= t2.High AND t2.Variable2< t2.Open) THEN t2.Date_Time END
END
)
FROM GBPAUD_DATA t2
WHERE t2.Date_Time >= t1.Open_Date AND t2.Date_Time <= t1.New_Closing_Time
),
t1.Variable1
);
UPDATE GBPAUD_TA
SET Variable3 = (SELECT Variable2
FROM GBPAUD_DATA
WHERE Date_Time = GBPAUD_TA.Variable1)
where EXISTS (SELECT Variable2
FROM GBPAUD_DATA
WHERE Date_Time = GBPAUD_TA.Variable1)
I have a total of 3 Variables.
Based upon googling and reading, I found a possible way by using host variables: I use the "?" in place of the variable, combine the variables into a tuple, and then use "executemany()"?
I tried this, but it did not work. It gave me an error:
"cursor.executemany(sql_update_query, SLTuple)
OperationalError: near "?": syntax error"
So what should I do? Any guidance is much appreciated!
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…