def test_fetch_all_data(self):
with self.db as cur:
cur.execute(insert("person", {"person_id": "mosky", "name": "Mosky Liu"}))
cur.execute(select("person"))
results = all_to_dicts(cur)
self.db._conn.rollback()
def test_select_customize():
gen = select('person',
OrderedDict([
('name like', 'Mosky%'),
('age >', 20),
]))
exp = 'SELECT * FROM "person" WHERE "name" LIKE \'Mosky%\' AND "age" > 20'
eq_(gen, exp)
def index():
with db as cur:
cur.execute(select(
'person',
request.args or None,
joins = left_join('detail', using=('person_id', )),
))
return jsonify(data=list(cur))
def index():
cur = conn.cursor()
cur.execute(select(
'person',
request.args or None,
joins = left_join('detail', using=('person_id', )),
))
rows = cur.fetchall()
cur.close()
return jsonify(data=rows)
def test_fetch_all_data(self):
with self.db as cur:
cur.execute(insert('person', {
'person_id': 'mosky',
'name' : 'Mosky Liu'
}))
cur.execute(select('person'))
results = all_to_dicts(cur)
self.db._conn.rollback()
def test_select_param():
gen = select('table', OrderedDict([
('custom_param', param('my_param')), ('auto_param', autoparam),
('using_alias', ___),
]))
exp = (
'SELECT * FROM "table" WHERE "custom_param" = %(my_param)s '
'AND "auto_param" = %(auto_param)s AND "using_alias" = %(using_alias)s'
)
eq_(gen, exp)
def _construct_select_query(**filter_definition):
"""Return SELECT statement that will be used as a filter.
:param filter_definition: definition of a filter that should be used for SELECT construction
:return:
"""
table_name = filter_definition.pop('table')
distinct = filter_definition.pop('distinct', False)
select_count = filter_definition.pop('count', False)
if distinct and select_count:
raise UnsupportedDefinitionError(
'SELECT (DISTINCT ...) is not supported')
if select_count and 'select' in filter_definition:
raise UnsupportedDefinitionError(
'SELECT COUNT(columns) is not supported')
if 'joins' in filter_definition:
join_definitions = filter_definition.pop('joins')
if not isinstance(join_definitions, (tuple, list)):
join_definitions = (join_definitions, )
filter_definition['joins'] = []
for join_def in join_definitions:
filter_definition['joins'].append(_expand_join(join_def))
if 'where' in filter_definition:
for key, value in filter_definition['where'].items():
if is_filter_query(value):
# We can do it recursively here
sub_query = value.pop(DEFAULT_FILTER_KEY)
if value:
raise ParsingInputError(
"Unknown keys for sub-query provided: %s" % value)
filter_definition['where'][key] = mosql_raw('( {} )'.format(
_construct_select_query(**sub_query)))
elif isinstance(value, str) and value.startswith(
'$') and QUERY_REFERENCE.fullmatch(value[1:]):
# Make sure we construct correct query with escaped table name and escaped column for sub-queries
filter_definition['where'][key] = mosql_raw('"{}"'.format(
'"."'.join(value[1:].split('.'))))
raw_select = select(table_name, **filter_definition)
if distinct:
# Note that we want to limit replace to the current SELECT, not affect nested ones
raw_select = raw_select.replace('SELECT', 'SELECT DISTINCT', 1)
if select_count:
# Note that we want to limit replace to the current SELECT, not affect nested ones
raw_select = raw_select.replace('SELECT *', 'SELECT COUNT(*)', 1)
return raw_select
def test_param_query(self):
with self.db as cur:
cur.execute(
select(
'person',
{'person_id': param('person_id')}
),
{'person_id': 'mosky'}
)
self.db._conn.rollback()
def test_fetch_all_data(self):
with self.db as cur:
cur.execute(
insert('person', {
'person_id': 'mosky',
'name': 'Mosky Liu'
}))
cur.execute(select('person'))
results = all_to_dicts(cur)
self.db._conn.rollback()
def construct_select_query(self, filter_definition):
""" Return SELECT statement that will be used as a filter
:param filter_definition: definition of a filter that should be used
for SELECT construction
:return:
"""
table_name = filter_definition.pop('table', self._DEFAULT_FILTER_TABLE_NAME)
distinct = filter_definition.pop('distinct', False)
select_count = filter_definition.pop('count', False)
if distinct and select_count:
raise ValueError('SELECT (DISTINCT ...) is not supported')
if select_count and 'select' in filter_definition:
raise ValueError('SELECT COUNT(columns) is not supported')
if 'joins' in filter_definition:
join_definitions = filter_definition.pop('joins')
if type(join_definitions) not in (tuple, list):
join_definitions = (join_definitions,)
filter_definition['joins'] = []
for join_def in join_definitions:
filter_definition['joins'].append(self._expand_join(join_def))
if 'where' in filter_definition:
for key, value in filter_definition['where'].items():
if self.is_filter_query(value):
# We can do it recursively here
sub_query = value.pop(self.DEFAULT_FILTER_KEY)
if value:
self.log.warning("Ignoring sub-query parameters: %s", value)
filter_definition['where'][key] = mosql_raw('( {} )'.format(
self.construct_select_query(sub_query)))
elif (isinstance(value, str) and value.startswith('$') and
self.QUERY_REFERENCE.fullmatch(value[1:])):
# Make sure we construct correct query with escaped table
# name and escaped column for sub-queries
filter_definition['where'][key] = mosql_raw('"{}"'.format(
'"."'.join(value[1:].split('.'))))
raw_select = select(table_name, **filter_definition)
if distinct:
# Note that we want to limit replace to the current SELECT, not affect nested ones
raw_select = raw_select.replace('SELECT', 'SELECT DISTINCT', 1)
if select_count:
# Note that we want to limit replace to the current SELECT, not affect nested ones
raw_select = raw_select.replace('SELECT *', 'SELECT COUNT(*)', 1)
return raw_select
def test_select_param():
gen = select(
'table',
OrderedDict([
('custom_param', param('my_param')),
('auto_param', autoparam),
('using_alias', ___),
]))
exp = (
'SELECT * FROM "table" WHERE "custom_param" = %(my_param)s '
'AND "auto_param" = %(auto_param)s AND "using_alias" = %(using_alias)s'
)
eq_(gen, exp)
def _get_select_query(self, fields=None):
"""The raw SQL that will be used to resolve the queryset."""
handler = get_engine_handler(self.db)
with handler.patch():
params = copy.deepcopy(self._params)
if params['joins']:
params['joins'] = [
join(table=(j.pop('table'),), **j)
for j in params['joins']
]
table = self.model._meta.db_table
alias = params.pop('alias', None)
kwargs = {k: v for k, v in params.items() if v}
# Inject default field names.
# If this query does not contain a GROUP BY clause, we can safely
# use a "*" to indicate all fields;
# If the query has aggregation (GROUP BY), however, we will need to
# choose a value to display for each field (especially pk because
# it is needed by Django). Since accessing those fields doesn't
# really make sense anyway, We arbitrarily use MIN.
table_name = alias or table
if fields is not None:
kwargs['select'] = [
f if '.' in f or isinstance(f, raw)
else raw('{table}.{field}'.format(
table=identifier(table_name), field=identifier(f))
) for f in fields
]
elif self._params['group_by']:
kwargs['select'] = (
handler.get_aggregated_columns_for_group_by(self, 'MIN')
)
else:
kwargs['select'] = handler.get_star(self)
kwargs['select'].extend(self.extra_fields)
if 'offset' in kwargs and 'limit' not in kwargs:
kwargs['limit'] = handler.no_limit_value()
if alias:
table = ((table, alias),)
query = select(table, **kwargs)
return query
def get_user():
"""Retreives a single user from a Database, and render it using a HTML template"""
try:
data = json.loads(request.data)
except ValueError:
return '', 400
else:
email = data['customer']['email']
# For more complex queries, consider moving queries to a separate module
with db as cur:
cur.execute(select('auth_user', {'email': email}, select=('username')))
try:
user = one_to_dict(cur)
except TypeError:
return '', 404
template = app.jinja_env.get_template('api/helpscout.html')
return jsonify({'html': template.render(user)})
def test_select_operationerror():
with assert_raises(OperatorError) as cxt:
select('person', {"person_id = '' OR true; --": 'mosky'})
exp = "this operator is not allowed: \"= '' OR TRUE; --\""
eq_(str(cxt.exception), exp)
def test_select_customize_operator():
gen = select('person', OrderedDict([
(('name', 'like'), 'Mosky%'), (('age', '>'), 20)
]))
exp = 'SELECT * FROM "person" WHERE "name" LIKE \'Mosky%\' AND "age" > 20'
eq_(gen, exp)
def test_select_customize():
gen = select('person', OrderedDict([
('name like', 'Mosky%'), ('age >', 20),
]))
exp = 'SELECT * FROM "person" WHERE "name" LIKE \'Mosky%\' AND "age" > 20'
eq_(gen, exp)
def test_select(self):
with self.db as cur:
cur.execute(select('person', {'person_id': 'mosky'}))
self.db._conn.rollback()
def test_select(self):
with self.db as cur:
cur.execute(select('person', {'person_id': 'mosky'}))
self.db._conn.rollback()
def test_param_query(self):
with self.db as cur:
cur.execute(select('person', {'person_id': param('person_id')}),
{'person_id': 'mosky'})
self.db._conn.rollback()
def test_select_operationerror():
with assert_raises(OperatorError) as cxt:
select('person', {"person_id = '' OR true; --": 'mosky'})
exp = "this operator is not allowed: \"= '' OR TRUE; --\""
eq_(str(cxt.exception), exp)
def test_select_customize_operator():
gen = select(
'person',
OrderedDict([(('name', 'like'), 'Mosky%'), (('age', '>'), 20)]))
exp = 'SELECT * FROM "person" WHERE "name" LIKE \'Mosky%\' AND "age" > 20'
eq_(gen, exp)
def test_param_query(self):
with self.db as cur:
cur.execute(select("person", {"person_id": param("person_id")}), {"person_id": "mosky"})
self.db._conn.rollback()
def test_select(self):
with self.db as cur:
cur.execute(select("person", {"person_id": "mosky"}))
self.db._conn.rollback()
from mosql.util import raw
from mosql.query import select, left_join
from mosql.db import Database, group
db = Database(psycopg2, host='127.0.0.1')
with db as cur:
## Use PostgreSQL to group:
cur.execute(
select(
'person',
joins=left_join('detail', using='person_id'),
where={'key': 'email'},
group_by='person_id',
select=('person_id', raw('array_agg(val)')),
# It is optional here.
order_by='person_id',
))
print 'Group the rows in PostgreSQL:'
for row in cur:
print row
print
## Use MoSQL (app-level) to group:
cur.execute(
select(
'person',
import psycopg2
from mosql.util import raw
from mosql.query import select, left_join
from mosql.db import Database, group
db = Database(psycopg2, host='127.0.0.1')
with db as cur:
## Use PostgreSQL to group:
cur.execute(select(
'person',
joins = left_join('detail', using='person_id'),
where = {'key': 'email'},
group_by = 'person_id',
select = ('person_id', raw('array_agg(val)')),
# It is optional here.
order_by = 'person_id',
))
print 'Group the rows in PostgreSQL:'
for row in cur:
print row
print
## Use MoSQL (app-level) to group:
cur.execute(select(
'person',
joins = left_join('detail', using='person_id'),
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import os
import psycopg2
from pprint import pprint
from mosql.query import select, left_join
conn = psycopg2.connect(host='127.0.0.1', database=os.environ['USER'])
cur = conn.cursor()
cur.execute(select(
'person',
{'person_id': 'mosky'},
joins = left_join('detail', using='person_id'),
# You can also use tuple to add multiple join statements.
#joins = (left_join('detail', using='person_id'), )
))
pprint(cur.fetchall())
cur.close()
#conn.commit() # Actually we don't want to commit here.
conn.close()
def test_select_directionerror():
with assert_raises(DirectionError) as cxt:
select('person', {'name like': 'Mosky%'},
order_by=('age ; DROP person; --', ))
exp = "this direction is not allowed: '; DROP PERSON; --'"
eq_(str(cxt.exception), exp)
def execute_select():
cur.execute(select("_benchmark", {"id": "mosky.liu"}))
return cur.fetchall()
def test_select_directionerror():
with assert_raises(DirectionError) as cxt:
select('person', {'name like': 'Mosky%'},
order_by=('age ; DROP person; --', ))
exp = "this direction is not allowed: '; DROP PERSON; --'"
eq_(str(cxt.exception), exp)
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import psycopg2
from pprint import pprint
from mosql.query import select, left_join
from mosql.db import Database, all_to_dicts
db = Database(psycopg2, host='127.0.0.1')
with db as cur:
cur.execute(select(
'person',
{'person_id': 'mosky'},
# It is same as using keyword argument:
#where = {'person_id': 'mosky'},
joins = left_join('detail', using='person_id'),
# You can also use tuple to add multiple join statements:
#joins = (left_join('detail', using='person_id'), )
))
pprint(all_to_dicts(cur))
def execute_select():
cur.execute(select('_benchmark', {'id': 'mosky.liu'}))
return cur.fetchall()
