Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.9k views
in Technique[技术] by (71.8m points)

Python how to pass variables to SQLite complex SQL update Query

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!


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Found the answer after I figured out the proper terminology: string formatting and interloping. Found the answer here.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...