Substitute literals in MySQL Python prepared statement

user_135946 0
Viewed 116

I'm new to MySQL Python connector and I'm trying to find out if in prepared statement I can substitute non-parameters? Here's example of query that I need to execute repeatedly while decrementing the counter:

UPDATE
        material_forecast_series sfs
    JOIN material_forecast sf ON sfs.F_ID = sf.F_ID
    JOIN material_demand sd ON sd.date_ID = sf.date_ID
        AND sd.D_ID = sfs.D_ID
    JOIN material_demand_series ds ON sfs.D_ID = ds.D_ID
    JOIN OMG_membership om ON ds.item_ID = om.item_ID
    JOIN material_FE sfe ON sfe.F_ID = sf.F_ID
SET sfe.v2 = IF(sf.v2 = 0, NULL, (sd.quantity - sf.v2) / sf.v2)
    WHERE
        sfs.forecast_ID = 15
            AND ds.demand_ID = 9
            AND ds.O_ID = 2
            AND om.OG_ID = 318
            AND sd.date_ID = 275
            AND sfe.date_ID = 274

In the snippet above last parameter sfe.date_ID will be decremented in the loop. No problem there, I will simply do sfe.date_ID=%s. However in this line: SET sfe.v2 = IF(sf.v2 = 0, NULL, (sd.quantity - sf.v2) / sf.v2) literal v2 needs to be changed to v3, v4, v5... each loop iteration. No problem if I'm to use Python parameter substitution and regular cursor but I want to use prepared statement for performance reason. Any other tricks to improve performance are greatly appreciated as well

prepared-statement mysql-python
2 Answers

How about creating a DynamicSQL , doing something like this inside your loop with the variable up_counter getting incremented in the end 

    set @up_counter = 1
    set @dynamic_sql_1 = 'UPDATE '
    ' material_forecast_series sfs '
    ' JOIN material_forecast sf ON sfs.F_ID = sf.F_ID '
    ' JOIN material_demand sd ON sd.date_ID = sf.date_ID '
    ' AND sd.D_ID = sfs.D_ID '
    ' JOIN material_demand_series ds ON sfs.D_ID = ds.D_ID '
    ' JOIN OMG_membership om ON ds.item_ID = om.item_ID '
    ' JOIN material_FE sfe ON sfe.F_ID = sf.F_ID '
    'SET sfe.v'

    set @dynamic_sql_2 = CONCAT(@up_counter,' = IF(sf.v',',@up_counter,' = 0, NULL, (sd.quantity - sf.v',@up_counter,') / sf.v',@up_counter,')')

    Set @dynamic_sql_2 = '  WHERE '
    'sfs.forecast_ID = 15 '
    ' AND ds.demand_ID = 9 '
    ' AND ds.O_ID = 2 '
    ' AND om.OG_ID = 318 '
    ' AND sd.date_ID = 275 '
    ' AND sfe.date_ID = %s '

    Set @final_sql = CONCAT(@dynamic_sql_1,@dynamic_sql_2,@dynamic_sql_2)

    PREPARE stmt1 FROM @final_sql; 
    EXECUTE stmt1 ; 
    DEALLOCATE PREPARE stmt1; 

    set @up_counter = @up_counter + 1
user_60515960

Sorry, it is not possible. To quote the documentation of PREPARE (emphasis on “data values” is mine):

Statement names are not case sensitive. preparable_stmt is either a string literal or a user variable that contains the text of the SQL statement. The text must represent a single statement, not multiple statements. Within the statement, ? characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it. The ? characters should not be enclosed within quotation marks, even if you intend to bind them to string values. Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

Since the field names such as v2 and v3 are syntactically not data values but identifiers, you can't use prepared statements to substitute them for placeholders.

user_32426130
Related