0

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?

Improve this question
2

3 Answers 3

Reset to default
2

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.)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

How can I turn the auto commit off?
@JaviTorre A very quick internet search for cx_oracle auto-commit gets you to the relevant documentation. If you are using a different database driver then search for the appropriate documentation for your driver.
1

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

Improve this answer

1 Comment

In Oracle, a naïve approach of reading and then executing will fail with scripts that contain multiple statements as Oracle forbids commands from containing multiple statements. (Some other RDBMS do not have this restriction and read and execute would be a valid approach.)
-1 
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()
Improve this answer

2 Comments

The OP has tagged Oracle and not PostgreSQL. Additionally, their SQL script contains multiple statements and not a single statement (which makes a huge difference).
While this code snippet may be the solution, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.