First, you need to install requests to make API calls and duckdb to interact with the DuckDB database.

pip install requests duckdb

‍

Use the PyPI JSON API to fetch the data you’re interested in. For example, to get information about a package called requests, you can do the following:

import requests

package_name = 'requests'
response = requests.get(f'https://pypi.org/pypi/{package_name}/json')
data = response.json()

From the JSON response, extract the data you need. For example, you might be interested in the version, release date, and download count.

releases = data['releases']
for version, downloads in releases.items():
   for download in downloads:
       print(f"Version: {version}, Download URL: {download['url']}, Upload time: {download['upload_time']}")

If you haven’t already, install DuckDB and initialize it:

pip install duckdb


In your Python code, import DuckDB and connect to a database (this will create a new database if it doesn’t exist):

import duckdb

conn = duckdb.connect(database=':memory:', read_only=False)

Create a table in DuckDB to store the data you’re fetching from PyPI:

conn.execute("""
CREATE TABLE package_data (
   package_name VARCHAR,
   version VARCHAR,
   download_url VARCHAR,
   upload_time TIMESTAMP
)
""")

Loop through the data you’ve fetched from PyPI and insert it into DuckDB:

for version, downloads in releases.items():
   for download in downloads:
       conn.execute("""
       INSERT INTO package_data (package_name, version, download_url, upload_time)
       VALUES (?, ?, ?, ?)
       """, (package_name, version, download['url'], download['upload_time']))

Run a query to ensure that the data has been inserted correctly:

result = conn.execute("SELECT * FROM package_data").fetchall()
for row in result:
   print(row)

Once you’re done with inserting and querying data, make sure to close the connection to DuckDB:

conn.close()


Additional Notes:

• If you’re dealing with a large amount of data from PyPI, consider using pagination and rate-limiting to avoid overloading the PyPI API.
• Ensure that you handle exceptions and errors gracefully, especially when dealing with network requests and database operations.
• If you’re planning to move data regularly, consider writing a script that automates the entire process.