使用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.

I找到了使用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屋！