Py and SQL

[Sells, Fred]

I find it easier to code like this

 

Sql = ‘’’select yadda, yadda, yadda

FROM a,b,c

Where this=that

ORDER BY deudderting’’’

 

With the appropriate %s(varname)  and  % against a dictionary rather than positional args, but that’s just me.

 

From: python-list-bounces+frsells=adventistcare.org at python.org [mailto:python-list-bounces+frsells=adventistcare.org at python.org] On Behalf Of Jerry Hill
Sent: Wednesday, November 30, 2011 5:15 PM
To: Verde Denim
Cc: Python list
Subject: Re: Py and SQL

 

On Wed, Nov 30, 2011 at 3:30 PM, Verde Denim <tdldev at gmail.com> wrote:

dbCursor1.execute('select lpad(' ', 2*level) || c "Privilege, Roles and Users" from ( select null p, name c from system_privilege_map where name like upper(\'%&enter_privliege%\') union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs) start with p is null connect by p = prior c')


I think this is your problem.  Your string is delimited with single quotes on the outside ('), but you also have a mix of single and double quotes inside your string.  If you were to assign this to a variable and print it out, you would probably see the problem right away. 

You have two options.  First, you could flip the outer quotes to double quotes, then switch all of the quotes inside the string to single quotes (I think that will work fine in SQL).  Second, you could use a triple-quoted string by switching the outer quotes to ''' or """.  Doing that would let you mix whatever kinds of quotes you like inside your string, like this (untested):

sql = '''select lpad(' ', 2*level) || c "Privilege, Roles and Users" from ( select null p, name c from system_privilege_map where name like upper(\'%&enter_privliege%\') union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs) start with p is null connect by p = prior c'''

dbCursor1.execute(sql)

Once you do that, I think you will find that the "&enter_priviliege" bit in your SQL isn't going to do what you want.  I assume you're expecting that to automatically pop up some sort of dialog box asking the user to enter a value for that variable?  That isn't going to happen in python.  That's a function of the database IDE you use.  You'll need to use python to ask the user for the privilege level, then substitute it into the sql yourself.

-- 
Jerry

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20111201/39cd94c0/attachment-0001.html>