如何在Jupyter Notebook中使用SQLAlchemy授予对DB2表的访问特权 [英] How to grant access privileges to DB2 table using SQLAlchemy in a jupyter notebook

问题描述

我已经按照几个示例在python jupyter笔记本中使用SQLAlchemy创建和删除DB2表.很好但是在创建表之后，我需要设置权限，以便其他人可以查看它. 我使用此代码从Pandas数据框"df"创建新表

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'))

我可以在QMF中运行SQL，它工作正常.它似乎在笔记本电脑上似乎不起作用. 我想知道是否有人看到我需要纠正的缺陷?

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?

谢谢

推荐答案

也许与事务隔离有关，请在授予/撤消之前/之后尝试显式的事务控制，或者配置为自动提交

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')

在本地Db2-LUW上为我工作.

Works for me on Db2-LUW on-premises.

这篇关于如何在Jupyter Notebook中使用SQLAlchemy授予对DB2表的访问特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！