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 _(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 create_table(conn: Connection, sql: str) -> None:
try:
c = conn.cursor()
c.execute(sql)
conn.commit()
except Error as e:
print(e)
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 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 _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 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 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: Suffixes, conn: Connection) -> None:
cur = conn.cursor()
cur.executemany(sql_dict['insert']['suffix'], record.to_namedtuple_collection())
conn.commit()
def _(record: GenusTypes, conn: Connection) -> None:
cur = conn.cursor()
cur.executemany(sql_dict['insert']['genus_type'], record.to_namedtuple_collection())
conn.commit()
def _(record: Field, conn: Connection) -> None:
cur = conn.cursor()
cur.execute(sql_dict['insert']['field'], record.to_namedtuple())
conn.commit()
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]
