58 lines
1.6 KiB
Python
58 lines
1.6 KiB
Python
"""Quick DB diagnostics: Avito listings count + recent insert rate."""
|
|
from __future__ import annotations
|
|
|
|
import os
|
|
import psycopg
|
|
|
|
dsn = os.environ["DATABASE_URL"].replace("postgresql+psycopg://", "postgresql://")
|
|
conn = psycopg.connect(dsn)
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("SELECT count(*) FROM listings WHERE source = 'avito'")
|
|
print("avito total:", cur.fetchone()[0])
|
|
|
|
cur.execute(
|
|
"SELECT count(*) FROM listings "
|
|
"WHERE source='avito' AND scraped_at > NOW() - interval '20 minutes'"
|
|
)
|
|
print("avito scraped last 20min:", cur.fetchone()[0])
|
|
|
|
cur.execute(
|
|
"""
|
|
SELECT column_name FROM information_schema.columns
|
|
WHERE table_name='listings' AND column_name IN
|
|
('created_at','first_seen_at','first_seen','inserted_at')
|
|
"""
|
|
)
|
|
cols = [r[0] for r in cur.fetchall()]
|
|
print(f"timestamp cols available: {cols}")
|
|
if "first_seen_at" in cols:
|
|
cur.execute(
|
|
"SELECT count(*) FROM listings "
|
|
"WHERE source='avito' AND first_seen_at > NOW() - interval '20 minutes'"
|
|
)
|
|
print("avito first_seen last 20min (new):", cur.fetchone()[0])
|
|
|
|
cur.execute(
|
|
"""
|
|
SELECT date_trunc('minute', scraped_at) AS m, count(*)
|
|
FROM listings
|
|
WHERE source='avito' AND scraped_at > NOW() - interval '30 minutes'
|
|
GROUP BY 1 ORDER BY 1 DESC
|
|
"""
|
|
)
|
|
print("\nper-minute scraped (last 30min):")
|
|
for row in cur.fetchall():
|
|
print(f" {row[0]} {row[1]}")
|
|
|
|
cur.execute(
|
|
"""
|
|
SELECT rooms, count(*)
|
|
FROM listings
|
|
WHERE source='avito' AND scraped_at > NOW() - interval '30 minutes'
|
|
GROUP BY 1 ORDER BY 1
|
|
"""
|
|
)
|
|
print("\nby rooms (last 30min):")
|
|
for row in cur.fetchall():
|
|
print(f" rooms={row[0]} count={row[1]}")
|