import sys
import time
import sqlite3
import string
import random
import tables
import pandas as pd
from collections import deque  

NUMBER_OF_RECORDS_TO_POPULATE = 1000000 # one millions

def get_size(obj, seen=None):
    """Recursively finds size of objects"""
    size = sys.getsizeof(obj)
    if seen is None:
        seen = set()
    obj_id = id(obj)
    if obj_id in seen:
        return 0
    # Important mark as seen *before* entering recursion to gracefully handle
    # self-referential objects
    seen.add(obj_id)
    if isinstance(obj, dict):
        size += sum([get_size(v, seen) for v in obj.values()])
        size += sum([get_size(k, seen) for k in obj.keys()])
    elif hasattr(obj, '__dict__'):
        size += get_size(obj.__dict__, seen)
    elif hasattr(obj, '__iter__') and not isinstance(obj, (str, bytes, bytearray)):
        size += sum([get_size(i, seen) for i in obj])
    return size

def random_char(y):
    """Generate a random string of a given size"""
    return ''.join(random.choice(string.ascii_letters) for x in range(y))

def populate_listoflist(n):
    llist=[]
    st = time.time()
    for i in range(n):
        target_seq = random_char(20)
        start = 20
        stop = 40
        seqid = random_char(10)
        item = [target_seq, start, stop, seqid]
        llist.append(item)
        if (i % (n/5)) == 0:
            print (i, get_size(llist)/1e9, "GB", "Each entry size", get_size(item))
    et = time.time()
    print (f'Total Run Time:{(et-st):.8f}', "Total Size: ", get_size(llist)/1e9,"GB")
    return llist

lol = populate_listoflist(NUMBER_OF_RECORDS_TO_POPULATE) # slows down with more record.

def populate_listofset(n):
    llist=[]
    st = time.time()
    for i in range(n):
        target_seq = random_char(20)
        start = 20
        stop = 40
        seqid = random_char(10)
        item = (target_seq, start, stop, seqid)
        llist.append(item)
        if (i % (n/5)) == 0:
            print (i, get_size(llist)/1e9, "GB", "Each entry size", get_size(item))
    et = time.time()
    print (f'Total Run Time:{(et-st):.8f}', "Total Size: ", get_size(llist)/1e9,"GB")
    return llist

los = populate_listofset(NUMBER_OF_RECORDS_TO_POPULATE)

df = pd.DataFrame(los, columns = ['target_seq', 'start','stop', 'seqid'])
print("Size of dataframe:", get_size(df)/1e9, "GB")

def populate_dictoflist(n):
    ldict = dict(target_seq=[], start=[], stop=[], seqid=[])
    st = time.time()
    for i in range(n):
        ldict["target_seq"].append(random_char(20))
        ldict["start"].append(20)
        ldict["stop"].append(40)
        ldict["seqid"].append(random_char(10))
        if (i % (n/5)) == 0:
            print (i, get_size(llist)/1e9, "GB", "Each entry size", get_size(item))
    et = time.time()
    et = time.time()
    print (f'Total Run Time:{(et-st):.8f}', "Total Size: ", get_size(llist)/1e9,"GB")
    return ldict

# dol = populate_dictoflist(NUMBER_OF_RECORDS_TO_POPULATE)

def populate_listofset_withdeque(n):
    llist = deque([])
    st = time.time()
    for i in range(n):
        target_seq = random_char(20)
        start = 20
        stop = 40
        seqid = random_char(10)
        item = (target_seq, start, stop, seqid)
        llist.append(item)
        if (i % (n/5)) == 0:
            print (i, get_size(llist)/1e9, "GB", "Each entry size", get_size(item))
    et = time.time()
    print (f'Total Run Time:{(et-st):.8f}', "Total Size: ", get_size(llist)/1e9,"GB")
    return llist

los_withdeque = populate_listofset_withdeque(NUMBER_OF_RECORDS_TO_POPULATE)

h5f.close()
h5f = tables.open_file('targets.h5', 'w')
class Target(tables.IsDescription):
    targetSeq = tables.StringCol(16) 
    targetStart = tables.Int64Col() 
    targetStop = tables.Int64Col() 
    targetSeqID = tables.StringCol(16) 

def populate_pytable(n):
    tbl = h5f.create_table('/', 'targetseq', Target, title="Target Sequences")
    row = tbl.row
    st = time.time()
    for i in range(n):
        row['targetSeq'] = random_char(20)
        row['targetStart'] = 20
        row['targetStop'] = 40
        row['targetSeqID'] = random_char(20)
        row.append()
        if (i % (n/5)) == 0:
            print (i, get_size(llist)/1e9, "GB", "Each entry size", get_size(item))
    et = time.time()
    print (f'Total Run Time:{(et-st):.8f}', "Total Size: ", get_size(tbl)/1e9,"GB")
    return tbl

#py_table = populate_pytable(NUMBER_OF_RECORDS_TO_POPULATE)
py_table = populate_pytable(10)

py_table.flush() # to free up memory
i

i = 0
for r in py_table:
    if i < 10:
        targetSeq = r['targetSeq']
        targetStart = r['targetStart']
        targetStop = r['targetStop']
        targetSeqID = r['targetSeqID']
        print(i, r, targetSeq, targetStart, targetStop,targetSeqID)
        i += 1
       

h5f.close() # to close file 

# create a db in memory
db = sqlite3.connect("file::memory:")
cur = db.cursor()

# Create table
cur.execute('''CREATE TABLE target
             (targetseq text, start int, stop int, seqid text)''')

def sql_populate(n):
    st = time.time()
    for i in range(n):
        item = [random_char(20), 20, 40, random_char(10)]
        cur.execute('insert into target values (?,?,?,?)', item)
        if (i % (n/5)) == 0:
            print (i, get_size(db)/1e9, "GB", "Each entry size", get_size(item))
    et = time.time()
    print (f'Total Run Time:{(et-st):.8f}', "Total Size: ", get_size(db)/1e9,"GB")
    return db

sql_populate(NUMBER_OF_RECORDS_TO_POPULATE)

# retriving information from sql db - rowwise without fetching whole db into memory
cur.row_factory = sqlite3.Row
cursor = cur.execute("SELECT targetseq FROM target")

# for row in cursor:
#     print(row[0])

tseq = []
for row in cursor:
    tseq.append(row[0])
tseq[1:10]

get_size(tseq)/ 1e9

cur.execute('SELECT * FROM target')
data = cur.fetchall()
print(get_size(data)/1e9,"GB")

i = 0
for row in data:
    eachrow = [row['targetseq'],row['start'],row['stop'],row['seqid']] 
    if i < 10:
        print(eachrow)
        i = i + 1
    

cur.close()
db.commit()
db.close()

# create a db in memory
db = sqlite3.connect("file::memory:")
cur = db.cursor()
# Create table
cur.execute('''CREATE TABLE target
             (targetseq text, start int, stop int, seqid text)''')

sql_populate(NUMBER_OF_RECORDS_TO_POPULATE*10)

cur.row_factory = sqlite3.Row
cursor = cur.execute("SELECT targetseq FROM target")
tseq = []
for row in cursor:
    tseq.append(row[0])
tseq[1:10]

cur.execute('SELECT * FROM target')
data = cur.fetchall()
print(get_size(data)/1e9,"GB")

i = 0
for row in data:
    eachrow = [row['targetseq'],row['start'],row['stop'],row['seqid']] 
    if i < 10:
        print(eachrow)
        i = i + 1

cur.close()
db.commit()
db.close()

def dataiter(n):
    for i in range(n):
        yield( (random_char(20), 20, 40, random_char(10)))

n = 1000000
t1 = time.time()
a = pd.DataFrame(dataiter(n),
                 columns = ['target_seq', 'start','stop', 'seqid'])
#a = pd.DataFrame(dataiter(n))
t2 = time.time()
print(t2-t1)

get_size(a)

a.target_seq = a.target_seq.astype('str')
a.start = a.start.astype('int8')
a.stop = a.stop.astype('int8')
a.seqid = a.seqid.astype('str')

get_size(a)