66 lines
2.0 KiB
Python
66 lines
2.0 KiB
Python
import argparse
|
|
from pathlib import Path
|
|
|
|
import psycopg
|
|
|
|
|
|
def fetch_emails(pg_host, pg_port, pg_db, pg_user, pg_password, table_name, domain):
|
|
conn = psycopg.connect(
|
|
host=pg_host,
|
|
port=pg_port,
|
|
dbname=pg_db,
|
|
user=pg_user,
|
|
password=pg_password,
|
|
connect_timeout=10,
|
|
)
|
|
try:
|
|
with conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
f"select distinct lower(email) from {table_name} where lower(email) like %s order by lower(email)",
|
|
(f"%@{domain.lower()}",),
|
|
)
|
|
return [row[0] for row in cur.fetchall()]
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
def write_sql(output_path, emails, source_meta):
|
|
lines = []
|
|
for email in emails:
|
|
safe = email.replace("'", "''")
|
|
source = source_meta.replace("'", "''")
|
|
lines.append(
|
|
f"INSERT OR IGNORE INTO address(name, source_meta) VALUES('{safe}', '{source}');"
|
|
)
|
|
Path(output_path).write_text("\n".join(lines), encoding="utf-8")
|
|
|
|
|
|
def main():
|
|
parser = argparse.ArgumentParser(description="Export domain mailboxes from PostgreSQL into Cloudflare D1 import SQL.")
|
|
parser.add_argument("--pg-host", required=True)
|
|
parser.add_argument("--pg-port", type=int, required=True)
|
|
parser.add_argument("--pg-db", required=True)
|
|
parser.add_argument("--pg-user", required=True)
|
|
parser.add_argument("--pg-password", required=True)
|
|
parser.add_argument("--table", default="registered_accounts")
|
|
parser.add_argument("--domain", required=True)
|
|
parser.add_argument("--source-meta", default="pg_import")
|
|
parser.add_argument("--output", required=True)
|
|
args = parser.parse_args()
|
|
|
|
emails = fetch_emails(
|
|
args.pg_host,
|
|
args.pg_port,
|
|
args.pg_db,
|
|
args.pg_user,
|
|
args.pg_password,
|
|
args.table,
|
|
args.domain,
|
|
)
|
|
write_sql(args.output, emails, args.source_meta)
|
|
print(f"exported={len(emails)} output={args.output}")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|