使用 Python 从 Dataricks 写入 Postgres [英] Write to Postgres from Dataricks using Python

问题描述

我在 Databricks 中有一个名为 customerDetails 的数据框.

I have a dataframe in Databricks called customerDetails.

+--------------------+-----------+
|        customerName| customerId|
+--------------------+-----------+
|John Smith          |       0001|
|Jane Burns          |       0002|
|Frank Jones         |       0003|
+--------------------+-----------+

我希望能够将其从 Databricks 复制到 Postgres 中的表中.

I would like to be able to copy this from Databricks to a table within Postgres.

我发现了这个帖子 使用 psycopg2 将单行复制到 Postgres，我试图将每一行从数据帧复制到 postgres 表?

I found this post which used psycopg2 to copy individual lines to Postgres, I am trying to copy each row from the dataframe to the postgres table?

import psycopg2

v1 = 'testing_name'
v2 = 'testing_id'


conn = psycopg2.connect(host="HOST_NAME",
                        port="PORT",
                        user="USER_NAME",
                        password="PASSWORD",
                        database="DATABASE_NAME")

cursor = conn.cursor()
cursor.execute("INSERT INTO customerTable (customerName, customerId) VALUES(%s, %s)", (v1, v2))
conn.commit()
cursor.close()
conn.close()

推荐答案

您可以将所有数据逐行插入表中.

You can insert, row by row, all the data into your table.

也请尽可能查看 cursor.executemany 的文档将您的数据重新排序为元组列表，并将该列表作为最后一个参数传递.

See the documentation for cursor.executemany too as you can reorder your data as a list of tuples and pass the list as the last argument.

代码将与您提供的示例几乎相同

The code will be almost identical to the example you gave

cursor = conn.cursor()
def append_to_table(row):
    cursor.execute("INSERT INTO customerTable (customerName, customerId) VALUES(%s, %s)", (row.customerName, row.customerId))

df.rdd.map(append_to_table)
conn.commit()
cursor.close()
conn.close()

这篇关于使用 Python 从 Dataricks 写入 Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！