PolisのDBからデータをエクスポート
2024-11-07 整理
psqlを入れる
$ sudo apt install postgresql-client-common接続する
$ psql -h localhost -p 5432 -U postgresパスワードはenvファイルに書いてある、デフォルトでは
POSTGRES_PASSWORD=oiPorg3Nrz0yqDLE conversation一覧と得票数を見る
sqlSELECT v.zid, c.topic, COUNT(v.zid) AS total_votes
FROM votes v
JOIN conversations c ON v.zid = c.zid
WHERE v.zid BETWEEN 1 AND 38
GROUP BY v.zid, c.topic
ORDER BY v.zid DESC;これをみてどのconversationをエクスポートしたいのか決める
例
sqlSELECT v.zid, c.topic, COUNT(v.zid) AS total_votes
FROM votes v
JOIN conversations c ON v.zid = c.zid
WHERE v.zid IN (38, 34, 31, 30, 29, 28,26)
GROUP BY v.zid, c.topic
ORDER BY v.zid DESC;result zid | topic | total_votes
-----+------------------------+-------------
38 | デジタル民主主義 | 4764
34 | 憲法 | 16881
31 | エネルギー政策 | 7958
30 | 経済政策・物価高対策 | 28510
29 | 社会保障（年金・医療） | 18050
28 | 税制 | 20061
26 | 政治資金規制改革 | 8900
(7 rows)書き出す
sql\COPY (SELECT * FROM comments WHERE zid IN (38, 34, 31, 30, 29, 28,26)) TO 'comments.csv' DELIMITER ',' CSV HEADER;sql\COPY (SELECT * FROM votes WHERE zid IN (38, 34, 31, 30, 29, 28,26)) TO '/home/ubuntu/votes.csv' DELIMITER ',' CSV HEADER;-----
pPolis2023-06-04
PolisのDBからデータをエクスポートした
>pol.isの自分の過去の投票データを取得して、いろいろやりたい
>Cloudflareの強力なWAFだかbot managementだかが有効化されていて、スクレイピングの難易度が超高い
インスタンスを立てたらデータを出せるだろうかという話題になり、立てて投票を集めるところまではやったという話をした
>Data can be exported from the admin page for a conversation.
そんなメニュー見当たらない…
pol.isではオフにしてあるってことかな
API
https://pol.is/api/v3/dataExport?conversation_id=4jbpxizvxf&format=csv {} になるpg_dumpしたらいいかな
:$ psql
Command 'psql' not found, but can be installed with:
$ sudo apt install postgresql-client-common
$ psql
Warning: No existing cluster is suitable as a default target. Please see man pg_wrapper(1) how to specify one.
Error: You must install at least one postgresql-client-<version> package:$ sudo apt update
$ sudo apt install postgresql-client
$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?:$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
12be7e86bdf7 compdem/polis-nginx-proxy:dev "/docker-entrypoint.…" 11 days ago Up 8 minutes 0.0.0.0:80->80/tcp, :::80->80/tcp, 0.0.0.0:443->443/tcp, :::443->443/tcp polis-dev-nginx-proxy-1
e605f36eaf7d compdem/polis-server:dev "docker-entrypoint.s…" 11 days ago Up 8 minutes 0.0.0.0:5000->5000/tcp, :::5000->5000/tcp, 0.0.0.0:9229->9229/tcp, :::9229->9229/tcp polis-dev-server-1
25846362cfd3 compdem/polis-math:dev "entrypoint ./bin/run" 12 days ago Up 8 minutes 0.0.0.0:18975->18975/tcp, :::18975->18975/tcp polis-dev-math-1
20b437d2786c maildev/maildev:1.1.1 "/home/node/bin/mail…" 12 days ago Up 8 minutes (healthy) 0.0.0.0:1025->1025/tcp, :::1025->1025/tcp, 0.0.0.0:1080->1080/tcp, :::1080->1080/tcp polis-dev-maildev-1
f8d749409bda compdem/polis-postgres:dev "docker-entrypoint.s…" 12 days ago Up 9 minutes 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp polis-dev-postgres-1
8dccf50c2348 compdem/polis-file-server:dev "docker-entrypoint.s…" 12 days ago Up 8 minutes 0.0.0.0:8080->8080/tcp, :::8080->8080/tcp polis-dev-file-server-1:$ psql -h localhost -p 5432
Password for user ubuntu:パスワードを調べる
example.envに書いてある
example.env###### DATABASE ######
# Optional DB replica for reads:
READ_ONLY_DATABASE_URL=
POSTGRES_DB=polis-dev
POSTGRES_HOST=postgres:5432
POSTGRES_PASSWORD=oiPorg3Nrz0yqDLE
POSTGRES_PORT=5432
POSTGRES_USER=postgres:$ psql -h localhost -p 5432 -U postgres
Password for user postgres:
psql (14.8 (Ubuntu 14.8-0ubuntu0.22.04.1), server 13.4)
Type "help" for help.
postgres=# 
:\c polis-dev
\dt
:# select * from comments where zid = 3;
:polis-dev=# COPY comments TO 'comments.csv' DELIMITER ',' CSV HEADER;
ERROR: relative path not allowed for COPY to file
polis-dev=# COPY comments TO '/home/ubuntu/comments.csv' DELIMITER ',' CSV HEADER;
ERROR: could not open file "/home/ubuntu/comments.csv" for writing: No such file or directory
HINT: COPY TO instructs the PostgreSQL server process to write a file. You may want a client-side facility such as psql's \copy.あー、そうか、Dockerの中で動いてるからか
コメントを書き出してみる
:# \COPY comments TO '/home/ubuntu/comments.csv' DELIMITER ',' CSV HEADER;
COPY 55できた
特定のconversation id (=3)のコメントと投票を書き出す
:polis-dev=# \COPY (SELECT * FROM comments WHERE zid = 3) TO '/home/ubuntu/comments.csv' DELIMITER ',' CSV HEADER;
COPY 8
polis-dev=# \COPY (SELECT * FROM votes WHERE zid = 3) TO '/home/ubuntu/votes.csv' DELIMITER ',' CSV HEADER;
COPY 454Postgres使ったことなかったけどGPT-4があれば何も怖くないな()