0

I've followed several examples to create and drop a DB2 table using SQLAlchemy within a python jupyter notebook. That works fine. But after creating the table, I need to set privileges so others can view it. I use this code to create a new table from a Pandas dataframe "df"

from sqlalchemy import create_engine, text
engine = create_engine(r"...")

df.to_sql(name='MYTABLE', schema='MYSCHEMA', con=engine, if_exists='replace', dtype=dashdb_typemap, index=False)

I can drop the table just fine with this code:

with engine.connect() as con:
    con.execute('DROP TABLE MYSCHEMA.MYTABLE')

But neither of these work to set permissions:

with engine.connect() as con:
    con.execute('GRANT ALL ON MYSCHEMA.MYTABLE TO PUBLIC')

with engine.connect() as con:
    con.execute(text('GRANT ALL ON MYSCHEMA.MYTABLE TO PUBLIC'))

I can run the SQL in QMF and it works fine. It just doesn't seem to work from the notebook. I'm wondering if anyone sees the flaw I need to correct?

Thanks

user3597723
  • 63
  • 1
  • 5
  • What Db2 version and platform? What do you mean by "neither of these work"? If you get an error, post it verbatim. – mustaccio Oct 29 '19 at 13:05
  • It's IBM's DB2 in the Cloud. Neither work in that the permissions are not granted when I run either of the two. I get no errors – user3597723 Oct 29 '19 at 23:43

1 Answers1

1

Maybe connected with transaction isolation, try explicit transaction control before/after the grant/revoke, or configure for autocommit

with engine.connect() as con:
    con.execute('COMMIT')
    con.execute('GRANT ALL ON MYSCHEMA.MYTABLE TO PUBLIC')
    con.execute('COMMIT')

Works for me on Db2-LUW on-premises.

mao
  • 11,321
  • 2
  • 13
  • 29