SQL Statement in Python

Question

I am getting one of 2 errors and I don't know why. They are ORA-00911: invalid character and ORA-00933: (something about the statement not ended correctly). I am hoping a fresh set of eyes can help with it. Here is the code:        orcl = cx_Oracle.connect("xyz/xyz@xyz")
        curs = orcl.cursor()
        database = "Database Connections\xyz.sde\xyz.xyz_xyz"
        sql1 = "create view RSIDs as " 
        sql2 = "select * " 
        sql3 = "from "+database+" where "
        sql4 = "xyz like '%RSID%' or xyz like '%m%'"
        sql = str(sql1+sql2+sql3+sql4)
        print sql
        curs.execute(sql)
        curs.close()
        orcl.close()

The put x's in for the sensitive stuff but left all the slashes, comma, quotes etc... the way I have them. I tried several different ways with the slashes because I know python is funny with them but I can't get it to create the view. The statement works in SQLPLus. I am using Python 2.7. Any help is appreciated.

MikePowell1 · Answer

Thanks for looking into it

ShawnThorne · Answer

I was getting the same ORA-00911 & ORA-00933 errors as well.  But after removing the reference to the SDE Connection File, it worked.

import cx_Oracle

def main():

try:

orcl = cx_Oracle.connect("xyz/xyz@xyz/xyz")
        curs = orcl.cursor()

vw = ("COUNTRIES_PY_VIEW")
        fc = ("COUNTRIES")

createView = (" CREATE OR REPLACE VIEW " + vw + " AS (select * from " + fc + " where country like '%States%' or country = 'Canada')")

print ("
 Create View Syntax ---> " + createView)
        curs.execute(createView)
        print ("
 Successfully created the View '" + vw + "'.
")

orcl.close()

print ("
 Completed!! 
")

del orcl
        del curs
        del createView
        del fc
        del vw

except arcpy.ExecuteError:
        print (arcpy.GetMessages(2))

except Exception as e:
        print (e[0])

if __name__ == '__main__':
    main()

Results :

Create View Syntax --->  CREATE OR REPLACE VIEW COUNTRIES_PY_VIEW AS (select * from COUNTRIES where country like '%States%' or country = 'Canada')

Successfully created the View 'COUNTRIES_PY_VIEW'.

Completed!!

MikePowell1 · Answer

Wow, that seem to be it. I took out the "database" variable and just put in the database table in the statement and that worked. It is creating the view. Thanks for the response