python - How do I pass a parameter from the mysql library in python to a stored procedure

Question

This is my sql stored procedure

I want to check if the column that i pass from my python script not exist, if not exist, then created it.

            CREATE DEFINER=`root`@`localhost` PROCEDURE `columnas_fundamental`(
            IN nombre_columna VARCHAR(60)
            )
            BEGIN
            -- veo si la columna existe
            IF NOT EXISTS(SELECT 1 COLUMN_NAME  
                        FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE table_schema = 'fundamental_anual_data'
                        AND table_name = 'fundamental'
                        AND column_name = nombre_columna)
                         
            -- Si existe me retorna un valor (1), sino, la creo yo
            THEN
               -- hacer alguna acción, es decir, ALTER TABLE si faltan algunas columnas
               ALTER TABLE fundamental ADD COLUMN nombre_columna VARCHAR(60);
            
            END IF;
            END
            
            My python code is it:
            

This method api_data(data=data, ticker=ticker) in ticker recive a list of ticker like ['AAPL', 'MSFT',...] for each ticker we have same columns and sometime a column is different

            def fundamental():
                global id
                MysQl.select_db_type('localhost')
                start_time = time.time()
                banderin = False
                for ticker in tickers:
                    logger.notify(ticker)
                    for i in api_data(data=data, ticker=ticker):
                        for key, value in i.items():
                            if 'id' in key:
                                if not banderin:
                                    id = replace_str(value)
                                    # MysQl.alter_table_str(id)
                                    col = MysQl.cursor.callproc('columnas_fundamental', [id,])
                                    logger.notify(col)
            
                            else:
                                MysQl.insert_update_table(column_name=id, date=key, symbol=ticker, value=value)
            
                    banderin = True
                    continue

标签： pythonmysql

Answer 1

Arguments go as tuple not arrays

See documentation

col = MysQl.cursor.callproc('columnas_fundamental', (id,))

for every argument in the stored procedure, there must be one in the tuple.

You have to rewrite your procedure Mysql doesn't like variables for cololmn names

DROP PROCEDURE IF exists column_fundamental;
DELIMITER //
CREATE PROCEDURE `columnas_fundamental`(
IN _nombre_columna VARCHAR(60)
)
BEGIN
    -- veo si la columna existe
    -- veo si la columna existe
    IF NOT EXISTS(SELECT 1 
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE table_schema = 'fundamental_anual_data'
                AND table_name = 'fundamental'
                AND column_name = _nombre_columna)
                 
    -- Si existe me retorna un valor (1), sino, la creo yo
    THEN
           -- hacer alguna acción, es decir, ALTER TABLE si faltan algunas columnas

           SET @sql = CONCAT('ALTER TABLE fundamental ADD COLUMN ',_nombre_columna,' VARCHAR(60);');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;            
    END IF;

END//
DELIMITER ;