def create_table(conn: Connection, sql: str) -> None:
try:
c = conn.cursor()
c.execute(sql)
conn.commit()
except Error as e:
print(e)
def _(record: Rank, conn: Connection) -> None:
cur = conn.cursor()
if record.field_id is not None:
cur.execute(sql_dict['insert']['rank'][1], record.to_namedtuple())
else:
cur.execute(sql_dict['insert']['rank'][0], record.to_namedtuple())
conn.commit()
def table_drop(conn: Connection, table_name: str) -> None:
with conn:
table_name = _safe_table_name(table_name)
conn.execute(f"DROP TABLE IF EXISTS {table_name}")
conn.execute(
f"DELETE FROM {_SCHEMA_TABLE_NAME} WHERE table_name = '{table_name}'"
)
def init_db(db_con: Connection) -> None:
table_init_str = "".join([
"CREATE TABLE IF NOT EXISTS activities (application text, start_date_iso",
" text, end_date_iso text, start_year integer, start_month integer, start_day integer,",
" start_time text, end_year integer, end_month integer, end_day integer, end_time text)",
])
db_con.cursor().execute(table_init_str)
def insert_db(connection: Connection, sequences: str):
try:
with connection:
sql = """INSERT INTO Sequences(identifier,sequence)
VALUES(?,?)"""
connection.executemany(sql, sequences.items())
except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
print("Operation failed", e)
def closeDb(self, dbConnection: Connection, dbCursor: Cursor) -> None:
"""- 关闭数据库。
- param
- `dbConnection` 数据库连接
"""
if dbCursor != None:
dbCursor.close()
if dbConnection != None:
dbConnection.close()
def table_merge(
conn: Connection,
table_names: List[str],
on: List[str],
how: str = "inner",
into_table: str = None,
) -> Optional[Iterable[Dict[str, Any]]]:
table_names = [_safe_table_name(name) for name in table_names]
assert len(table_names) == len(
set(table_names)), f"Table names must all be unique"
on = [_safe_column_name(col) for col in on]
left = table_names[0]
statement_join = f"SELECT * FROM {left}"
for right in table_names[1:]:
clause_on = " AND ".join(f"{left}.{col} = {right}.{col}" for col in on)
statement_join += f" {how.upper()} JOIN {right} ON ({clause_on})"
with conn:
if into_table:
combined_schema = {}
for table_name in table_names:
combined_schema.update(_fetch_table_schema(conn, table_name))
# Make sure the receiving table exists and is empty
into_table = _safe_table_name(into_table)
table_drop(conn, into_table)
table_create(conn, into_table, combined_schema)
# Coalesce all shared columns
if len(table_names) > 1:
coalesce_helper = lambda x: ",".join(f"{table}.{x}"
for table in table_names)
select_map = {
col: _safe_column_name(col)
for col in combined_schema.keys()
}
select_map.update({
col: f"COALESCE({coalesce_helper(col)}) AS {col}"
for col in on
})
clause_select = ",".join(select_map[col]
for col in combined_schema.keys())
statement_join = statement_join.replace(
"SELECT *", f"SELECT {clause_select}")
# Pre-pend the insert statement so the output goes into the table
conn.execute(f"INSERT INTO {into_table} " + statement_join)
# Otherwise perform the merge and yield records from the cursor
else:
cursor = conn.execute(statement_join)
return _output_named_records(cursor)
def __ensure_table(self, connection: Connection) -> None:
info = connection.execute(*compile(
Q(T.sqlite_master).fields(
'*').where((T.sqlite_master.name == 'version')
& (T.sqlite_master.type == 'table')))).fetchone()
if not info:
connection.execute(
'CREATE TABLE version (number INTEGER NOT NULL)')
connection.execute(
*Q(T(self.table), result=Result(
compile=compile)).insert({T(self.table).number: 0}))
def _statement_insert_record_tuple(
conn: Connection,
table_name: str,
columns: Tuple[str],
record: Tuple[str],
replace: bool = False,
) -> None:
table_name = _safe_table_name(table_name)
verb_insert = "INSERT " + ("OR REPLACE " if replace else "")
placeholders = ", ".join("?" for _ in columns)
column_names = ", ".join(_safe_column_name(name) for name in columns)
conn.execute(
f"{verb_insert} INTO {table_name} ({column_names}) VALUES ({placeholders})",
record)
def _check_table_not_empty(self, conn: Connection,
table_name: str) -> None:
cursor = conn.cursor()
cursor.execute(f"SELECT * FROM {_safe_table_name(table_name)}")
records = cursor.fetchall()
self.assertGreaterEqual(len(records), 1)
cursor.close()
def create_table_if_not_exist(conn: Connection):
sql_paper = """ CREATE TABLE IF NOT EXISTS papers (
title text,
url text PRIMARY KEY,
date text,
authors text,
tasks text,
url_pdf text,
url_abs text,
arxiv_id text,
Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
); """
sql_repo = """CREATE TABLE IF NOT EXISTS repos (
name text,
paper_url text NOT NULL,
url text NOT NULL,
readme text,
private BOOLEAN NOT NULL CHECK (private IN (0,1)),
framework text,
mentioned_in_paper BOOLEAN NOT NULL CHECK (private IN (0,1)),
mentioned_in_github BOOLEAN NOT NULL CHECK (private IN (0,1)),
stars INTEGER,
lang text,
forks INTEGER,
PRIMARY KEY(name, paper_url)
FOREIGN KEY (paper_url) REFERENCES papers(url));"""
sql_files = """CREATE TABLE IF NOT EXISTS files (
path text,
url text,
repo_name text,
name text,
size integer,
FOREIGN KEY (repo_name) REFERENCES repos(name),
PRIMARY KEY (path, repo_name));"""
if conn:
c = conn.cursor()
c.execute(sql_paper)
c.execute(sql_repo)
c.execute(sql_files)
conn.commit()
else:
raise AttributeError
def table_create(conn: Connection, table_name: str, schema: Dict[str,
str]) -> None:
table_name = _safe_table_name(table_name)
sql_schema = ", ".join(f"{_safe_column_name(name)} {dtype}"
for name, dtype in schema.items())
with conn:
# Create new table (ignore if exists)
conn.execute(f"CREATE TABLE IF NOT EXISTS {table_name} ({sql_schema})")
# Every time a table is created, store its schema in our helper table
_statement_insert_record_dict(
conn,
_SCHEMA_TABLE_NAME,
{
"table_name": table_name,
"schema_json": json.dumps(schema)
},
replace=True,
)
def table_select_all(conn: Connection,
table_name: str,
sort_by: List[str] = None,
sort_ascending: bool = True) -> Iterable[Dict[str, Any]]:
table_name = _safe_table_name(table_name)
statement_select = f"SELECT * FROM {table_name}"
if sort_by:
sort_by = ",".join(_safe_column_name(col) for col in sort_by)
statement_select += f" ORDER BY {sort_by} {'ASC' if sort_ascending else 'DESC'}"
cursor = conn.execute(statement_select)
return _output_named_records(cursor)
def CriarBancoDados():
with sqlite3.connect("funcionarios.db") as Connection:
c = Connection.cursor()
c.execute(
"CREATE TABLE funcionarios(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, nome TEXT, idade INTEGER,cargo TEXT)"
)
c.execute(
"INSERT INTO funcionarios VALUES('0','Felipe','26','Analista de sistema')"
)
c.close()
pass
def crawl_task(conn: Connection, cur: Cursor,
contest: ContestListPage.Contest) -> bool:
slug: str = contest.contest_slug
cur.execute('SELECT COUNT(*) FROM tasks WHERE contest_slug = ?', (slug, ))
count_result = cur.fetchone()
exists_in_table = (count_result[0] > 0)
if exists_in_table:
print(f' -> There already exists in table')
return False
tlprr: TaskListPageRequestResult = TaskListPageRequestResult.create_from_request(
slug)
if tlprr.is_closed:
print(f' -> Task list: 404')
return True
print(f' -> Task size: {len(tlprr.task_list_page.tasks)}')
seq_of_parameters: List[TaskDBInsertData] = tlprr.generate_insert_data()
cur.executemany('INSERT INTO tasks VALUES (?,?,?,'
'?,?,?)', seq_of_parameters)
conn.commit()
return True
def _check_table_exists(self, connection: Connection, table_name: str):
c = connection.cursor()
logging.debug(f"Finding out if the {table_name} table already exist.")
c.execute(
f"SELECT count(name) FROM sqlite_master WHERE type='table' AND name='{table_name}';"
)
#if the count is diff than 1, then table does not exist
if c.fetchone()[0] != 1:
return False
return True
def _get_recipe(db: Connection, uuid: UUID, version: int) -> Optional[GetRecipe]:
recipe = db.execute(GET_RECIPE, (str(uuid), version)).fetchone()
if recipe is None:
return None
ingredients = db.execute(GET_INGREDIENTS, (str(recipe[0])))
steps = db.execute(GET_STEPS, (str(recipe[0])))
return GetRecipe(
uuid=UUID(recipe[1]),
version=recipe[2],
name=recipe[3],
duration_minute=recipe[4],
ingredients=[
Ingredient(
name=i[0],
numerator=i[1],
denominator=i[2],
measure=Measure[i[3]],
)
for i in ingredients
],
steps=[Step(name=s[0], duration_minute=s[1]) for s in steps],
)
def init_db(conn: Connection) -> None:
add_functions(conn)
conn.create_function("X_SIMILARITY",
narg=3,
func=quick_ratio,
deterministic=True)
conn.create_function("X_NORM_CASE",
narg=1,
func=normcase,
deterministic=True)
conn.create_aggregate("X_QUANTILES",
n_arg=-1,
aggregate_class=cast(Any, _Quantiles))
def create_table(connection: Connection, query: str) -> None:
connection.execute(query)
connection.commit()
def _fetch_table_schema(conn: Connection, table_name: str) -> Dict[str, str]:
with conn:
schema_json = conn.execute(
f'SELECT schema_json FROM {_SCHEMA_TABLE_NAME} WHERE table_name = "{table_name}"'
).fetchone()[0]
return json.loads(schema_json)
def get_rank_id(conn: Connection, rank: str):
cur = conn.cursor()
cur.execute(sql_queries['select']['rank_id_by_label'], (rank.lower(),))
return cur.fetchone()
def get_entity_id(conn: Connection, name: str) -> int:
cur = conn.cursor()
cur.execute(sql_queries['select']['entity_id_by_name'], (name,))
return cur.fetchone()
def get_cons_status_codes(conn: Connection) -> dict:
status_codes = {}
cur = conn.cursor()
for row in cur.execute(sql_queries['select']['all_cons_codes']):
status_codes[row[1]] = row[0]
return status_codes
def _(record: GenusTypes, conn: Connection) -> None:
cur = conn.cursor()
cur.executemany(sql_dict['insert']['genus_type'], record.to_namedtuple_collection())
conn.commit()
def commit_close(con: Connection) -> None:
con.commit()
con.close()
def _(record: Suffixes, conn: Connection) -> None:
cur = conn.cursor()
cur.executemany(sql_dict['insert']['suffix'], record.to_namedtuple_collection())
conn.commit()
def crawl_contest(conn: Connection, cur: Cursor,
contest: ContestListPage.Contest) -> None:
slug: str = contest.contest_slug
# 開始するページ番号の決定
cur.execute('SELECT MAX(pagenum) FROM submissions WHERE contest = ?',
(slug, ))
pagenum_max_result: Tuple[Optional[int]] = cur.fetchone()
pagenum_max: Optional[int] = pagenum_max_result[0]
pagenum: int = 1
if pagenum_max is not None:
pagenum = pagenum_max + 1
# return
while True:
# ページ取得
result: SubmissionListPageRequestResult = SubmissionListPageRequestResult.create_from_request(
slug, pagenum)
# print(result)
# exit()
count_result: Tuple[Optional[int]]
exists_in_table: bool
if result.is_closed:
print(f' -> Page {result.pagenum}: 404')
# コンテスト情報挿入
cur.execute('SELECT COUNT(*) FROM contests WHERE contest_slug = ?',
(slug, ))
count_result = cur.fetchone()
exists_in_table = (count_result[0] == 1)
if not exists_in_table:
cur.execute('INSERT INTO contests VALUES (?,?,?,?,?,?)',
(slug, contest.contest_name, contest.time_unix,
int((contest.time +
timedelta(minutes=contest.duration_minutes)
).timestamp()), 1, 1))
conn.commit()
break
else:
print(
f' -> Page {result.pagenum}: size={len(result.submission_list_page.submissions)}, '
f'min={result.submission_list_page.submissions[0].time}, max={result.submission_list_page.submissions[-1].time}'
)
# コンテスト情報挿入
cur.execute('SELECT COUNT(*) FROM contests WHERE contest_slug = ?',
(slug, ))
count_result = cur.fetchone()
exists_in_table = (count_result[0] == 1)
if not exists_in_table:
cur.execute(
'INSERT INTO contests VALUES (?,?,?,?,?,?)',
(slug, result.submission_list_page.contest_title,
result.submission_list_page.contest_starttime_unix,
result.submission_list_page.contest_endtime_unix, 0, 0))
# 提出情報挿入
seq_of_parameters: List[DBInsertData] = result.generate_insert_data()
try:
cur.executemany(
'INSERT INTO submissions VALUES (?,?,?,?,'
'?,?,?,?,?,?,?,?,?,?)', seq_of_parameters)
except sqlite3.Error as e:
print(e)
break
conn.commit()
# 最後のページなら抜ける
if result.is_last_page:
break
pagenum += 1
time.sleep(3)
cur.execute(
'UPDATE contests SET crawl_completed = 1 WHERE contest_slug = ?',
(slug, ))
conn.commit()
def _(record: Field, conn: Connection) -> None:
cur = conn.cursor()
cur.execute(sql_dict['insert']['field'], record.to_namedtuple())
conn.commit()
def add_functions(conn: Connection) -> None:
conn.row_factory = Row
conn.create_collation("X_COLLATION", strcoll)
conn.create_function("X_STRXFRM", narg=1, func=strxfrm, deterministic=True)
conn.create_function("X_NORMALIZE",
narg=1,
func=_normalize,
deterministic=True)
conn.create_function("X_LOWER", narg=1, func=_lower, deterministic=True)
conn.create_function("X_UUID_B",
narg=0,
func=_uuid_bytes,
deterministic=False)
def get_sequence(fasta_db: Connection, virus_id: str) -> str:
cursor = fasta_db.cursor()
cursor.execute("SELECT sequence FROM Sequences WHERE identifier = ?",
(virus_id, ))
result = cursor.fetchall()
return result[0][0]
