Run SQL from Python

Question

I have looked at how to run an .sql from my python code (e.g. execute *.sql file with python MySQLdb) but have been unable to make it work.

Here is my Python which calls the sql:

import arcpy, cx_Oracle
connection = cx_Oracle.connect('p760/123.abc@oraclebd01')
cursor = connection.cursor()
SDE = "C:\\Users\\david\\AppData\\Roaming\\ESRI\\Desktop10.1\\ArcCatalog\\p760.sde"
CodeSpace = r"G:\Projects\P760\Spatial_Processing\CODE\SQL\\"

fd = open(CodeSpace + 'run_pre_landbase_v1.sql', 'r')
sqlFile = fd.read()
fd.close()
sqlCommands = sqlFile.split(';')
for command in sqlCommands:
    print command
    try:
        cursor.execute(command)
    except:
        print "Command skipped"

Here is the SQL code that works fine when I press F5 in Toad for Oracle:

/* Create initial landbase leading to the eliminate and proxy functions outside 
   of ORACLE. Create table event MUx requires editing if there are more than 2
   FMUs to process. The multiunion occurs in ARCMAP. Attempt to have the fields 
   listed below in the file and as spelt. The creation of the multiunion is not 
   currently coded, so, must be manually created*/

--Define over-arching variables;
DEFINE STRATA = 'STRATA_TABLE'; --INPUT
DEFINE LB = 'Pre_Landbase_v1'; -- OUTPUT
DEFINE MU = 'ffp_mu_v2'; -- OUTPUT
DEFINE REFYEAR = '2015'; -- VARIABLE

/*Define Multiunion Files. Add additional MUx where Landbase has more than 2 
FMUs. Also add an additional UNION to the MULTIUNION table creation code below.
Insert other DEFINE's for extra FMU's where required.*/
DEFINE MUa = 'ffp_mu_v1'; -- INPUT TEST
--DEFINE MUb = 'ZZ_MUb' -- INPUT TEST
--DEFINE MUa = 'MU_TABLE_XX' -- INPUT FROM PYTHON
--DEFINE MUb = 'MU_TABLE_XX' -- INPUT FROM PYTHON

/*Define column definitions for MultiUnion. */
DEFINE UKEY = 'MKEY';
DEFINE MU1 = 'feature_ty'; --LAKE, RIVER, etc.
DEFINE MU2 = 'HYDROBUF';
--DEFINE MU3 = 'TRAPLINES';
--DEFINE MU4 = 'WATERSHED';
DEFINE MU5 = 'DISP_TYPE';
DEFINE MU6 = 'FIRE_NUMbe';
DEFINE MU7 = 'YEAR';
--DEFINE MU8 = 'OPEN_ID'; -- ARIS ID for cutblock
--DEFINE MU9 = 'CC_TYPE'; --Cutblock type (e.g. complete, planned, priority, etc.)
--DEFINE MU10 = 'CC_CATGRY'; --Cutblock Category (e.g. RSA, NONE)
DEFINE MU11 = 'UKEY'; -- AVI link
DEFINE MU12 = 'POLYGON'; -- RSA SU/Polygon number
DEFINE MU13 = 'AREA_HA';
--DEFINE MU14 = 'SLOPE';
DEFINE MU15 = 'STATUS';
--DEFINE MU16 = 'FRST_NTNS';
DEFINE MU17 = 'PPA'; --Parks and Protected Areas
--DEFINE MU18 = 'MUNIC'; --Municipalities
--DEFINE MU19 = 'FIRESMART';
DEFINE MU20 = 'PSP';
DEFINE MU21 = 'kde_contou';
DEFINE MU22 = 'subunit';
DEFINE MU23 = 'QQ';
DEFINE MU24 = 'RR';
DEFINE MU25 = 'SS';
DEFINE MU26 = 'TT';
DEFINE MU27 = 'UU';
DEFINE MU28 = 'VV';
DEFINE MU29 = 'WW';
DEFINE MU30 = 'XX';

-------------------------Create the Multiunion Table----------------------------
DROP TABLE MUx;COMMIT;
CREATE TABLE MUx AS
SELECT * FROM &&MUa
--Insert extra UNION, SELECT for other FMU's where required.
;
COMMIT;

DROP TABLE &&MU;COMMIT;
CREATE TABLE &&MU AS SELECT
&&UKEY MU_UKEY,  UPPER(TRIM(&&MU1)) HYDROLOGY, UPPER(TRIM(&MU2)) HYDRO_BUF,  
UPPER(TRIM(&&MU5)) DIDS_TYPE, UPPER(TRIM(&&MU6)) FIRE_NUM, 
UPPER(TRIM(&&MU7)) FIRE_YEAR,
UPPER(TRIM(&&MU11)) UKEY,  'X' POLYGON, &&MU13 AREA_HA, 
UPPER(TRIM(&&MU17)) PPA, 'X' OPEN_ID,
UPPER(TRIM(&&MU20)) PSP, UPPER(TRIM(&&MU21)) KDE, UPPER(TRIM(&&MU22)) CaribouRNG/*, 
UPPER(TRIM(&&MU23)) QQ, UPPER(TRIM(&&MU24)) RR, UPPER(TRIM(&&MU25)) SS, 
UPPER(TRIM(&&MU26)) TT, UPPER(TRIM(&&MU27)) UU, UPPER(TRIM(&&MU28)) VV, 
UPPER(TRIM(&&MU29)) WW, UPPER(TRIM(&&MU30)) XX)*/
FROM MUx;
COMMIT;
DROP TABLE MUx; COMMIT;

I am a bit lost, and don't understand everything that may be affecting my call in python. I have made connections using this method previously but in these cases I ran individual lines of code which were written in python. This is my first time trying to run a full SQL.

Can you share what your editor/console is outputting as the error? — FirebladeDan, Jul 17 '15 at 17:11
"doesn't work" is possibly the least useful problem description. What do you expect, and what happens ? — bruno desthuilliers, Jul 17 '15 at 17:33
Change your 'except' block so that the error is displayed instead of hidden. Eg: `import traceback` then `except: traceback.print_exc(1)` Then you'll have the error message and can add it to your question. — dsh, Jul 17 '15 at 17:43

score 1 · Answer 1 · answered Jul 17 '15 at 17:44

Since you didn't care to describe your problem (cf my comment about the usefulness of "doesn't work" as a problem's description), we can only guess, but here are three obvious missing steps:

committing your transaction
closing your cursor
closing your db connection.

If your problem is that "I have no exception, traceback or whatsoever but the database is not updated", then 1. is very probably the culprit.

Also, your bare except clause really doesn't help debugging. You should only catch expected exceptions (I don't know what cx_Oracle may raise but that's hopefully documented), or at least print the exception:

for command in sqlCommands:
    print command
    try:
        cursor.execute(command)
    except exception as e:
        print "Command failed with %s - skipped" % e

And finally (no pun intended), you should improve the parsing of the SQL file - skip the "/* */" comment sections, remove the "-- comment" parts etc.

Or since Oracle certainly has a command line tool that can run a sql file, you could just run it with os.system() or a subprocess...

Run SQL from Python

1 Answers1