I am building a software for a pharmacy to keep track of the pharmacy sales. Iam using VS Code on windows 10 I decided to make a toy data to test the software with. so first I created a table called sales in the sales.sqlite database file. The table had five columns (time_stamp: INTEGER, medications:REAL, beauty:REAL, total:REAL, user:TEXT). Then I randomly generated 10000 Unix timestamps and stored them in timestamps.txt file in order to load them later. after that I executed the following python code to store my toy data in the sales table in sales.sqlite file:
# load the timestamp and store them in a list
with open('timestamps.txt', 'r', encoding='utf-8') as f:
time_stamps = [int(stamp) for stamp in f.read().split('\n')]
# sort the timestamps
time_stamps.sort()
import random
# generate random values for each variable
medications = [random.randint(100, 5000) for i in range(10000)]
beauty = [random.randint(0, 5000) for i in range(10000)]
total = [medications[i] + beauty[i] for i in range(10000)]
user = [random.choice(['abosoar', 'abosoar1']) for i in range(10000)]
import sqlite3
# create entries by zipping the generated values of each variable
# Iterate over enteries and eatch time store one
for entry in zip(time_stamps, medications, beauty, total, user):
db = sqlite3.connect('databases\\sales.sqlite')
c = db.cursor()
c.execute('INSERT INTO sales (time_stamp, medications, beauty, total, user) VALUES (?, ?, ?, ?, ?);', entry)
db.commit()
c.close()
db.close()
After less than 30 seconds the IDE has crashed and the free storage of my C:\ drive fall from 2.3+ GB to 79 MB only!!
I cleaned temp files by typing %temp% and temp in the run window but I only restored 200 MB of my free storage.
I used advanced system care program to delete junk files but I only restored 500 MB using this method.
I noticed that there is a files called pagefile.sys and hiberfile.sys that have been modified when the code was running and their sizes are 2.9 GB and 1.6 GB I am wondering if there is any relation between them and the problem.
There are two problems now:
First: I can't free the space sqlite has taken
Second: the software should work in low space PC so the storage problem has to be addressed in order for the software to run.
I hope the above information could help to solve my problem.
When computer has not enough RAM to keep all running programs and data then it may try to move some information on disk to file pagefile.sys - and later it may try to get it back (when programs will need it).
Similar when you turn off computer in hibernate mode then it saves RAM in file hiberfile.sys to get it back when you restart computer. If you had many data in RAM then it would save many information in hibernate.sys.
If you restart computer in normal way (without hibernate or sleep mode) then maybe it will clear pagefile.sys - or you may try to remove it and it should recreate it without alll old data (which it can't get after normal restart).
As for me you don't create so much data in this code but maybe your computer has problem with all data created with [... for i in range(10000)]. You could use less RAM if you would create single elements directly in main for-loop without keeping all in memory.
I runs for-loop which keeps in memory only one line from file. It also create only one medications, beauty, total, user in every loop.
import sqlite3
import random
# --- before loop ---
db = sqlite3.connect('databases\\sales.sqlite')
c = db.cursor()
# --- loop ---
for line in open('timestamps.txt'): #, encoding='utf-8'):
time_stamp = int(line)
medications = random.randint(100, 5000)
beauty = random.randint(0, 5000)
total = medications + beauty
user = random.choice(['abosoar', 'abosoar1'])
c.execute('INSERT INTO sales (time_stamp, medications, beauty, total, user) VALUES (?, ?, ?, ?, ?);', (time_stamp, medications, beauty, total, user)
db.commit()
# --- after loop ---
c.close()
db.close()