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 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: