Dataframe to SQL Pandas, Also Copy Grants

user_10935257 0
Viewed 159

I've been happily using dataframe to SQL in pandas, but the issue i'm having is that now I need to customize beyond the command and include a SQL statement to copy grants as I want to refresh a table by replacing it, but I don't want to lose the grants on it.

Since it's a function for which I cannot see the underlying SQL and add the grant statement, wondering if there's any work arounds/ what I can do.

(DF to SQL documentation) https://pandas.pydata.org/pandas-docs/version/0.23.4/generated/pandas.DataFrame.to_sql.html

(Snowflake create table documentation that includes copy grants for replacements as an option) https://docs.snowflake.com/en/sql-reference/sql/create-table.html

connection = engine.connect()


df.to_sql("Testtable", con=engine, index=False,chunksize=16000, if_exists='replace')
snowflake-cloud-data-platform pandas python
1 Answers

Yes. This would be a very nice feature of the to_sql() function!

I have solved it by executing a query that drops the table, and then creates it again copying the grants. (Snowflake in my case)

Afterwards I use to_sql() with if_exist='append'.

So like this:

sql = """
create or replace table "ANALYTICS"."PUBLIC"."TEST" COPY GRANTS (
  COUNTRY VARCHAR(16777216),
  DEBT FLOAT,
  POPULATION FLOAT
)
"""

conn.execute(sql)

test.to_sql("test", con=conn, if_exists='append', index=False, method=pd_writer)

So if you for example add or remove columns in your dataframe you have to change the query to match the new table you are trying to push to the database. Would be better if there was an option like if_exists='replace_copy_grants'.

user_171289700
Related