1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374 |
- import sqlite3
- from datetime import datetime
-
- def create_table_files():
- conn = sqlite3.connect("files.db", detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
- cur = conn.cursor()
- cur.execute("CREATE TABLE files (name VARCHAR(255) PRIMARY KEY, moment TIMESTAMP, length INT);")
- conn.commit()
- conn.close()
-
- def populate_table_files():
- conn = sqlite3.connect("files.db", detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
- cur = conn.cursor()
- with open("files.txt", "r") as f:
- for line in f:
- file, fsize = line.split(" ")
- fsize = int(fsize.rstrip())
- if file.endswith(".ast"):
- year, month, rem = file.split("-")
- day = rem.split(".")[0]
- cur.execute("INSERT INTO files VALUES (?, ?, ?);", (file, datetime(int(year), int(month), int(day), 0, 0, 0, 0), fsize))
- print("year=%s month=%s day=%s hour=0 minut=0 length=%s" % (year, month, day, fsize))
- else:
- year, month, day, rem = file.split("-")
- compact_moment = rem.split(".")[0]
- hour = compact_moment[:2]
- minut = compact_moment[2:]
- cur.execute("INSERT INTO files VALUES (?, ?, ?);", (file, datetime(int(year), int(month), int(day), int(hour), int(minut), 0, 0), fsize))
- print("year=%s month=%s day=%s hour=%s minut=%s length=%s"%(year, month, day, hour, minut, fsize))
- f.close()
- conn.commit()
- conn.close()
-
- def unpopulate_table_files():
- conn = sqlite3.connect("files.db", detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
- cur = conn.cursor()
- cur.execute("DELETE FROM files;")
- conn.commit()
- conn.close()
-
- def query_from_table_files(number=30):
- conn = sqlite3.connect("files.db", detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
- cur = conn.cursor()
- cur.execute("SELECT * FROM files;")
- rows = cur.fetchmany(number)
- conn.close()
- for row in rows:
- print(row)
-
- def query_from_table_files_with_moment(moment=datetime(2020,11,21,15,23,0,0)):
- conn = sqlite3.connect("files.db", detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
- cur = conn.cursor()
- cur.execute("SELECT * FROM files;")
- rows = cur.fetchall()
- conn.close()
- for row in rows:
- if row[1] >= moment:
- print(row)
-
-
- def drop_table_files():
- conn = sqlite3.connect("files.db", detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
- cur = conn.cursor()
- cur.execute("DROP TABLE files;")
- conn.commit()
- conn.close()
-
-
- # create_table_files()
- # drop_table_files()
- # populate_table_files()
- # unpopulate_table_files()
- # query_from_table_files()
- query_from_table_files_with_moment()
|