Featured Post
How to Write ETL Logic in Python: Sample Code to Practice
- Get link
- Other Apps
Here's an example Python code that uses the mysql-connector library to connect to a MySQL database, extract data from a table, transform it, and load it as a JSON file. Here's an example:
Python ETL Sample Code
import mysql.connector
import json
# Connect to the MySQL database
cnx = mysql.connector.connect(user='username', password='password',
host='localhost',
database='database_name')
# Define a cursor to execute SQL queries
cursor = cnx.cursor()
# Define the SQL query to extract data
query = ("SELECT column1, column2, column3 FROM table_name")
# Execute the SQL query
cursor.execute(query)
# Fetch all rows from the result set
rows = cursor.fetchall()
# Transform the rows into a list of dictionaries
result = []
for row in rows:
result.append({'column1': row[0], 'column2': row[1], 'column3': row[2]})
# Save the result as a JSON file
with open('output.json', 'w') as outfile:
json.dump(result, outfile)
# Close the cursor and database connection
cursor.close()
cnx.close()
In this example, you will need to replace username, password, localhost, database_name, table_name, column1, column2, and column3 with the appropriate values for your MySQL database and table.
The code will extract the data from the specified table, transform it into a list of dictionaries, and save it as a JSON file named output.json.
Comments
Post a Comment
Thanks for your message. We will get back you.