def connect_to_database(): """ Load the database login file and get a read/write connection. """ creds_file = os.path.abspath(os.path.dirname(os.path.abspath(__file__)) + '/../msqlrw.txt') uname, pword = load_database_credentials(creds_file) return get_database_connection(use_mysql=True, username=uname, password=pword, hostname='jsubmit.jlab.org', database_name="CLAS12OCR")
def setup_database(args): """ Setup database connection based on command line options. """ logger = logging.getLogger('SubMit') if args.lite: use_mysql = False username, password = "******", "none" database_name = args.lite else: use_mysql = True if args.test_database: cred_file_name = '/..' + fs.test_db_cred_file #the ../ is needed due to the path difference in client/src and utils/ database_name = fs.MySQL_Test_DB_Name else: cred_file_name = '/..' + fs.prod_db_cred_file database_name = fs.MySQL_Prod_DB_Name cred_file_loc = os.path.dirname( os.path.abspath(__file__)) + cred_file_name cred_file = os.path.normpath(cred_file_loc) username, password = database.load_database_credentials(cred_file) logger.debug('Connecting to MySQL: {}'.format(use_mysql)) db_conn, sql = database.get_database_connection( use_mysql=use_mysql, database_name=database_name, username=username, password=password, hostname=fs.db_hostname) return db_conn, sql
def connect_to_database(database_name="CLAS12TEST"): creds_file = os.path.abspath( os.path.dirname(os.path.abspath(__file__)) + '/../msqlrw.txt') uname, pword = load_database_credentials(creds_file) return get_database_connection(use_mysql=True, username=uname, password=pword, hostname='jsubmit.jlab.org', database_name=database_name)
def single_user(user_id): connection = get_database_connection() try: with connection.cursor() as cursor: sql = "SELECT * FROM Users WHERE Users.id=%s" cursor.execute(sql, (user_id)) result = cursor.fetchall() return Response(json.dumps(result, ensure_ascii=False, indent=2).encode('utf8'), mimetype='application/json', content_type='application/json; charset=utf-8') finally: connection.close()
def users(): connection = get_database_connection() try: with connection.cursor() as cursor: sql = "SELECT `id`, `username`, `name`, `picture`, `rating`, `description` FROM `Users`" cursor.execute(sql) result = cursor.fetchall() return Response(json.dumps(result, ensure_ascii=False, indent=2).encode('utf8'), mimetype='application/json', content_type='application/json; charset=utf-8') finally: connection.close()
def user(user_id): connection = get_database_connection() try: with connection.cursor() as cursor: sql = "SELECT * FROM Participations INNER JOIN Good_Deeds ON Participations.good_deed_id = Good_Deeds.id WHERE Participations.user_id=%s" cursor.execute(sql, (user_id)) result = cursor.fetchall() return Response(json.dumps(result, ensure_ascii=False, indent=2).encode('utf8'), mimetype='application/json', content_type='application/json; charset=utf-8') finally: connection.close()
def gooddeeds_without_participation_ok(): connection = get_database_connection() try: with connection.cursor() as cursor: sql = "SELECT DISTINCT title, description, address, start_date, end_date, creator_user_id, latitude, longitude, Participations.status_id FROM Good_Deeds LEFT JOIN Participations ON Good_Deeds.id=Participations.good_deed_id WHERE Participations.status_id <> 1 OR Participations.status_id IS NULL" cursor.execute(sql) result = cursor.fetchall() return Response(json.dumps(result, ensure_ascii=False, indent=2).encode('utf8'), mimetype='application/json', content_type='application/json; charset=utf-8') finally: connection.close()
def connect_to_database(sqlite_db_name): creds_file = os.path.abspath(os.path.dirname(os.path.abspath(__file__)) + '/../msqlrw.txt') uname, pword = load_database_credentials(creds_file) mysql=True db_name="CLAS12OCR" if sqlite_db_name != None: mysql=False db_name = "CLAS12OCR.db" return get_database_connection(use_mysql=mysql, username=uname, password=pword, hostname='jsubmit.jlab.org', database_name=db_name)
def accept_gooddeed_participation(participation_id): connection = get_database_connection() param_status_id = request.form['status-id'] try: with connection.cursor() as cursor: sql = "UPDATE Participations SET status_id=%s WHERE Participations.id=%s" cursor.execute(sql, (param_status_id, participation_id)) connection.commit() finally: connection.close() return "", 200
async def create_poll_route(): form = await request.form json_data = {key: form[key] for key in form.keys()} results = {form[key]: 0 for key in json_data.keys() if key != "title"} print(json_data) conn = get_database_connection() query_result = conn.run( "insert into pollster (id, results, title) values (uuid_generate_v4(), :results, :title) returning id", title=json_data["title"], results=json.dumps(results)) conn.commit() conn.close() return redirect(url_for("get_poll_route", id=query_result[0][0]))
def create_participation(): connection = get_database_connection() param_user_id = request.form['user-id'] param_deed_id = request.form['good-deed-id'] try: with connection.cursor() as cursor: sql = "INSERT INTO Participations (user_id, status_id, good_deed_id) VALUES (%s, %s, %s)" cursor.execute(sql, (param_user_id, 2, param_deed_id)) connection.commit() finally: connection.close() return "", 200
async def get_poll_route(id): conn = get_database_connection() query_result = conn.run( "select id, title, results from pollster where id=:id", id=id) conn.close() print(query_result) poll_id = query_result[0][0] poll_title = query_result[0][1] poll_results = query_result[0][2] return await render_template("poll.html", title=poll_title, options=poll_results, poll_id=str(poll_id))
async def results_route(id): conn = get_database_connection() query_result = conn.run("select title, results from pollster where id=:id", id=id) conn.close() poll_title = query_result[0][0] poll_results = [{ "category": key, "value": query_result[0][1][key] } for key in query_result[0][1].keys()] print(poll_results) return await render_template("results.html", poll_title=poll_title, poll_results=json.dumps(poll_results))
async def vote_route(id): form = await request.form print([k for k in form.keys()]) vote = form["vote"] conn = get_database_connection() result_query = conn.run("select results from pollster where id=:id", id=id) results = result_query[0][0] results[vote] += 1 update_query = conn.run( "update pollster set results=:results where id=:id", id=id, results=json.dumps(results)) conn.commit() conn.close() return redirect(url_for("results_route", id=id))
def gooddeeds(): connection = get_database_connection() param_creator_user_id = request.form['creator-user-id'] param_title = request.form['title'] param_description = request.form['description'] param_address = request.form['address'] param_start_date = request.form['start-date'] param_end_date = request.form['end-date'] param_latitude = request.form['latitude'] param_longitude = request.form['longitude'] try: with connection.cursor() as cursor: sql = "INSERT INTO Good_Deeds (title, description, address, start_date, end_date, creator_user_id, latitude, longitude) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" cursor.execute(sql, (param_title, param_description, param_address, param_start_date, param_end_date, param_creator_user_id, param_latitude, param_longitude)) connection.commit() finally: connection.close() return "", 200
def main(): #Build schema for SOS data logger.info("Creating SOS file schema") create_sos_schema() #Populate SOS data logger.info("Running SOS file reads") fwf.main() #Create TCAD data schema and populate logger.info("Running TCAD file reads") #df = tcad.download_read() df = tcad.read_tcad() create_tcad_schema() conn = db.get_database_connection(local_dev=local_dev) db.execute_values(conn, df, "tcad") #Run normalization code for addresses logger.info("Running address normalization schema") run_schema("sql", "create_normalized_addresses.sql") #Run normalization code for addresses logger.info("Running index creation for names (biz/person") run_schema("sql", "create_name_search_index.sql")
import io import os from os import path from constants import Queries, CountryConstants, StateConstants, CityConstants, FOLDER_PATH, ENDLINE import database connection = database.get_database_connection() cursor = connection.cursor(dictionary=True) def check_directory(): if path.exists(FOLDER_PATH) == False: os.mkdir(FOLDER_PATH) def bind_value(query, literal, value): return query.replace('$' + literal, str(value).replace('\'', "\'\'", 1), 1) def map_country_values(country): values_query = Queries.INSERT_INTO_COUNTRIES_QUERY values_query = bind_value(values_query, CountryConstants.COUNTRY_ID, country[CountryConstants.COUNTRY_ID]) values_query = bind_value(values_query, CountryConstants.COUNTRY_NAME, country[CountryConstants.COUNTRY_NAME]) values_query = bind_value(values_query, CountryConstants.COUNTRY_ISO3, country[CountryConstants.COUNTRY_ISO3]) values_query = bind_value(values_query, CountryConstants.COUNTRY_ISO2, country[CountryConstants.COUNTRY_ISO2]) values_query = bind_value(values_query, CountryConstants.COUNTRY_PHONECODE, country[CountryConstants.COUNTRY_PHONECODE])
else: use_mysql = True if args.test_database: cred_file_name = fs.test_db_cred_file database_name = fs.MySQL_Test_DB_Name else: cred_file_name = fs.prod_db_cred_file database_name = fs.MySQL_Prod_DB_Name cred_file_loc = os.path.dirname( os.path.abspath(__file__)) + cred_file_name cred_file = os.path.normpath(cred_file_loc) username, password = database.load_database_credentials(cred_file) db_conn, sql = database.get_database_connection( use_mysql=use_mysql, database_name=database_name, username=username, password=password, hostname=fs.db_hostname) # Create tables for table, primary_key, foreign_keys, fields in zip( fs.tables, fs.pks, fs.foreign_keys, fs.table_fields): create_table(db_conn, sql, table, primary_key, foreign_keys) for field, field_type in fields: add_field(db_conn, sql, table, field, field_type) db_conn.close()
formalized into unittest.TestCase tests, but a test database structure needs to be coded first. """ import fs from database import (get_database_connection, load_database_credentials, get_users, select_by_user_submission_id) if __name__ == '__main__': creds_file = '../msqlrw.txt' uname, pword = load_database_credentials(creds_file) db_connection, sql = get_database_connection(use_mysql=True, hostname='jsubmit.jlab.org', username=uname, password=pword) # A simple test query query = """ SELECT User, timestamp FROM UserSubmissions WHERE UserSubmissionID > 65 AND User = '******'; """ sql.execute(query) result = sql.fetchall() print(result) # Test function users = get_users(sql) print(users) print(type(users))
def populate_meta_data_table(col, table): """Populate meta data for SOS.""" conn = db.get_database_connection(local_dev=local_dev) for entry in col: df = md.df_meta[list(entry)].dropna() db.execute_values(conn, df, table)
def run_independent_query(query, params=[]): con = get_database_connection() cur = con.cursor() cur.execute(query, params) return cur.fetchall()
def req_context(db): """run tests within a test request context so that 'g' is present""" with app.test_request_context('/'): yield con = get_database_connection() con.rollback()
cred_file_loc = os.path.dirname(os.path.abspath(__file__)) + cred_file_name print("cred file loc is") print(cred_file_loc) cred_file = os.path.normpath(cred_file_loc) print("cred file is:") print(cred_file) username, password = database.load_database_credentials(cred_file) print("The password we have is {0}".format(password)) db_conn, sql = database.get_database_connection( use_mysql=use_mysql, database_name=database_name, username=username, password=password, hostname='jsubmit.jlab.org' ) # Create tables for table, primary_key, foreign_keys, fields in zip( fs.tables, fs.pks, fs.foreign_keys, fs.table_fields): create_table(db_conn, sql, table, primary_key, foreign_keys) for field, field_type in fields: add_field(db_conn, sql, table, field, field_type) db_conn.close()