123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990 |
- import sqlite3
- import os
- from multiFeedParsing import FeedEntry,TwtxtEntry
- class AntennaDB():
- def __init__(self, dbPath="antenna.sqlite"):
- self.dbPath = dbPath
- def createDB(self):
- connection = sqlite3.connect(self.dbPath)
- cursor = connection.cursor()
- cursor.execute("CREATE TABLE IF NOT EXISTS entries (feedurl text, author text, updated datetime, title text, link text primary key)")
- cursor.execute("CREATE TABLE IF NOT EXISTS twtxt (feedurl text, author text, posted datetime, twt text)")
- connection.close()
- def getEntries(self):
- connection = sqlite3.connect(self.dbPath)
- cursor = connection.cursor()
- cursor.execute("SELECT feedurl, author, updated, title, link FROM entries ORDER BY updated DESC")
- results = []
- for result in cursor.fetchall():
- results.append(FeedEntry(feedurl = result[0], author = result[1], updated = result[2], title = result[3], link = result[4]))
- connection.close()
- return results
- def getTwts(self):
- connection = sqlite3.connect(self.dbPath)
- cursor = connection.cursor()
- cursor.execute("SELECT feedurl, author, posted, twt FROM twtxt ORDER BY posted DESC")
- results = []
- for result in cursor.fetchall():
- results.append(TwtxtEntry(feedurl = result[0], author = result[1], posted = result[2], twt = result[3]))
- connection.close()
- return results
- def deleteFeeds(self, urls):
- urlTuples = []
- for url in urls:
- urlTuples.append((url,))
- connection = sqlite3.connect(self.dbPath)
- cursor = connection.cursor()
- cursor.executemany("DELETE FROM entries WHERE feedurl LIKE ?", urlTuples)
- cursor.executemany("DELETE FROM twtxt WHERE feedurl LIKE ?", urlTuples)
- connection.commit()
- connection.close()
-
- def insertFeedEntries(self, entries, limit=0):
- entries = [e for e in entries if e.updated > limit]
- entrytuples = []
- for entry in entries:
- entrytuples.append((entry.feedurl, entry.author, entry.updated, entry.title, entry.link))
- connection = sqlite3.connect(self.dbPath)
- cursor = connection.cursor()
- cursor.executemany("INSERT INTO entries (feedurl, author, updated, title, link) VALUES (?,?,?,?,?) ON CONFLICT (link) DO UPDATE SET author = excluded.author, updated = excluded.updated, title = excluded.title", entrytuples)
- connection.commit()
- connection.close()
- return len(entries)
- def insertTwtxtEntries(self, entries, limit=0):
- entries = [e for e in entries if e.posted > limit]
- entrytuples = []
- for entry in entries:
- entrytuples.append((entry.feedurl, entry.author, entry.posted, entry.twt))
- connection = sqlite3.connect(self.dbPath)
- cursor = connection.cursor()
- cursor.executemany("INSERT INTO twtxt (feedurl, author, posted, twt) VALUES (?,?,?,?)", entrytuples)
- connection.commit()
- connection.close()
- return len(entries)
- def pruneDB(self, limit):
- connection = sqlite3.connect(self.dbPath)
- cursor = connection.cursor()
- cursor.execute("DELETE FROM entries WHERE updated < ?", (limit,))
- cursor.execute("DELETE FROM twtxt WHERE posted < ?", (limit,))
- connection.commit()
- connection.close()
|