Google suggest Keywords

# suggest_to_sqlite.py
# Fetch Google autocomplete suggestions for a keyword
# and store them into a local SQLite DB.

import sqlite3
import requests
from bs4 import BeautifulSoup
from datetime import datetime
from pathlib import Path

DB_PATH = Path("E:\\testing3\\suggestions.db")  # change if you want a different location
UA = "Mozilla/5.0 (compatible; Plus2net-Suggest-Collector/1.0)"
TIMEOUT = 20

def init_db(db_path: Path):
    con = sqlite3.connect(str(db_path))
    cur = con.cursor()
    cur.execute("""
        CREATE TABLE IF NOT EXISTS suggestions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            keyword TEXT NOT NULL,
            suggestion TEXT NOT NULL,
            source TEXT NOT NULL,
            fetched_at TEXT NOT NULL,
            UNIQUE(keyword, suggestion, source)
        )
    """)
    # helpful index for lookups by keyword
    cur.execute("CREATE INDEX IF NOT EXISTS idx_suggestions_keyword ON suggestions(keyword)")
    con.commit()
    con.close()

def fetch_suggestions(keyword: str) -> list[str]:
    url = "https://suggestqueries.google.com/complete/search"
    params = {"output": "toolbar", "hl": "en", "q": keyword}
    headers = {"User-Agent": UA}
    r = requests.get(url, params=params, headers=headers, timeout=TIMEOUT)
    r.raise_for_status()
    soup = BeautifulSoup(r.text, "xml")
    sugs = [node["data"] for node in soup.find_all("suggestion")]
    return sugs

def save_suggestions(db_path: Path, keyword: str, suggestions: list[str], source: str = "google"):
    con = sqlite3.connect(str(db_path))
    cur = con.cursor()
    now = datetime.utcnow().isoformat(timespec="seconds") + "Z"
    rows = [(keyword, s, source, now) for s in suggestions]
    # use INSERT OR IGNORE to avoid duplicates on (keyword, suggestion, source)
    cur.executemany("""
        INSERT OR IGNORE INTO suggestions (keyword, suggestion, source, fetched_at)
        VALUES (?, ?, ?, ?)
    """, rows)
    con.commit()
    inserted = cur.rowcount  # number of rows changed by last operation (works for executemany in sqlite3)
    con.close()
    return inserted

def main():
    init_db(DB_PATH)
    keyword = input("Enter keyword: ").strip()
    if not keyword:
        print("No keyword provided.")
        return

    try:
        suggestions = fetch_suggestions(keyword)
    except requests.RequestException as e:
        print("Error fetching suggestions:", e)
        return

    if not suggestions:
        print("No suggestions found.")
        return

    # print to console
    print("\nSuggestions:")
    for s in suggestions:
        print(" -", s)

    # save to DB
    inserted = save_suggestions(DB_PATH, keyword, suggestions)
    print(f"\nSaved {inserted} new suggestion(s) to {DB_PATH}")

    # optional: show last few rows for this keyword
    con = sqlite3.connect(str(DB_PATH))
    cur = con.cursor()
    cur.execute("""
        SELECT suggestion, fetched_at FROM suggestions
        WHERE keyword = ?
        ORDER BY fetched_at DESC, suggestion ASC
        LIMIT 10
    """, (keyword,))
    rows = cur.fetchall()
    con.close()

    print("\nRecent entries in DB for this keyword:")
    for s, ts in rows:
        print(f" - {s}  ({ts})")

if __name__ == "__main__":
    main()