def native_tx_to_bin_sqlite(tx):
"""
Converts a native tuple tx into a bin tuple for sqlite
:param tx:
:return:
"""
return (tx[0], tx[1], tx[2], tx[3], f8_to_int(tx[4]),
Binary(b64decode(tx[5])), Binary(b64decode(tx[6])),
Binary(b64decode(tx[7])), f8_to_int(tx[8]), f8_to_int(tx[9]),
tx[10], tx[11])
def akazer(pics=None, akaze=None, columns='ak_points,ak_desc'):
"""
Not just for AKAZE any more!
Parameters
----------
pics = {id: local-path-to-pic, ...}
akaze = cv2.AKAZE_create() or cv2.ORB_create() or cv2.KAZE_create() etc..
columns = two comma separated column names for the new data
Returns
-------
list of image data formatted for entering into cards database
"""
new_data = []
if pics is None:
pics = cards()
if akaze is None:
akaze = cv2.AKAZE_create()
c1, c2 = columns.split(',')
for kk, vv in pics.viewitems():
if vv:
im = cv2.imread(vv)
else:
im = None
if im is not None:
akps, adesc = akaze.detectAndCompute(im, None)
jk = [(a.pt, a.angle, a.class_id, a.octave, a.response, a.size) for a in akps]
new_data.append({'id': kk, c1: jk, c2: Binary(adesc.dumps())})
else:
print("for id: {}, akazer failed to find pic on path: {}".format(kk, vv))
return new_data
def to_bin_tuple(self, sqlite_encode=False):
"""
The transaction object as a bin tuple in the following order:
'block_height', 'timestamp', 'address', 'recipient', 'amount', 'signature', 'public_key', 'block_hash',
'fee', 'reward', 'operation', 'openfield'
Bin format means amounts will be integers, and all content unencoded.
"""
if sqlite_encode:
# sqlite needs .binary() to encode blobs
return (self.block_height, self.timestamp, self.address, self.recipient, self.amount,
Binary(self.signature), Binary(self.public_key), Binary(self.block_hash),
self.fee, self.reward, self.operation, self.openfield)
return (self.block_height, self.timestamp, self.address, self.recipient, self.amount, self.signature,
self.public_key, self.block_hash, self.fee, self.reward, self.operation, self.openfield)
def match(self, s, db, soffset=0, offsets={}):
y = self.sparse(s)
# keys for the signal
keys = self.extractKeys(y)
# Quantiztion factor for the offsets
qt = max(self.dictionary.sizes)
for hash_key, offset in keys:
result = db.selectFingerprints(Binary(hash_key)[0:5])
for r in result:
if not r[0] in offsets:
offsets[r[0]] = []
offsets[r[0]].append((r[1] - offset - soffset) // qt * qt)
maxsongid = None
max_offset = 0.
second_max = 0.
# proceed the histogram
for song, o in offsets.items():
# extract the most common offset
tmp_max = o.count(max(set(o), key=o.count))
if tmp_max >= max_offset:
second_max = max_offset
max_offset = tmp_max
maxsongid = song
elif tmp_max > second_max:
second_max = tmp_max
songid = None
if max_offset > 4 and (second_max == 0 or max_offset / second_max >
1 + 0.6 / np.log(max_offset)):
songid = maxsongid
return songid, offsets
def stopOperation(self):
self.toggleState(False)
self.resourceWorker.terminate()
self.resourceWorker.runThread = False
self.geneticAlgorithm.terminate()
self.timer.stop()
if len(self.topChromosomes):
self.parent.btnStop.setText('View Result')
self.parent.btnStop.clicked.disconnect(self.stopOperation)
self.parent.btnStop.clicked.connect(self.dialog.close)
self.parent.lblCPU.setText('CPU Usage: Stopped')
self.parent.lblMemory.setText('Memory Usage: Stopped')
self.parent.lblStatus.setText('Status: Stopped')
self.totalResource['cpu'] = mean(self.totalResource['cpu'])
self.totalResource['memory'] = mean(self.totalResource['memory'])
self.meta = [[chromosome[1], chromosome[0].fitnessDetails] for chromosome in
self.topChromosomes]
conn = db.getConnection()
cursor = conn.cursor()
cursor.execute('INSERT INTO results (content) VALUES (?)', [Binary(
pickle.dumps({'data': [chromosome[0].data for chromosome in self.topChromosomes],
'meta': self.meta,
'time': self.time.toString('hh:mm:ss'),
'resource': self.totalResource,
'rawData': self.data},
pickle.HIGHEST_PROTOCOL))])
conn.commit()
conn.close()
else:
self.dialog.close()
def test_insert_image_blob(self):
img = new("RGB", (256, 256), "red")
data = img_to_buf(img, 'jpeg').read()
tempDB = self.__make_tempDB()
tempDB.insert_image_blob(0, 0, 0, Binary(data))
result = tempDB.cursor.execute("select count(*) from tiles;")
assert result.fetchone()[0] == 1
def sign_up_user():
"""
Register user.
Confirms whether login and password are in range and whether user exist.
Hashes and encrypts password using :func:`codec`.
Stores user's data in database.
request: {
"username": str,
"password": str
}
:return: dict of execution status
:rtype: {
"login_out_of_range": bool,
"password_out_of_range": bool,
"ok": bool
}
"""
username = request.authorization.username
password = request.authorization.password
# Make sure credentials are in range.
if len(username) not in range(4, 20, 1):
return {"login_out_of_range": True}
elif len(password) not in range(4, 20, 1):
return {"login_out_of_range": False, "password_out_of_range": True}
connection = create_connection("data.sqlite3")
select_user = f"SELECT id FROM users WHERE username LIKE :username"
query_data = execute_read_query(connection, select_user, 0,
{'username': username})
# If user isn't registered, encrypt password and store in database.
if query_data is None:
password_hash = codec(password, 1)
password_hash = Binary(password_hash)
data_dict = {'username': username, 'password_hash': password_hash}
create_user = f"INSERT INTO users (username, password_hash, registered)" \
f"VALUES (:username, :password_hash, strftime('%s','now'))"
execute_query(connection, create_user, data_dict)
else:
connection.close()
return {
"login_out_of_range": False,
"password_out_of_range": False,
'ok': False
}
connection.close()
return {
"login_out_of_range": False,
"password_out_of_range": False,
'ok': True
}
def data2db(datas: List[str]): # 데이터를 DB에 삽입
now = datetime.datetime.today().strftime("%Y-%m-%d %H:%M")
for data in datas:
file = data.split('\\')[-1]
file_name, file_extension = file.split('.')
with open(data, 'rb') as blob:
blob_data = blob.read()
DbWriter(db_path).insert(file_name, file_extension, Binary(blob_data), now)
def processLog(self, file_path):
# process sequence log
if isfile(file_path):
with open(file_path, 'rb') as log_file:
ablob = log_file.read()
return Binary(ablob)
else:
return None
async def save(self, task: TaskInterface):
async with aiosqlite.connect(self.storage_path) as con:
await con.execute(
"INSERT INTO tasks(ID, NAME, URL, COMMAND, OBJECT) VALUES (?, ?, ?, ?, ?)",
[
str(task.task_id), task.name, task.url, task.command_type,
Binary(pickle.dumps(task))
])
await con.commit()
def dump_resource(self, resource):
""" Dump the resource to the database. Check for redirects.
"""
url_id = self._select_url(resource.url)
for name, value in resource.headers.items():
self.execute("INSERT INTO header(url_id, name, value) " \
"VALUES (?, ?, ?)", url_id, name, value)
content = Binary(resource.content)
self.execute("INSERT INTO content(url_id, content, hash) " \
"VALUES (?, ?, ?)", url_id, content, resource.hash)
def insert_tile(self, zoom, row, col, data):
with self.db_connection as db_connection:
cursor = db_connection.cursor()
compression = self.compression
data_type = self.data_type
if compression and (data_type == "xray"):
db_connection.text_factory = str
data_compressed = blosc.pack_array(data, cname=compression)
data = data_compressed
if data_type == "image/TIFF":
try:
assert data.dtype == "uint8"
except:
raise TypeError("dtype %s not supported" % data.dtype)
image = Image.fromarray(np.uint8(data))
buf = ioBuffer()
if compression == "tiff_lzw":
TiffImagePlugin.WRITE_LIBTIFF = True
image.save(buf, "TIFF", compression=compression)
TiffImagePlugin.WRITE_LIBTIFF = False
else:
image.save(buf, "TIFF", compression=compression)
buf.seek(0)
data = Binary(buf.read())
if data_type == "image/JPEG2000":
try:
assert data.dtype == "uint8"
except:
raise TypeError("dtype %s not supported" % data.dtype)
image = Image.fromarray(np.uint8(data))
buf = ioBuffer()
image.save(buf, "j2k")
buf.seek(0)
data = Binary(buf.read())
try:
cursor.execute("""
INSERT INTO tiles
(zoom_level, tile_row, tile_column, tile_data)
VALUES (?,?,?,?)
""", (zoom, row, col, data))
except:
raise
def makeMultiLineString(c):
if c.partCount==1:
values = ["<BI",1,2]
[ptrn,coords]=lineSt(c.getPart(0))
elif c.partCount>1:
values = ["<BI",1,5]
[ptrn,coords]=multiLine(c)
else:
return False
values[0]+=ptrn
values.extend(coords)
return Binary(pack(*values))
def makeMultiPolygon(c):
if c.partCount==1:
values = ["<BI",1,3]
[ptrn,coords]=linearRing(c.getPart(0))
elif c.partCount>1:
values = ["<BI",1,6]
[ptrn,coords]=multiRing(c)
else:
return False
values[0]+=ptrn
values.extend(coords)
return Binary(pack(*values))
def _itercompress(self,
content,
isinstance=isinstance,
str=str,
bytes=bytes,
Binary=Binary):
"""Iteratively compress chunks generated by callable `content`"""
compressor = compressobj()
for uncompressed_chunk in content():
if isinstance(uncompressed_chunk, str):
chunk = compressor.compress(uncompressed_chunk.encode())
elif isinstance(uncompressed_chunk, bytes):
chunk = compressor.compress(uncompressed_chunk)
else:
_type = type(uncompressed_chunk).__name__
raise TypeError("Content chunk is not str or bytes", _type)
if chunk:
yield Binary(chunk)
chunk = compressor.flush(Z_FINISH)
if chunk:
yield Binary(chunk)
def store_spatial_index(self, geofield):
spindex = self.workspace.spatial_indexes.pop((self.name, geofield))
spindex.close()
temppath = spindex._temppath
# get the byte data from the files
with open(temppath + '.idx', 'rb') as fobj:
idx = Binary(fobj.read())
with open(temppath + '.dat', 'rb') as fobj:
dat = Binary(fobj.read())
# delete the temporary files from disk
os.remove(temppath + '.idx')
os.remove(temppath + '.dat')
# update the idx and dat columns in the spatial_indexes table
idxtable = self.workspace.table('spatial_indexes')
cur = idxtable._cursor()
cur.execute(
'''UPDATE {rtree} SET rtree_idx=:idx, rtree_dat=:dat,
WHERE tbl = '{table}' AND col = '{geofield}' '''.format(
rtree=idxtable.name, table=self.name, geofield=geofield),
dict(idx=idx, dat=dat))
cur.close()
def car_info(self, url):
soup = self.get_response(url)
left_index = soup.find("div", {"class": "xq-left"}).findAll('p')
name = left_index[0].text
image_byte = requests.get(left_index[1].img['src']).content
right_index = soup.find("ul", {"class": "xq-right"}).findAll('li')
founded = right_index[3].span.text
models = right_index[5].span.text
website = right_index[7].span.text
print("Insert Car Logo {}".format(name))
_sql = "insert into car_logo(name,image,founded,models,website) values (?,?,?,?,?)"
self.db.insert(_sql, (name, Binary(image_byte), founded, models, website))
def test_combine_worker_dbs():
session_folder = make_session_folder()
# make a random number of tempdbs with dummy data
img = new("RGB", (256, 256), "red")
data = img_to_buf(img, 'jpeg').read()
z = randint(2, 5)
for x in xrange(z):
TempDB(session_folder).insert_image_blob(x, 0, 0, Binary(data))
# confirm that combine_worker_dbs assimilates all tempdb's into gpkg
chdir(session_folder) # necessary to put gpkg in session_folder
gpkg = Geopackage("test.gpkg", 4326)
combine_worker_dbs(gpkg)
result = gpkg.execute("select count(*) from tiles;")
assert (result.fetchone())[0] == z
def update(self, desc="blobs/update"):
"""Run `self.__download_as_blob()` and insert result (optionally compressed) into `self.table` as BLOB"""
blob = Binary(bytes(self.compressor(self.__download_as_blob())))
retrieved_at = int(datetime.now().timestamp())
with self.sqltransactions.exclusive(desc) as (connection, execute):
if self.is_stale(ignore_conflicts=True) is False:
return # data was updated while waiting to acquire lock
self.drop(connection=connection)
execute(f"""INSERT INTO `{self.table}`
(`identifier`,`blob`,`timestamp`,`retrieved_at`)
VALUES(?,?,?,?)""", [
self.identifier, blob, self.timestamp, retrieved_at])
msg = f"Inserted new blob into {self.table}"
GeneFabLogger.info(f"{msg}:\n {self.identifier}")
def dump_wkb(self):
# geometry memoryview to sqlite3 db blob
# py2: db requires buffer, py3: db requires memview
if self._wkb:
# use existing wkb
wkb_mem = self._wkb
elif self._shp != None:
# if no wkb, then dump from shp
if self._shp.is_empty:
return None
wkb_mem = memoryview(self._shp.wkb)
else:
raise Exception('Geometry must have _wkb or _shp, but has neither')
if PY2:
wkb_mem = buffer(wkb_mem.tobytes())
return Binary(wkb_mem)
def compressXelapedia(source, dest):
createXelapedia(dest)
# connect with the database with the compressed articles
con = sqlite.connect(dest)
con.text_factory = str
# connect the database with the uncompressed articles
con.execute('ATTACH ? AS source', (source, ))
# update the configuration
con.execute('UPDATE config SET value=\'lzma\' WHERE key=\'type\'')
# empty the destination database
con.execute('DELETE FROM articles')
con.execute('DELETE FROM titles')
con.execute('DELETE FROM redurects')
# copy the titles
con.execute('INSERT INTO titles(title, article_id) ' +
'SELECT title, article_id FROM source.titles')
con.commit()
# we dont need the table attached directly anymore
con.execute('DETACH source')
conSource = sqlite.connect(source)
conSource.text_factory = str
# now copy and compress the articles
#con.create_function('compress', 1, compressFunction)
#con.execute('INSERT INTO articles(id, contents) ' +
# 'SELECT id, compress(contents) FROM source.articles ORDER BY id')
cur = conSource.execute('SELECT id, contents FROM articles ORDER BY id')
for id, uncompressed in cur:
compressed = Binary(compress(uncompressed))
con.execute('INSERT INTO articles(id, contents) VALUES(?,?)', (
id,
compressed,
))
stdout.write('.')
stdout.flush()
con.commit()
def sqlite_geoms():
print 'load shapefile'
t = time()
#data = pg.VectorData(r"C:\Users\kimok\Desktop\gazetteer data\raw\global_urban_extent_polygons_v1.01.shp", encoding='latin')
#data = pg.VectorData(r"C:\Users\kimok\Desktop\gazetteer data\raw\atlas_urban.geojson", encoding='latin')
data = pg.VectorData(
r"C:\Users\kimok\Desktop\gazetteer data\raw\global_settlement_points_v1.01.shp",
encoding='latin')
#data = pg.VectorData(r"C:\Users\kimok\Desktop\gazetteer data\raw\ne_10m_admin_0_countries.shp", encoding='latin')
print time() - t
print 'making shapely'
t = time()
shapelys = [
shape(f.geometry) for f in data
] # CRUCIAL SPEEDUP, SHAPELY SHOULD BE FROM SHAPE, NOT ASSHAPE WHICH IS INDIRECT REFERENCING
print time() - t
print 'dump wkb'
t = time()
wkbs = [s.wkb for s in shapelys]
print time() - t
print 'convert to binary'
from sqlite3 import Binary
t = time()
blobs = [Binary(wkb) for wkb in wkbs]
print time() - t
print 'insert wkb into db'
fields = ['ID', 'geom']
typs = ['int', 'BLOB']
w = Writer('testgeodb::data', fields=zip(fields, typs), replace=True)
t = time()
for i, blb in enumerate(blobs):
w.add([i, blb])
print time() - t
print 'load wkb from db'
t = time()
shapelys = [wkb_loads(bytes(blb)) for ID, blb in w.select('*')]
print time() - t
def rast_to_wkb(rast):
# raster to wkb buffer
wkb = rast.wkb
buf = Binary(wkb)
return buf
def test_execute_query(self):
passwords = {'pwd1': Binary(b'alb'), 'pwd2': Binary(b'ing'), 'pwd3': Binary(b'dan'), 'pwd4': Binary(b'sus')}
self.assertIsNone(execute_query(self.connection, queries['add_users'], passwords))
self.assertIsNone(execute_query(self.connection, queries['add_messages']))
def get_pickle(data):
mypickle = pickle.dumps(data, protocol=2)
mybinary = Binary(mypickle)
return mybinary
def _dict_to_blob(self, dict):
dict = json.dumps(dict).encode('utf-8')
return Binary(dict)
def shapely_to_wkb(shp):
# shapely to wkb buffer
wkb = shp.wkb
buf = Binary(wkb)
return buf
def geoj_to_wkb(geoj):
# geojson to wkb buffer
wkb = shape(geoj).wkb
buf = Binary(wkb)
return buf
def _write_uuid_to_sqlite3(uuid_):
#return buffer(uuid_.bytes_le)
return Binary(uuid_.bytes_le)
def _write_numpy_to_sqlite3(arr):
out = io.BytesIO()
np.save(out, arr)
out.seek(0)
#return buffer(out.read())
return Binary(out.read())