I have a sql script called myscript.sql that looks like this:

-- Comment that I have in my sql script.
MERGE INTO my_table i using
(select several_columns
from my_other_table f
where condition
) f on (my join conditions)
WHEN MATCHED THEN
  UPDATE SET whatever;
  
COMMIT;

I have tried to call it from python the same way I do from a SQL Developer worksheet, which is:

cursor().execute(r'''@"path_to_my_script\myscript.sql"''')

But it does not work, the following error is raised:

DatabaseError: ORA-00900: invalid SQL statement

How could I execute the script?

CodePudding user response：

In Oracle, it is invalid to pass multiple statements as a single command (this is to help prevent SQL injection attacks). Given that your script contains multiple statements then it is impossible to run it with a single command.

If your script only has SQL statements (and no PL/SQL statements) then:

1. Read the script into a string.
2. Split the string into statements on the SQL statement terminator ;
3. Open a connection to the database.
4. Turn auto-commit off.
5. Run each statement individually.
6. Finally, and optionally, COMMIT the transaction (in your case the script includes COMMIT as the final statement).

If you have PL/SQL statements then you will need to check whether each statement is SQL or PL/SQL and would be terminated by, respectively, ; or / (on a new line) and split the string accordingly.

(Note: It is also possible to terminate SQL statements with / on a new-line. If you want to include PL/SQL statements in your script then it may be simplest to terminate all statements with / on a new-line. This means that for SQL statements you should include only / and not ; as well. For PL/SQL statements, you should terminate the block with END; and include the final ; and then terminate the statement with / on a new line.)

CodePudding user response：

You can read the contents of the SQL file and pass it to the execute() method.

See this other question and its answers that look very much related: reading external sql script in python

CodePudding user response：

from sqlalchemy import create_engine

engine = create_engine('postgresql://username:password@host:port/database')
connection = engine.connect()
result = connection.execute("Your SQL QUERY")
connection.close()