Py and SQL

[Jerry Hill]

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/20111130/45824aac/attachment.html>