How to load data from PyPI to MySQL Destination

First, ensure that Python and MySQL are installed on your system. For Python, install necessary packages using pip if not already installed: `requests` for fetching data from PyPI, and `mysql-connector-python` for interacting with MySQL. Execute the following commands in your terminal:
```bash
pip install requests mysql-connector-python
```

Write a Python script to fetch data from the PyPI API. Use the `requests` library to make HTTP requests to PyPI. For example, to get package details, you can use:
```python
import requests

def fetch_package_data(package_name):
url = f"https://pypi.org/pypi/{package_name}/json"
response = requests.get(url)
if response.status_code == 200:
return response.json()
else:
raise Exception("Failed to fetch data from PyPI")

package_data = fetch_package_data("example-package")
```

Once you have the JSON data from PyPI, parse and structure it to match the schema of your MySQL database. Extract relevant fields such as package name, version, summary, etc.
```python
def parse_data(data):
package_info = {
'name': data['info']['name'],
'version': data['info']['version'],
'summary': data['info']['summary'],
'author': data['info']['author']
}
return package_info

structured_data = parse_data(package_data)
```

Use the `mysql.connector` library to establish a connection to your MySQL database. Ensure you have the necessary credentials and that the database is accessible.
```python
import mysql.connector

def connect_to_database():
connection = mysql.connector.connect(
host='your_host',
user='your_username',
password='your_password',
database='your_database'
)
return connection

db_connection = connect_to_database()
```

If you don’t already have a table to store the data, create one. Define the schema to match the structured data.
```python
def create_table(connection):
cursor = connection.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS pypi_packages (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
version VARCHAR(50),
summary TEXT,
author VARCHAR(255)
)
''')
connection.commit()

create_table(db_connection)
```

Prepare and execute an SQL statement to insert the structured data into the MySQL table. Handle any exceptions that may arise during this process.
```python
def insert_data(connection, package_info):
cursor = connection.cursor()
sql = '''
INSERT INTO pypi_packages (name, version, summary, author)
VALUES (%s, %s, %s, %s)
'''
values = (package_info['name'], package_info['version'], package_info['summary'], package_info['author'])
cursor.execute(sql, values)
connection.commit()

insert_data(db_connection, structured_data)
```

After successfully inserting the data, close the database connection to ensure no resources are left unnecessarily open.
```python
def close_connection(connection):
connection.close()

close_connection(db_connection)
```

By following these steps, you can directly fetch data from PyPI and store it in a MySQL database using Python, without relying on third-party connectors or integrations. Adjust the code snippets to fit your exact use case, such as handling more fields or different data types as needed.