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

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? — Vini.g.fer
– Vini.g.fer, Commented Apr 12, 2016 at 13:49
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). — Vini.g.fer
– Vini.g.fer, Commented Apr 12, 2016 at 13:54
not sure if this is relevant, but are the curly braces around the statements necessary? — trans1st0r
– trans1st0r, Commented Apr 12, 2016 at 13:54
@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. — trans1st0r
– trans1st0r, Commented Apr 12, 2016 at 14:19

trans1st0r · Accepted Answer · 2016-04-12 14:30:24Z

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.

edited Apr 12, 2016 at 14:30

answered Apr 12, 2016 at 14:24

trans1st0r

2,0832 gold badges18 silver badges23 bronze badges

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

2 Comments

Lee Murray Over a year ago

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 Over a year ago

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]

ventsyv · Accepted Answer · 2016-04-12 14:26:40Z

0

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.

edited Apr 12, 2016 at 14:26

answered Apr 12, 2016 at 14:10

ventsyv

3,5725 gold badges31 silver badges61 bronze badges

3 Comments

trans1st0r Over a year ago

The missing ; at the end is not an issue. See oracle.com/technetwork/articles/dsl/…

Lee Murray Over a year ago

nope my SQL work fine & creates the tables in the MURRAYLR schema. Thanks for your suggestion.

ventsyv Over a year ago

@LeeMurray See the expanded answer.

Collectives™ on Stack Overflow

Running a entire SQL script via python

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related