使用“更新”并“设置”在Python中更新雪花表

Question

我一直在使用Python将数据和写入雪花读取数据已有一段时间了 我拥有的完整更新权，请使用我的同事在互联网上发现的雪花帮手课程。请参阅下面的课程，其中包括我的个人雪花连接信息，并在模式中使用了“测试”表。

from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine
import keyring
import pandas as pd
from sqlalchemy import text

# Pull the username and password to be used to connect to snowflake
stored_username = keyring.get_password('my_username', 'username')
stored_password = keyring.get_password('my_password', 'password')

class SNOWDBHelper:

    def __init__(self):
        self.user = stored_username
        self.password = stored_password
        self.account = 'account'
        self.authenticator = 'authenticator'
        self.role = stored_username + '_DEV_ROLE'
        self.warehouse = 'warehouse'
        self.database = 'database'
        self.schema = 'schema'

    def __connect__(self):
        self.url = URL(
            user=stored_username,
            password=stored_password,
            account='account',
            authenticator='authenticator',
            role=stored_username + '_DEV_ROLE',
            warehouse='warehouse',
            database='database',
            schema='schema'

        )
        # =============================================================================
        self.url = URL(
            user=self.user,
            password=self.password,
            account=self.account,
            authenticator=self.authenticator,
            role=self.role,
            warehouse=self.warehouse,
            database=self.database,
            schema=self.schema

        )
        self.engine = create_engine(self.url)
        self.connection = self.engine.connect()

    def __disconnect__(self):
        self.connection.close()

    def read(self, sql):
        self.__connect__()
        result = pd.read_sql_query(sql, self.engine)
        self.__disconnect__()
        return result

    def write(self, wdf, tablename):
        self.__connect__()
        wdf.to_sql(tablename.lower(), con=self.engine, if_exists='append', index=False)
        self.__disconnect__()

# Initiate the SnowDBHelper()
SNOWDB = SNOWDBHelper()

query = """SELECT * FROM """ + 'TEST'
snow_table = SNOWDB.read(query)

我现在需要更新现有的雪花表，我的同事建议我可以使用读取功能将包含更新SQL的查询发送到我的雪花表。因此，我改编了一个更新查询，我在雪花UI中成功使用了更新表，并使用读取功能将其发送到雪花。 它实际上告诉我表中的相关行已更新，但是它们没有。请参阅下面的更新查询，我用来尝试更改“测试”表中的字段“”字段， “ X”和我回来的成功消息。整体上不喜欢这种黑客更新尝试方法（表更新是各种副作用？），但是有人可以帮助您在此框架内更新的方法吗？

# Query I actually store in file: '0-Query-Update-Effective-Dating.sql'
UPDATE "Database"."Schema"."Test" AS UP
SET UP.FIELD = 'X'

# Read the query in from file and utilize it
update_test = open('0-Query-Update-Effective-Dating.sql')
update_query = text(update_test.read())
SNOWDB.read(update_query)

# Returns message of updated rows, but no rows updated
   number of rows updated  number of multi-joined rows updated
0                     316                                    0

Answer 1

sql2pandas |更新熊猫中的行