def run_query_from_args(self, args: object) -> Dict[NotePath, Note]:
qb = QueryBuilder()
sql = qb.build_sql_from_args(args)
if args.name_query:
if sql:
self.save_query(args.name_query[0], sql)
else:
self._error('cannot save query with no parameters')
return self._get_notes(sql)
def test_02_ini_searches(self):
loader = TestDataLoader()
searches = loader.load_searches('test_1')
db = Database(DATABASE_PATH, ARCHIVE_PATH)
qb = QueryBuilder()
for search in searches:
expected_paths = sort_notepaths(search.matches)
sql = qb.build_sql_from_lists(search.path_terms, search.text_terms,
search.tag_terms, search.field_terms)
saved_notes = db._get_notes(sql)
actual_paths = sort_notepaths(list(saved_notes.keys()))
self.assertListEqual(expected_paths, actual_paths)
def main():
url = "https://nhlstatisticsforohtu.herokuapp.com/players.txt"
reader = PlayerReader(url)
stats = Statistics(reader)
qb = QueryBuilder()
matcher = (qb.oneOf(
qb.playsIn("PHI").hasAtLeast(10,
"assists").hasFewerThan(5,
"goals").build(),
qb.playsIn("EDM").hasAtLeast(30, "points").build()).build())
for player in stats.matches(matcher):
print(player)
def main():
url = "https://nhlstatisticsforohtu.herokuapp.com/players.txt"
reader = PlayerReader(url)
stats = Statistics(reader)
query = QueryBuilder()
# matcher = query.playsIn("NYR").build()
# matcher = query.playsIn("NYR").hasAtLeast(5, "goals").hasFewerThan(10, "goals").build()
m1 = query.playsIn("PHI").hasAtLeast(10, "assists").hasFewerThan(
5, "goals").build()
m2 = query.playsIn("EDM").hasAtLeast(40, "points").build()
matcher = query.oneOf(m1, m2).build()
for player in stats.matches(matcher):
print(player)
def insert(self, table, data):
cursor = self.connector.cursor()
cursor.execute(
QueryBuilder('?').insert(table, list(data.keys())),
(list(data.values())))
self.connector.commit()
return cursor.lastrowid
def update(self, table, data, where):
cursor = self.connector.cursor()
cursor.execute(
QueryBuilder('?').update(table, list(data.keys()), where),
list(data.values()))
self.connector.commit()
return cursor.rowcount
def insert(self, t, d):
with self.connector.cursor() as cursor:
cursor.execute(
QueryBuilder('%s').insert(t, list(d.keys())) + ' returning id',
(list(d.values())))
res = cursor.fetchone()['id']
self.connector.commit()
return res
def insert(self, table, data):
self.connector.begin()
with self.connector.cursor() as cursor:
cursor.execute(
QueryBuilder('%s').insert(table, list(data.keys())),
(list(data.values())))
self.connector.commit()
return cursor.lastrowid
def update(self, table, data, where):
with self.connector.cursor() as cursor:
cursor.execute(
QueryBuilder('%s').update(table, list(data.keys()), where),
list(data.values()))
res = cursor.rowcount
self.connector.commit()
return res
class Ctable(object):
def __init__(self):
self._q = QueryBuilder()
self._c = ["firstname", "surname", "idnumber"]
self._t = "clients"
def _columns(self):
columns = []
columns.append(
self._q.column_().int_().primary_().auto_increment().build_())
columns.append(self._q.column_(self._c[0]).string_().build_())
columns.append(self._q.column_(self._c[1]).string_().build_())
columns.append(
self._q.column_(
self._c[2]).string_(15).unique().not_null().build_())
return columns
def table_create(self):
cols = self._columns()
return self._q.createTable(self._t, ",".join(cols))
async def producer_handler(self, websocket, path):
async for message in websocket:
# produce an item
try:
print('producing {}'.format(message))
command = json.loads(message)
qb = QueryBuilder(command["nodes"])
tree = qb.build_query()
await websocket.send(
json.dumps({
"type": "info",
"data": "running query"
}))
ne = NodeExecutor(self.ctx, tree, websocket)
await self.queue.put(ne)
except Exception as e:
await websocket.send(
json.dumps({
"type": "error",
"title": "Exception during query execution",
"data": str(e)
}))
def _get_note_ids(self, notepaths: Union[NotePath, List[NotePath]], cursor
) -> List[NoteID]:
if isinstance(notepaths, NotePath):
notepaths = [notepaths]
paths = [QueryBuilder.escape(path) for path in notepaths]
p = ', '.join(paths)
sql = 'SELECT id FROM notes WHERE path in (' + p + ');'
cursor.execute(sql)
ids = []
while True:
record = cursor.fetchone()
if record:
ids.append(record[0])
else:
return ids
class Atable(object):
def __init__(self):
self._q = QueryBuilder()
self._c = ["accountnumber", "clientid", "accounttype", "amount"]
self._t = "accounts"
def _columns(self):
columns = []
columns.append(
self._q.column_().int_().primary_().auto_increment().build_())
columns.append(self._q.column_(self._c[0]).int_().unique().build_())
columns.append(self._q.column_(self._c[1]).int_().build_())
columns.append(
self._q.column_(self._c[2]).string_().not_null().build_())
columns.append(self._q.column_(self._c[3]).real_().not_null().build_())
return columns
def table_create(self):
cols = self._columns()
return self._q.createTable(self._t, ",".join(cols))
class Ttable(object):
def __init__(self):
self._q = QueryBuilder()
self._c = [
"reference", "creference", "account", "transactionstatus", "amount"
]
self._t = "transactions"
def _columns(self):
columns = []
columns.append(
self._q.column_().int_().primary_().auto_increment().build_())
columns.append(self._q.column_(self._c[0]).string_().build_())
columns.append(self._q.column_(self._c[1]).string_().build_())
columns.append(self._q.column_(self._c[2]).real_().build_())
columns.append(self._q.column_(self._c[3]).string_(2).build_())
columns.append(self._q.column_(self._c[4]).real_().not_null().build_())
return columns
def table_create(self):
cols = self._columns()
return self._q.createTable(self._t, ",".join(cols))
def export_json(self, out_dir, blacklist):
# Get all maps that were requested.
try:
con = psycopg2.connect(self.con_string)
cur = con.cursor()
cur = con.cursor()
cur.execute("SELECT DISTINCT ON (map) map FROM " + self.db_schema + "." + self.db_table + ";")
maps = cur.fetchall()
# Export json for total requests
# and every single map.
if maps:
# Day
query_builder = QueryBuilder(blacklist)
sql = query_builder.get_day("60");
cur.execute(sql)
rows = cur.fetchall()
self.export_data(rows, join(out_dir, "day_total.json" ), "All Maps", "#3182bd", True)
for map in maps:
sql = query_builder.get_day("60", str(map[0]));
cur.execute(sql)
rows = cur.fetchall()
self.export_data(rows, join(out_dir, "day_"+str(map[0])+".json" ), str(map[0]), "#3182bd", True)
# Week
sql = query_builder.get_week("60");
cur.execute(sql)
rows = cur.fetchall()
self.export_data(rows, join(out_dir, "week_total.json" ), "All Maps", "#3182bd", True)
for map in maps:
sql = query_builder.get_week("60", str(map[0]));
cur.execute(sql)
rows = cur.fetchall()
self.export_data(rows, join(out_dir, "week_"+str(map[0])+".json" ), str(map[0]), "#3182bd", True)
# Month
sql = query_builder.get_month("1800");
cur.execute(sql)
rows = cur.fetchall()
self.export_data(rows, join(out_dir, "month_total.json" ), "All Maps", "#3182bd", True)
for map in maps:
sql = query_builder.get_month("1800", str(map[0]));
cur.execute(sql)
rows = cur.fetchall()
self.export_data(rows, join(out_dir, "month_"+str(map[0])+".json" ), str(map[0]), "#3182bd", True)
# Year
sql = query_builder.get_year("7200");
cur.execute(sql)
rows = cur.fetchall()
self.export_data(rows, join(out_dir, "year_total.json" ), "All Maps", "#3182bd", True)
for map in maps:
sql = query_builder.get_year("7200", str(map[0]));
cur.execute(sql)
rows = cur.fetchall()
self.export_data(rows, join(out_dir, "year_"+str(map[0])+".json" ), str(map[0]), "#3182bd", True)
except psycopg2.Error:
exc_type, exc_value, exc_traceback = sys.exc_info()
print sys.exc_info()
if con:
con.rollback()
finally:
if con:
con.close()
def delete(self, table, where):
cursor = self.connector.cursor()
cursor.execute(QueryBuilder('?').delete(table, where))
self.connector.commit()
return cursor.rowcount
def select(self, table, fields=None, where=None):
with self.connector.cursor() as cursor:
cursor.execute(QueryBuilder('%s').select(table, fields, where))
res = cursor.fetchall()
return res if res != () else None
def select(self, table, fields=None, where=None):
cursor = self.connector.cursor()
cursor.execute(QueryBuilder('?').select(table, fields, where))
res = cursor.fetchall()
return res if res != [] else None
def __init__(self):
self._q = QueryBuilder()
self._c = [
"reference", "creference", "account", "transactionstatus", "amount"
]
self._t = "transactions"
def __init__(self):
self._q = QueryBuilder()
self._c = ["accountnumber", "clientid", "accounttype", "amount"]
self._t = "accounts"
def delete(self, table, where):
with self.connector.cursor() as cursor:
cursor.execute(QueryBuilder('%s').delete(table, where))
res = cursor.rowcount
self.connector.commit()
return res
def run(self) -> None:
self._init_options()
self.args = self.parser.parse_args()
if self.args.license:
print_license()
sys.exit(0)
path = get_data_path(DEFAULT_DATABASE_FILENAME)
self.db = Database(path)
if self.args.database:
self.db.print_sqlite_info()
sys.exit(0)
if self.args.list_queries:
self.db.print_saved_query_names()
sys.exit(0)
# Save notes first, so the query can include them.
if self.args.save_notes:
self.save_notes(self.args)
# Run the query and retrieve the notes.
if self.args.remove_query:
self.db.remove_saved_query(self.args.remove_query[0])
start_time = time.time()
if self.args.query:
notes = self.db.run_saved_query(self.args.query[0])
else:
notes = self.db.run_query_from_args(self.args)
elapsed = time.time() - start_time
# Output notes sorted by notepath.
paths = sorted(list(notes.keys()))
# But output statistics and query info before outputting any notes.
summary = QueryBuilder().summarize_query_from_args(self.args)
if summary:
path = self.db.get_file_path()
size = get_readable_filesize(path)
moddate = get_readable_moddate(path)
print(summary)
print('')
print('DATABASE SIZE: ', size)
print('DATABASE MODIFIED:', moddate)
print('WHEN SEARCHED: ', timestamp_for_journal())
print('SEARCH DURATION: ', '{0:.4f}'.format(elapsed), 'seconds')
print('NOTES FOUND: ', '{:,}'.format(len(notes)))
word_count = sum(notes[path].count_words() for path in paths)
print('WORD COUNT: ', '{:,}'.format(word_count))
print('')
else:
print('NO SEARCH WAS MADE.')
# Output notes last.
for path in paths:
if self.args.paths_only:
print(path)
else:
print(notes[path])
def __init__(self):
self._q = QueryBuilder()
self._c = ["firstname", "surname", "idnumber"]
self._t = "clients"