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]