execute a sql script file from cx_oracle?

Multi tool use
Is there a way to execute a sql script file using cx_oracle in python.
I need to execute my create table scripts in sql files.
python database cx-oracle
add a comment |
Is there a way to execute a sql script file using cx_oracle in python.
I need to execute my create table scripts in sql files.
python database cx-oracle
add a comment |
Is there a way to execute a sql script file using cx_oracle in python.
I need to execute my create table scripts in sql files.
python database cx-oracle
Is there a way to execute a sql script file using cx_oracle in python.
I need to execute my create table scripts in sql files.
python database cx-oracle
python database cx-oracle
asked Sep 23 '11 at 17:11
user747858
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
PEP-249, which cx_oracle tries to be compliant with, doesn't really have a method like that.
However, the process should be pretty straight forward. Pull the contents of the file into a string, split it on the ";" character, and then call .execute on each member of the resulting array. I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file.
f = open('tabledefinition.sql')
full_sql = f.read()
sql_commands = full_sql.split(';')
for sql_command in sql_commands:
curs.execute(sql_command)
3
Splitting on;
will fail if there are string literals that contain semicolons; this might take a little bit of simple scanning.
– 9000
Sep 23 '11 at 17:39
1
Agreed - that was my point when I said "I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file." You stated the obvious issue more clearly though. Thanks!
– chipchilders
Sep 26 '11 at 17:44
Also assumes that the only delimiter is;
. Which is usually the case, but if the sql script contains things like stored procedures, then the delimiter gets changed before defining the procedure...
– Lucas
Mar 20 at 20:22
add a comment |
Another option is to use SQL*Plus (Oracle's command line tool) to run the script. You can call this from Python using the subprocess
module - there's a good walkthrough here: http://moizmuhammad.wordpress.com/2012/01/31/run-oracle-commands-from-python-via-sql-plus/.
For a script like tables.sql
(note the deliberate error):
CREATE TABLE foo ( x INT );
CREATE TABLER bar ( y INT );
You can use a function like the following:
from subprocess import Popen, PIPE
def run_sql_script(connstr, filename):
sqlplus = Popen(['sqlplus','-S', connstr], stdin=PIPE, stdout=PIPE, stderr=PIPE)
sqlplus.stdin.write('@'+filename)
return sqlplus.communicate()
connstr
is the same connection string used for cx_Oracle
. filename
is the full path to the script (e.g. 'C:temptables.sql'
). The function opens a SQLPlus session (with '-S' to silence its welcome message), then queues "@filename" to send to it - this will tell SQLPlus to run the script.
sqlplus.communicate
sends the command to stdin, waits for the SQL*Plus session to terminate, then returns (stdout, stderr) as a tuple. Calling this function with tables.sql
above will give the following output:
>>> output, error = run_sql_script(connstr, r'C:temptables.sql')
>>> print output
Table created.
CREATE TABLER bar (
*
ERROR at line 1:
ORA-00901: invalid CREATE command
>>> print error
This will take a little parsing, depending on what you want to return to the rest of your program - you could show the whole output to the user if it's interactive, or scan for the word "ERROR" if you just want to check whether it ran OK.
the accepted answer does not work in the case of PL/SQL .. this sqlplus bit does ok in that case.
– Skylar Saveland
Aug 20 '13 at 21:54
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f7532534%2fexecute-a-sql-script-file-from-cx-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
PEP-249, which cx_oracle tries to be compliant with, doesn't really have a method like that.
However, the process should be pretty straight forward. Pull the contents of the file into a string, split it on the ";" character, and then call .execute on each member of the resulting array. I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file.
f = open('tabledefinition.sql')
full_sql = f.read()
sql_commands = full_sql.split(';')
for sql_command in sql_commands:
curs.execute(sql_command)
3
Splitting on;
will fail if there are string literals that contain semicolons; this might take a little bit of simple scanning.
– 9000
Sep 23 '11 at 17:39
1
Agreed - that was my point when I said "I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file." You stated the obvious issue more clearly though. Thanks!
– chipchilders
Sep 26 '11 at 17:44
Also assumes that the only delimiter is;
. Which is usually the case, but if the sql script contains things like stored procedures, then the delimiter gets changed before defining the procedure...
– Lucas
Mar 20 at 20:22
add a comment |
PEP-249, which cx_oracle tries to be compliant with, doesn't really have a method like that.
However, the process should be pretty straight forward. Pull the contents of the file into a string, split it on the ";" character, and then call .execute on each member of the resulting array. I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file.
f = open('tabledefinition.sql')
full_sql = f.read()
sql_commands = full_sql.split(';')
for sql_command in sql_commands:
curs.execute(sql_command)
3
Splitting on;
will fail if there are string literals that contain semicolons; this might take a little bit of simple scanning.
– 9000
Sep 23 '11 at 17:39
1
Agreed - that was my point when I said "I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file." You stated the obvious issue more clearly though. Thanks!
– chipchilders
Sep 26 '11 at 17:44
Also assumes that the only delimiter is;
. Which is usually the case, but if the sql script contains things like stored procedures, then the delimiter gets changed before defining the procedure...
– Lucas
Mar 20 at 20:22
add a comment |
PEP-249, which cx_oracle tries to be compliant with, doesn't really have a method like that.
However, the process should be pretty straight forward. Pull the contents of the file into a string, split it on the ";" character, and then call .execute on each member of the resulting array. I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file.
f = open('tabledefinition.sql')
full_sql = f.read()
sql_commands = full_sql.split(';')
for sql_command in sql_commands:
curs.execute(sql_command)
PEP-249, which cx_oracle tries to be compliant with, doesn't really have a method like that.
However, the process should be pretty straight forward. Pull the contents of the file into a string, split it on the ";" character, and then call .execute on each member of the resulting array. I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file.
f = open('tabledefinition.sql')
full_sql = f.read()
sql_commands = full_sql.split(';')
for sql_command in sql_commands:
curs.execute(sql_command)
answered Sep 23 '11 at 17:35
chipchilders
1123
1123
3
Splitting on;
will fail if there are string literals that contain semicolons; this might take a little bit of simple scanning.
– 9000
Sep 23 '11 at 17:39
1
Agreed - that was my point when I said "I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file." You stated the obvious issue more clearly though. Thanks!
– chipchilders
Sep 26 '11 at 17:44
Also assumes that the only delimiter is;
. Which is usually the case, but if the sql script contains things like stored procedures, then the delimiter gets changed before defining the procedure...
– Lucas
Mar 20 at 20:22
add a comment |
3
Splitting on;
will fail if there are string literals that contain semicolons; this might take a little bit of simple scanning.
– 9000
Sep 23 '11 at 17:39
1
Agreed - that was my point when I said "I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file." You stated the obvious issue more clearly though. Thanks!
– chipchilders
Sep 26 '11 at 17:44
Also assumes that the only delimiter is;
. Which is usually the case, but if the sql script contains things like stored procedures, then the delimiter gets changed before defining the procedure...
– Lucas
Mar 20 at 20:22
3
3
Splitting on
;
will fail if there are string literals that contain semicolons; this might take a little bit of simple scanning.– 9000
Sep 23 '11 at 17:39
Splitting on
;
will fail if there are string literals that contain semicolons; this might take a little bit of simple scanning.– 9000
Sep 23 '11 at 17:39
1
1
Agreed - that was my point when I said "I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file." You stated the obvious issue more clearly though. Thanks!
– chipchilders
Sep 26 '11 at 17:44
Agreed - that was my point when I said "I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file." You stated the obvious issue more clearly though. Thanks!
– chipchilders
Sep 26 '11 at 17:44
Also assumes that the only delimiter is
;
. Which is usually the case, but if the sql script contains things like stored procedures, then the delimiter gets changed before defining the procedure...– Lucas
Mar 20 at 20:22
Also assumes that the only delimiter is
;
. Which is usually the case, but if the sql script contains things like stored procedures, then the delimiter gets changed before defining the procedure...– Lucas
Mar 20 at 20:22
add a comment |
Another option is to use SQL*Plus (Oracle's command line tool) to run the script. You can call this from Python using the subprocess
module - there's a good walkthrough here: http://moizmuhammad.wordpress.com/2012/01/31/run-oracle-commands-from-python-via-sql-plus/.
For a script like tables.sql
(note the deliberate error):
CREATE TABLE foo ( x INT );
CREATE TABLER bar ( y INT );
You can use a function like the following:
from subprocess import Popen, PIPE
def run_sql_script(connstr, filename):
sqlplus = Popen(['sqlplus','-S', connstr], stdin=PIPE, stdout=PIPE, stderr=PIPE)
sqlplus.stdin.write('@'+filename)
return sqlplus.communicate()
connstr
is the same connection string used for cx_Oracle
. filename
is the full path to the script (e.g. 'C:temptables.sql'
). The function opens a SQLPlus session (with '-S' to silence its welcome message), then queues "@filename" to send to it - this will tell SQLPlus to run the script.
sqlplus.communicate
sends the command to stdin, waits for the SQL*Plus session to terminate, then returns (stdout, stderr) as a tuple. Calling this function with tables.sql
above will give the following output:
>>> output, error = run_sql_script(connstr, r'C:temptables.sql')
>>> print output
Table created.
CREATE TABLER bar (
*
ERROR at line 1:
ORA-00901: invalid CREATE command
>>> print error
This will take a little parsing, depending on what you want to return to the rest of your program - you could show the whole output to the user if it's interactive, or scan for the word "ERROR" if you just want to check whether it ran OK.
the accepted answer does not work in the case of PL/SQL .. this sqlplus bit does ok in that case.
– Skylar Saveland
Aug 20 '13 at 21:54
add a comment |
Another option is to use SQL*Plus (Oracle's command line tool) to run the script. You can call this from Python using the subprocess
module - there's a good walkthrough here: http://moizmuhammad.wordpress.com/2012/01/31/run-oracle-commands-from-python-via-sql-plus/.
For a script like tables.sql
(note the deliberate error):
CREATE TABLE foo ( x INT );
CREATE TABLER bar ( y INT );
You can use a function like the following:
from subprocess import Popen, PIPE
def run_sql_script(connstr, filename):
sqlplus = Popen(['sqlplus','-S', connstr], stdin=PIPE, stdout=PIPE, stderr=PIPE)
sqlplus.stdin.write('@'+filename)
return sqlplus.communicate()
connstr
is the same connection string used for cx_Oracle
. filename
is the full path to the script (e.g. 'C:temptables.sql'
). The function opens a SQLPlus session (with '-S' to silence its welcome message), then queues "@filename" to send to it - this will tell SQLPlus to run the script.
sqlplus.communicate
sends the command to stdin, waits for the SQL*Plus session to terminate, then returns (stdout, stderr) as a tuple. Calling this function with tables.sql
above will give the following output:
>>> output, error = run_sql_script(connstr, r'C:temptables.sql')
>>> print output
Table created.
CREATE TABLER bar (
*
ERROR at line 1:
ORA-00901: invalid CREATE command
>>> print error
This will take a little parsing, depending on what you want to return to the rest of your program - you could show the whole output to the user if it's interactive, or scan for the word "ERROR" if you just want to check whether it ran OK.
the accepted answer does not work in the case of PL/SQL .. this sqlplus bit does ok in that case.
– Skylar Saveland
Aug 20 '13 at 21:54
add a comment |
Another option is to use SQL*Plus (Oracle's command line tool) to run the script. You can call this from Python using the subprocess
module - there's a good walkthrough here: http://moizmuhammad.wordpress.com/2012/01/31/run-oracle-commands-from-python-via-sql-plus/.
For a script like tables.sql
(note the deliberate error):
CREATE TABLE foo ( x INT );
CREATE TABLER bar ( y INT );
You can use a function like the following:
from subprocess import Popen, PIPE
def run_sql_script(connstr, filename):
sqlplus = Popen(['sqlplus','-S', connstr], stdin=PIPE, stdout=PIPE, stderr=PIPE)
sqlplus.stdin.write('@'+filename)
return sqlplus.communicate()
connstr
is the same connection string used for cx_Oracle
. filename
is the full path to the script (e.g. 'C:temptables.sql'
). The function opens a SQLPlus session (with '-S' to silence its welcome message), then queues "@filename" to send to it - this will tell SQLPlus to run the script.
sqlplus.communicate
sends the command to stdin, waits for the SQL*Plus session to terminate, then returns (stdout, stderr) as a tuple. Calling this function with tables.sql
above will give the following output:
>>> output, error = run_sql_script(connstr, r'C:temptables.sql')
>>> print output
Table created.
CREATE TABLER bar (
*
ERROR at line 1:
ORA-00901: invalid CREATE command
>>> print error
This will take a little parsing, depending on what you want to return to the rest of your program - you could show the whole output to the user if it's interactive, or scan for the word "ERROR" if you just want to check whether it ran OK.
Another option is to use SQL*Plus (Oracle's command line tool) to run the script. You can call this from Python using the subprocess
module - there's a good walkthrough here: http://moizmuhammad.wordpress.com/2012/01/31/run-oracle-commands-from-python-via-sql-plus/.
For a script like tables.sql
(note the deliberate error):
CREATE TABLE foo ( x INT );
CREATE TABLER bar ( y INT );
You can use a function like the following:
from subprocess import Popen, PIPE
def run_sql_script(connstr, filename):
sqlplus = Popen(['sqlplus','-S', connstr], stdin=PIPE, stdout=PIPE, stderr=PIPE)
sqlplus.stdin.write('@'+filename)
return sqlplus.communicate()
connstr
is the same connection string used for cx_Oracle
. filename
is the full path to the script (e.g. 'C:temptables.sql'
). The function opens a SQLPlus session (with '-S' to silence its welcome message), then queues "@filename" to send to it - this will tell SQLPlus to run the script.
sqlplus.communicate
sends the command to stdin, waits for the SQL*Plus session to terminate, then returns (stdout, stderr) as a tuple. Calling this function with tables.sql
above will give the following output:
>>> output, error = run_sql_script(connstr, r'C:temptables.sql')
>>> print output
Table created.
CREATE TABLER bar (
*
ERROR at line 1:
ORA-00901: invalid CREATE command
>>> print error
This will take a little parsing, depending on what you want to return to the rest of your program - you could show the whole output to the user if it's interactive, or scan for the word "ERROR" if you just want to check whether it ran OK.
edited Oct 9 '12 at 10:22
answered Oct 9 '12 at 10:04


Neil Vass
3,30821622
3,30821622
the accepted answer does not work in the case of PL/SQL .. this sqlplus bit does ok in that case.
– Skylar Saveland
Aug 20 '13 at 21:54
add a comment |
the accepted answer does not work in the case of PL/SQL .. this sqlplus bit does ok in that case.
– Skylar Saveland
Aug 20 '13 at 21:54
the accepted answer does not work in the case of PL/SQL .. this sqlplus bit does ok in that case.
– Skylar Saveland
Aug 20 '13 at 21:54
the accepted answer does not work in the case of PL/SQL .. this sqlplus bit does ok in that case.
– Skylar Saveland
Aug 20 '13 at 21:54
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f7532534%2fexecute-a-sql-script-file-from-cx-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
q,R8yravEmNv8Hr3,8FBw,hhF16dJ UGpW,V6H