使用 SQLite 和 Python 进行数据分析

pip install sqlite-utils

pipx install sqlite-utils

brew install sqlite-utils

pip install datasette

pipx install datasette

brew install datasette

datasette install datasette-codespaces

wget https://congress-legislators.datasettes.com/legislators.db

datasette legislators.db

datasette install datasette-cluster-map

datasette plugins

datasette uninstall datasette-cluster-map

!git clone https://github.com/python/peps /tmp/peps

import pathlib

files = list(pathlib.Path("/tmp/peps").glob("pep-*.txt"))

def parse_pep(s):
    intro, body = s.split("\n\n", 1)
    pep = {}
    current_key = None
    current_value = None
    for line in intro.split("\n"):
        # If the line starts with whitespace, it's a continuation of the previous value
        if line.startswith(" ") or line.startswith("\t"):
            if current_key is not None:
                current_value += " " + line.strip()
                pep[current_key] = current_value.strip()
        else:
            # Split the line into key and value
            parts = line.split(": ", 1)
            if len(parts) == 2:
                key, value = parts
                # Update the current key and value
                current_key = key
                current_value = value
                # Add the key-value pair to the pep dictionary
                pep[current_key] = current_value.strip()
    pep["Body"] = body.strip()
    return pep

peps = []
for file in files:
    peps.append(parse_pep(file.read_text()))

%pip install sqlite-utils

import sqlite_utils
db = sqlite_utils.Database("/tmp/peps.db")
db["peps"].insert_all(peps, pk="PEP", replace=True)

OperationalError: table peps has no column named PEP-Delegate

db["peps"].insert_all(peps, pk="PEP", alter=True, replace=True)
print(db["peps"].count)
# Outputs 429 

db["peps"].enable_fts(["Title", "Body"])

select
  PEP,
  peps.Title,
  Version,
  Author,
  Status,
  Type,
  Created,
  peps.Body,
  peps_fts.rank
from
  peps
join
  peps_fts on peps.rowid = peps_fts.rowid
where
  peps_fts match :search
order by
  peps_fts.rank
limit
  20

brew install vercel-cli

npm i -g vercel

pnpm i -g vercel

vercel login

datasette install datasette-publish-vercel

datasette publish vercel /tmp/peps.db --project python-peps

select
  party,
  count(*)
from
  executive_terms
where
  type = 'prez'
group by
  party

select
  top_folders,
  sum(size_gb) as total_gb,
  count(*) as num_files
from raw
group by top_folders
order by sum(size_gb) desc

  cast (sum(size_gb) as integer) as total_gb,

select html_url from releases where repo in (
  select id from repos where full_name in (
    select repo from plugin_repos
  )
)
order by created_at desc

select
  full_name,
  (
    select
      html_url
    from
      releases
    where
      releases.repo = repos.id
    order by
      created_at desc
    limit
      1
  ) as latest_release
from
  repos

with presidents as (
  select
    executives.name
  from
    executive_terms
    join executives
      on executive_terms.executive_id = executives.id
  where
    executive_terms.type = 'prez'
),
vice_presidents as (
  select
    executives.name
  from
    executive_terms
    join executives
      on executive_terms.executive_id = executives.id
  where
    executive_terms.type = 'viceprez'
)
select
  distinct name
from
  presidents
where name in vice_presidents

select
  legislators.id,
  legislators.name,
  json_group_array(json_object(
    'type', legislator_terms.type,
    'state', legislator_terms.state,
    'start', legislator_terms.start,
    'end', legislator_terms.end,
    'party', legislator_terms.party
   )) as terms,
   count(*) as num_terms
from
  legislators join legislator_terms on legislator_terms.legislator_id = legislators.id
  group by legislators.id
order by
  id
limit
  10

with cte as (
  select
    repos.full_name,
    releases.created_at,
    releases.id as rel_id,
    releases.name as rel_name,
    releases.created_at as rel_created_at,
    rank() over (partition by repos.id order by releases.created_at desc) as rel_rank
  from repos
    left join releases on releases.repo = repos.id
)
select
  full_name,
  max(created_at) as max_created_at,
  count(rel_id) as releases_count,
  json_group_array(
    json_object(
      'id', rel_id,
      'name', rel_name,
      'created_at', rel_created_at
    )
  ) filter (where rel_id is not null and rel_rank <= 3) as recent_releases
from cte
group by full_name
order by releases_count desc

with cte as (
  select
    repos.full_name,
    releases.created_at,
    releases.id as rel_id,
    releases.name as rel_name,
    releases.created_at as rel_created_at,
    rank() over (partition by repos.id order by releases.created_at desc) as rel_rank
  from repos
    left join releases on releases.repo = repos.id
)
select * from cte