Running a entire SQL script via python

Question

I'm looking to run the following test.sql located in a folder on my C: drive. I've been playing with cx_Oracle and just can't get it to work.

test.sql contains the following.

CREATE TABLE MURRAYLR.test
( customer_id number(10) NOT NULL,
 customer_name varchar2(50) NOT NULL,
 city varchar2(50)
);
CREATE TABLE MURRAYLR.test2
( customer_id number(10) NOT NULL,
 customer_name varchar2(50) NOT NULL,
 city varchar2(50)
);

This is my code:

import sys 
import cx_Oracle 
connection = cx_Oracle.connect('user,'password,'test.ora') 
cursor = connection.cursor() 
f = open("C:\Users\desktop\Test_table.sql")
full_sql = f.read() 
sql_commands = full_sql.split(';') 
for sql_command in sql_commands: 
 cursor.execute(sql_command)
 cursor.close() 
connection.close()

• Vini.g.fer: Your question seems a bit vague ... can you specify what error are you having, what result are you getting, and what did you expected? Why the python tags if your question don't mention it, and you didn't share any python code?
• Wayne Werner: Please post your MCVE
• Vini.g.fer: Don't have an oracle database right now. I noticed an parenthesis missing at the very end of the file. I just added it in the edit. Try running with it. Also not sure if "{" and "}" are accepted in .sql files. Try running without them (almost 100% sure database will accept the file without them).
• trans1st0r: not sure if this is relevant, but are the curly braces around the statements necessary?
• trans1st0r: @LeeMurray can you show the error message? Also, try 'print full_sql.split(';')` . On my system is shows \n characters in the strings which might be the culprit.

Answer 1

This answer is relevant only if your test.sql file contains new lines '\n\' characters (like mine which I got from copy-pasting your sql code). You will need to remove them in your code, if they are present. To check, do

print full_sql 

To fix the '\n's,

sql_commands = full_sql.replace('\n', '').split(';')[:-1]

The above should help.

It removes the '\n's and removes the empty string token at the end when splitting the sql string.

• Lee Murray: I though you had nailed it then I added a bit of extra code to the sql & it broke again. BEGIN EXECUTE IMMEDIATE 'DROP table test1'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END
• trans1st0r: Can you show the error? I see that with this new string, some statements have whitespace around them, so maybe this will help: sql_commands = [i.strip() for i in sql_commands]

Answer 2

MURRAYLR.test is not acceptable table name in any DBMS I've used. The connection object the cx_oracle.connect returns should already have a schema selected. To switch to a different schema set the current_schema field on the connection object or add using <Schemaname>; in your sql file. Obviously make sure that the schema exists.

• trans1st0r: The missing ; at the end is not an issue. See oracle.com/technetwork/articles/dsl/…
• Lee Murray: nope my SQL work fine & creates the tables in the MURRAYLR schema. Thanks for your suggestion.
• ventsyv: @LeeMurray See the expanded answer.