def get_user(user):
cur = db.get_cursor()
query = "SELECT * FROM USERINFO WHERE USERID = '%s'"
res = db.exec_query(query % user, cur)
if res is None:
return None
else:
return res[0]
def auth_status(user, pwd):
cur = db.get_cursor()
query = "SELECT PASSWD FROM USERINFO WHERE USERID = '%s'"
res = db.exec_query(query % user, cur)
if res is not None:
return res[0][0] == pwd
else:
return 0
def get_user(user):
curcnx = db.get_curcnx()
query = "SELECT * FROM USERINFO WHERE USERID = %s"
res = db.exec_query(curcnx, query, user)
if res is None:
return None
else:
return res[0]
import pandas as pd
from tqdm import tqdm
from db import exec_query, find_table_name, table_load, table_write
drop_table_names = find_table_name(like="stats")["table_name"].to_list()
if len(drop_table_names) > 0:
exec_query("".join([
"DROP TABLE {};".format(drop_table_name)
for drop_table_name in drop_table_names
]))
table_names = []
table_names += find_table_name(like="train")["table_name"].to_list()
table_names += find_table_name(like="test")["table_name"].to_list()
table_names += find_table_name(like="cv_result")["table_name"].to_list()
for table_name in tqdm(table_names):
df = table_load(table_name=table_name)
stats = pd.concat(
[
df.dtypes.rename("dtype").astype(str).to_frame(),
df.isnull().sum().rename("null_count").to_frame(),
df.describe().T.rename(columns={
"25%": "per_25",
"50%": "per_50",
"75%": "per_75"
}),
],
axis=1,
sort=False,
import os
import pandas as pd
from db import exec_query, table_write
print("Initializing Database...")
# Drop tables if they exist
exec_query("DROP TABLE IF EXISTS train;")
exec_query("DROP TABLE IF EXISTS test;")
exec_query("DROP TABLE IF EXISTS memo;")
# Read data
train = pd.read_csv(os.environ["PROJECT_DIR"] + "/input/train.csv")
test = pd.read_csv(os.environ["PROJECT_DIR"] + "/input/test.csv")
memo = pd.read_csv(os.environ["PROJECT_DIR"] + "/input/memo.csv")
# Insert train, test data into DB
table_write(table_name="train", df=train)
table_write(table_name="test", df=test)
table_write(table_name="memo", df=memo)
# Create Index
exec_query("CREATE INDEX train_index on train (index);")
exec_query("CREATE INDEX test_index on test (index);")
exec_query("CREATE INDEX memo_index on memo (index);")
print("Done!!")
def test_db():
if not os.path.exists(dbname):
db.exec_cmd(dbname, nbt_global.project_table)
db.exec_cmd(dbname, nbt_global.bugs_table)
db.exec_cmd(dbname, nbt_global.wiki_table)
# projects
db.exec_cmd(
dbname,
'insert into projects values("Example1","This is an Example project 1");'
)
db.exec_cmd(
dbname,
'insert into projects values("Example2","This is an Example project 1");'
)
db.exec_cmd(
dbname,
'insert into projects values("Example3","This is an Example project 1");'
)
db.exec_cmd(
dbname,
'insert into projects values("Example4","This is an Example project 1");'
)
db.exec_cmd(
dbname,
'insert into projects values("Example5","This is an Example project 1");'
)
# bugs
db.exec_cmd(
dbname,
'insert into bugs values((select rowid from projects where shortname="Example1"), "Your leg is broken", "The leg is broken for the following component", "CRITICAL", "OPEN");'
)
db.exec_cmd(
dbname,
'insert into bugs values((select rowid from projects where shortname="Example2"), "My leg is broken", "The leg is broken for the following component", "CRITICAL", "OPEN");'
)
db.exec_cmd(
dbname,
'insert into bugs values((select rowid from projects where shortname="Example3"), "Our leg is broken", "The leg is broken for the following component", "CRITICAL", "OPEN");'
)
db.exec_cmd(
dbname,
'insert into bugs values((select rowid from projects where shortname="Example4"), "His leg is broken", "The leg is broken for the following component", "CRITICAL", "OPEN");'
)
db.exec_cmd(
dbname,
'insert into bugs values((select rowid from projects where shortname="Example5"), "Her leg is broken", "The leg is broken for the following component", "CRITICAL", "OPEN");'
)
# wiki
db.exec_cmd(
dbname,
'insert into wiki values((select rowid from projects where shortname="Example1"), "Example Wiki Page 1", "This is a wiki page");'
)
db.exec_cmd(
dbname,
'insert into wiki values((select rowid from projects where shortname="Example3"), "Example Wiki Page 2", "This is a wiki page");'
)
db.exec_cmd(
dbname,
'insert into wiki values((select rowid from projects where shortname="Example2"), "Example Wiki Page 3", "This is a wiki page");'
)
db.exec_cmd(
dbname,
'insert into wiki values((select rowid from projects where shortname="Example5"), "Example Wiki Page 4", "This is a wiki page");'
)
db.exec_cmd(
dbname,
'insert into wiki values((select rowid from projects where shortname="Example4"), "Example Wiki Page 5", "This is a wiki page");'
)
print(db.exec_query(dbname, 'select * from projects;'))
print(
db.exec_query(
dbname,
'select * from bugs where projectid = (select rowid from projects where shortname = "Example3");'
))
print(
db.exec_query(
dbname,
'select * from bugs where projectid = (select rowid from projects where shortname = "Example5");'
))
print(
db.exec_query(
dbname,
'select * from bugs where projectid = (select rowid from projects where shortname = "Example1");'
))
print(
db.exec_query(
dbname,
'select * from bugs where projectid = (select rowid from projects where shortname = "Example2");'
))
print(
db.exec_query(
dbname,
'select * from bugs where projectid = (select rowid from projects where shortname = "Example4");'
))
print(
db.exec_query(
dbname,
'select * from wiki where projectid = (select rowid from projects where shortname = "Example3");'
))
print(
db.exec_query(
dbname,
'select * from wiki where projectid = (select rowid from projects where shortname = "Example5");'
))
print(
db.exec_query(
dbname,
'select * from wiki where projectid = (select rowid from projects where shortname = "Example1");'
))
print(
db.exec_query(
dbname,
'select * from wiki where projectid = (select rowid from projects where shortname = "Example2");'
))
print(
db.exec_query(
dbname,
'select * from wiki where projectid = (select rowid from projects where shortname = "Example4");'
))
def commit(self, bucket):
curcnx = db.get_curcnx()
db.exec_query(curcnx, self.query_docstatus, self.get_value())
db.exec_query(curcnx, self.query_docinfo, (self._docid, bucket))
def test_db():
if not os.path.exists(dbname):
db.exec_cmd(dbname, nbt_global.project_table)
db.exec_cmd(dbname, nbt_global.bugs_table)
db.exec_cmd(dbname, nbt_global.wiki_table)
# projects
db.exec_cmd(dbname, 'insert into projects values("Example1","This is an Example project 1");')
db.exec_cmd(dbname, 'insert into projects values("Example2","This is an Example project 1");')
db.exec_cmd(dbname, 'insert into projects values("Example3","This is an Example project 1");')
db.exec_cmd(dbname, 'insert into projects values("Example4","This is an Example project 1");')
db.exec_cmd(dbname, 'insert into projects values("Example5","This is an Example project 1");')
# bugs
db.exec_cmd(dbname, 'insert into bugs values((select rowid from projects where shortname="Example1"), "Your leg is broken", "The leg is broken for the following component", "CRITICAL", "OPEN");')
db.exec_cmd(dbname, 'insert into bugs values((select rowid from projects where shortname="Example2"), "My leg is broken", "The leg is broken for the following component", "CRITICAL", "OPEN");')
db.exec_cmd(dbname, 'insert into bugs values((select rowid from projects where shortname="Example3"), "Our leg is broken", "The leg is broken for the following component", "CRITICAL", "OPEN");')
db.exec_cmd(dbname, 'insert into bugs values((select rowid from projects where shortname="Example4"), "His leg is broken", "The leg is broken for the following component", "CRITICAL", "OPEN");')
db.exec_cmd(dbname, 'insert into bugs values((select rowid from projects where shortname="Example5"), "Her leg is broken", "The leg is broken for the following component", "CRITICAL", "OPEN");')
# wiki
db.exec_cmd(dbname, 'insert into wiki values((select rowid from projects where shortname="Example1"), "Example Wiki Page 1", "This is a wiki page");')
db.exec_cmd(dbname, 'insert into wiki values((select rowid from projects where shortname="Example3"), "Example Wiki Page 2", "This is a wiki page");')
db.exec_cmd(dbname, 'insert into wiki values((select rowid from projects where shortname="Example2"), "Example Wiki Page 3", "This is a wiki page");')
db.exec_cmd(dbname, 'insert into wiki values((select rowid from projects where shortname="Example5"), "Example Wiki Page 4", "This is a wiki page");')
db.exec_cmd(dbname, 'insert into wiki values((select rowid from projects where shortname="Example4"), "Example Wiki Page 5", "This is a wiki page");')
print(db.exec_query(dbname, 'select * from projects;'))
print(db.exec_query(dbname, 'select * from bugs where projectid = (select rowid from projects where shortname = "Example3");'))
print(db.exec_query(dbname, 'select * from bugs where projectid = (select rowid from projects where shortname = "Example5");'))
print(db.exec_query(dbname, 'select * from bugs where projectid = (select rowid from projects where shortname = "Example1");'))
print(db.exec_query(dbname, 'select * from bugs where projectid = (select rowid from projects where shortname = "Example2");'))
print(db.exec_query(dbname, 'select * from bugs where projectid = (select rowid from projects where shortname = "Example4");'))
print(db.exec_query(dbname, 'select * from wiki where projectid = (select rowid from projects where shortname = "Example3");'))
print(db.exec_query(dbname, 'select * from wiki where projectid = (select rowid from projects where shortname = "Example5");'))
print(db.exec_query(dbname, 'select * from wiki where projectid = (select rowid from projects where shortname = "Example1");'))
print(db.exec_query(dbname, 'select * from wiki where projectid = (select rowid from projects where shortname = "Example2");'))
print(db.exec_query(dbname, 'select * from wiki where projectid = (select rowid from projects where shortname = "Example4");'))