def procedure_grille(length: int, inds: tuple, industrie: defaultdict,
bdd: sqlite3.Cursor):
i = 0
while True:
if inds == (0, 0):
id1 = (inds[0] + 1, inds[1])
id2 = (inds[0], inds[1] + 1)
[industrie[id1].reception.put(x) for x in industrie[inds].memoire]
[industrie[id2].reception.put(x) for x in industrie[inds].memoire]
else:
print("lecture:", inds, file=sys.stderr)
grille = industrie[inds].reception.get()
print(type(grille))
for grid in industrie.get(inds).memooire:
calcul = add(grille, grid)
if calcul is not None:
id1 = (inds[0] + 1, inds[1])
id2 = (inds[0], inds[1] + 1)
industrie[id1].reception.put(calcul)
industrie[id2].reception.put(calcul)
if inds == (length, length):
grille = industrie[inds].reception.get()
print(grille)
i += 1
cmd = "INSERT INTO Mokrwaze(mo_num, ligne) VALUES (?, ?)"
bdd.executemany(cmd, ["".join(x) for x in grille])
def download_data(self, cursor: sqlite3.Cursor, past_days: int):
today = date.today()
end = today.strftime("%Y-%m-%d")
start = (today - timedelta(days=past_days)).strftime("%Y-%m-%d")
url_params = f"?start={start}&end={end}"
if not confirmation_dialog_result(cursor):
return
with urllib.request.urlopen(
f"http://api.coindesk.com/v1/bpi/historical/close.json{url_params}"
) as url:
data = json.loads(url.read().decode())
data_to_insert = [(close_date, data['bpi'][close_date])
for close_date in data['bpi']]
cursor.execute("""
DROP table IF EXISTS prices;
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS prices(
close_date VARCHAR(255) PRIMARY KEY,
usd_price FLOAT
);
""")
cursor.executemany(
"""
INSERT INTO prices VALUES(?, ?);
""", data_to_insert)
self.db_conn.commit()
def dump(x: list, y: sqlite3.Cursor) -> None:
create_tables = """
CREATE TABLE IF NOT EXISTS Weights (
id INTEGER PRIMARY KEY AUTOINCREMENT,
class_id INTEGER,
feature_id INTEGER,
value REAL,
FOREIGN KEY (class_id) REFERENCES classes(id),
FOREIGN KEY (feature_id) REFERENCES features(id)
)
CREATE TABLE IF NOT EXISTS Classes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
class STRING UNIQUE
)
CREATE TABLE IF NOT EXISTS Features (
id INTEGER PRIMARY KEY AUTOINCREMENT,
feature STRING UNIQUE
)
"""
cmd_id_val = """
SELECT value,id FROM Weights
WHERE class_id=EXISTS(SELECT id FROM Classes WHERE class=?)
AND
feature_id=EXISTS(SELECT id FROM Features WHERE feature=?)
"""
cmd_update = "UPDATE Weights SET value=? WHERE id=?"
y.executemany(cmd_id_val, map(lambda r: r[:-1], x))
tmp = y.fetchall()
y.executemany(cmd_update, tmp)
def parse_one_csv(cursor: Cursor, in_file: typing.Union[io.IO[str],
io.TextIO]):
# отбрасываем первые 5 строк из-за которых csv не csv
for _ in range(6):
in_file.readline()
reader = csv.DictReader(in_file, delimiter=';', quotechar='"')
n_lines = 0
args = []
for n_lines, line in enumerate(reader):
this_date, this_time = (line[JUKORAMA_HEADER].split())
this_date = datetime.datetime.strptime(this_date,
"%d.%m.%Y").date().toordinal()
this_time = datetime.datetime.strptime(this_time, "%H:%M").time()
minuts = to_int(this_time)
try:
args.append(
make_args_for_request(this_date, minuts,
int(float(line[TEMP_HEADER]))))
except ValueError:
logger.error(
"can't pass this line {line}. probably {temp} is not a number",
line=line,
temp=line[TEMP_HEADER])
cursor.executemany(SQL_INSERT, args)
logger.info("updated to DB {n_lines} lines.", n_lines=n_lines)
def partition_geocode(con: sqlite3.Connection, cur: sqlite3.Cursor, quarter: str, county_cht: str):
""" Geocode address of the same county in quarter fashion """
cur.execute('''SELECT 土地區段位置或建物區門牌 FROM "{0}/TRX"
WHERE 縣市 = ?
GROUP BY 土地區段位置或建物區門牌;'''.format(quarter), (county_cht,))
for address, in cur.fetchall():
cur.execute('''SELECT GEO.編號
FROM "{0}/TRX" AS TRX, "{0}/GEO" AS GEO
WHERE TRX.編號 = GEO.編號
AND TRX.土地區段位置或建物區門牌 = ?
AND GEO.LAT_Avg ISNULL;'''.format(quarter), (address,))
identities = cur.fetchall()
if not identities:
continue
print("[%d] "%(len(identities)) + address)
try:
results = selective_geocode(address)
except geo.AddressError:
continue
if len(results["lat"]) != 5 or len(results["lon"]) != 5:
continue
results["lat"].append(sum(results["lat"]) / len(results["lat"]))
results["lon"].append(sum(results["lon"]) / len(results["lon"]))
combined = [num for zipped in zip(results["lat"], results["lon"]) for num in zipped]
values = [(tuple(combined) + identity) for identity in identities]
cur.executemany('''UPDATE "{0}/GEO" SET
LAT_1 = ?, LON_1 = ?,
LAT_2 = ?, LON_2 = ?,
LAT_3 = ?, LON_3 = ?,
LAT_4 = ?, LON_4 = ?,
LAT_5 = ?, LON_5 = ?,
LAT_Avg = ?, LON_Avg = ?
WHERE 編號 = ?;'''.format(quarter), values)
con.commit()
def add(self, swhids: Iterable[str], chunk_size: int, cur: sqlite3.Cursor):
"""Insert the SWHID inside the database."""
for swhids_chunk in grouper(swhids, chunk_size):
cur.executemany(
"""INSERT INTO swhids VALUES (?)""",
[(swhid_chunk, ) for swhid_chunk in swhids_chunk],
)
def insert_tag_mappings(
cursor: Cursor,
data: Union[List[ManuallyTrackedBalance], List[BlockchainAccountData],
List['XpubData']],
object_reference_keys: List[Literal['label', 'address', 'xpub.xpub',
'derivation_path'], ],
) -> None:
"""
Inserts the tag mappings from a list of potential data entries. If multiple keys are given
then the concatenation of their values is what goes in as the object reference.
"""
mapping_tuples = []
for entry in data:
if entry.tags is not None:
reference = ''
for key in object_reference_keys:
value = rgetattr(entry, key)
if value is not None:
reference += value
mapping_tuples.extend([(reference, tag) for tag in entry.tags])
cursor.executemany(
'INSERT INTO tag_mappings(object_reference, tag_name) VALUES (?, ?)',
mapping_tuples,
)
def add_tokens_without_chunks(self, cursor: sqlite3.Cursor,
sentence: nlelement.Sentence, sent_id,
doc_id):
class MyGenerator:
def __init__(self, sentence, doc_id, sent_id):
self.sentence = sentence
self.doc_id, self.sent_id, self.chunk_id = doc_id, sent_id, 0
self.tok_iter = iter(sentence.tokens)
def __iter__(self):
return self
def __next__(self):
tok = next(self.tok_iter)
return (doc_id, sent_id, tok.tid, tok.surface,
tok.basic_surface, tok.read, tok.part, tok.attr1,
tok.attr2, tok.conj_type, tok.conj_form,
tok.named_entity)
cursor.executemany(
"""
INSERT INTO TOKENS(
DOCUMENT_ID, SENTENCE_ID, CHUNK_ID, TID, SURFACE, BASE, READ, PART, ATTR1, ATTR2, CONJ_TYPE, CONJ_FORM, NAMED_ENTITY
) VALUES (
?, ?, -1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
""", MyGenerator(sentence, doc_id, sent_id))
def insert_into_db(data: Tuple, table_name: str, cols_names: List[str],
cursor: sqlite3.Cursor) -> None:
cols_to_insert = f"({','.join(cols_names)})"
question_mark_str = f"({','.join(['?' for col in cols_names])})"
insert_string = (
f"INSERT INTO {table_name} {cols_to_insert} VALUES {question_mark_str}"
)
cursor.executemany(insert_string, data)
def store_judge_status(curr: sqlite3.Cursor, conn: sqlite3.Connection,
df: pd.DataFrame) -> None:
create_database_table(curr)
df.apply(clean_string)
curr.executemany(f"INSERT INTO {TABLE_NAME} VALUES (?, ?)", df.values)
conn.commit()
def insert_many(cursor: sqlite3.Cursor, table: str, rows: List[List[Optional[str]]],
verbose: bool = False) -> List[List[Optional[str]]]:
"""Insert many rows into a table."""
placeholders = ','.join(['?'] * len(rows[0]))
sql = f"INSERT INTO {table} VALUES ({placeholders})"
if verbose:
print(f"Running {sql} with {rows}")
cursor.executemany(sql, rows)
return []
def create_dbcan_targets(con: sqlite3.Connection, cur: sqlite3.Cursor,
targets: Set[str]) -> None:
cur.execute("DROP TABLE IF EXISTS dbcan_targets")
cur.execute(
"CREATE TEMP TABLE dbcan_targets (dbcan_ids text NOT NULL UNIQUE)")
cur.executemany("INSERT INTO dbcan_targets VALUES (?)",
((c, ) for c in targets))
cur.execute(
"CREATE UNIQUE INDEX dbcan_targets_index ON dbcan_targets (dbcan_ids)")
con.commit()
return
def save(self, cursor: sqlite3.Cursor):
# Insert the application data to a sqlite database
app_query = "REPLACE INTO applications (app_id) VALUES (?)"
cursor.execute(app_query, (self.app_id,))
application_id = cursor.lastrowid
records = [
(application_id, app_history.downloads, app_history.updates, app_history.date) for app_history in self.get_history()
]
history_query = "REPLACE INTO applications_history (application_id, downloads, updates, date) VALUES (?, ?, ?, ?)"
cursor.executemany(history_query, records)
def _frame_to_table(
cls,
*,
frame: Frame,
label: tp.Optional[str], # can be None
cursor: sqlite3.Cursor,
include_columns: bool,
include_index: bool,
# store_filter: tp.Optional[StoreFilter]
) -> None:
# here we provide a row-based represerntation that is externally usable as an slqite db; an alternative approach would be to store one cell pre column, where the column iststored as as binary BLOB; see here https://stackoverflow.com/questions/18621513/python-insert-numpy-array-into-sqlite3-database
# for interface compatibility with StoreXLSX, where label can be None
if label is None:
label = 'None'
field_names, dtypes = cls.get_field_names_and_dtypes(
frame=frame,
include_index=include_index,
include_columns=include_columns,
force_brackets=True # needed for having nu7mbers as field names
)
index = frame._index
columns = frame._columns
if not include_index:
create_primary_key = ''
else:
primary_fields = ', '.join(field_names[:index.depth])
# need leading comma
create_primary_key = f', PRIMARY KEY ({primary_fields})'
field_name_to_field_type = (
(field, cls._dtype_to_affinity_type(dtype))
for field, dtype in zip(field_names, dtypes))
create_fields = ', '.join(f'{k} {v}'
for k, v in field_name_to_field_type)
create = f'CREATE TABLE {label} ({create_fields}{create_primary_key})'
cursor.execute(create)
# works for IndexHierarchy too
insert_fields = ', '.join(f'{k}' for k in field_names)
insert_template = ', '.join('?' for _ in field_names)
insert = f'INSERT INTO {label} ({insert_fields}) VALUES ({insert_template})'
values = cls._get_row_iterator(frame=frame,
include_index=include_index)
cursor.executemany(insert, values())
def db_insertprices(tickersymbol: str, df: pandas.core.frame.DataFrame,
c: sqlite3.Cursor):
_bulk = [x.split(',') for x in df.dropna().to_csv().split()[1:]]
_bulk = map(
lambda t, o, h, l, c, v:
(tickersymbol, int(t.replace('-', '')), float(o), float(h), float(l),
float(c), round(float(v))), *zip(*_bulk))
_bulk = list(_bulk)
_init = datetime.datetime.now()
logging.info('_bulk[0] : {}'.format(_bulk[0]))
c.executemany('INSERT INTO prices VALUES (?, ?, ?, ?, ?, ?, ?)', _bulk)
_fini = datetime.datetime.now()
logging.info('{} inserted... ({} sec)'.format(tickersymbol,
(_fini - _init)))
def rename_assets_in_db(cursor: Cursor, rename_pairs: List[Tuple[str, str]]) -> None:
"""
Renames assets in all the relevant tables in the Database.
Takes a list of tuples in the form:
[(from_name_1, to_name_1), (from_name_2, to_name_2), ...]
Good from DB version 1 until now.
"""
# [(to_name_1, from_name_1), (to_name_2, from_name_2), ...]
changed_symbols = [(e[1], e[0]) for e in rename_pairs]
cursor.executemany(
'UPDATE multisettings SET value=? WHERE value=? and name="ignored_asset";',
changed_symbols,
)
rename_assets_in_timed_balances(cursor, rename_pairs)
replaced_symbols = [e[0] for e in rename_pairs]
replaced_symbols_q = ['pair LIKE "%' + s + '%"' for s in replaced_symbols]
query_str = (
f'SELECT id, pair, fee_currency FROM trades WHERE fee_currency IN '
f'({",".join("?"*len(replaced_symbols))}) OR ('
f'{" OR ".join(replaced_symbols_q)})'
)
cursor.execute(query_str, replaced_symbols)
updated_trades = []
for q in cursor:
new_pair = q[1]
for rename_pair in rename_pairs:
from_asset = rename_pair[0]
to_asset = rename_pair[1]
if from_asset not in q[1] and from_asset != q[2]:
# It's not this rename pair
continue
if from_asset in q[1]:
new_pair = q[1].replace(from_asset, to_asset)
new_fee_currency = q[2]
if from_asset == q[2]:
new_fee_currency = to_asset
updated_trades.append((new_pair, new_fee_currency, q[0]))
cursor.executemany(
'UPDATE trades SET pair=?, fee_currency=? WHERE id=?',
updated_trades,
)
def procedure_corpus2(corpus, cursor: sqlite3.Cursor, debug: bool=False):
for (j, (classes, sequence)) in corpus:
if debug:
print("procedure: ", len(corpus) - j, sequence, file=stderr)
add_feature_cmd = "INSERT OR IGNORE INTO Features(feature) VALUES (?);"
add_classes_cmd = "INSERT OR IGNORE INTO Classes(classe) VALUES (?);"
cursor.execute('BEGIN TRANSACTION;')
cursor.execute(add_feature_cmd, (sequence,))
cursor.executemany(add_classes_cmd, zip(classes))
generate_regex(
OptimString(sequence, Point('.'), pointee=None, control=None),
classes=classes,
cursor=cursor
)
def insert_tag_mappings(
cursor: Cursor,
data: Union[List[ManuallyTrackedBalance], List[BlockchainAccountData]],
object_reference_key: Literal['label', 'address'],
) -> None:
"""Inserts the tag mappings from a list of potential data entries"""
mapping_tuples = []
for entry in data:
if entry.tags is not None:
reference = getattr(entry, object_reference_key)
mapping_tuples.extend([(reference, tag) for tag in entry.tags])
cursor.executemany(
'INSERT INTO tag_mappings(object_reference, tag_name) VALUES (?, ?)', mapping_tuples,
)
def write_events(
ulid_factory: ulid.api.api.Api,
cursor: sqlite3.Cursor,
events: List[Tuple[StateChangeID, str]],
) -> List[EventID]:
events_ids: List[EventID] = list()
query = ("INSERT INTO state_events("
" identifier, source_statechange_id, data"
") VALUES(?, ?, ?)")
cursor.executemany(query,
_prepend_and_save_ids(ulid_factory, events_ids, events))
return events_ids
def overwrite(self, cursor: Cursor, data: Dict[str, Any]):
# fetch
data_indb = dict((i[0], i[1:]) for i in self.iter_raw(cursor))
# remove
params = [(k,) for k in (set(data_indb) - set(data))]
cursor.executemany(self.SQL_DELETE_ITEM % self._tablename, params)
# update
params = []
for k, v in data.items():
encode_value = self._encode_value(v)
if encode_value != data_indb.get(k):
params.append((k, *encode_value))
cursor.executemany(self.SQL_UPSERT_ITEM % self._tablename, params)
def _insert_events(
self,
c: Cursor,
stored_events: List[StoredEvent],
**kwargs: Any,
) -> None:
params = []
for stored_event in stored_events:
params.append((
stored_event.originator_id.hex,
stored_event.originator_version,
stored_event.topic,
stored_event.state,
))
c.executemany(self.insert_events_statement, params)
def record_uid_loci(cursor: Cursor, table_name: str, pk,
uid_loci: Iterable) -> None:
""" Given a cursor to some database, the name of the table, and the primary key associated with this insert, insert
our uid_loci pairs appropriately.
:param cursor: Cursor to the database containing the table to log to.
:param table_name: Name of the table to log to. Table must already exist.
:param pk: Primary key.
:param uid_loci: List of (uid, loci) pairs to query our database with. Order of tuple matters here!!
:return: None.
"""
cursor.executemany(
f"""
INSERT INTO {table_name}
VALUES (?, ?, ?);
""", ((pk, a[0], a[1]) for a in uid_loci))
def _add_gitcoin_extra_data(cursor: Cursor, actions: List[LedgerAction]) -> None:
"""May raise sqlcipher.IntegrityError"""
db_tuples = []
for action in actions:
if action.extra_data is not None:
db_tuples.append(
action.extra_data.serialize_for_db(parent_id=action.identifier),
)
if len(db_tuples) == 0:
return
query = """INSERT INTO ledger_actions_gitcoin_data(
parent_id, tx_id, grant_id, clr_round, tx_type
)
VALUES (?, ?, ?, ?, ?);"""
cursor.executemany(query, db_tuples)
def setupClientsTable(cursor: sqlite3.Cursor):
cursor.execute(
"CREATE TABLE IF NOT EXISTS Clientes (dni TEXT PRIMARY KEY, nombre TEXT, apellidos TEXT, ciudad TEXT)"
)
clients = [['0001', 'Luis', 'Garcia', 'Madrid'],
['0002', 'Antonio', 'López', 'Valencia'],
['0003', 'Juan', 'Martín', 'Madrid'],
['0004', 'María', 'García', 'Madrid'],
['0005', 'Javier', 'González', 'Barcelona'],
['0006', 'Ana', 'López', 'Barcelona'],
['0007', 'Ana', 'López', 'Madrid'],
['0008', 'Ana', 'López', 'Barcelona']]
cursor.executemany(
"INSERT OR IGNORE INTO Clientes VALUES (:dni, :nombre, :apellidos, :ciudad)",
clients)
def setupSalesTable(cursor: sqlite3.Cursor):
cursor.execute(
"CREATE TABLE IF NOT EXISTS Ventas (cifc TEXT, dni TEXT, codcoche TEXT, color TEXT, PRIMARY KEY (cifc,dni,codcoche))"
)
sales = [['0001', '0001', '0001', 'blanco'],
['0001', '0002', '0005', 'rojo'],
['0002', '0003', '0008', 'blanco'],
['0002', '0001', '0006',
'rojo'], ['0003', '0004', '0011', 'rojo'],
['0004', '0005', '0014',
'verde'], ['0004', '0005', '0013', 'azul'],
['0004', '0004', '0014', 'verde']]
cursor.executemany(
"INSERT OR IGNORE INTO Ventas VALUES (:cifc, :dni, :codcoche, :color)",
sales)
def _update_tags(cursor: sqlite3.Cursor,
bookmarks: List[Bookmark]) -> None:
for bookmark in bookmarks:
cursor.execute(
"SELECT name FROM tag WHERE bookmarkId IS ?",
(str(bookmark.id), ),
)
old_tags = cursor.fetchall()
if old_tags and {tag[0] for tag in old_tags} != set(bookmark.tags):
cursor.execute(
"DELETE FROM tag WHERE bookmarkId IS ?",
(str(bookmark.id), ),
)
cursor.executemany(
"INSERT INTO tag (name, bookmarkId) VALUES (?, ?)",
[(tag, str(bookmark.id)) for tag in bookmark.tags],
)
def write_item_to_database(curs: sqlite3.Cursor, item, roi: ROI,
rle: List[Tuple[int, int, int]],
image: np.ndarray, image_id: str) -> None:
"""
Method to write the specified item to the database
:param curs: Cursor pointing to the database
:param item: The item to write to the database
:param roi: The ROI associated with the item
:param rle: The run length encoded area of this item
:param image: The image from which the roi is derived
:param image_id: The id of the image
:return: None
"""
# Calculate statistics
stats = roi.calculate_statistics(image[..., item.channel_index])
ellp = roi.calculate_ellipse_parameters()
# Prepare data for SQL statement
rle = [(hash(roi), x[0], x[1], x[2]) for x in rle]
# Write item to database
curs.execute(
"INSERT OR IGNORE INTO roi VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
(hash(roi), image_id, False, roi.ident,
f"({item.center[1]:.0f}, {item.center[0]:.0f})",
item.edit_rect.width, item.edit_rect.height, None))
curs.executemany("INSERT OR IGNORE INTO points VALUES (?, ?, ?, ?)",
rle)
curs.execute(
"INSERT OR IGNORE INTO statistics VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
(hash(roi), image_id, stats["area"], stats["intensity average"],
stats["intensity median"], stats["intensity maximum"],
stats["intensity minimum"], stats["intensity std"],
ellp["eccentricity"], ellp["roundness"],
f"({item.center[1]:.0f}, {item.center[0]:.0f})", item.width / 2,
item.height / 2, item.angle, ellp["area"], str(
ellp["orientation"]), ellp["shape_match"]))
def delete_unassociated_roi(curs: sqlite3.Cursor,
unassociated: Iterable[Tuple[int]]) -> None:
"""
Method to delete unassociated roi from the database
:param curs: Cursor pointing to the database
:param unassociated: List of hashes from unassociated roi, prepared for executemany
:return: None
"""
curs.executemany("DELETE FROM roi WHERE hash=?", unassociated)
curs.executemany("DELETE FROM points WHERE hash=?", unassociated)
curs.executemany("DELETE FROM statistics WHERE hash=?", unassociated)
def add(paths, cursor: sqlite3.Cursor, num_processes=None):
for path in paths:
cprint("Hashing {}".format(path), "blue")
files = get_image_files(path)
files = new_image_files(files, cursor)
results: List = []
for result in hash_files_parallel(files, num_processes):
results.append((
result[0],
result[1],
result[2],
result[3],
result[4])
)
sql = 'INSERT into images values(?,?,?,?,?);'
result = cursor.executemany(sql, results)
cprint("...done inserting "+str(result.rowcount), "blue")
cursor.connection.commit()
def maj(sequences, classes, features, data, cursor: sqlite3.Cursor, debug=False):
add_value_cmd = "INSERT INTO Examples(corpus_id,class_id,feature_id,value) VALUES (?,?,?,0.0)"
select_ids = '''SELECT Corpus.id,Classes.id,Features.id
FROM Corpus,Classes,Features
WHERE Corpus.sequence=? AND Classes.classe=? AND Features.feature=?'''
add_classes_cmd = "INSERT OR IGNORE INTO Classes(classe) VALUES (?)"
add_features_cmd = "INSERT OR IGNORE INTO Features(feature) VALUES (?)"
add_sequence_cmd = "INSERT OR IGNORE INTO Corpus(sequence) VALUES (?)"
cursor.execute('BEGIN')
to_tuple = lambda x: (x,)
to_tuple_seq = lambda t: map(to_tuple, iter(t))
# màj des séquences du corpus
cursor.executemany(add_sequence_cmd, to_tuple_seq(sequences))
sequences.clear()
# màj des classes
cursor.executemany(add_classes_cmd, to_tuple_seq(classes))
classes.clear()
# màj des features
cursor.executemany(add_features_cmd, to_tuple_seq(features))
features.clear()
# màj des examples
cursor.executemany(
add_value_cmd,
list(
map(
lambda x: cursor.execute(select_ids, x).fetchone(),
data
)
)
)
if debug:
print("màj effectuée", file=stderr)
