def add_audio_from_data(new_audio: [pyaudio.PyAudio], user: User,
connection: sqlite3.connect) -> str:
cursor = connection.cursor()
cursor.execute("INSERT INTO audio VALUES (NULL, ?, DATETIME())",
(user.name, ))
connection.commit()
def reserve_audio_ID(connection: sqlite3.connect, user: User):
cursor = connection.cursor()
cursor.execute("INSERT INTO audio VALUES(NULL, ? , DATETIME())",
(user.name, ))
connection.commit()
cursor.execute("SELECT ID FROM audio WHERE username = ? ORDER BY ID DESC",
(user.name, ))
return cursor.fetchone()[0]
async def duplicate_exists(rss: rss_class.Rss, conn: sqlite3.connect,
link: str, title: str, summary: str) -> tuple:
flag = False
link = link.replace("'", "''")
title = title.replace("'", "''")
image_hash = None
cursor = conn.cursor()
sql = "SELECT * FROM main WHERE 1=1"
args = []
for mode in rss.duplicate_filter_mode:
if mode == "image":
try:
summary_doc = Pq(summary)
except Exception as e:
logger.warning(e)
# 没有正文内容直接跳过
continue
img_doc = summary_doc("img")
# 只处理仅有一张图片的情况
if len(img_doc) != 1:
continue
url = img_doc.attr("src")
# 通过图像的指纹来判断是否实际是同一张图片
content = await download_image(url, rss.img_proxy)
if not content:
continue
try:
im = Image.open(BytesIO(content))
except UnidentifiedImageError:
continue
image_hash = str(imagehash.average_hash(im))
# GIF 图片的 image_hash 实际上是第一帧的值，为了避免误伤直接跳过
if im.format == "GIF":
continue
logger.debug(f"image_hash: {image_hash}")
sql += " AND image_hash=?"
args.append(image_hash)
if mode == "link":
sql += " AND link=?"
args.append(link)
if mode == "title":
sql += " AND title=?"
args.append(title)
if "or" in rss.duplicate_filter_mode:
sql = sql.replace("AND", "OR").replace("OR", "AND", 1)
cursor.execute(f"{sql};", args)
result = cursor.fetchone()
if result is not None:
result_id = result[0]
cursor.execute(
"UPDATE main SET datetime = DATETIME('Now','LocalTime') WHERE id = ?;",
(result_id, ),
)
cursor.close()
conn.commit()
flag = True
return flag, image_hash
def insert_snapshots(connection: sqlite3.connect):
cursor = connection.cursor()
snapshots = finally_generator.create_inserts()
query = """INSERT INTO Sensors_snapshots(Date_snapshot, Room_ID, Temperature, Pressure,Wet)
VALUES(?,?,?,?,?);"""
cursor.executemany(query, snapshots)
connection.commit()
print(f'Строк добавлено: {cursor.rowcount}')
def _insert_records(table_name, con: sqlite3.connect, data):
for d in data:
cls = InsertData(table_name, d)
cmd = cls.create_insert_record_command()
cur = con.cursor()
records = tuple(str(val) for _, val in d.items())
cur.execute(cmd, records)
con.commit()
cur.close()
def connect():
try:
database = SqliteConnect(getEpgDbFilePath())
database.text_factory = str
cursor = database.cursor()
except SqliteError as e:
notify(DB_CONNECTION_ERROR, e.message)
return None
return database, cursor
def execute_query(query: str, connection: sqlite3.connect):
cursor = connection.cursor()
query = query
try:
cursor.execute(query)
except Exception as e:
print(e)
return e
connection.commit()
def add_follower(connection: sqlite3.connect, user: User, following):
cursor = connection.cursor()
cursor.execute(
"INSERT INTO following VALUES (NULL, :user, :following, DATETIME() )",
{
'user': user.name,
'following': following.name
})
connection.commit()
async def insert_into_cache_db(conn: sqlite3.connect, item: dict,
image_hash: str) -> None:
cursor = conn.cursor()
link = item["link"].replace("'", "''")
title = item["title"].replace("'", "''")
cursor.execute(
"INSERT INTO main (link, title, image_hash) VALUES (?, ?, ?);",
(link, title, image_hash),
)
cursor.close()
conn.commit()
def check_issuer_and_subject_counts(conn: sqlite3.connect = None,
fingerprint: str = None) -> None:
"""
Finds any cert that is CA signed but also has self-signed versions.
Args:
conn (sqlite3.connect): Database connection to which query against.
fingerprint (str): Public Key fingerprint of which query will be made.
"""
# Obtain iterative cursor for the database connection
cur = conn.cursor()
# Query against the fingerprint
cur.execute("SELECT * FROM x509info WHERE fingerprint = '%s';" %
fingerprint)
# Track fingerprints that are both CA and self-signed
diff_count = 0
same_count = 0
# Iterate over the results
for _, _, issuer, subject, _, _, _ in cur:
# Count CA and self-signed
if (issuer != subject): diff_count += 1
else: same_count += 1
# Display only the cases where both signed and self signed exist
if ((same_count > 0) and (diff_count > 0)):
print(fingerprint, same_count, diff_count)
def is_following(connection: sqlite3.connect, user: User,
following: User) -> bool:
cursor = connection.cursor()
cursor.execute(
'''SELECT ID FROM following WHERE username = ? AND following = ?''',
(user.name, following.name))
if cursor.fetchone():
return True
else:
return False
def print_table(connection: sqlite3.connect, table_name: str):
cursor = connection.cursor()
# TODO Make it so that table_name is parsed into execute
cursor.execute("SELECT * FROM audio ") # , (table_name,))
table = cursor.fetchall()
names = ''
for column_name in cursor.description:
names += column_name[0] + ' '
print(names)
for row in table:
print('|' + '-' * len(str(row)))
print('| ' + str(row))
async def cache_db_manage(conn: sqlite3.connect) -> None:
cursor = conn.cursor()
# 用来去重的 sqlite3 数据表如果不存在就创建一个
cursor.execute("""
CREATE TABLE IF NOT EXISTS main (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"link" TEXT,
"title" TEXT,
"image_hash" TEXT,
"datetime" TEXT DEFAULT (DATETIME('Now', 'LocalTime'))
);
""")
cursor.close()
conn.commit()
cursor = conn.cursor()
# 移除超过 config.db_cache_expire 天没重复过的记录
cursor.execute(
"DELETE FROM main WHERE datetime <= DATETIME('Now', 'LocalTime', ?);",
(f"-{config.db_cache_expire} Day", ),
)
cursor.close()
conn.commit()
def __init__(self):
self.Connector = Connect("Memories.db", detect_types=TimeStamps)
self.Shell = self.Connector.cursor()
self.SQLTables = "SELECT name FROM sqlite_master WHERE type = 'table'"
class SQLInterface:
def __init__(self):
self.Connector = Connect("Memories.db", detect_types=TimeStamps)
self.Shell = self.Connector.cursor()
self.SQLTables = "SELECT name FROM sqlite_master WHERE type = 'table'"
def Check(self, Year=str()):
with self.Connector: #connector as text manager
Table = 'My' + Year + 'Memories' #table name
DB = self.Shell.execute(self.SQLTables).fetchall(
) #execute the query fetching all the results
Tables = [str(Tab[0]) for Tab in DB] #getting names of al tables
if not Table in Tables: #if the table is in the list
SQLQueryTable = """
CREATE TABLE """ + Table + """
(
MemoryID text PRIMARY KEY,
Memory text,
Timestamp timestamp,
Date date
)
"""
self.SQLShell.execute(
SQLQueryTable) #SQL command to create the table
def Insert(self, Text=str()):
Now = Time.now() # timestamps
Today = Now.date() # date
MemoryID = str(Now) + str("".join(Picks(Alphabet, k=6)))
Table = "My" + str(Now.year) + "Memories"
try:
if not Text == '':
with self.Connector: # database as context manger
SQLQuery = "INSERT INTO " + Table + " VALUES(:MemID, :Text, :Now, :Date)"
Values = {
"MemID": MemoryID,
"Text": Text,
"Now": Now,
"Date": Today
} #values safely inserted
self.Shell.execute(
SQLQuery, Values) #execute the sql query correctly
print('Memory inserted in the database')
except Exception as Error:
print(Error)
def Show(self, Random=True, All=False):
YearsQuery = self.SQLTables + " AND name LIKE '%Memories'" #SQLQuery to find the name of all tables of memories
Years = [
str(Tab[0]) for Tab in self.Shell.execute(YearsQuery).fetchall()
] #list of names of the memory tables
Memories = []
with self.Connector:
if All:
for Year in Years:
MemoryQuery = "SELECT Memory, Date FROM " + Year #query for memories of a selected year
YearMemories = self.Shell.execute(MemoryQuery).fetchall(
) #getting memories from the selected year
YearMemories = [[Data[0], Data[1]] for Data in YearMemories
] #formatting properly the data
Memories.extend(
YearMemories
) #proper extension of the list of all memories
else:
MemoryQuery = "SELECT Memory, Date FROM " + Years[
-1] #query for memories of the last registered year
YearMemories = self.Shell.execute(MemoryQuery).fetchall(
) #getting all memories from the selected year
Memories = [[Data[0], str(Data[1])] for Data in YearMemories
] #formatting properly the data
if len(Memories) == 0:
MemoryQuery = "SELECT Memory, Date FROM " + Years[
-2] #query for memories of the previous year
YearMemories = self.Shell.execute(MemoryQuery).fetchall(
) #getting memories from the selected year
Memories = [[Data[0], Data[1]] for Data in YearMemories
] #formatting properly the data
if Random: #shuffle the memory order if the user want to
Mix(Memories) #mix the memories
pass
def remove_follow(connection: sqlite3.connect, user: User, following: User):
cursor = connection.cursor()
cursor.execute(
"DELETE FROM following WHERE username = ? AND following = ?",
(user.name, following.name))
connection.commit()
style.use('seaborn-whitegrid')
print("style set")
# initialize variables and 1st run assignments.
dbpath = "/media/pi/SOLARUSB/SolarMonitor/"
dbfile = "SolarDataCollection.db"
# Define the datatype [list] for the plot
x1 = []
y11 = []
y12 = []
y21 = []
y22 = []
# Connect to database file
DBconnect = ConnectSQLite(dbpath + dbfile)
DBcursor = DBconnect.cursor()
# Get and plot data function
def GetDataAndPlot(SD, ED, DP11, DP21='Null', DP12='Null', DP22='Null'):
# Setup size of figure
fig = plt.figure(figsize=(14, 9))
# Retrive data from the database and load into lists to plot
# If the first datapoint of the 2nd subplot is not null then create 2 subplots
if DP21 != 'Null':
ax2 = plt.subplot2grid((2, 1), (1, 0), rowspan=1, colspan=1)
ax1 = plt.subplot2grid((2, 1), (0, 0), rowspan=1, colspan=1)
# Add the 2nd y axis on the subplots if not null
pass
def GUIMenu(Root=Tk()):
MenuFrame = Frame(Root)
TopText = 'Welcome to\nPositivity.Jar'
MenuTopLabel = Label(MenuFrame, text=TopText, font=('Courier', 32),
bd=10) #welcome label
##################################################################################################End GUI implementation
################################################################################################Start SQl implementation
Connector = Connect("Memories.db", detect_types=TimeStamps)
SQLShell = Connector.cursor()
SQLTables = "SELECT name FROM sqlite_master WHERE type = 'table'"
def YearTable(
): #this function declares a table of the current year inside the database if non-existent
with Connector: #database as context manager
Table = "My" + str(Time.now().year) + "Memories" #table name
DB = SQLShell.execute(SQLTables).fetchall()
DataBase = [str(Tab[0]) for Tab in DB] #getting names of al tables
if not Table in DataBase: #if the table is in the list
SQLQueryTable = """
CREATE TABLE """ + Table + """
(
MemoryID text PRIMARY KEY,