Writing DataFrame to mysql table using pySpark

Question

I am trying to insert records into a table MySql. The table contains id, and nameas columns.

I do as shown below in the shell pyspark.

name = 'tester_1'
id = '103'  
import pandas as pd
l = [id,name]

df = pd.DataFrame([l])

df.write.format('jdbc').options(
      url='jdbc:mysql://localhost/database_name',
      driver='com.mysql.jdbc.Driver',
      dbtable='DestinationTableName',
      user='your_user_name',
      password='your_password').mode('append').save()

I get an error below the attribute

AttributeError: 'DataFrame' object has no attribute 'write'

What am I doing wrong? What is the correct way to insert records into a table MySqlfrompyspark

Answer 1

Use a Spark DataFrame instead of pandas', as it .writeis available only in the Spark Dataframe

So the final code can be

data =['103', 'tester_1']

df = sc.parallelize(data).toDF(['id', 'name'])

df.write.format('jdbc').options(
      url='jdbc:mysql://localhost/database_name',
      driver='com.mysql.jdbc.Driver',
      dbtable='DestinationTableName',
      user='your_user_name',
      password='your_password').mode('append').save()