Write to Postgres from Databricks using Python [duplicate]

Question

I have a dataframe in Databricks called customerDetails.

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

I would like to be able to copy this from Databricks to a table within 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()

Steven Black · Accepted Answer · 2018-04-24 16:09:38Z

0

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

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()

edited Apr 24, 2018 at 16:09

answered Apr 24, 2018 at 15:44

Steven Black

2,2521 gold badge19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Data_101 Over a year ago

Thanks for the reply, I tried this and received an error: 'DataFrame' object has no attribute 'map'

Steven Black Over a year ago

oops, try using the rdd conversion

Data_101 Over a year ago

Thanks. It runs without an error now, only it it doesn't seem to actually copy anything to the table.

Steven Black Over a year ago

hmm, try printing out the row.customerName to make sure it's actually running through each row

Data_101 Over a year ago

it doesn't seem to be printing anything.

|

Collectives™ on Stack Overflow

Write to Postgres from Databricks using Python [duplicate]

1 Answer 1

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Linked

Related