def test_token_with_filter_value(self):
token = Token(template='WHERE {}', value_type=FilterValue)
token.value = {'a': 1, 'b__gt': 2, 'c__lt': 3, 'd__gte': 4,
'e__lte': 5, 'f__eq': 'test_eq', 'g__neq': 'test_neq',
'h__in': ['p1', 2], 'i__is': True, 'j__is_not': None}
self.assertIsInstance(token.value.value, And)
sql_template, values = token.eval()
expected_parts = (
"WHERE",
"AND",
"a = %s",
"b > %s",
"c < %s",
"d >= %s",
"e <= %s",
"f = %s",
"g != %s",
"h IN %s",
"i IS %s",
"j IS NOT %s",
)
expected_values = (
1, 2, 3, 4, 5, 'test_eq', 'test_neq', ['p1', 2], True, None)
for part in expected_parts:
self.assertIn(part, sql_template,
'%s is not found in\n%s' % (part, sql_template))
for val in expected_values:
self.assertIn(val, values,
'%s is not found in\n%s' % (val, expected_values))
def test_subtoken_eval(self):
t = Token(template='FROM {}', value_type=StringValue,
subtoken=Token(template='({})', value_type=TupleValue))
t.value = 'my_fn'
t.subtoken.value = ('test', 2, True, )
result = t.eval()
self.assertEqual(result, ('FROM my_fn(%s, %s, %s)', ('test', 2, True)))
def test_token_with_iterable_value(self):
token = Token(template='({})', value_type=CommaValue)
with self.assertRaises(ValueError) as err:
token.value = 'a'
self.assertIn('must be list or tuple', str(err))
token.value = ['a', 'b', 'c']
self.assertEqual(token.eval(), "(a, b, c)")
def test_token_with_tuple_value_type(self):
token = Token(template='VALUES ({})', value_type=TupleValue)
with self.assertRaises(ValueError) as err:
token.value = 'a'
self.assertIn('must be list or tuple', str(err))
token.value = ('a', 3)
self.assertEqual(token.eval(), ("VALUES (%s, %s)", ('a', 3)),)
def test_token_with_dict_value_and_join_types(self):
token = Token(template='{join_type} {table_name}',
value_type=DictValue)
# Check correctness of all join types
for join_type in ('INNER', 'CROSS', 'LEFT_OUTER', 'RIGHT_OUTER',
'FULL_OUTER'):
token.value = dict(join_type=getattr(JOIN, join_type),
table_name='MyTable')
self.assertIsInstance(token.value, DictValue, token.value)
self.assertEqual(
token.eval(), "{} MyTable".format(getattr(JOIN, join_type)))
def test_subtoken_eval_with_multiple_tuple_values(self):
"""Check correctness of composing tuple str and tuple values
This is not a real case
"""
t = Token(template='({})', value_type=TupleValue,
subtoken=Token(template='({})', value_type=TupleValue))
t.value = ('val', 1, False, )
t.subtoken.value = ('subtoken_val', 2, True, )
result = t.eval()
expected = ('(%s, %s, %s)(%s, %s, %s)',
('val', 1, False, 'subtoken_val', 2, True,))
self.assertEqual(result, expected)
def test_token_required(self):
token = Token(template='INSERT INTO {}', value_type=StringValue,
required=True)
with self.assertRaises(TokenError) as err:
token.eval()
self.assertIn('is not set and required', str(err))
token.value = 'reports'
val = token.eval()
self.assertIsInstance(val, str)
class UpdateQuery(QueryBuilder):
TOKENS = OrderedDict([
('UPDATE',
Token(template='UPDATE {}', value_type=StringValue, required=True)),
('SET',
Token(template='SET {}', value_type=CommaDictValue, required=True)),
('FROM', Token(template='FROM {}', value_type=StringValue)),
('WHERE', Token(template='WHERE {}', value_type=FilterValue)),
])
# NOTE: this is a full copy from SelectQuery
def filter(self, *args, **kwargs):
token = self._get_token('WHERE')
new_value = None
if args:
# In case of QueryOperators
new_value = args[0]
elif kwargs:
# In case of simple key-value filters
new_value = kwargs
# Stub to prevent errors
if new_value is None:
return self
if token.value:
token.value.update(new_value)
else:
self._set_token_value('WHERE', new_value)
return self
def update(self, table_name):
"""Update a table
:param table_name: str
:return: self
"""
sanitized_tn = self._sanitize_table_name(table_name)
self._set_token_value('UPDATE', sanitized_tn)
return self
def data(self, **kwargs):
self._set_token_value('SET', kwargs)
return self
def _from(self, table_name):
"""Update FROM (JOIN in fact) postgres syntax
SQL representation:
UPDATE accounts
SET contact_first_name = first_name,
contact_last_name = last_name
FROM salesmen
WHERE salesmen.id = accounts.sales_id;
:param table_name: str
:return: self
"""
sanitized_tn = self._sanitize_table_name(table_name)
self._set_token_value('FROM', sanitized_tn)
return self
class InsertQuery(QueryBuilder):
"""Insert query builder.
Query example:
>>> insert('users')\
.data(name='Alex', gender='M')\
.returning('id')\
.execute(cursor)
"""
TOKENS = OrderedDict([
('INSERT', Token(template='INSERT INTO {}', value_type=StringValue)),
('DEFAULT', Token(template='DEFAULT VALUES', value_type=NullValue)),
# part of: INSERT INTO table ({fields})
('fields', Token(template='({})', value_type=CommaValue)),
('VALUES', Token(template='VALUES ({})', value_type=TupleValue)),
# ('values_multi', Token(template='VALUES %s')), # for multiple rows
('RETURNING', Token(template='RETURNING {}', value_type=CommaValue))
])
def insert(self, table_name):
self._set_table_name('INSERT', table_name)
return self
def data(self, **kwargs):
"""Insert values data
:return:
"""
self._set_token_value('fields', tuple(kwargs.keys()))
self._set_token_value('VALUES', tuple(kwargs.values()))
return self
def values_multi(self, list_of_values):
"""Method allows to build multiple rows insert
:param list_of_values: Iterable: list of multiple rows values
For example:
[('Alex', 'M'), ('Jane', 'F')]
"""
if isinstance(list_of_values, Iterable):
prepared_values = [', '.join(item) for item in list_of_values]
self._set_token_value(
'values_multi',
# prepare rows by join of prepared_values
', '.join(['({})'.format(item) for item in prepared_values]))
return self
def defaults(self):
"""Allows to insert row with all defaults values
Simulate the following: INSERT INTO {table_name} DEFAULT VALUES'
"""
# reset tokens exclude table name
self._reset_tokens(exclude=('INSERT', ))
self._set_token_value('DEFAULT', True)
return self
def returning(self, *fields):
fields = list(filter(None, fields))
self._set_token_value('RETURNING', fields)
return self
class SelectQuery(QueryBuilder):
"""Select query builder.
The idea is to use builder pattern to make select query to database with
query tokens.
Example:
>>> from pg_requests import query_facade as qf
>>> qf.select('MyTable').fields('a', 'b').filter(score__gt=0).order_by('a').desc()
"""
TOKENS = OrderedDict([
('SELECT',
Token(template='SELECT {}', value_type=CommaValue, required=True)),
# Table tokens
('FROM', Token(template='FROM {}', value_type=StringValue)),
('FROM__ALIAS', Token(template="AS '{}' ", value_type=StringValue)),
# User-function tokens
# Use sub-token to glue token str value + sub-token value without space
('FROM__FN',
Token(template='FROM {}',
value_type=StringValue,
subtoken=Token(template='({})', value_type=TupleValue))),
('FROM__FN_NAME', Token(template='FROM {}', value_type=StringValue)),
('FROM__FN_ARGS', Token(template='({})', value_type=TupleValue)),
('JOIN',
Token(template='{join_type} {table_name}', value_type=DictValue)),
# NOTE: JOIN__ON and JOIN__USING mutual exclusive
# FIXME: JOIN__ON must be conditional type
('JOIN__ON', Token(template='ON ({})', value_type=StringValue)),
('JOIN__USING', Token(template='USING ({})', value_type=CommaValue)),
# NOTE: here is quite complex logic, see ConditionalValue imp
('WHERE', Token(template='WHERE {}', value_type=FilterValue)),
('GROUP_BY', Token(template='GROUP BY {}', value_type=CommaValue)),
# TODO: add tests for having
('GROUP_BY__HAVING', Token(template='HAVING {}',
value_type=FilterValue)),
('ORDER_BY', Token(template='ORDER BY {}', value_type=CommaValue)),
('DESC', Token(template='DESC', value_type=NullValue)),
('LIMIT', Token(template='LIMIT {}', value_type=StringValue)),
('OFFSET', Token(template='OFFSET {}', value_type=StringValue)),
])
def fields(self, *fields):
"""Select fields to fetch
:param fields: list of str
:return: self
"""
# Filter False parameters
fields = list(filter(None, fields))
if fields:
# Substitute as SELECT %s, %s...
self._set_token_value('SELECT', fields)
else:
self._set_token_value('SELECT', '*')
return self
def call_fn(self, fn_name, args):
"""Call user-defined function, like
SELECT * FROM my_function('param1', 2, True)
:param fn_name: str: function name
:param args: tuple: function arguments tuple
:return: function result
"""
self.fields('*')._set_token_value('FROM__FN', fn_name)
# Custom setter for sub-token
token = self._get_token('FROM__FN')
# NOTE: subtoken value will be glued with the main token value without
# a space
token.subtoken.value = args
return self
def select(self, table_name, alias=None):
"""Select from a table. It means SQL FROM operator.
:param table_name: str: table name
:param alias: alias for a table - 'AS' keyword
"""
# TODO: add sub-query feature + custom frontend function
# Set default selection fields as '*'
sanitized_tn = self._sanitize_table_name(table_name)
self.fields('*')._set_token_value('FROM', sanitized_tn)
# NOTE: SELECT * FROM <table_name> AS <alias>
if alias is not None:
self._set_token_value('FROM__ALIAS', alias)
return self
def join(self, table_name, join_type=JOIN.INNER, on=None, using=None):
if join_type not in JOIN:
raise ValueError("Wrong join type '%r', must be '%r'" %
(join_type, JOIN))
self._set_token_value('JOIN',
dict(join_type=join_type, table_name=table_name))
# FIXME: JOIN__ON must be conditional type or not ???
if on is not None:
self._set_token_value('JOIN__ON', on)
elif using is not None:
self._set_token_value('JOIN__USING', using)
return self
def filter(self, *args, **kwargs):
token = self._get_token('WHERE')
new_value = None
if args:
# In case of QueryOperators
new_value = args[0]
elif kwargs:
# In case of simple key-value filters
new_value = kwargs
# Stub to prevent errors
if new_value is None:
return self
if token.value:
token.value.update(new_value)
else:
self._set_token_value('WHERE', new_value)
return self
# NOTE: python 3 syntax only
# def order_by(self, *args, desc=False):
def order_by(self, *args, **kwargs):
args = list(filter(None, args))
if args:
self._set_token_value('ORDER_BY', args)
# Descending order option
if kwargs.get('desc'):
self._set_token_value('DESC', True)
return self
def desc(self):
self._set_token_value('DESC', True)
return self
def limit(self, value):
self._set_token_value('LIMIT', int(value))
return self
def offset(self, value):
self._set_token_value('OFFSET', int(value))
return self
def group_by(self, *args):
args = list(filter(None, args))
if args:
self._set_token_value('GROUP_BY', args)
return self
def having(self, *args, **kwargs):
if args:
self._set_token_value('GROUP_BY__HAVING', args[0])
elif kwargs:
self._set_token_value('GROUP_BY__HAVING', kwargs)
return self
def test_token_with_null_value(self):
token = Token(template='DEFAULT VALUES', value_type=NullValue)
token.value = "any value shouldn't be appeared"
self.assertIsInstance(token.value, NullValue)
self.assertIsInstance(token.value, NullValue)
self.assertEqual(token.eval(), "DEFAULT VALUES")
def test_token_with_string_value(self):
token = Token(template='INSERT INTO {}', value_type=StringValue)
token.value = 'MyTable'
self.assertIsInstance(token.value, StringValue, token.value)
self.assertEqual(token.eval(), "INSERT INTO MyTable")