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?

Javi Torre
  • 724
  • 8
  • 23
  • 1
    Maybe this post could help you! https://stackoverflow.com/questions/19472922/reading-external-sql-script-in-python – JLMelandri8 Jan 23 '23 at 10:03
  • See https://github.com/oracle/python-oracledb/blob/main/samples/sqlp.py and https://github.com/oracle/python-oracledb/blob/v1.2.2/samples/sample_env.py#L171-L204 for Python code that can work with some limited form of .SQL files. – Christopher Jones Jan 23 '23 at 20:57

3 Answers3

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

MT0
  • 143,790
  • 11
  • 59
  • 117
  • How can I turn the auto commit off? – Javi Torre Jan 23 '23 at 10:13
  • 1
    @JaviTorre A very quick internet search for `cx_oracle auto-commit` gets you to the [relevant documentation](https://cx-oracle.readthedocs.io/en/latest/user_guide/txn_management.html). If you are using a different database driver then search for the appropriate documentation for your driver. – MT0 Jan 23 '23 at 10:16
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

notrev
  • 665
  • 1
  • 5
  • 16
  • 1
    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.) – MT0 Jan 23 '23 at 10:20
-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()
Mohit Dagar
  • 522
  • 6
  • 21
  • 2
    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). – MT0 Jan 23 '23 at 10:24
  • 1
    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. – Shawn Hemelstrand Jan 24 '23 at 05:18