英文:
How to run delete query on Memsql/singlestore using pyspark
问题
我能够连接到SingleStore并使用spark.read.format("memsql")将数据加载到数据框中，还可以使用dataframe.write.format("memsql")将数据写入SingleStore。
但是，我需要运行一个删除查询，例如：Delete from tableA where id=1; 通过我的Pyspark代码删除SingleStore中的记录。
我无法在SingleStore网站中找到任何相关文档。
英文:
I am able to connect to singlestore and load the data into a dataframe using
spark.read.format("memsql") and also write to singlestore using dataframe.write.format("memsql")
But I need to run a delete query for ex: Delete from tableA where id=1; to delete a record in singlestore through my pyspark code
I am not able to find any documentation in the singlestore website.
答案1
得分: 1
SingleStore的pyspark连接器允许Python使用与SingleStore连接的数据框架。所有数据框架都是不可变的（无法更改）和WORM（只写一次，多次读取）。要使用的技术是通过创建第二个数据框架来过滤数据框架。
df = spark.createDataFrame(data)
df2 = df.where(id = 1)
df2.show()
你可以在Python中使用不同的连接方法来删除行。SingleStoreDB Python连接器允许你在不使用数据框架的情况下连接，然后使用标准SQL来删除行。
import singlestoredb as s2
conn = s2.connect(host='localhost', port='3306', user='root', password='passkey', database='dbTest', results_type='tuple')
with conn:
conn.autocommit(True)
with conn.cursor() as cur:
cur.execute('DELETE FROM testID WHERE ID = 2')
cur.execute('SELECT * FROM testID')
for row in cur.fetchall():
print(row)
详细信息可参考文档链接：https://docs.singlestore.com/db/v8.0/en/developer-resources/connect-with-application-development-tools/connect-with-python/connect-using-the-singlestoredb-python-client.html#delete-operation-example-755699
英文:
SingleStore’s pyspark connector allows python to use dataframes connected to SingleStore. All dataframes are immutable (unable to be changed) and WORM (write once, read many times). The technique to be used is to filter the dataframe by creating a second dataframe.
df = spark.createDataFrame(data)
df2 = df.where(id = 1)
df2.show()
You can use a different connection method in python to delete the rows. The SingleStoreDB Python Connector will allow you to connect without a dataframe and then use standard SQL to delete the row.
import singlestoredb as s2
conn = s2.connect(host='localhost', port='3306', user='root', password='passkey', database='dbTest', results_type='tuple')
with conn:
conn.autocommit(True)
with conn.cursor() as cur:
cur.execute('DELETE FROM testID WHERE ID = 2')
cur.execute('SELECT * FROM testID')
for row in cur.fetchall():
print(row)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论