def run_queries():
print("Querying...\n")
for query_info in queries_info:
execute_query(redis_graph,
query_info.description,
query_info.query)
def setup_filter_options(context, object_list=None):
where_column = where = None
ids = None
BASE_SQL_COUNT = 'select {0}, count(distinct database_id) from finder_column where {0} is not null group by {0} order by 1'
if object_list:
ids = []
# get ids from
for ds in object_list:
ids.append('{0}'.format(ds.id))
ids = ','.join(ids)
where = 'finder_database.id in ({0})'.format(ids)
where_column = ' database_id in ({0})'.format(ids)
BASE_SQL_COUNT = 'select {0}, count(distinct database_id) from finder_column where {0} is not null and database_id in ({1}) group by {0} order by 1'
context['options_category'] = utils.count_value('category', where=where)
context['options_profiler_status'] = utils.count_value(
'profiler_status_short', include_nulls=False, where=where)
context['options_source_agency'] = utils.count_value('source_agency',
include_nulls=False,
where=where,
order_by_counts=False)
context['options_socrata_status'] = utils.count_value(
'socrata_status',
include_nulls=False,
where=where,
order_by_counts=False)
context['simple_types'] = utils.execute_query(
BASE_SQL_COUNT.format('profiler_type', ids))
context['detailed_types'] = utils.execute_query(
BASE_SQL_COUNT.format('profiler_type_most_detected', ids))
context['options_socrata_type'] = utils.execute_query(
BASE_SQL_COUNT.format('socrata_type', ids))
context['options_update_frequency'] = utils.count_value(
'socrata_update_frequency', where=where, order_by_counts=False)
def view_friendship():
"""
returns the degree of separation between two users
"""
if request.method == 'GET':
return render_template('view_friendship.html')
elif request.method == 'POST':
client = get_db_client()
user_1 = request.form.get('user_1', None)
user_2 = request.form.get('user_2', None)
if not (utils.user_exists(client, user_1) and utils.user_exists(client, user_2)):
message = "Users Doesnot Exist. Please use our create users API for creating users first."
elif user_1 == user_2:
message = "Same users entered."
else:
command = "select @rid from Users where email_id = '%s'" % user_1
rid_1 = utils.execute_query(client, command)[0].rid
command = "select @rid from Users where email_id = '%s'" % user_2
rid_2 = utils.execute_query(client, command)[0].rid
command = "Select shortestPath(%s, %s)" % (rid_1, rid_2)
results = utils.execute_query(client, command)
path_len = len(results[0].shortestPath) - 1
if path_len == -1:
message = "%s, %s are not connected" % (user_1, user_2)
else:
message = "%s, %s are %s order connections." % (user_1, user_2, path_len)
return render_template('view_friendship.html', message=message, user_1=user_1, user_2=user_2)
def run_queries():
print("Querying...\n")
for query_info in queries_info:
execute_query(redis_graph,
query_info.description,
query_info.query)
def run_queries():
print("Querying...\n")
queries = imdb_queries.IMDBQueries().queries()
for q in queries:
execute_query(redis_graph,
q.description,
q.query)
def init_db(client):
try:
command = "create class Users extends V"
utils.execute_query(client, command)
except:
pass
try:
command = "create class Connections extends E"
utils.execute_query(client, command)
except:
pass
def add_item(user, data): utils.execute_query( '''INSERT INTO public.items (username, data, created_ts) VALUES ( :username, :data, NOW() ) ''', username=user.username, data=data, ) return True
def add_building():
"""
return all buildings
:return:
"""
identify_number = request.json['in']
address = request.json['address']
owner = request.json['ownername']
print(request.json)
execute_query("INSERT INTO Building (identify_number, address, owner) \
VALUES (\'{0}\', \'{1}\', \'{2}\');".format(identify_number, address, owner))
# TODO add code status
return jsonify("Successful")
def delete_item(user, item_id): utils.execute_query( ''' UPDATE public.items SET deleted_ts = NOW() WHERE id=:item_id AND username=:username AND deleted_ts IS NULL ''', item_id=item_id, username=user.username )
def update_item(user, item_id, data): utils.execute_query( ''' UPDATE public.items SET data = :data, updated_ts = NOW() WHERE id=:item_id AND username=:username AND deleted_ts IS NULL ''', data=data, item_id=item_id, username=user.username, )
def test_sql_queries(function: Callable):
"""
Проверка SQL запроса на корректность
:param function: функция, возвращаюшая SQL запрос
"""
filename = f'./results/{function.__name__}'
orig_values = utils.read_pickled(filename)
query = function()
if type(query) != str:
pytest.fail(
f'Ваша функция {function.__name__} должна возвращать SQL запрос в виде строки'
)
if 'select' not in query.lower():
pytest.fail(
f'Запрос функции {function.__name__} должна быть SELECT запросом, но вместо этого там "{query}"'
)
try:
results, columns = utils.execute_query(query)
except Exception as e:
pytest.fail(
f'Непредвиденная ошибка во время выполнения запроса функции {function.__name__} '
f'! Получена ошибка: "{e}"')
return
try:
utils.compare_results(results, orig_values)
except ValueError as e:
pytest.fail(f'Неверный ответ для функции {function.__name__} ! {e}')
except Exception as e:
pytest.fail(
f'Непредвиденная ошибка во время проверки результата функции {function.__name__}'
)
def _county_limit_data(self):
"""Get FHA and GSE county limits."""
qry_args = self.request.values() * 2
query = """
SELECT
state_name, county_name, cl.complete_fips,
gse_limit, fha_limit, va_limit
FROM
oah_county_limits cl
INNER JOIN oah_county c ON c.complete_fips = cl.complete_fips
INNER JOIN oah_state s ON SUBSTR(c.complete_fips, 1, 2) = s.state_fips
WHERE
s.state_fips = ? OR s.state_abbr = ?
"""
rows = execute_query(query, qry_args)
for row in rows:
state, county, fips, gse, fha, va = row
self.data.append({
'state': state,
'county': county,
'complete_fips': fips,
'gse_limit': str(gse),
'fha_limit': str(fha),
'va_limit': str(va),
})
def _county_limit_data(self):
"""Get FHA and GSE county limits."""
qry_args = self.request.values() * 2
query = """
SELECT
state_name, county_name, cl.complete_fips,
gse_limit, fha_limit, va_limit
FROM
oah_county_limits cl
INNER JOIN oah_county c ON c.complete_fips = cl.complete_fips
INNER JOIN oah_state s ON SUBSTR(c.complete_fips, 1, 2) = s.state_fips
WHERE
s.state_fips = ? OR s.state_abbr = ?
"""
rows = execute_query(query, qry_args)
for row in rows:
state, county, fips, gse, fha, va = row
self.data.append({
'state': state,
'county': county,
'complete_fips': fips,
'gse_limit': str(gse),
'fha_limit': str(fha),
'va_limit': str(va),
})
def add_new_user():
"""
add a new user into Users table with given email id. CSRF considered
"""
if request.method == 'GET':
return render_template('add_new_user.html')
elif request.method == 'POST':
email_id = request.form.get('email_id', None)
client = get_db_client()
# check if already exist in command, unique email_ids
if utils.user_exists(client, email_id):
message = "User already exists."
else:
command = "INSERT INTO Users (email_id) values %s" % ("('"+ email_id + "')")
utils.execute_query(client, command)
message = "user %s created successfully" %(email_id)
return render_template('message.html', message=message)
def add_em():
"""
add electricity meters and all metrics
:return:
"""
EM_identify_number = request.json['EM_in']
EM_count = request.json['EM_count']
EM_date = request.json['EM_date']
EM_tot_max = request.json['EM_tot_max']
EM_tot_min = request.json['EM_tot_min']
execute_query(
"INSERT INTO EMeter (EM_count,EM_date,EM_tot_max,EM_tot_min,EM_identify_number) \
values ('{0}','{1}','{2}','{3}','{4}') \
".format(EM_count, EM_date, EM_tot_max, EM_tot_min,
EM_identify_number))
# TODO add code status
return jsonify("Successful")
def seach_map(request):
context = {}
queryset = Database.objects.exclude(lat_min=None,
lat_max=None,
long_min=None,
long_max=None).order_by(
'lat_min', '-lat_max',
'database_id')
queryset = filter_datasets_with_params(request, context, queryset)
datasets = queryset.all()
context['databaseBoundingBoxes'] = datasets
setup_filter_options(context, datasets)
#Display map style
map_type = request.GET.get('map_style', 'boxes')
if map_type is not None:
if map_type == 'borough':
areas = utils.execute_query(
"select boroname, ST_AsGeoJSON(geom) from nyc_boroughs")
named_areas = []
for area in areas:
named_areas.append({
'title': area[0].encode('ascii', 'ignore'),
'geojson': ast.literal_eval(area[1])
})
context['named_areas'] = named_areas
context['map_center'] = 'NYC'
elif map_type == 'single-heatmap':
# ids = Database.objects.filter(category='Education').values_list('id', flat=True).order_by('id')
ids = queryset.values_list('id', flat=True).order_by('id')
gps_data = utils.execute_query(
'select lat::float, long::float, sum(count)::int from finder_gpsdata where lat is not null and database_id in ({0}) group by lat, long '
.format(str(ids[:10])[1:-1]))
context['gps_data'] = gps_data
print 'dbs=', len(ids)
print 'gps_data=', len(gps_data)
context['map_style'] = map_type
return render(request, 'finder/search-map.html', context)
def get_customers():
"""
HW DB with customers
:return: template
"""
city = request.form.get('city')
if not None:
query = "SELECT * FROM customers WHERE City='{}';".format(city)
records = utils.execute_query(query)
result = render_template('get_customers.html', fake_data=records)
return result
def query_metadata(request,**kwargs):
key_mapping = {
'date' : 'date'
,'instance':'exec_id'
,'list': 'type'
}
sql = utils.compose_sql(kwargs['query'],kwargs[key_mapping[kwargs['query']]])
result = utils.execute_query(sql)
response = HttpResponse(result,content_type="text/plain")
return response
def get_items(user): return utils.execute_query( ''' SELECT * FROM public.items WHERE username=:username AND deleted_ts IS NULL ; ''', username=user.username ).fetchall()
def add_relationship():
"""
use an intermediate table to store the relationship between
buildings and EMs
:return:
"""
building = request.json['building']
em = request.json['em']
"""
Overall the logic is :
1. check whether EM already exists;if it exists ,return error
2. check whether the record exists;if exists ,return error
3. return successfully
or we just need to return all available EMs to generate a drop-down list
"""
execute_query(
"INSERT INTO B_EM_Relationship (building_identify_number,EM_identify_number) \
VALUES ('{0}','{1}');".format(building, em))
execute_query(
"INSERT INTO EMeter (EM_identify_number) values ('{0}')".format(em))
return jsonify("success")
def create_friends():
"""
given two users, create friendship connection between them
"""
message = ""
if request.method == 'GET':
return render_template('create_connection.html')
if request.method == 'POST':
client = get_db_client()
user_1 = request.form.get('user_1', None)
user_2 = request.form.get('user_2', None)
if not (utils.user_exists(client, user_1) or utils.user_exists(client, user_2)):
message = "Users Doesnot Exist. Please use our create users API for creating users first."
elif user_1 == user_2:
message = "Cannot create friends with self."
else:
if not utils.are_friends(client, user_1, user_2):
command = "create edge Connections from (select from Users where email_id = '%s') to (select from Users where email_id = '%s')" % (user_1, user_2)
utils.execute_query(client, command)
message = "connection created successfully from %s to %s" % (user_1, user_2)
else:
message = "They are already good friends."
return render_template('create_connection.html', message=message, user_1=user_1, user_2=user_2)
def create_random_graph():
"""
creates MAX_USER_COUNT number of random users in db.
and creates random connections between them.
"""
client = get_db_client()
command = "INSERT INTO Users (email_id) values %s" % ((", ").join(\
utils.get_random_insert_values()))
response = utils.execute_query(client, command)
for counter in range(0,settings.MAX_USER_COUNT):
# for nth user lets create random friends_count number of friends
friends_count = random.randint(0,5)
# name of those random number of users chosen to be friends of nth user
usernames=random.sample(range(0,settings.MAX_USER_COUNT),friends_count)
for username in usernames:
user_email = utils.get_user_email(username)
current_user_email = utils.get_user_email(counter)
if not utils.are_friends(client, user_email, current_user_email):
command = "create edge Connections from (select from Users where\
email_id = '%s') to (select from Users where email_id = '%s')"\
% (current_user_email, user_email)
utils.execute_query(client, command)
message = "%s users created succesfully." %(settings.MAX_USER_COUNT)
return render_template('message.html', message=message)
def query_trigger_names(request):
sql = 'SELECT sys_id,sys_class_name,name from ops_trigger;'
result = utils.execute_query(sql)
response = HttpResponse(result,content_type="text/plain")
return response;
def catalog_overview(request):
context = {}
if Database.objects.count() > 0:
context['chart_list'] = [
{
'title': 'Dataset Categories',
'data': utils.count_as_json('category', order_by_counts=False),
'colors': GColors.PALLETE
},
{
'title':
'Rows by Category',
'data':
utils.execute_query_as_json(
'select category, sum(rows) from finder_database where profiler_status = \'OK\' and category is not null group by category order by 1'
),
'colors':
GColors.PALLETE
},
{
'title':
'Datasets w/ Geo Data',
'data':
utils.execute_query_as_json(utils.sql_count_null_or_not_null(
'columns_geo_count',
label_not_null='Have',
label_null='Don`t Have'),
sort=False),
'colors': [GColors.BLUE, GColors.RED]
},
{
'title':
'Datasets with GPS',
'data':
utils.execute_query_as_json(utils.sql_count_null_or_not_null(
'gps_values',
label_not_null='Have',
label_null='Don`t Have'),
sort=False),
'colors': [GColors.BLUE, GColors.RED]
},
{
'title':
'Datasets w/ Temp. Data',
'data':
utils.execute_query_as_json(utils.sql_count_null_or_not_null(
'columns_temporal_count',
label_not_null='Have',
label_null='Don`t Have'),
sort=False),
'colors': [GColors.BLUE, GColors.RED]
},
{
'title':
'Datasets w/ Num. Data',
'data':
utils.execute_query_as_json(utils.sql_count_null_or_not_null(
'columns_numeric_count',
label_not_null='Have',
label_null='Don`t Have'),
sort=False),
'colors': [GColors.BLUE, GColors.RED]
},
{
'title':
'Datasets w/ Text. Data',
'data':
utils.execute_query_as_json(utils.sql_count_null_or_not_null(
'columns_text_count',
label_not_null='Have',
label_null='Don`t Have'),
sort=False),
'colors': [GColors.BLUE, GColors.RED],
},
{
'title': 'Socrata Status',
'data': utils.count_as_json('socrata_status',
include_nulls=True),
'colors': [GColors.BLUE, GColors.RED],
},
{
'title': 'Top 5 Dataset Owners',
'data': utils.count_as_json('owner', limit=5),
'full_data': utils.count_as_json('owner'),
'colors': GColors.PALLETE
},
{
'title': 'Top 5 Dataset Authors',
'data': utils.count_as_json('author', limit=5),
'full_data': utils.count_as_json('author'),
'colors': GColors.PALLETE
},
{
'title':
'Provided Types',
'data':
utils.count_as_json('socrata_type',
from_model='Column',
limit=5),
'full_data':
utils.count_as_json('socrata_type', from_model='Column'),
'colors':
GColors.PALLETE
},
{
'title':
'Dataset Rows Histogram (k)', #log(2, rows)::int
'data':
utils.execute_query_as_json(
'select rows/1000 from finder_database where rows is not null and rows != 0 order by 1'
),
# 'data': utils.execute_query_as_json('select ln(rows) from finder_database where rows is not null and rows != 0 order by 1'),
'type':
'histogram',
# 'colors': [GColors.BLUE],
},
{
'title':
'Dataset Columns Histogram',
'data':
utils.execute_query_as_json(
'select columns_count from finder_database where columns_count is not null order by 1'
),
'type':
'histogram',
# 'colors': [GColors.BLUE],
},
{
'title':
'Geo Columns Histogram',
'data':
utils.execute_query_as_json(
'select columns_geo_count from finder_database where columns_geo_count is not null order by 1'
),
'type':
'histogram',
'colors': [str(SimpleType.objects.get(name='Geo').color)],
},
{
'title':
'Temporal Columns Histogram',
'data':
utils.execute_query_as_json(
'select columns_temporal_count from finder_database where columns_temporal_count is not null order by 1'
),
'type':
'histogram',
'colors': [str(SimpleType.objects.get(name='Temporal').color)],
},
{
'title':
'Numeric Columns Histogram',
'data':
utils.execute_query_as_json(
'select columns_numeric_count from finder_database where columns_numeric_count is not null order by 1'
),
'type':
'histogram',
'colors': [str(SimpleType.objects.get(name='Numeric').color)],
},
{
'title':
'Textual Columns Histogram',
'data':
utils.execute_query_as_json(
'select columns_text_count from finder_database where columns_text_count is not null order by 1'
),
'type':
'histogram',
'colors': [str(SimpleType.objects.get(name='Textual').color)],
},
# { 'title':'Geo Index',
# 'data': utils.execute_query_as_json("(select 'Address', count(*) from finder_gpsdata where address is not null) union \
# (select 'Borough', count(*) from finder_gpsdata where borough is not null) union \
# (select 'GPS', count(*) from finder_gpsdata where lat is not null) union \
# (select 'Zipcode', count(*) from finder_gpsdata where zipcode is not null) union \
# (select 'Total', count(*) from finder_gpsdata) ", sort=False),
# 'type': 'column',
# 'colors':GColors.PALLETE,
# },
]
data = utils.execute_query_as_json(
'select columns_null_count from finder_database where columns_null_count is not null order by 1'
)
# if data and len(data) > 0 and (type(data) == str and data != '[]'):
# context['chart_list'].append({ 'title':'Null Columns Histogram',
# 'data': data,
# 'type': 'histogram',
# 'colors': [str(SimpleType.objects.get(name='Null').color)],
# })
context['chart_profiler_status'] = {
'data': utils.count_as_json('profiler_status_short',
order_by_counts=False),
'colors': [GColors.RED, GColors.BLUE, GColors.GRAY]
}
context['errors'] = utils.execute_query(
'select profiler_status, count(*) from finder_database where profiler_status like \'Error:%\' group by profiler_status having count(*) > 1 order by 2 desc limit 6'
)
context['column_type_counts'] = {
'data':
utils.count_as_json('profiler_type',
from_model='column',
order_by_counts=False),
'colors':
TypeColors.simple_type_colors()
}
data = utils.count_value('profiler_type_most_detected',
from_model='column',
order_by_counts=False)
# print 'data= ', data
SQL_COUNT_DETAILED_TYPES_ON_COLUMNS = "\
select dt.name as type, \
(select count(*) \
from finder_column c \
where (dt.name = st.name and dt.name = c.profiler_type_most_detected) \
or (dt.name <> st.name and st.name || '-' || dt.name = c.profiler_type_most_detected) \
) as count \
from profiler_simpletype st \
join profiler_detailedtype dt on dt.simple_type_id = st.id \
order by st.global_order_presentation, dt.order_in_type_presentation;"
context['column_type_detailed_counts'] = {
"data":
utils.execute_query_as_json(SQL_COUNT_DETAILED_TYPES_ON_COLUMNS),
'colors': TypeColors.detailed_type_colors()
}
context['datasets_total'] = Database.objects.count()
context['datasets_total_size'] = Database.objects.filter(
profiler_status='OK').aggregate(
sum=Sum('profiler_input_file_size'))['sum']
context['datasets_total_size'] = round(
float(context['datasets_total_size']) / 1000000.0,
1) if type(context['datasets_total_size']) is float else 0 #Giga
context['datasets_success'] = Database.objects.filter(
profiler_status='OK').count()
context['datasets_success_percent'] = round(
context['datasets_success'] * 100.0 /
context['datasets_total'], 2) if context['datasets_total'] > 0 else 0
context['columns_total'] = Column.objects.count()
context['columns_names_distinct'] = Column.objects.values(
'name').distinct().count()
context['columns_names_distinct_percent'] = round(
context['columns_names_distinct'] * 100.0 /
context['columns_total'], 2) if context['columns_total'] > 0 else 0
context['records_total'] = Database.objects.filter(
profiler_status='OK').aggregate(sum=Sum('rows'))['sum']
context['values_total'] = Database.objects.filter(
profiler_status='OK').aggregate(sum=Sum('values'))['sum']
context['values_missing'] = Database.objects.filter(
profiler_status='OK').aggregate(sum=Sum('values_missing'))['sum']
context['values_missing_percent'] = round(
context['values_missing'] * 100.0 /
context['values_total'], 2) if context['values_total'] > 0 else 0
try:
context['system'] = System.objects.latest('update_time')
except (System.DoesNotExist) as e:
context[
'system'] = 'No system data. Make sure to import something first.'
# print 'System info not found.'
return render(request, 'finder/profiler_info.html', context)
if key in PAYROLL_LIST and value:
columns = columns + key + ', '
values = values + repr(value) + ', '
columns = columns[:-2] + ')'
values = values[:-2] + ')'
cgitb.enable()
print("Content-Type: text/html;charset=utf-8")
print("Content-type:text/html\r\n\r\n")
print('<html>')
print('<head>')
print('<title>INSERT PAYMENT INFO</title>')
print('</head>')
print('<body>')
print('employee: ' + str(entry['ssn']) + '<br />')
query = 'INSERT INTO payment ' + columns + ' VALUES ' + values + ';'
print(query + '<br />')
execute_query('mssql', 'hr_pii', query)
query = 'IF NOT EXISTS ( SELECT * FROM dbo.key_table WHERE ssn = %s )INSERT dbo.key_table (ssn) VALUES (%s)' % (
repr(entry['ssn']), repr(entry['ssn']))
execute_query('mssql', 'hr_pii', query)
print('</body>')
print('</html>')
import random
from utils import get_query, execute_query, fakesql
my_dbname = "customers_info"
my_table = 'customer'
with open('customers.json', 'r') as file:
string = file.read()
customer_list = json.loads(string)
entry = random.choice(customer_list)
ssn = entry['ssn']
query = fakesql('customer', ['ssn', 'job', 'company', 'blood_group', 'name', 'sex', 'mail', 'zipcode', 'city', 'state', 'phone', 'race', 'ethnic', 'religion', 'trade_union', 'health', 'sexual', 'politics', 'birthdate'], ["sex = 'M'", "sex = 'F'", "state = 'CA'", "state = 'WA'"])
#query = 'SELECT * FROM customer WHERE ssn = %s;' % repr(ssn)
cgitb.enable()
print("Content-Type: text/html;charset=utf-8")
print("Content-type:text/html\r\n\r\n")
print('<html>')
print('<head>')
print('<title>CUSTOMER INFORMATION CHECK</title>')
print('</head>')
print('<body>')
print('ssn: ' + ssn + '<br />')
print('Query results:<br />')
execute_query('mysql', 'customers_info', query)
print('</body>')
print('</html>')
def _data(self):
"""Calculate results."""
data = []
ltv = float(self.request['loan_amount']) / self.request['price'] * 100
minltv = maxltv = ltv
# lock times
locks = {
30: [0, 30],
45: [31, 45],
60: [46, 60],
}
minlock = maxlock = self.request['lock']
if self.request['lock'] in locks:
minlock = locks[self.request['lock']][0]
maxlock = locks[self.request['lock']][1]
qry_args = [
self.request['loan_amount'], self.request['loan_amount'],
self.request['minfico'], self.request['maxfico'], minltv, maxltv,
self.request['state'], self.request['loan_amount'],
self.request['loan_amount'], self.request['minfico'],
self.request['maxfico'], minltv, maxltv, self.request['state'],
minltv, maxltv, self.request['minfico'], self.request['maxfico'],
self.request['loan_amount'], self.request['loan_amount'],
self.request['state'], self.request['rate_structure'].upper(),
self.request['loan_term'], self.request['loan_type'].upper(),
minlock, maxlock
]
query = """
SELECT
r.Institution AS r_institution,
r.Lock AS r_lock,
r.BaseRate AS r_baserate,
r.TotalPoints AS r_totalpoints,
r.Planid AS r_planid,
COALESCE(adjr.adjvalueR,0) AS adjvaluer,
COALESCE(adjp.adjvalueP,0) AS adjvaluep
FROM
oah_rates r
INNER JOIN oah_limits l ON r.planid = l.planid
LEFT OUTER JOIN (
SELECT
planid,
sum(adjvalue) adjvalueR
FROM oah_adjustments
WHERE 1=1
AND (
(MINLOANAMT <= ? AND MAXLOANAMT >= ? AND MINLOANAMT <> 0 AND MAXLOANAMT <> 999999999)
OR (MINFICO <= ? AND MAXFICO >= ? AND (MINFICO > 0 OR (MINFICO <> 0 AND MAXFICO <> 999)) AND MINLTV <= ? AND MAXLTV >= ?)
OR (STATE=?)
)
AND AffectRateType='R'
GROUP BY planid
) adjr ON adjr.PlanID = r.planid
LEFT OUTER JOIN (
SELECT
planid,
sum(adjvalue) adjvalueP
FROM oah_adjustments
WHERE 1=1
AND (
(MINLOANAMT <= ? AND MAXLOANAMT >= ? AND MINLOANAMT <> 0 AND MAXLOANAMT <> 999999999)
OR (MINFICO <= ? AND MAXFICO >= ? AND (MINFICO > 0 OR (MINFICO <> 0 AND MAXFICO <> 999)) AND MINLTV <= ? AND MAXLTV >= ?)
OR (STATE=?)
)
AND AffectRateType='P'
GROUP BY planid
) adjp ON adjp.PlanID = r.planid
WHERE 1=1
-- Limits stuff
AND (l.minltv <= ? AND l.maxltv >= ?)
AND (l.minfico <= ? AND l.maxfico >= ?)
AND (l.minloanamt <= ? AND l.maxloanamt >= ?)
AND (r.stateid=?)
AND r.loanpurpose='PURCH'
AND r.pmttype = ?
AND r.loanterm = ?
AND r.loantype = ?
AND r.lock BETWEEN ? AND ?
%s
ORDER BY r_Institution, r_BaseRate
"""
additional_query = ""
if self.request['rate_structure'].upper() == 'ARM':
additional_query = "AND r.io = 0 AND r.intadjterm = ? "
qry_args.append(
self.request['arm_type'][:self.request['arm_type'].index('/')])
rows = execute_query(query % additional_query, qry_args,
oursql.DictCursor)
self.data = self._calculate_results(rows)
import db_credentials
def main():
pass
if __name__ == "__main__":
MYDB = mysql.connector.connect(host=db_credentials.host,
user=db_credentials.user,
passwd=db_credentials.passwd,
database=db_credentials.database)
SQL = "SELECT id, name, fft_100_close FROM unlabelled_record a WHERE fft_100_close = (SELECT MIN(fft_100_close) FROM unlabelled_record b WHERE b.name = a.name)"
table_rows = utils.execute_query(MYDB, SQL)
min_index_list = []
min_index_list = utils.exctract_label(table_rows, min_index_list)
SQL = "SELECT id, name, fft_100_close FROM unlabelled_record a WHERE fft_100_close = (SELECT MAX(fft_100_close) FROM unlabelled_record b WHERE b.name = a.name)"
table_rows = utils.execute_query(MYDB, SQL)
max_index_list = []
max_index_list = utils.exctract_label(table_rows, max_index_list)
SQL = "SELECT * FROM unlabelled_record"
NAME_DICT = {
0: "index",
1: "date_of_day",
2: "hour",
3: "name",
4: "volume",
def _data(self):
"""Calculate results."""
data = []
ltv = float(self.request['loan_amount']) / self.request['price'] * 100
minltv = maxltv = ltv
# lock times
locks = {
30: [0, 30],
45: [31, 45],
60: [46, 60],
}
minlock = maxlock = self.request['lock']
if self.request['lock'] in locks:
minlock = locks[self.request['lock']][0]
maxlock = locks[self.request['lock']][1]
qry_args = [
self.request['loan_amount'], self.request['loan_amount'],
self.request['minfico'], self.request['maxfico'], minltv, maxltv,
self.request['state'], self.request['loan_amount'],
self.request['loan_amount'], self.request['minfico'],
self.request['maxfico'], minltv, maxltv, self.request['state'],
minltv, maxltv, self.request['minfico'], self.request['maxfico'],
self.request['loan_amount'], self.request['loan_amount'],
self.request['state'], self.request['rate_structure'].upper(),
self.request['loan_term'], self.request['loan_type'].upper(),
minlock, maxlock]
query = """
SELECT
r.Institution AS r_institution,
r.Lock AS r_lock,
r.BaseRate AS r_baserate,
r.TotalPoints AS r_totalpoints,
r.Planid AS r_planid,
COALESCE(adjr.adjvalueR,0) AS adjvaluer,
COALESCE(adjp.adjvalueP,0) AS adjvaluep
FROM
oah_rates r
INNER JOIN oah_limits l ON r.planid = l.planid
LEFT OUTER JOIN (
SELECT
planid,
sum(adjvalue) adjvalueR
FROM oah_adjustments
WHERE 1=1
AND (
(MINLOANAMT <= ? AND MAXLOANAMT >= ? AND MINLOANAMT <> 0 AND MAXLOANAMT <> 999999999)
OR (MINFICO <= ? AND MAXFICO >= ? AND (MINFICO > 0 OR (MINFICO <> 0 AND MAXFICO <> 999)) AND MINLTV <= ? AND MAXLTV >= ?)
OR (STATE=?)
)
AND AffectRateType='R'
GROUP BY planid
) adjr ON adjr.PlanID = r.planid
LEFT OUTER JOIN (
SELECT
planid,
sum(adjvalue) adjvalueP
FROM oah_adjustments
WHERE 1=1
AND (
(MINLOANAMT <= ? AND MAXLOANAMT >= ? AND MINLOANAMT <> 0 AND MAXLOANAMT <> 999999999)
OR (MINFICO <= ? AND MAXFICO >= ? AND (MINFICO > 0 OR (MINFICO <> 0 AND MAXFICO <> 999)) AND MINLTV <= ? AND MAXLTV >= ?)
OR (STATE=?)
)
AND AffectRateType='P'
GROUP BY planid
) adjp ON adjp.PlanID = r.planid
WHERE 1=1
-- Limits stuff
AND (l.minltv <= ? AND l.maxltv >= ?)
AND (l.minfico <= ? AND l.maxfico >= ?)
AND (l.minloanamt <= ? AND l.maxloanamt >= ?)
AND (r.stateid=?)
AND r.loanpurpose='PURCH'
AND r.pmttype = ?
AND r.loanterm = ?
AND r.loantype = ?
AND r.lock BETWEEN ? AND ?
%s
ORDER BY r_Institution, r_BaseRate
"""
additional_query = ""
if self.request['rate_structure'].upper() == 'ARM':
additional_query = "AND r.io = 0 AND r.intadjterm = ? "
qry_args.append(
self.request['arm_type'][:self.request['arm_type'].index('/')])
rows = execute_query(
query % additional_query, qry_args, oursql.DictCursor)
self.data = self._calculate_results(rows)
for key, value in entry.items():
if key in SALES_LIST and value:
columns = columns + key + ', '
values = values + repr(value) + ', '
columns = columns[:-2] + ')'
values = values[:-2] + ')'
cgitb.enable()
print("Content-Type: text/html;charset=utf-8")
print("Content-type:text/html\r\n\r\n")
print('<html>')
print('<head>')
print('<title>INSERT SALES INFO</title>')
print('</head>')
print('<body>')
print('salesperson: ' + str(entry['ssn']) + '<br />')
query = 'INSERT INTO sales ' + columns + ' VALUES ' + values + ';'
print(query + '<br />')
execute_query('mysql', 'sales_info', query)
query = 'INSERT INTO key_table VALUES (' + repr(
entry['ssn']) + ') ON DUPLICATE KEY UPDATE ssn=ssn'
execute_query('mysql', 'sales_info', query)
print('</body>')
print('</html>')
import json
import queries as qs
import utils as utl
if __name__ == "__main__":
with open('Postgres/data.json') as f:
data = json.load(f)
print('data: ', data)
# 2. ПОДКЛЮЧЕНИЕ К БАЗАМ ДАННЫХ
con = utl.create_connection(**data['defaul_db'])
utl.execute_query(con, qs.drop_database_query)
# Теперь внутри дефолтной БД postgres нужно создать базу данных hillel_postgres_lesson.
utl.execute_query(con, qs.create_database_query)
con = utl.create_connection(**data['custom_db'])
# 3. СОЗДАНИЕ ТАБЛИЦ
utl.execute_query(con, qs.create_users_table)
utl.execute_query(con, qs.create_posts_table)
utl.execute_query(con, qs.create_likes_table)
utl.execute_query(con, qs.create_comments_table)
# # 4. ВСТАВКА ЗАПИСЕЙ
# users = [tuple(user) for user in data['users']]
# user_records = ", ".join(["%s"] * len(users))
# cursor = con.cursor()
# cursor.execute(qs.insert_user_query(user_records), users)
import datetime import sqlalchemy import utils utils.execute_query( """ CREATE TABLE IF NOT EXISTS public.items ( id serial primary key, username text NOT NULL, data text NOT NULL, created_ts timestamp without time zone NOT NULL, updated_ts timestamp without time zone, deleted_ts timestamp without time zone ); """) def add_item(user, data): utils.execute_query( '''INSERT INTO public.items (username, data, created_ts) VALUES ( :username, :data, NOW() ) ''', username=user.username, data=data, ) return True
