Pulling every tweet for a keyword into SQLite (and what each run actually costs)

I wanted a small local archive of posts about DuckDB: something I can query with plain SQL, re-run whenever I like, and not worry about duplicates. Here's the script I ended up with, the bill for each run, and the one mistake that made my second run cost as much as the first.

Disclosure: I work on TwitterAPI.io, the API used below. Written with AI assistance; the code and every number come from my own runs.

The query

"duckdb" lang:en min_faves:20 -filter:replies

English posts that mention DuckDB, have at least 20 likes, and aren't replies. The min_faves filter keeps the archive to posts people actually engaged with, and it also keeps the bill down, because you pay per tweet returned.

The script

One file, standard library only. It pages through advanced_search (20 tweets per page), writes each tweet into SQLite keyed by its ID, and adds up the credits as it goes.

"""Collect every tweet for a search query into SQLite, page by page, and report the cost.

usage: TWITTERAPI_KEY=... python3 collect.py "<query>" [max_pages]
Re-running is safe: tweets are keyed by id, so pages you already have are not duplicated.
"""
import json, os, sqlite3, sys, time, urllib.parse, urllib.request

API = "https://api.twitterapi.io/twitter/tweet/advanced_search"
KEY = os.environ["TWITTERAPI_KEY"]
query = sys.argv[1]
max_pages = int(sys.argv[2]) if len(sys.argv) > 2 else 10

db = sqlite3.connect("tweets.db")
db.execute("""CREATE TABLE IF NOT EXISTS tweets (
    id TEXT PRIMARY KEY, query TEXT, created_at TEXT, author TEXT,
    text TEXT, likes INT, retweets INT, replies INT, raw TEXT)""")

def get_page(cursor):
    params = {"query": query, "queryType": "Latest", "cursor": cursor}
    req = urllib.request.Request(f"{API}?{urllib.parse.urlencode(params)}", headers={"X-API-Key": KEY})
    with urllib.request.urlopen(req, timeout=30) as r:
        return json.load(r)

cursor, calls, fetched, new, credits = "", 0, 0, 0, 0
started = time.time()
while calls < max_pages:
    page = get_page(cursor)
    calls += 1
    tweets = page.get("tweets") or []
    fetched += len(tweets)
    credits += max(len(tweets) * 15, 15)  # billed per request: 15 credits per tweet, 15 minimum
    page_new = 0
    for t in tweets:
        cur = db.execute("INSERT OR IGNORE INTO tweets VALUES (?,?,?,?,?,?,?,?,?)", (
            t["id"], query, t.get("createdAt"), (t.get("author") or {}).get("userName"),
            t.get("text"), t.get("likeCount"), t.get("retweetCount"), t.get("replyCount"), json.dumps(t)))
        new += cur.rowcount; page_new += cur.rowcount
    db.commit()
    # "Latest" returns newest first: once a whole page is already stored, everything older is too.
    if tweets and page_new == 0:
        break
    if not page.get("has_next_page") or not tweets:
        break
    cursor = page.get("next_cursor") or ""

total = db.execute("SELECT COUNT(*) FROM tweets WHERE query=?", (query,)).fetchone()[0]
cost = credits / 100_000  # 100,000 credits = $1
print(json.dumps({"query": query, "calls": calls, "tweets_returned": fetched, "new_rows": new,
                  "rows_in_db_for_query": total, "credits": credits, "cost_usd": round(cost, 5),
                  "seconds": round(time.time() - started, 1)}))

Run it:

export TWITTERAPI_KEY=...        # from your dashboard
python3 collect.py '"duckdb" lang:en min_faves:20 -filter:replies' 15

What it cost

The billing rule is simple: 15 credits per tweet returned, with a 15-credit minimum per request, and 100,000 credits = $1. A full page of 20 tweets is 300 credits, or $0.003. A request that returns nothing still costs 15 credits.

Run	Requests	Tweets returned	New rows	Credits	Cost
1st run (empty database)	15	300	300	4,500	$0.045
2nd run, first version of the script	15	300	0	4,500	$0.045
2nd run, with the "stop on a known page" check	1	20	0	300	$0.003

The first run took about 45 seconds for 15 pages.

For scale: reading the same 300 posts through the official X API's pay-per-use tier at $0.005 per post read would be $1.50, about 33 times more per read.

The mistake: re-runs that pay for data you already have

My first version had no stop condition other than has_next_page. INSERT OR IGNORE meant no duplicate rows, so the database looked fine. But the second run walked the same 15 pages and paid for all 300 tweets again, while saving zero new ones.

The fix is the page_new == 0 check. With queryType=Latest the newest posts come first. So when the scheduled re-run reaches a page where every tweet is already stored, everything after it is already stored too, and it can stop. A daily re-run then costs about one page per 20 new posts, plus one page to confirm there is nothing newer.

Two caveats. First, this assumes the previous run finished: if a first backfill dies halfway, the next run sees the newest page as "known" and stops before reaching the gap. For long backfills, saving next_cursor after each page and resuming from it avoids this. Second, with a min_faves filter, posts that cross the threshold later show up deeper in the results; the stop check skips them, so run a full re-crawl now and then if you need them.

Querying it

-- most-liked posts in the archive
SELECT likes, substr(text, 1, 80) FROM tweets ORDER BY likes DESC LIMIT 5;

-- posts per day
SELECT substr(created_at, 5, 6) AS day, COUNT(*) FROM tweets GROUP BY day ORDER BY COUNT(*) DESC;

The raw column keeps the full JSON of each tweet, so you can add columns later without fetching again.

Notes if you try it

• Rate limits: paid accounts get 20 requests per second. Free accounts are limited to 1 request every 5 seconds until credits are purchased, so add time.sleep(5) in the loop if you're on free credits.
• Free credits: new accounts get up to $1 of free credits (bot check, one per person). That's enough for this whole exercise many times over.
• Narrow queries save money: min_faves, lang: and -filter:replies cut the number of tweets returned, and returned tweets are what you pay for.

Pricing details are on the pricing page.