topic Re: SQL Statement in Python in Python Questions

SQL Statement in Python

MikePowell — Wed, 05 Feb 2014 17:09:37 GMT

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("xxx/xxx@xxx")
        curs = orcl.cursor()
        database = "Database Connections\\xxx.sde\\xxx.xxx_xxx"
        sql1 = "create view RSIDs as " 
        sql2 = "select * " 
        sql3 = "from "+database+" where "
        sql4 = "xxxx like '%RSID%' or xxxxx 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.

Re: SQL Statement in Python

David_JAnderson — Sat, 11 Dec 2021 11:14:08 GMT

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("xxx/xxx@xxx")
        curs = orcl.cursor()
        database = "Database Connections\\xxx.sde\\xxx.xxx_xxx"
        sql1 = "create view RSIDs as " 
        sql2 = "select * " 
        sql3 = "from "+database+" where "
        sql4 = "xxxx like '%RSID%' or xxxxx 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.

Have you checked the value of the database variable? I think in that in the SQL statement it needs to just be the table name, without the Database Connections\\xxx.sde\\ prefix. I am not sure why you have that as a variable as it does not seem to change. You might also consider using a multiline string instead of a bunch of concatenated together variables.

Re: SQL Statement in Python

MikePowell — Thu, 06 Feb 2014 19:00:25 GMT

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

Re: SQL Statement in Python

ShawnThorne — Sat, 11 Dec 2021 11:14:10 GMT

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("xxxx/xxxx@XXXXXX/XXXXXX")
        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 ("\n Create View Syntax ---> " + createView)
        curs.execute(createView)
        print ("\n Successfully created the View '" + vw + "'.\n")

        orcl.close()

        print ("\n Completed!! \n")

        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!!

Re: SQL Statement in Python

MikePowell — Thu, 06 Feb 2014 20:23:04 GMT

Thanks for looking into it