def setup_test_db(params):
""" Create a database and table for this benchmark to use. """
conn = util.get_connection(params, db="information_schema")
# creating database_cursor to perform SQL operation
db_cursor = conn.cursor()
print('Creating database %s' % params.db_name)
db_cursor.execute('DROP DATABASE IF EXISTS %s' % params.db_name)
db_cursor.execute('CREATE DATABASE %s' % params.db_name)
db_cursor.execute('USE %s' % params.db_name)
print('Creating table test_taglist')
db_cursor.execute('DROP TABLE IF EXISTS test_taglist')
db_cursor.execute("""\
CREATE TABLE test_taglist(
tagid INT AUTO_INCREMENT PRIMARY KEY,
tag TEXT NOT NULL)""")
print('Creating table test_autotags')
db_cursor.execute('DROP TABLE IF EXISTS test_autotags')
db_cursor.execute("""\
CREATE TABLE test_autotags(
metadataid BIGINT NOT NULL,
tagname TEXT NOT NULL,
probability DECIMAL(5,4) NOT NULL DEFAULT 0.000)""")
print('Creating table test_metadata')
db_cursor.execute('DROP TABLE IF EXISTS test_metadata')
db_cursor.execute("""\
CREATE TABLE test_metadata(
line_number varchar(255) DEFAULT NULL,
id BIGINT NOT NULL PRIMARY KEY,
hash varchar(255) DEFAULT NULL,
user_nsid varchar(255) DEFAULT NULL,
user_nickname varchar(255) DEFAULT NULL,
date_taken varchar(255) DEFAULT NULL,
date_uploaded varchar(255) DEFAULT NULL,
capture_device varchar(255) DEFAULT NULL,
title varchar(255) DEFAULT NULL,
description varchar(255) DEFAULT NULL,
user_tags varchar(255) DEFAULT NULL,
machine_tags varchar(255) DEFAULT NULL,
longitude varchar(255) DEFAULT NULL,
latitude varchar(255) DEFAULT NULL,
coord_accuracy varchar(255) DEFAULT NULL,
page_url varchar(255) DEFAULT NULL,
download_url varchar(255) DEFAULT NULL,
license_name varchar(255) DEFAULT NULL,
license_url varchar(255) DEFAULT NULL,
server_id varchar(255) DEFAULT NULL,
farm_id varchar(255) DEFAULT NULL,
secret varchar(255) DEFAULT NULL,
secret_original varchar(255) DEFAULT NULL,
extension varchar(255) DEFAULT NULL,
marker varchar(255) DEFAULT NULL,
KEY id (id))""")
conn.commit()
db_cursor.close()
conn.close()
def connect(table_name, dates_with_attributes):
connection = get_connection('anki_user', 'ankireddy', 'localhost', 'dates',
5432)
cursor = get_cursor(connection)
ins_query = get_insert_query(table_name)
insert_data(connection, cursor, ins_query, dates_with_attributes)
connection.close()
def _get_connections(tenant, args, key):
tgt_swiftcon = None
if args.default_storage == 'rgw':
# Get RGW connection from Keystone.
src_swiftcon = util.get_connection(
tenant.name,
args.user.split(':')[1],
key,
args.authurl,
{'tenant_name': tenant.name, 'region_name': args.region}
)
if args.act == 'copy':
storurl = 'https://%s:%s/v1/AUTH_%s' % (
args.host, args.port, tenant.id)
tgt_swiftcon = util.get_connection(
tenant.name,
args.user.split(':')[1],
key,
args.authurl,
{'tenant_name': tenant.name, 'region_name': args.region,
'object_storage_url': storurl}
)
else:
storurl = 'https://%s:%s/swift/v1' % (args.host, args.port)
src_swiftcon = util.get_connection(
tenant.name,
args.user.split(':')[1],
key,
args.authurl,
{'tenant_name': tenant.name, 'region_name': args.region,
'object_storage_url': storurl}
)
if args.act == 'copy':
# Get Swift connection from Keystone.
tgt_swiftcon = util.get_connection(
tenant.name,
args.user.split(':')[1],
key,
args.authurl,
{'tenant_name': tenant.name, 'region_name': args.region}
)
return src_swiftcon, tgt_swiftcon
def drop_dbs(params):
""" Create a database and table for this benchmark to use. """
conn = util.get_connection(params, db="information_schema")
db_cursor = conn.cursor()
db_cursor.execute('DROP DATABASE %s' % params.db_name)
conn.commit()
db_cursor.close()
conn.close()
def process_tag_entities(params):
num_lines = len([line.strip() for line in open(str(params.tag_list), 'r')])
query = "LOAD DATA INFILE '{}' INTO TABLE test_taglist (tag)".format(
str(params.tag_list.absolute()))
with util.get_connection(params) as conn:
conn.execute(query)
count = conn.get("SELECT COUNT(*) AS count FROM test_taglist").count
return count, num_lines - count
def cleanup():
""" Cleanup the database this benchmark is using. """
conn = util.get_connection(params)
db_cursor = conn.cursor()
db_cursor.execute('DROP DATABASE %s' % params.db_name)
conn.commit()
db_cursor.close()
conn.close()
def load_table(db_details, data, column_names, table_name):
TARGET_DB = db_details['TARGET_DB']
connection = get_connection(db_type=TARGET_DB['DB_TYPE'],
db_host=TARGET_DB['DB_HOST'],
db_port=TARGET_DB['DB_PORT'],
db_name=TARGET_DB['DB_NAME'],
db_user=TARGET_DB['DB_USER'],
db_pass=TARGET_DB['DB_PASS'])
#query = build_query(table_name, column_names)
insert_data(connection, data, table_name, column_names)
def load_Products(db_details,df):
TARGET_DB = db_details['TARGET_DB']
con = get_connection(db_type=TARGET_DB['DB_TYPE'],
db_host=TARGET_DB['DB_HOST'],
db_name=TARGET_DB['DB_NAME'],
db_user=TARGET_DB['DB_USER'],
db_pass=TARGET_DB['DB_PASS']
)
df.to_sql('dim_products', con, if_exists='append')
con.close()
def _get_connections(tenant_name, user_name, key, auth_url):
conns = {}
for (region, suffix) in six.iteritems(REGION_SUFFIX_MAP):
conn = util.get_connection(tenant_name, user_name, key, auth_url, {
'tenant_name': tenant_name,
'region_name': region
})
conns[region] = conn
return conns
def load_table(db_details, data, column_names, table_name):
TARGET_DB = db_details['TARGET_DB']
connection = get_connection(db_type=TARGET_DB['DB_TYPE'],
db_host=TARGET_DB['DB_HOST'],
db_name=TARGET_DB['DB_NAME'],
db_user=TARGET_DB['DB_USER'],
db_pass=TARGET_DB['DB_PASS'])
cursor = connection.cursor()
query = build_insert_query(table_name, column_names)
insert_data(connection, cursor, query, data)
connection.close()
def truncate_table(db_details, table_name):
target_db = db_details['TARGET_DB']
connection = get_connection(db_type=target_db['DB_TYPE'],
db_host=target_db['DB_HOST'],
db_name=target_db['DB_NAME'],
db_user=target_db['DB_USER'],
db_pass=target_db['DB_PASS'])
query = f'TRUNCATE TABLE {table_name}'
cursor = connection.cursor()
cursor.execute(query)
connection.close()
def process_tag_entities(params):
num_lines = len([line.strip() for line in open(str(params.tag_list), 'r')])
query = "LOAD DATA LOCAL INFILE '{}' INTO TABLE test_taglist (tag)".format(
str(params.tag_list.absolute()))
conn = util.get_connection(params)
db_cursor = conn.cursor()
db_cursor.execute(query)
db_cursor.execute("SELECT COUNT(*) FROM test_taglist")
(count, ) = db_cursor.fetchone()
conn.commit()
db_cursor.close()
conn.close()
return count, num_lines - count
def process_metadata_entities(params):
all_data = pd.read_csv(str(params.data_file),
sep='\t',
names=util.property_names)
batch, num_lines, blocks, results = get_parameters(params, all_data)
query = "LOAD DATA INFILE '{}' INTO TABLE test_metadata ({})".format(
str(params.data_file.absolute()),
','.join(util.property_names_sql[:-3]))
with util.get_connection(params) as conn:
conn.execute(query)
count = conn.get("SELECT COUNT(*) AS count FROM test_metadata").count
return count, num_lines - count
def _get_connections(tenant_name, user_name, key, auth_url):
conns = {}
for (region, suffix) in six.iteritems(REGION_SUFFIX_MAP):
conn = util.get_connection(
tenant_name,
user_name,
key,
auth_url,
{'tenant_name': tenant_name, 'region_name': region}
)
conns[region] = conn
return conns
def main(couch_params, main_db, avatar_db, master_params, wikifeat_home):
# Set up credentials
credentials = util.get_credentials(couch_params.adminuser, couch_params.adminpass)
global gh, ph
gh = util.get_headers(credentials)
ph = util.put_headers(credentials)
global wf_dir
wf_dir = wikifeat_home
# Establish a connection to couchdb
conn = util.get_connection(couch_params.use_ssl, couch_params.host, couch_params.port)
conn.connect()
setup_main_db(conn, main_db)
setup_user_db(conn)
setup_avatar_db(conn, avatar_db)
if master_params.skip_master is False:
create_master_user(conn, master_params)
def process_autotags_entities(params):
num_lines = len([line.strip() for line in open(str(params.tag_file), 'r')])
query = \
'''LOAD DATA INFILE '{}'
INTO TABLE test_autotags
FIELDS TERMINATED BY '\t'(metadataid,tagname,probability)'''.format(str(params.tag_file.absolute()))
query2 = \
'''UPDATE test_autotags INNER JOIN test_taglist ON test_autotags.tagname=test_taglist.tag
SET test_autotags.tagid = test_taglist.idx'''
query3 = \
'''ALTER TABLE test_autotags DROP column tagname;'''
with util.get_connection(params) as conn:
conn.execute(query)
conn.execute(query2)
conn.execute(query3)
count = conn.get("SELECT COUNT(*) AS count FROM test_autotags").count
return count, num_lines - count
def process_metadata_entities(params):
all_data = pd.read_csv(str(params.data_file),
sep='\t',
names=util.property_names)
batch, num_lines, blocks, results = get_parameters(params, all_data)
query = "LOAD DATA LOCAL INFILE '{}' INTO TABLE test_metadata ({})".format(
str(params.data_file.absolute()),
','.join(util.property_names_sql[:-3]))
conn = util.get_connection(params)
db_cursor = conn.cursor()
db_cursor.execute(query)
db_cursor.execute("SELECT COUNT(*) FROM test_metadata")
(count, ) = db_cursor.fetchone()
conn.commit()
db_cursor.close()
conn.close()
return count, num_lines - count
def read_table(db_details,table_name,limit=0):
source_db=db_details['SOURCE_DB']
connection=get_connection(db_type=source_db['DB_TYPE'],
db_host=source_db['DB_HOST'],
db_name=source_db['DB_NAME'],
db_user=source_db['DB_USER'],
db_pass=source_db['DB_PASS']
)
cursor=connection.cursor()
if limit==0:
query=f'select * from {table_name}'
else :
query=f'select * from {table_name} LIMIT {limit}'
cursor.execute(query)
data=cursor.fetchall()
columns=cursor.column_names
connection.close()
return data,columns
def main(couch_params, main_db, avatar_db, master_params, wikifeat_home):
# Set up credentials
credentials = util.get_credentials(couch_params.adminuser,
couch_params.adminpass)
global gh, ph
gh = util.get_headers(credentials)
ph = util.put_headers(credentials)
global wf_dir
wf_dir = wikifeat_home
# Establish a connection to couchdb
conn = util.get_connection(couch_params.use_ssl, couch_params.host,
couch_params.port)
conn.connect()
setup_main_db(conn, main_db)
setup_user_db(conn)
setup_avatar_db(conn, avatar_db)
if master_params.skip_master is False:
create_master_user(conn, master_params)
def load_data(db_details,df,table_name):
TARGET_DB = db_details['TARGET_DB']
con = get_connection(db_type=TARGET_DB['DB_TYPE'],
db_host=TARGET_DB['DB_HOST'],
db_name=TARGET_DB['DB_NAME'],
db_user=TARGET_DB['DB_USER'],
db_pass=TARGET_DB['DB_PASS']
)
cursor = con.cursor()
# creating column list for insertion
cols = ",".join([str(i) for i in df.columns.tolist()])
# Insert DataFrame recrds one by one.
for i, row in df.iterrows():
sql = "INSERT INTO " + table_name + " (" + cols + ") VALUES (" + "%s," * (len(row) - 1) + "%s)"
cursor.execute(sql, tuple(row))
# the connection is not autocommitted by default, so we must commit to save our changes
con.commit()
con.close()
def load_table(db_details, data, column_names, table_name):
target_db = db_details['TARGET_DB']
logger.debug(target_db)
connection = get_connection(db_type=target_db['DB_TYPE'],
db_host=target_db['DB_HOST'],
db_name=target_db['DB_NAME'],
db_user=target_db['DB_USER'],
db_pass=target_db['DB_PASS']
)
cursor = connection.cursor()
query = build_insert_query(table_name, column_names)
logger.debug(query)
insert_data(connection, cursor, query, data)
connection.close()
def read_table(db_details, table_name, limit=0):
SOURCE_DB = db_details['SOURCE_DB']
connection = get_connection(db_type=SOURCE_DB['DB_TYPE'],
db_host=SOURCE_DB['DB_HOST'],
db_name=SOURCE_DB['DB_NAME'],
db_user=SOURCE_DB['DB_USER'],
db_pass=SOURCE_DB['DB_PASS'])
cursor = connection.cursor()
if limit == 0:
query = f'SELECT * FROM {table_name}'
else:
query = f'SELECT * FROM {table_name} LIMIT {limit}'
cursor.execute(query)
data = cursor.fetchall()
column_names = cursor.column_names
df = pd.DataFrame(data=data, columns=column_names)
return df
connection.close()
def read_table(db_details, table_name, limit=0):
logger.info(db_details)
connection = get_connection(db_type=db_details['DB_TYPE'],
db_host=db_details['DB_HOST'],
db_name=db_details['DB_NAME'],
db_user=db_details['DB_USER'],
db_pass=db_details['DB_PASS']
)
cursor = connection.cursor()
if limit == 0:
query = f'SELECT * FROM {table_name}'
else:
query = f'SELECT * FROM {table_name} LIMIT {limit}'
cursor.execute(query)
data = cursor.fetchall()
column_names = get_column_names(cursor, db_details['DB_TYPE'])
connection.close()
return data, column_names
def read_table(db_details, table_name, limit=0):
SOURCE_DB = db_details['SOURCE_DB']
connection = get_connection(db_type=SOURCE_DB['DB_TYPE'],
db_host=SOURCE_DB['DB_HOST'],
db_port=SOURCE_DB['DB_PORT'],
db_name=SOURCE_DB['DB_NAME'],
db_user=SOURCE_DB['DB_USER'],
db_pass=SOURCE_DB['DB_PASS'])
cursor = connection.cursor()
if limit == 0:
query = 'SELECT * FROM {}'.format(table_name)
else:
query = 'SELECT * FROM {} LIMIT {}'.format(table_name, limit)
cursor.execute(query)
data = cursor.fetchall()
column_names = cursor.column_names
connection.close()
return data, column_names
def read_table(db_details,DB_USER,DB_PASS ,table_name, limit=0):
read_db = db_details['RETAIL']
connection = get_connection(db_type=read_db['DB_TYPE'],
db_host=read_db['DB_HOST'],
db_port=read_db['DB_PORT'],
db_name=read_db['DB_NAME'],
db_user=DB_USER,
db_pass=DB_PASS
)
cursor = connection.cursor()
if limit == 0:
query = f'SELECT * FROM {table_name}'
else:
query = f'SELECT * FROM {table_name} LIMIT {limit}'
cursor.execute(query)
data = cursor.fetchall()
column_names = get_column_names(cursor, read_db['DB_TYPE'])
connection.close()
return data, column_names
def process_autotags_entities(params):
num_lines = len([line.strip() for line in open(str(params.tag_file), 'r')])
conn = util.get_connection(params)
db_cursor = conn.cursor()
# Read data from file
query = \
'''LOAD DATA LOCAL INFILE '{}'
INTO TABLE test_autotags
FIELDS TERMINATED BY '\t'(metadataid,tagname,probability)'''.format(str(params.tag_file.absolute()))
db_cursor.execute(query)
# Add column (tagid) for index of autotag taken from test_taglist table
query1 = \
'''ALTER TABLE test_autotags ADD column tagid INT AFTER metadataid;'''
query2 = \
'''UPDATE test_autotags INNER JOIN test_taglist ON test_autotags.tagname=test_taglist.tag
SET test_autotags.tagid = test_taglist.tagid'''
db_cursor.execute(query1)
db_cursor.execute(query2)
# Drop column (tagname) from test_autotags and add tagid and metadataid as index
query3 = \
'''ALTER TABLE test_autotags DROP column tagname;''' #, ADD PRIMARY KEY(metadataid,tagid);'''
query4 = \
'''create index tagid ON test_autotags(tagid);'''
query5 = \
'''create index metadataid ON test_autotags(metadataid);'''
db_cursor.execute(query3)
db_cursor.execute(query4)
db_cursor.execute(query5)
db_cursor.execute("SELECT COUNT(*) FROM test_autotags")
(count, ) = db_cursor.fetchone()
conn.commit()
db_cursor.close()
conn.close()
return count, num_lines - count
def read_table(db_details, table_name, limit=0):
SOURCE_DB = db_details['SOURCE_DB']
connection = get_connection(
db_type = SOURCE_DB['DB_TYPE'],
db_host = SOURCE_DB['DB_HOST'],
db_port = SOURCE_DB['DB_PORT'],
db_name = SOURCE_DB['DB_NAME'],
db_user = SOURCE_DB['DB_USER'],
db_pass = SOURCE_DB['DB_PASS']
)
print('read query building')
if limit == 0:
query = sqlalchemy.text(f'select * from {table_name}')
else:
query = sqlalchemy.text(f'select * from {table_name} limit {limit}')
result = connection.execute(query).fetchall()
print("data fetched")
column_names = result[0].keys()
# connection.dispose()
return result,column_names
def drop_dbs(params):
""" Create a database and table for this benchmark to use. """
with util.get_connection(params, db="information_schema") as conn:
print('Dropping database %s' % params.db_name)
conn.query('DROP DATABASE %s' % params.db_name)
def setup_test_db(params):
""" Create a database and table for this benchmark to use. """
with util.get_connection(params, db="information_schema") as conn:
print('Creating database %s' % params.db_name)
conn.query('DROP DATABASE IF EXISTS %s' % params.db_name)
conn.query('CREATE DATABASE %s' % params.db_name)
conn.query('USE %s' % params.db_name)
print('Creating table test_taglist')
conn.query('DROP TABLE IF EXISTS test_taglist')
conn.query("""\
CREATE TABLE test_taglist(
idx INT AUTO_INCREMENT PRIMARY KEY,
tag TEXT NOT NULL DEFAULT '')""")
print('Creating table test_autotags')
conn.query('DROP TABLE IF EXISTS test_autotags')
conn.query("""\
CREATE TABLE test_autotags(
metadataid BIGINT,
tagid INT AUTO_INCREMENT,
tagname TEXT,
probability DECIMAL(5,4) NOT NULL DEFAULT 0.000,
KEY (metadataid,tagid))""")
# conn.query("""\
# CREATE TABLE test_autotags(
# idx INT AUTO_INCREMENT PRIMARY KEY,
# metadataid BIGINT,
# tagid INT,
# tagname TEXT,
# probability DECIMAL(5,4) NOT NULL DEFAULT 0.000)""")
print('Creating table test_metadata')
conn.query('DROP TABLE IF EXISTS test_metadata')
# t_query = ''
conn.query("""\
CREATE TABLE test_metadata(
line_number varchar(255) DEFAULT NULL,
id BIGINT NOT NULL PRIMARY KEY,
hash varchar(255) DEFAULT NULL,
user_nsid varchar(255) DEFAULT NULL,
user_nickname varchar(255) DEFAULT NULL,
date_taken varchar(255) DEFAULT NULL,
date_uploaded varchar(255) DEFAULT NULL,
capture_device varchar(255) DEFAULT NULL,
title varchar(255) DEFAULT NULL,
description varchar(255) DEFAULT NULL,
user_tags varchar(255) DEFAULT NULL,
machine_tags varchar(255) DEFAULT NULL,
longitude varchar(255) DEFAULT NULL,
latitude varchar(255) DEFAULT NULL,
coord_accuracy varchar(255) DEFAULT NULL,
page_url varchar(255) DEFAULT NULL,
download_url varchar(255) DEFAULT NULL,
license_name varchar(255) DEFAULT NULL,
license_url varchar(255) DEFAULT NULL,
server_id varchar(255) DEFAULT NULL,
farm_id varchar(255) DEFAULT NULL,
secret varchar(255) DEFAULT NULL,
secret_original varchar(255) DEFAULT NULL,
extension varchar(255) DEFAULT NULL,
marker varchar(255) DEFAULT NULL)""")
def cleanup():
""" Cleanup the database this benchmark is using. """
with util.get_connection(params) as conn:
conn.query('DROP DATABASE %s' % params.db_name)
import PySimpleGUI as sg
import util
import ui_tools as ui
import robots
import db_tools as db
from collections import OrderedDict
config = util.get_config()
con = util.get_connection()
driver = util.get_chrome_driver()
driver.implicitly_wait(10)
# Do the UI stuff
sg.theme(config["gui"]["theme"]) # Add a touch of color
# add the quicklinks buttons
def add_links(layout):
rows = db.query_dict(
con, """
select u.universitiesid, ul.pretty_name as uni_pretty_name, ul.url as uni_url,
c.coursesid, cl.pretty_name as course_pretty_name, cl.url as course_url
from universities u
left join courses c on u.universitiesid = c.universitiesid
left join universities_links ul on ul.universitiesid = u.universitiesid
left join courses_links cl on cl.coursesid = c.coursesid and cl.universitiesid = u.universitiesid
order by u.universitiesid, c.coursesid;
""")
structure = OrderedDict()
for row in rows:
