def insert_bookmark(self, bm: Bookmark) -> int:
query = """
-- name: insert_bookmark<!
-- record_class: Bookmark
insert into bookmarks (URL, metadata, tags, desc, flags)
values (:URL, :metadata, :tags, :desc, :flags)
returning *;
"""
queries = aiosql.from_str(query,
"sqlite3",
record_classes=self.record_classes)
result = queries.insert_bookmark(
self.conn.connection,
URL=bm.URL,
metadata=bm.metadata,
tags=bm.tags,
desc=bm.desc,
flags=bm.flags,
)
self.conn.connection.commit()
return result
def get_all_tags(self, with_frequency: bool = False):
# noinspection SqlResolve
query = """
-- name: get_all_tags
with RECURSIVE split(tags, rest) AS (
SELECT '', tags || ','
FROM bookmarks
UNION ALL
SELECT substr(rest, 0, instr(rest, ',')),
substr(rest, instr(rest, ',') + 1)
FROM split
WHERE rest <> '')
SELECT tags, count(tags) as n
FROM split
WHERE tags <> ''
group by tags
ORDER BY 2 desc;
"""
queries = aiosql.from_str(query, "sqlite3")
sql_result = queries.get_all_tags(self.conn.connection)
return sql_result
def test_fromstr_queryloader_cls(sql):
mock_loader = mock.MagicMock(wraps=QueryLoader)
aiosql.from_str(sql, "aiosqlite", loader_cls=mock_loader)
assert mock_loader.called
def test_fromstr_queries_cls(sql):
class TestQueries(Queries):
pass
queries = aiosql.from_str(sql, "aiosqlite", queries_cls=TestQueries)
assert isinstance(queries, TestQueries)
import aiosql, sqlite3
import numpy as np
spacer = lambda i: "\n" if i % 5 == 0 else ""
feat_cols = ",".join(
[f"cast(feature_{i} as float){spacer(i)}" for i in range(129)])
resp_cols = ",".join([f"cast({col} as float)" for col in ["weight", "resp"]])
sql_query = f"""
-- name: random_feat_obs
select {resp_cols},{feat_cols} from train where weight != '0' and random() %2 limit :num;
"""
queries = aiosql.from_str(sql_query, "sqlite3")
conn = sqlite3.connect("train_data.db")
def get_data(num):
return np.array(queries.random_feat_obs(num=num, conn=conn))