How to execute .sql file on cx_oracle?

Question

I want to execute a .sql file using cx_Oracle. I have to execute many files and the statements may or may not contain ';' before termination. I have gone though following solution

f = open('tabledefinition.sql')
full_sql = f.read()
sql_commands = full_sql.split(';')

for sql_command in sql_commands:
    curs.execute(sql_command)

but this does not work. Is there any way to pass file as parameter like i do for connection as follows:

dsn_tns = cx_Oracle.makedsn('server', 'port', service_name='service_name')
conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns)

like sql_file = 'mysql.sql' ?

What do you mean by "does not work."? Please add the relevant error messages you get. Also, splitting an sql with semicolon may work for multiple SQL queries, but will definitely fail for PL/SQL blocks. Show us some sample entries in the sql file which you want to run. — Kaushik Nayak
– Kaushik Nayak, Commented Jan 30, 2019 at 6:48
Ideally from command prompt I used command -> user/password@host:port/db_service .@my_sql.sql . so I want to know if there is a way to have that .@my_sql.sql as parameter to connect function ? — Elric
– Elric, Commented Jan 30, 2019 at 7:12
So, what about the other answer from the question you copied that code snipped from? — shmee
– shmee, Commented Jan 30, 2019 at 7:14
I can do that using os.system or subprocess.Popen() but my question is to achieve my goal by using cx_Oracle — Elric
– Elric, Commented Jan 30, 2019 at 7:27
In that case you have obviously seen and tried the correct answer. It still applies; there is no way to pass a file to cx_oracle. Even if there was, it would still rely on the file to have the correct format (just as much as the solution you tried does). So, if you need help with bringing the file content into a format that cx_oracle can parse, then please add the information @KaushikNayak asked for. — shmee
– shmee, Commented Jan 30, 2019 at 7:59

Community · Accepted Answer · 2021-12-22 06:07:11Z

1

You can run the SQL as is using the module:

from subprocess import Popen, PIPE

Then:

sqlrun = Popen(['sqlplus', 'User/Pass@DBINSTANCE'], shell=False , stdin=PIPE, stdout=PIPE, stderr=PIPE , encoding='utf8')

sqlrun.stdin.write('@' + 'tabledefinition.sql')

stdout, stderr = sqlrun.communicate()

return status: sqlrun.returncode
Output strings: stdout, stderr

This for Unix shell execution. NOTE: also tested successfull on powershell

edited Dec 22, 2021 at 6:07

CommunityBot

11 silver badge

answered Mar 3, 2021 at 7:51

Avner Cohen

901 gold badge1 silver badge5 bronze badges

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

Collectives™ on Stack Overflow

How to execute .sql file on cx_oracle?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related