How to make insert here?

Question

Python

Hello! I am using python+firebird. I want to create a new record in the table

. The table has a trigger for generating ID and date:

AS
BEGIN
 if(new.id is null) then new.id=gen_id(d_gen_commands,1);
 new.datetime="now";
 post_event "d_new_command";
END

---------------------

cur.execute(f"insert into d_commands values {(num, date_and_time, 1, 'open_door,0', -1062731554)}")

My code above fully works, but I do not need to enter 5 values, but only 3 of them.

And here is my solution:

cur.execute(f"insert into d_commands (executor, text, phis_addr) values ({int(1)}, {str(action)}, {int(-1062731554)})")

Yes, maybe with *int* and *str* it doesn't look nice, but I'm already desperate that "you never know"... I tried everything I found, that's why I'm here :) It gives the following error:

('Error while preparing SQL statement:\n- SQLCODE: -206\n- Dynamic SQL Error\n- SQL error code = -206\n- Column unknown\n- OPEN_DOOR\n- At line 1, column 63', -206, 335544569)

I read somewhere else that with my dynamic request, you must manually call the trigger from the beginning of the post. I don’t know if it’s true or not and I don’t know how xD
Please tell me how to solve this problem. Thank you!

Answer 1

Akina , Gleb Lukashonok , please do not build SQL queries using string formatting unless absolutely necessary.
Use placeholders and may Bobby Tables be with you .

cur.execute("insert into d_commands (executor, text, phis_addr) values (?, ?, ?)", (1, str(action), -1062731554))

In this case, escaping will be done automatically, without the risk that you will forget to do it.