def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
self.sqlite_folder = os.path.join(self.sql_root, 'data')
self.sqlite_path = os.path.join(self.sqlite_folder, 'test_db.sqlite')
if not os.path.exists(self.sqlite_folder):
os.makedirs(self.sqlite_folder)
if not os.path.exists(self.sqlite_path):
open(self.sqlite_path, 'a').close()
def __init__(self, cfg, bdb, api_def, fips, logger):
self.bdb = bdb
self.cfg = cfg
self.api_def = api_def
self.logger = logger
self.fips = fips
root_location = os.path.abspath(os.path.dirname(__file__))
snaql_factory = Snaql(root_location, 'queries')
self.queries = snaql_factory.load_queries('queries.sql')
def query_raster_by_training_regions(r_tablename, v_tablename):
"""
:param r_tablename:
:param v_tablename:
:return: dict of min/max BGR pixel values for training regions
"""
results = {}
band_lookup = {1: "R", 2: "G", 3: "B"}
snaql_factory = Snaql(".", "sql")
queries = snaql_factory.load_queries("adrc_queries.sql")
sample_regions = get_sample_regions(v_tablename, r_tablename)
for sr in sample_regions:
sample_region = sr[0]
results[sample_region] = {
"src_raster": r_tablename,
"ftype": sr[1],
"min": {
"B": None,
"G": None,
"R": None
},
"max": {
"B": None,
"G": None,
"R": None
}
}
for band in (1, 2, 3):
sql = queries.get_zonal_statistics(band_id=band,
vector_name=v_tablename,
raster_name=r_tablename,
sample_region_id=sample_region)
rs = u.query_pg(db_connection_string, sql)
if len(rs) > 0:
for r in rs:
min_value = r[1]
max_value = r[2]
results[sample_region]["min"][
band_lookup[band]] = min_value
results[sample_region]["max"][
band_lookup[band]] = max_value
return results
def find_fips_cols(cfg, bdb, fips_file):
root_location = os.path.abspath(os.path.dirname(__file__))
snaql_factory = Snaql(root_location, 'resources/queries')
queries = snaql_factory.load_queries('queries.sql')
query = queries.get_full_table(table_name='"{}"'.format(cfg.table_name))
cursor = bdb.execute(query)
sets = fips_sets(read_fips(fips_file))
col_name_list = [tuple[0] for tuple in cursor.description]
return fips_cols(sets, col_name_list, cursor)
class TestSchemaCases(unittest.TestCase):
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
def test_usual_case(self):
news_queries = self.snaql.load_queries('unsafe_news.sql')
now = datetime.datetime.utcnow()
schema = Schema({
'news_id': And(Use(int), lambda i: i > 0),
'rating': And(Use(float), lambda r: r > 0),
'date_from': And(Use(guard_date)),
})
context = {
'news_id': '123',
'date_from': now,
'rating': 4.22,
}
news_queries.select_by_id(schema=schema, **context)
def test_usual_string_case(self):
news_queries = self.snaql.load_queries('unsafe_news.sql')
now = datetime.datetime.utcnow()
schema = Schema({
'slug': And(Use(escape_string), Use(lambda s: "`%s_slug`" % s)),
})
context = {
'slug': 'cool',
}
result = news_queries.select_by_slug(schema=schema, **context)
self.assertEqual(result, 'SELECT * FROM news WHERE slug = `cool_slug`')
def test_wrong_case(self):
news_queries = self.snaql.load_queries('unsafe_news.sql')
now = datetime.datetime.utcnow()
schema = Schema({
'news_id': And(Use(int), lambda i: i > 0),
'rating': And(Use(float), lambda r: r > 0),
'date_from': And(Use(guard_date)),
})
context = {
'news_id': '0',
'date_from': now,
'rating': 4.22,
}
with self.assertRaises(SchemaError):
news_queries.select_by_id(schema=schema, **context)
class TestSchemaCases(unittest.TestCase):
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
def test_usual_case(self):
news_queries = self.snaql.load_queries('unsafe_news.sql')
now = datetime.datetime.utcnow()
schema = Schema({
'news_id': And(Use(int), lambda i: i > 0),
'rating': And(Use(float), lambda r: r > 0),
'date_from': And(Use(guard_date)),
})
context = {
'news_id': '123',
'date_from': now,
'rating': 4.22,
}
news_queries.select_by_id(schema=schema, **context)
def test_usual_string_case(self):
news_queries = self.snaql.load_queries('unsafe_news.sql')
now = datetime.datetime.utcnow()
schema = Schema({
'slug':
And(Use(escape_string), Use(lambda s: "`%s_slug`" % s)),
})
context = {
'slug': 'cool',
}
result = news_queries.select_by_slug(schema=schema, **context)
self.assertEqual(result, 'SELECT * FROM news WHERE slug = `cool_slug`')
def test_wrong_case(self):
news_queries = self.snaql.load_queries('unsafe_news.sql')
now = datetime.datetime.utcnow()
schema = Schema({
'news_id': And(Use(int), lambda i: i > 0),
'rating': And(Use(float), lambda r: r > 0),
'date_from': And(Use(guard_date)),
})
context = {
'news_id': '0',
'date_from': now,
'rating': 4.22,
}
with self.assertRaises(SchemaError):
news_queries.select_by_id(schema=schema, **context)
class SnaqlConn:
def __init__(self):
self.root_location = os.path.abspath(os.path.dirname(__file__))
self.snaql_factory = Snaql(self.root_location, 'queries')
def get_queries(self, file_name):
return self.snaql_factory.load_queries(file_name)
def query_raster_by_training_regions(r_tablename, v_tablename):
"""
:param r_tablename:
:param v_tablename:
:return: dict of min/max BGR pixel values for training regions
"""
results = {}
band_lookup = {1:"R", 2:"G", 3:"B"}
snaql_factory = Snaql(".", "sql")
queries = snaql_factory.load_queries("adrc_queries.sql")
sample_regions = get_sample_regions(v_tablename, r_tablename)
for sr in sample_regions:
sample_region = sr[0]
results[sample_region] = {
"src_raster":r_tablename,
"ftype":sr[1],
"min":{"B":None, "G":None, "R":None},
"max":{"B":None, "G":None, "R":None}
}
for band in (1,2,3):
sql = queries.get_zonal_statistics(
band_id=band,
vector_name=v_tablename,
raster_name=r_tablename,
sample_region_id=sample_region
)
rs = u.query_pg(db_connection_string, sql)
if len(rs) > 0:
for r in rs:
min_value = r[1]
max_value = r[2]
results[sample_region]["min"][band_lookup[band]] = min_value
results[sample_region]["max"][band_lookup[band]] = max_value
return results
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
self.sqlite_folder = os.path.join(self.sql_root, 'data')
self.sqlite_path = os.path.join(self.sqlite_folder, 'test_db.sqlite')
if not os.path.exists(self.sqlite_folder):
os.makedirs(self.sqlite_folder)
if not os.path.exists(self.sqlite_path):
open(self.sqlite_path, 'a').close()
def __init__(self, table_name, db_connection_string, refresh=True,
search_distance=2.0, search_angle_delta=5.0):
self.table_name = table_name
self.db_connection_string = db_connection_string
if refresh:
# by default, we clear any groups assigned to lines
self.reset_table()
# parameters that control group membership
self.search_distance = search_distance
self.search_angle_delta = search_angle_delta
# list of ungrouped line ids that will shrink over time
self.ungrouped_line_ids = self.get_line_ids()
# id to mark group membership
self.group_id = 1
snaql_factory = Snaql(".", "sql")
self.queries = snaql_factory.load_queries("adrc_queries.sql")
def get_sample_regions(v_tablename, r_tablename):
"""
:param v_tablename:
:param r_tablename:
:return: gid/ftype from the training_regions Pg table so we can go through them
"""
sample_regions = None
snaql_factory = Snaql(".", "sql")
queries = snaql_factory.load_queries("adrc_queries.sql")
if "myrasters.nls_" in r_tablename:
raster_src = r_tablename[len("myrasters.nls_"):]
sql = queries.get_training_regions(vector_name=v_tablename,
raster_src=raster_src)
rs = u.query_pg(db_connection_string, sql)
sample_regions = [[int(x[0]), x[1]] for x in rs]
return sample_regions
class TestMigrations(unittest.TestCase):
def setUp(self):
self.path = os.path.join(
os.path.abspath(os.path.dirname(__file__)),
'queries'
)
self.snaql = Snaql(self.path)
def test_guard_integer_exc(self):
migrate_queries = self.snaql.load_queries('migrations.sql')
call_order = [fn.func_name for fn in migrate_queries.ordered_blocks]
# All of these solutions are valid
possible_solutions = (
[
'create_templates',
'create_clusters',
'create_dummy',
'create_flavors',
'create_nodes',
],
[
'create_templates',
'create_dummy',
'create_flavors',
'create_nodes',
'create_clusters',
],
[
'create_templates',
'create_dummy',
'create_clusters',
'create_nodes',
'create_clusters',
],
[
'create_templates',
'create_dummy',
'create_clusters',
'create_flavors',
'create_nodes',
],
[
'create_templates',
'create_dummy',
'create_flavors',
'create_clusters',
'create_nodes',
],
)
self.assertTrue(call_order in possible_solutions)
def get_sample_regions(v_tablename, r_tablename):
"""
:param v_tablename:
:param r_tablename:
:return: gid/ftype from the training_regions Pg table so we can go through them
"""
sample_regions = None
snaql_factory = Snaql(".", "sql")
queries = snaql_factory.load_queries("adrc_queries.sql")
if "myrasters.nls_" in r_tablename:
raster_src = r_tablename[len("myrasters.nls_"):]
sql = queries.get_training_regions(
vector_name=v_tablename,
raster_src=raster_src
)
rs = u.query_pg(db_connection_string, sql)
sample_regions = [[int(x[0]), x[1]] for x in rs]
return sample_regions
class TestMigrations(unittest.TestCase):
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
def test_guard_integer_exc(self):
migrate_queries = self.snaql.load_queries('migrations.sql')
call_order = [fn.func_name for fn in migrate_queries.ordered_blocks]
# All of these solutions are valid
possible_solutions = (
[
'create_templates',
'create_clusters',
'create_dummy',
'create_flavors',
'create_nodes',
],
[
'create_templates',
'create_dummy',
'create_flavors',
'create_nodes',
'create_clusters',
],
[
'create_templates',
'create_dummy',
'create_clusters',
'create_nodes',
'create_clusters',
],
[
'create_templates',
'create_dummy',
'create_clusters',
'create_flavors',
'create_nodes',
],
[
'create_templates',
'create_dummy',
'create_flavors',
'create_clusters',
'create_nodes',
],
)
self.assertTrue(call_order in possible_solutions)
def load(self, query_parent, query_dir, query_file):
"""Load queries from template files to instance attributes.
Args:
query_parent (str): Query folder parent directory
query_dir (str): Query folder name
query_file (str): Query file name.
Returns:
The QueryManager instance.
Raises:
QueryManagerError, if any snaql Exception occurs.
"""
try:
self.builder = Snaql(query_parent,
query_dir).load_queries(query_file)
except (SnaqlException, TemplateNotFound) as error:
raise QueryManagerError(error.args)
self.mount()
return self
def main():
template = Snaql('','queries').load_queries('model.sql')
ioloop = tornado.ioloop.IOLoop.current()
dsn = "user=postgres password=postgres dbname=qa_bot host=localhost port=5432"
db = momoko.Pool(
dsn=dsn,
size=1,
max_size=3,
ioloop=ioloop,
setsession=("SET TIME ZONE UTC",),
raise_connect_errors=False,
)
future = db.connect()
ioloop.add_future(future, lambda x: ioloop.stop())
ioloop.start()
a = User(db,template)
a.chat_id = 135195422
future = a.selectThis()
ioloop.add_future(future, lambda x: ioloop.stop())
ioloop.start()
print(future.result())
a.task_list = [6,7,8]
a.changeTask()
future = a.updateThis(fields = ["task_list"])
ioloop.add_future(future, lambda x: ioloop.stop())
ioloop.start()
print(future.result())
future = a.selectThis()
ioloop.add_future(future, lambda x: ioloop.stop())
ioloop.start()
print(future.result())
class TestIntegration(unittest.TestCase):
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
self.sqlite_folder = os.path.join(self.sql_root, 'data')
self.sqlite_path = os.path.join(self.sqlite_folder, 'test_db.sqlite')
if not os.path.exists(self.sqlite_folder):
os.makedirs(self.sqlite_folder)
if not os.path.exists(self.sqlite_path):
open(self.sqlite_path, 'a').close()
def tearDown(self):
shutil.rmtree(self.sqlite_folder)
def test_req(self):
queries = self.snaql.load_queries('integration.sql')
conn = sqlite3.connect(self.sqlite_path)
conn.execute(queries.create_artists())
query = queries.insert_artist(
**{
'id': 1,
'name': 'Lana Del Rey',
'age': 30,
'instrument': 'voice',
'creation_date': '2015-10-13',
})
conn.execute(query)
conn.commit()
conn.close()
conn = sqlite3.connect(self.sqlite_path)
query = queries.get_artists(id=1)
response = conn.execute(query)
self.assertEqual(response.fetchone(), ('Lana Del Rey', 30, 'voice'))
conn.close()
class TestIntegration(unittest.TestCase):
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
self.sqlite_folder = os.path.join(self.sql_root, 'data')
self.sqlite_path = os.path.join(self.sqlite_folder, 'test_db.sqlite')
if not os.path.exists(self.sqlite_folder):
os.makedirs(self.sqlite_folder)
if not os.path.exists(self.sqlite_path):
open(self.sqlite_path, 'a').close()
def tearDown(self):
shutil.rmtree(self.sqlite_folder)
def test_req(self):
queries = self.snaql.load_queries('integration.sql')
conn = sqlite3.connect(self.sqlite_path)
conn.execute(queries.create_artists())
query = queries.insert_artist(**{
'id': 1,
'name': 'Lana Del Rey',
'age': 30,
'instrument': 'voice',
'creation_date': '2015-10-13',
})
conn.execute(query)
conn.commit()
conn.close()
conn = sqlite3.connect(self.sqlite_path)
query = queries.get_artists(id=1)
response = conn.execute(query)
self.assertEqual(response.fetchone(), ('Lana Del Rey', 30, 'voice'))
conn.close()
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
class TestUseCases(unittest.TestCase):
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
def test_usual_case(self):
users_queries = self.snaql.load_queries('users.sql')
self.assertEqual(users_queries.users_by_country(),
("SELECT count(*) AS count "
"FROM user "
"WHERE country_code = 'UA'"))
self.assertEqual(users_queries.select_all(), ("SELECT * " "FROM user"))
def test_subrendering(self):
users_queries = self.snaql.load_queries('users.sql')
self.assertEqual(users_queries.users_count_cond(),
("SELECT count(*) AS count "
"FROM user"))
context = {'by_country': True, 'country_code': 42}
self.assertEqual(users_queries.users_count_cond(**context),
("SELECT count(*) AS count "
"FROM user "
"WHERE country_code = 42"))
def test_complex_subrendering(self):
users_queries = self.snaql.load_queries('users.sql')
context = {'users_ids': [1, 2, 3]}
self.assertEqual(users_queries.users_select_cond(**context),
("SELECT * "
"FROM user "
"WHERE user_id IN (1, 2, 3)"))
def test_escaping(self):
users_queries = self.snaql.load_queries('users.sql')
context = {'user_name': "semirook"}
self.assertEqual(users_queries.users_escaping(**context),
("SELECT * FROM user "
"WHERE user_name = 'semirook'"))
def test_clean_env(self):
users_queries = self.snaql.load_queries('users.sql')
self.assertEqual(users_queries.select_all(), ("SELECT * " "FROM user"))
self.assertFalse(self.snaql.jinja_env.sql_params)
def test_multiple_ns(self):
users_queries = self.snaql.load_queries('users.sql')
news_queries = self.snaql.load_queries('news.sql')
self.assertEqual(users_queries.select_all(), ("SELECT * " "FROM user"))
self.assertEqual(news_queries.select_all(), ("SELECT * " "FROM news"))
def test_guards(self):
news_queries = self.snaql.load_queries('news.sql')
news_queries.select_by_id(
news_id=10,
date_from=datetime.datetime.now(),
rating='5.6',
)
def test_simple_tmpl(self):
queries = self.snaql.load_queries('integration.sql')
result_list = queries.simple_tmpl(var=[1, 2, 3])
self.assertEqual(result_list, '[1, 2, 3]')
result_dict = queries.simple_tmpl(var={'a': 1, 'b': 2})
self.assertTrue(result_dict in ("{'a': 1, 'b': 2}",
"{'b': 2, 'a': 1}"))
class TestGuards(unittest.TestCase):
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
def test_guards_normal(self):
news_queries = self.snaql.load_queries('news.sql')
date = datetime.datetime.now()
self.assertEqual(
news_queries.select_by_id(
news_id=10,
date_from=date,
rating='5.6',
),
("SELECT *\n FROM news\n "
"WHERE id = 10\n "
"AND creation_date >= "
"'{0.year:04}-{0.month:02}-{0.day:02} "
"{0.hour:02}:{0.minute:02}:{0.second:02}.{0.microsecond:06}'\n "
"AND rating >= 5.6").format(date))
def test_guard_integer_exc(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(SnaqlGuardException,
news_queries.select_by_id,
news_id='mouse')
def test_guard_datetime_exc(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(SnaqlGuardException,
news_queries.select_by_id,
date_from='tomorrow')
def test_guard_case_exc(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(SnaqlGuardException,
news_queries.get_news,
sort_order='REV')
def test_guard_case(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertEqual(news_queries.get_news(sort_order='ASC'),
'SELECT *\n FROM news\n ORDER BY creation_date ASC')
def test_guard_regexp(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertEqual(news_queries.select_by_slug(slug='latest_news'),
"SELECT *\n FROM news\n WHERE slug = 'latest_news'")
def test_guard_exc(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(
SnaqlGuardException,
news_queries.select_by_slug,
slug='latest news',
)
def test_conditions_sugar_without_context(self):
news_queries = self.snaql.load_queries('news.sql')
response = news_queries.get_news(conditions=[
news_queries.cond_ids_in_news,
news_queries.cond_date_from_news,
news_queries.cond_date_to_news,
])
self.assertEqual(response, 'SELECT *\n FROM news')
def test_conditions_sugar_with_context(self):
news_queries = self.snaql.load_queries('news.sql')
today = datetime.date.today()
context = {
'ids': (1, 2, 3),
'date_from': today,
'sort_order': 'ASC',
}
response = news_queries.get_news(conditions=[
news_queries.cond_ids_in_news,
news_queries.cond_date_from_news,
news_queries.cond_date_to_news,
],
**context)
self.assertEqual(response,
("SELECT *\n FROM news\n WHERE id IN (1, 2, 3) "
"AND creation_date >= "
"'{0.year:04}-{0.month:02}-{0.day:02}' "
"ORDER BY creation_date ASC").format(today))
def test_conditions_sugar_wrong_context(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(SnaqlException,
news_queries.get_news,
conditions=[news_queries.cond_date_to_another_news])
def test_conditions_sugar_outside_context(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(
SnaqlException,
news_queries.cond_date_to_another_news,
)
class TestUseCases(unittest.TestCase):
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
def test_usual_case(self):
users_queries = self.snaql.load_queries('users.sql')
self.assertEqual(
users_queries.users_by_country(), (
"SELECT count(*) AS count "
"FROM user "
"WHERE country_code = ?"
)
)
self.assertEqual(
users_queries.select_all(), (
"SELECT * "
"FROM user"
)
)
def test_subrendering(self):
users_queries = self.snaql.load_queries('users.sql')
self.assertEqual(
users_queries.users_count_cond(), (
"SELECT count(*) AS count "
"FROM user"
)
)
context = {'by_country': True, 'country_code': 42}
self.assertEqual(
users_queries.users_count_cond(**context), (
"SELECT count(*) AS count "
"FROM user "
"WHERE country_code = 42"
)
)
def test_complex_subrendering(self):
users_queries = self.snaql.load_queries('users.sql')
context = {'users_ids': [1, 2, 3]}
self.assertEqual(
users_queries.users_select_cond(**context), (
"SELECT * "
"FROM user "
"WHERE user_id IN (1, 2, 3)"
)
)
def test_escaping(self):
users_queries = self.snaql.load_queries('users.sql')
context = {'user_name': "semirook"}
self.assertEqual(
users_queries.users_escaping(**context), (
"SELECT * FROM user "
"WHERE user_name = 'semirook'"
)
)
def test_clean_env(self):
users_queries = self.snaql.load_queries('users.sql')
self.assertEqual(
users_queries.select_all(), (
"SELECT * "
"FROM user"
)
)
self.assertFalse(self.snaql.jinja_env.sql_params)
def test_multiple_ns(self):
users_queries = self.snaql.load_queries('users.sql')
news_queries = self.snaql.load_queries('news.sql')
self.assertEqual(
users_queries.select_all(), (
"SELECT * "
"FROM user"
)
)
self.assertEqual(
news_queries.select_all(), (
"SELECT * "
"FROM news"
)
)
def test_guards(self):
news_queries = self.snaql.load_queries('news.sql')
news_queries.select_by_id(
news_id=10,
date_from=datetime.datetime.now(),
rating='5.6',
)
def test_simple_tmpl(self):
queries = self.snaql.load_queries('integration.sql')
result_list = queries.simple_tmpl(var=[1, 2, 3])
self.assertEqual(result_list, '[1, 2, 3]')
result_dict = queries.simple_tmpl(var={'a': 1, 'b': 2})
self.assertTrue(
result_dict in ("{'a': 1, 'b': 2}", "{'b': 2, 'a': 1}")
)
return result
def sort_group (f, seq):
return groupby(
sorted(seq, key = f), key = f
)
def head (seq):
return seq[0]
app = Flask(__name__)
CORS(app, resources={r"/*": {"origins": "*"}})
api = Api(app)
root_location = os.path.abspath(os.path.dirname(__file__))
snaql_factory = Snaql(root_location, 'queries')
users_queries = snaql_factory.load_queries('users.sql')
threads_queries = snaql_factory.load_queries('threads.sql')
own_messages = None
class Threads(Resource):
def get(self):
global own_messages
page = request.args.get('page', default = 0, type = int)
limit = request.args.get('count', default = 50, type = int)
order = request.args.get('order', default = 'classic')
co = create_connection()
cursor = co.cursor()
if own_messages is None:
cursor.execute(
def setUp(self):
self.path = os.path.join(
os.path.abspath(os.path.dirname(__file__)),
'queries'
)
self.snaql = Snaql(self.path)
def __init__(self):
self.root_location = os.path.abspath(os.path.dirname(__file__))
self.snaql_factory = Snaql(self.root_location, 'queries')
])
url = options.url % options.token + "setWebhook?url=%s" % options.myurl
files = {
'certificate': open('/usr/share/nginx/qa_bot/qa_bot_company.pem', 'rb')
}
set_hook = api.post(url, files=files)
if set_hook.status_code != 200:
logging.error("Cant set hook: %s. Quit", set_hook.text)
exit(1)
application.db = momoko.Pool(
dsn=options.dsn,
size=1,
max_size=3,
ioloop=ioloop,
setsession=("SET TIME ZONE UTC", ),
raise_connect_errors=False,
)
application._db_templates = Snaql('models/',
'queries').load_queries('model.sql')
future = application.db.connect()
ioloop.add_future(future, lambda f: ioloop.stop())
ioloop.start()
future.result()
application.listen(options.serverport)
ioloop.start()
import os
from snaql.factory import Snaql
from psycopg2.extras import DictCursor
snaql_factory = Snaql(os.path.dirname(__file__), "./")
queries = snaql_factory.load_queries('queries.sql')
def exec_query(conn, sql, params=None):
with conn.cursor(cursor_factory=DictCursor) as cur:
cur.execute(sql, params)
return cur.fetchall()
class TestUseCases(unittest.TestCase):
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
def test_usual_case(self):
users_queries = self.snaql.load_queries('users.sql')
self.assertEqual(
users_queries.users_by_country(), (
"SELECT count(*) AS count "
"FROM user "
"WHERE country_code = ?"
)
)
self.assertEqual(
users_queries.select_all(), (
"SELECT * "
"FROM user"
)
)
def test_subrendering(self):
users_queries = self.snaql.load_queries('users.sql')
self.assertEqual(
users_queries.users_count_cond(), (
"SELECT count(*) AS count "
"FROM user"
)
)
context = {'by_country': True, 'country_code': 42}
self.assertEqual(
users_queries.users_count_cond(**context), (
"SELECT count(*) AS count "
"FROM user "
"WHERE country_code = 42 "
)
)
def test_complex_subrendering(self):
users_queries = self.snaql.load_queries('users.sql')
context = {'users_ids': [1, 2, 3]}
self.assertEqual(
users_queries.users_select_cond(**context), (
"SELECT * "
"FROM user "
"WHERE user_id IN (1, 2, 3) "
)
)
def test_escaping(self):
users_queries = self.snaql.load_queries('users.sql')
context = {'user_name': "'semirook';"}
self.assertEqual(
users_queries.users_escaping(**context), (
"SELECT * FROM user "
"WHERE user_name = \\'semirook\\'; "
)
)
def test_clean_env(self):
users_queries = self.snaql.load_queries('users.sql')
self.assertEqual(
users_queries.select_all(), (
"SELECT * "
"FROM user"
)
)
self.assertFalse(self.snaql.jinja_env.sql_params)
def test_multiple_ns(self):
users_queries = self.snaql.load_queries('users.sql')
news_queries = self.snaql.load_queries('news.sql')
self.assertEqual(
users_queries.select_all(), (
"SELECT * "
"FROM user"
)
)
self.assertEqual(
news_queries.select_all(), (
"SELECT * "
"FROM news"
)
)
import os
import json
from flask import Flask, jsonify
from flask import render_template, url_for
from . import db
import psycopg2.extras
from snaql.factory import Snaql
root_location = os.path.abspath(os.path.dirname(__file__))
snaql_factory = Snaql(root_location, 'queries')
limits_queries = snaql_factory.load_queries('limits.sql')
#creating the app
app = Flask(__name__)
db.init_app(app)
#Comands to execute the app for windows
#venv/Scripts/activate
# $env:FLASK_ENV = "development"
#set FLASK_APP= app.py
#flask run
#################### NAVEGABLE PAGES ###########################
@app.route('/')
def hello_world():
return render_template('index.html')
@app.route('/simple-map')
import re
from termcolor import colored
# from langdetect import detect
# from langdetect import detect_langs
import api
# db
from snaql.factory import Snaql
import os
# корень проекта
root_location = os.path.abspath(os.path.dirname(__file__))
# регистрация директории с шаблонами
snaql_factory = Snaql(root_location, 'sql')
# регистрация шаблона с SQL-блоками
queries = snaql_factory.load_queries('queries.sql')
connection = sqlite3.connect(root_location + '/sql/storage.db')
cursor = connection.cursor()
def main(data, source):
service = getattr(api, source)
print(service.name)
table = data['fromLang'] + '_' + data['toLang'] + '_' + source;
sql = queries.check(table=table, fromText=data['text'])
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
class TestGuards(unittest.TestCase):
def setUp(self):
self.sql_root = os.path.abspath(os.path.dirname(__file__))
self.snaql = Snaql(self.sql_root, 'queries')
def test_guards_normal(self):
news_queries = self.snaql.load_queries('news.sql')
date = datetime.datetime.now()
self.assertEqual(
news_queries.select_by_id(
news_id=10,
date_from=date,
rating='5.6',
), (
"SELECT * FROM news "
"WHERE id = 10 "
"AND creation_date >= "
"'{0.year:04}-{0.month:02}-{0.day:02} "
"{0.hour:02}:{0.minute:02}:{0.second:02}.{0.microsecond:06}' "
"AND rating >= 5.6"
).format(date)
)
def test_guard_integer_exc(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(
SnaqlGuardException,
news_queries.select_by_id,
news_id='mouse'
)
def test_guard_datetime_exc(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(
SnaqlGuardException,
news_queries.select_by_id,
date_from='tomorrow'
)
def test_guard_datetime_exc(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(
SnaqlGuardException,
news_queries.select_by_id,
date_from='mouse'
)
def test_guard_case_exc(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(
SnaqlGuardException,
news_queries.get_news,
sort_order='REV'
)
def test_guard_case(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertEqual(
news_queries.get_news(sort_order='ASC'),
'SELECT * FROM news ORDER BY creation_date ASC'
)
def test_guard_regexp(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertEqual(
news_queries.select_by_slug(slug='latest_news'),
"SELECT * FROM news WHERE slug = 'latest_news'"
)
def test_guard_exc(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(
SnaqlGuardException,
news_queries.select_by_slug,
slug='latest news',
)
def test_conditions_sugar_without_context(self):
news_queries = self.snaql.load_queries('news.sql')
response = news_queries.get_news(conditions=[
news_queries.cond_ids_in_news,
news_queries.cond_date_from_news,
news_queries.cond_date_to_news,
])
self.assertEqual(response, 'SELECT * FROM news')
def test_conditions_sugar_with_context(self):
news_queries = self.snaql.load_queries('news.sql')
today = datetime.date.today()
context = {
'ids': (1, 2, 3),
'date_from': today,
'sort_order': 'ASC',
}
response = news_queries.get_news(conditions=[
news_queries.cond_ids_in_news,
news_queries.cond_date_from_news,
news_queries.cond_date_to_news,
], **context)
self.assertEqual(
response, (
"SELECT * FROM news WHERE id IN (1, 2, 3) "
"AND creation_date >= "
"'{0.year:04}-{0.month:02}-{0.day:02}' "
"ORDER BY creation_date ASC"
).format(today)
)
def test_conditions_sugar_wrong_context(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(
SnaqlException,
news_queries.get_news,
conditions=[news_queries.cond_date_to_another_news]
)
def test_conditions_sugar_outside_context(self):
news_queries = self.snaql.load_queries('news.sql')
self.assertRaises(
SnaqlException,
news_queries.cond_date_to_another_news,
)
