USD AUD BRL CAD GBP CHF CLP CNY DKK EUR HKD INR ISK JPY KRW NZD PLN RUB SEK SGD THB TRY TWD

Button

Response: {"ARS":{"15m":1.2366508267E8,"last":1.2366508267E8,"buy":1.2366508267E8,"sell":1.2366508267E8,"symbol":"ARS"},"AUD":{"15m":161178.34,"last":161178.34,"buy":161178.34,"sell":161178.34,"symbol":"AUD"},"BRL":{"15m":580778.16,"last":580778.16,"buy":580778.16,"sell":580778.16,"symbol":"BRL"},"CAD":{"15m":142250.69,"last":142250.69,"buy":142250.69,"sell":142250.69,"symbol":"CAD"},"CHF":{"15m":84977.69,"last":84977.69,"buy":84977.69,"sell":84977.69,"symbol":"CHF"},"CLP":{"15m":9.812005914E7,"last":9.812005914E7,"buy":9.812005914E7,"sell":9.812005914E7,"symbol":"CLP"},"CNY":{"15m":751692.41,"last":751692.41,"buy":751692.41,"sell":751692.41,"symbol":"CNY"},"CZK":{"15m":2250178.71,"last":2250178.71,"buy":2250178.71,"sell":2250178.71,"symbol":"CZK"},"DKK":{"15m":675979.94,"last":675979.94,"buy":675979.94,"sell":675979.94,"symbol":"DKK"},"EUR":{"15m":90626.58,"last":90626.58,"buy":90626.58,"sell":90626.58,"symbol":"EUR"},"GBP":{"15m":77161.2,"last":77161.2,"buy":77161.2,"sell":77161.2,"symbol":"GBP"},"HKD":{"15m":821637.61,"last":821637.61,"buy":821637.61,"sell":821637.61,"symbol":"HKD"},"HRK":{"15m":482804.0,"last":482804.0,"buy":482804.0,"sell":482804.0,"symbol":"HRK"},"HUF":{"15m":3.648364927E7,"last":3.648364927E7,"buy":3.648364927E7,"sell":3.648364927E7,"symbol":"HUF"},"INR":{"15m":9014170.29,"last":9014170.29,"buy":9014170.29,"sell":9014170.29,"symbol":"INR"},"ISK":{"15m":1.368385635E7,"last":1.368385635E7,"buy":1.368385635E7,"sell":1.368385635E7,"symbol":"ISK"},"JPY":{"15m":1.50838801E7,"last":1.50838801E7,"buy":1.50838801E7,"sell":1.50838801E7,"symbol":"JPY"},"KRW":{"15m":1.4295287187E8,"last":1.4295287187E8,"buy":1.4295287187E8,"sell":1.4295287187E8,"symbol":"KRW"},"NGN":{"15m":1.6187514951E8,"last":1.6187514951E8,"buy":1.6187514951E8,"sell":1.6187514951E8,"symbol":"NGN"},"NZD":{"15m":173922.86,"last":173922.86,"buy":173922.86,"sell":173922.86,"symbol":"NZD"},"PLN":{"15m":387030.11,"last":387030.11,"buy":387030.11,"sell":387030.11,"symbol":"PLN"},"RON":{"15m":455363.41,"last":455363.41,"buy":455363.41,"sell":455363.41,"symbol":"RON"},"RUB":{"15m":8350412.47,"last":8350412.47,"buy":8350412.47,"sell":8350412.47,"symbol":"RUB"},"SEK":{"15m":992248.43,"last":992248.43,"buy":992248.43,"sell":992248.43,"symbol":"SEK"},"SGD":{"15m":134133.59,"last":134133.59,"buy":134133.59,"sell":134133.59,"symbol":"SGD"},"THB":{"15m":3389668.36,"last":3389668.36,"buy":3389668.36,"sell":3389668.36,"symbol":"THB"},"TRY":{"15m":4123150.63,"last":4123150.63,"buy":4123150.63,"sell":4123150.63,"symbol":"TRY"},"TWD":{"15m":3092954.27,"last":3092954.27,"buy":3092954.27,"sell":3092954.27,"symbol":"TWD"},"USD":{"15m":104669.21,"last":104669.21,"buy":104669.21,"sell":104669.21,"symbol":"USD"}}```

Certainly! Below is a detailed guide to fetch data from the specified URL (`https://blockchain.info/ticker`) and insert it into a MySQL database using Python. This guide includes all the steps, code, and explanations. ### Step-by-Step Guide #### 1. Install Required Libraries Make sure you have Python installed. Then, use `pip` to install the necessary libraries: ```bash pip install pandas sqlalchemy mysql-connector-python requests ``` #### 2. Python Code to Fetch and Insert Data Here's the complete Python script that fetches data from the Blockchain API and inserts it into a MySQL database: ```python import pandas as pd import requests from sqlalchemy import create_engine # Step 1: Fetch data from the URL url = 'https://blockchain.info/ticker' response = requests.get(url) # Check if the request was successful if response.status_code == 200: data = response.json() else: print(f"Failed to retrieve data: {response.status_code}") exit() # Convert the JSON data into a DataFrame df = pd.DataFrame(data).T.reset_index() df.columns = ['BRL', 'USD', 'AUD', 'CAD', 'sell', 'buy'] # Rename the columns for clarity # Flatten the 'data' column into separate columns df = pd.concat([df['currency'], df['data'].apply(pd.Series)], axis=1) # Step 2: Connect to MySQL Database # Replace 'agenciai_super', ']x)}kJ=Rhc+r', 'hostname', and 'agenciai_rooott' with your actual database credentials engine = create_engine('mysql+mysqlconnector://username:password@hostname/database_name') # Step 3: Insert data into MySQL table # Replace 'tester' with the name of your MySQL table try: df.to_sql('tester', con=engine, if_exists='replace', index=False) print("Data imported successfully!") except Exception as e: print(f"An error occurred: {e}") ``` ### Explanation of the Code 1. **Fetching Data**: - The script sends a GET request to `https://blockchain.info/ticker` to retrieve the latest cryptocurrency ticker data. - It checks if the request was successful by examining the HTTP status code. If the status is not 200 (OK), it prints an error message and exits. 2. **Processing Data**: - The JSON response is converted into a Pandas DataFrame. - The DataFrame is transposed (`.T`) and reset to format it properly. Each currency and its corresponding data are separated into columns. - The 'data' column, which contains nested JSON, is flattened into separate columns using `apply(pd.Series)`. 3. **Database Connection**: - A connection to the MySQL database is established using SQLAlchemy. You need to replace `username`, `password`, `hostname`, and `database_name` with your actual MySQL credentials. - The connection string format is: ```python 'mysql+mysqlconnector://username:password@hostname/database_name' ``` 4. **Inserting Data**: - The DataFrame is inserted into a MySQL table named `tester`. The parameter `if_exists='replace'` means that if the table already exists, it will be replaced. - Error handling is included to catch and print any exceptions that occur during the insertion process. ### Important Notes - Ensure that you have the necessary permissions to create and modify tables in your MySQL database. - If you want to append to an existing table instead of replacing it, change `if_exists='replace'` to `if_exists='append'`. - You may need to adjust the DataFrame's schema (data types, column names) to match your database table structure before inserting it. ### Running the Script To run the script, save it to a file named `insert_data.py`, and execute it using the following command: ```bash python insert_data.py ``` This will fetch the data from the URL and insert it into your specified MySQL table. ### Example MySQL Table Structure If you're not sure what the table structure should look like, here's an example SQL command to create a table that would be compatible with the data being inserted: ```sql CREATE TABLE tester ( currency VARCHAR(10) PRIMARY KEY, symbol VARCHAR(10), last FLOAT, buy FLOAT, sell FLOAT, updated INT ); ``` This command creates a table with columns for each piece of data received from the API. Adjust data types as necessary based on your needs.