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()
def run_independent_query(query, params=[]):
con = get_database_connection()
cur = con.cursor()
cur.execute(query, params)
return cur.fetchall()
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()
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()
