Creating a trigger using ODBC execute

[TopperTycho]

I am using pyodbc with the 10.3.32-Mariadb driver, I downloaded. My MariaDB is located on a NAS in my local network.
I am automating a database so all tables can be automatically generated for a personal project. So I don't have to make all the tables and their links by hand. Here I want to use a trigger for each data table so a DateTime value is automatically added to it.
Found a SQL query (see below) that can make the trigger which works when using an external program (HeidiSQL) to send SQL queries to the database. But trying to implement this query in my Python code doesn't work. I guess it has something to do with the end-of-statement symbols which is why the DELIMITER is added to the query. So the whole statement could be executed as one big query.

Does anyone have an idea or solution for implementing this in my Python code?

DELIMITER //

CREATE TRIGGER sensor_data_insert
BEFORE INSERT ON sensor_data
FOR EACH ROW
BEGIN
  DECLARE last_datetime DATETIME(3);
  DECLARE sampling_interval INT;
  SET sampling_interval = 1;

  SELECT MAX(datetime) INTO last_datetime FROM sensor_data;

  IF last_datetime IS NULL THEN
    SET NEW.datetime = NOW(3);
  ELSE
    SET NEW.datetime = last_datetime + INTERVAL sampling_interval SECOND_MICROSECOND;
  END IF;
END //

DELIMITER ;

[v-chojas]

How exactly does it not work?

[TopperTycho]

I am running a function I created which runs the following code:

cursor.execute(f"SHOW TABLES LIKE '{table_name}'")
if not cursor.fetchone():
  cursor.execute(f"CREATE TABLE {table_name} (datetime DATETIME(3) PRIMARY KEY, data FLOAT)")
  cursor.execute(f"""
    DELIMITER //
    CREATE TRIGGER {table_name}_insert
    BEFORE INSERT ON {table_name}
    FOR EACH ROW
    BEGIN
        DECLARE last_datetime DATETIME(3);
        DECLARE sampling_interval INT;
        SET sampling_interval = 1;
        SELECT MAX(datetime) INTO last_datetime FROM {table_name};
        IF last_datetime IS NULL THEN
            SET NEW.datetime = NOW(3);
        ELSE
            SET NEW.datetime = last_datetime + INTERVAL sampling_interval SECOND_MICROSECOND;
        END IF;
    END //
    DELIMITER ;
  """)
cursor.commit()

Running this code gives the following error:

cursor.execute(f"""
pyodbc.ProgrammingError: ('42000', "[42000] [ma-3.1.15][10.3.32-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER //\n            CREATE TRIGGER M0001_insert\n            BEFORE INSER...' at line 1 (1064) (SQLExecDirectW)")

And without using the DELIMITER, I get the following error:

cursor.execute(f"""
pyodbc.Error: ('HY000', '[HY000] [ma-3.1.15][10.3.32-MariaDB]This command is not supported in the prepared statement protocol yet (1295) (SQLExecDirectW)')

In both cases, the table is created but the trigger isn't.

[TopperTycho]

I tried using the 'mysql.connector' library for this function and it runs without any problems, even the DELIMITER wasn't needed. Would still prefer to use one library instead of different ones depending on the SQL query.

[v-chojas]

The error is coming from the ODBC driver, so this is not a pyODBC issue. The only other recommendation is to check that you have escaped the strings correctly, or perhaps to read query contents from a file instead.

[gordthompson]

It looks like you may be encountering a limitation of the ODBC driver described here:

https://mariadb.com/kb/en/about-mariadb-connector-odbc/#multiple-statement-execution

[TopperTycho]

Thanks! Really clear comment! For now I switched to 'mysql.connector' for executing this query, this works for me at the moment.

[gordthompson]

Try using MariaDB ODBC Connector 3.2.0-Development version. I seems to work for me.