def upsert_news(
agency: str,
id: str,
url: str,
title: str,
desc: Optional[str],
created_at: int,
updated_at: int
):
with db.transaction():
db.query(
''' insert into news_articles (agency, id, url, title, "desc", created_at, updated_at)
values (:agency, :id, :url, :title, :desc,
to_timestamp(:created_at), to_timestamp(:updated_at))
on conflict (id, agency) do update
set updated_at = to_timestamp(:updated_at)
,url = :url
,title = :title
,"desc" = :desc
''',
agency=agency,
id=id,
url=url,
title=title,
desc=desc,
created_at=created_at,
updated_at=updated_at,
)
pass
def save_tweet(t):
id = int(t['id_str'])
created_at = int(timestamp_from_id(id))
db.query(''' insert into fav_tweets (id, tweet, created_at)
values (:id, :tweet, to_timestamp(:created_at))
on conflict (id) do update
set tweet = :tweet
''',
id=id,
tweet=json.dumps(t),
created_at=created_at)
def save(t):
_id = t['id']
with db.transaction():
db.query(
'insert into timeline_tweets (id, tweet, created_at)'
'values (:id, :tweet, to_timestamp(:created_at))'
'on conflict (id) do update set tweet = :tweet',
id=_id,
tweet=json.dumps(t),
created_at=timestamp_from_id(_id))
pass
def run_migration(index, m: migrations.MSql):
with db.transaction():
db.query(
''' insert into migration_history (id, "desc", created_at, sql, sha256)
values (:id, :desc, now(), :sql, :sha256) ''',
id=index,
desc=m.desc,
sql=m.sql,
sha256=m.sha256(),
)
db.query(m.sql)
pass
def news_by_url(agency: str, url: str):
with db.transaction():
return db.query(
'select * from news_articles where url = :url and agency = :agency',
url=url,
agency=agency,
).all()
pass
def recent_news(agency: str, size: int=50):
with db.transaction():
return db.query(
'select * from news_articles where agency = :agency'
' order by updated_at desc'
' limit :limit',
agency=agency,
limit=size,
).all()
pass
def query_history():
with db.transaction():
return db.query(
'select * from migration_history order by created_at').all()
def init_migration():
with db.transaction():
db.query(INIT_MIGRATION)
pass
def recent_tweets(size):
with db.transaction():
return db.query(
'select * from timeline_tweets order by id desc limit :size',
size=size).all()
def load_oldest_id():
with db.transaction():
xs = db.query(
'select * from timeline_tweets order by id desc limit 1').all()
if xs: return xs[0].id