nkFuGMFk

1import io, pickle, sqlite3, sys, pandas
2import numpy as np
3
4def parse(results):
5    return results.apply(unpack_qc)
6
7def unpack_qc(value):
8    print(value, type(value))
9    qc = np.load(io.BytesIO(value))
10
11    return qc
12
13def pack_array(arr):
14    out = io.BytesIO()
15
16    if type(arr) is np.ndarray:
17        np.save(out, arr)
18    out.seek(0)
19    return sqlite3.Binary(out.read())
20  
21def dbinteract(command, values=[], tries=0):
22  '''
23  execute the given SQL command;
24  catch errors and retry a maximum number of times;
25  '''
26
27  max_retry = 100
28
29  conn = sqlite3.connect('serialize.db', isolation_level=None, timeout=60)
30  cur = conn.cursor()
31
32  try:
33    cur.execute(command, values)
34    try:
35      result = cur.fetchall()
36    except:
37      result = None
38    cur.close()
39    conn.close()
40    return result
41  except:
42    print('bad db request')
43    print(command)
44    print(values)
45    print(sys.exc_info())
46    conn.rollback()
47    cur.close()
48    conn.close()
49    if tries < max_retry:
50      dbinteract(command, values, tries+1)
51    else:
52      print('database interaction failed after', max_retry, 'retries')
53      return -1
54
55conn = sqlite3.connect('serialize.db', isolation_level=None)
56cur = conn.cursor()
57query = "CREATE TABLE IF NOT EXISTS cereal (truth BLOB, uid INTERGER PRIMARY KEY);"
58cur.execute(query)
59
60# ends in zero
61data = np.zeros(2, dtype=bool)
62query = "INSERT INTO cereal VALUES(?,?);"
63dbinteract(query, [pack_array(data), 0] )
64# doesnt end in zero
65data = np.ones(3, dtype=bool)
66query = "INSERT INTO cereal VALUES(?,?);"
67dbinteract(query, [pack_array(data), 1] )
68data = np.zeros(5, dtype=bool)
69
70print('\n===== unpack without a bytes typecast directly, works fine =====\n')
71query = 'SELECT uid, truth FROM cereal;'
72cur.execute(query)
73rawresults = cur.fetchall()
74print(unpack_qc(rawresults[0][1]))
75print(unpack_qc(rawresults[1][1]))
76
77print('\n===== unpack without a bytes typecast from df, works fine =====\n')
78df = pandas.DataFrame(rawresults)
79df.columns = ['uid', 'truth']
80df[['truth']] = df[['truth']].apply(parse)
81print(df['truth'])
82
83print('\n===== unpack with a bytes typecast from df, fails =====\n')
84df = pandas.DataFrame(rawresults).astype('bytes')
85df.columns = ['uid', 'truth']
86df[['truth']] = df[['truth']].apply(parse)
87print(df['truth'])