def test_datetime(): from datetime import datetime, date, time assert sqlrepr(datetime(2005, 7, 14, 13, 31, 2)) == "'2005-07-14 13:31:02'" assert sqlrepr(date(2005, 7, 14)) == "'2005-07-14'" assert sqlrepr(time(13, 31, 2)) == "'13:31:02'" # now dates before 1900 assert sqlrepr(datetime(1428, 7, 14, 13, 31, 2)) == "'1428-07-14 13:31:02'" assert sqlrepr(date(1428, 7, 14)) == "'1428-07-14'"
def test_empty_AND(): assert AND() is None assert AND(True) is True # sqlrepr() is needed because AND() returns an SQLExpression that overrides # comparison. The following # AND('x', 'y') == "foo bar" # is True! (-: Eeek! assert sqlrepr(AND(1, 2)) == sqlrepr(SQLOp("AND", 1, 2)) == "((1) AND (2))" assert sqlrepr(AND(1, 2, '3'), "sqlite") == \ sqlrepr(SQLOp("AND", 1, SQLOp("AND", 2, '3')), "sqlite") == \ "((1) AND ((2) AND ('3')))"
def test_sets(): try: set except NameError: pass else: assert sqlrepr(set([1])) == "(1)" try: from sets import Set except ImportError: pass else: assert sqlrepr(Set([1])) == "(1)"
def test_sets(): try: set except NameError: pass else: assert sqlrepr(set([1])) == "(1)" if sys.version_info[:3] < (2, 6, 0): # Module sets was deprecated in Python 2.6 try: from sets import Set except ImportError: pass else: assert sqlrepr(Set([1])) == "(1)"
def test_insert(): # Single column, no keyword arguments. instance = Insert('test', [('test',)]) assert sqlrepr(instance, 'mysql') == "INSERT INTO test VALUES ('test')" # Multiple columns, no keyword arguments. instance2 = Insert('test', [('1st', '2nd', '3th', '4th')]) assert sqlrepr(instance2, 'postgres') == "INSERT INTO test VALUES ('1st', '2nd', '3th', '4th')" # Multiple rows, multiple columns, "valueList" keyword argument. instance3 = Insert('test', valueList=[('a1', 'b1'), ('a2', 'b2'), ('a3', 'b3')]) assert sqlrepr(instance3, 'sqlite') == "INSERT INTO test VALUES ('a1', 'b1'), ('a2', 'b2'), ('a3', 'b3')" # Multiple columns, "values" keyword argument. instance4 = Insert('test', values=('v1', 'v2', 'v3')) assert sqlrepr(instance4, 'mysql') == "INSERT INTO test VALUES ('v1', 'v2', 'v3')" # Single column, "valueList" keyword argument. instance5 = Insert('test', valueList=[('v1',)]) assert sqlrepr(instance5, 'mysql') == "INSERT INTO test VALUES ('v1')" # Multiple rows, Multiple columns, template. instance6 = Insert('test', valueList=[('a1', 'b1'), ('a2', 'b2')], template=['col1', 'col2']) assert sqlrepr(instance6, 'mysql') == "INSERT INTO test (col1, col2) VALUES ('a1', 'b1'), ('a2', 'b2')" # Multiple columns, implicit template (dictionary value). instance7 = Insert('test', valueList=[{'col1': 'a1', 'col2': 'b1'}]) assert sqlrepr(instance7, 'mysql') == "INSERT INTO test (col2, col1) VALUES ('b1', 'a1')" # Multiple rows, Multiple columns, implicit template. instance8 = Insert('test', valueList=[{'col1': 'a1', 'col2': 'b1'}, {'col1': 'a2', 'col2': 'b2'}]) assert sqlrepr(instance8, 'mysql') == "INSERT INTO test (col2, col1) VALUES ('b1', 'a1'), ('b2', 'a2')"
def test_str_or_sqlrepr(): select = Select(['id', 'name'], staticTables=['employees'], where='value>0', orderBy='id') assert sqlrepr(select, 'sqlite') == \ 'SELECT id, name FROM employees WHERE value>0 ORDER BY id' select = Select(['id', 'name'], staticTables=['employees'], where='value>0', orderBy='id', lazyColumns=True) assert sqlrepr(select, 'sqlite') == \ 'SELECT id FROM employees WHERE value>0 ORDER BY id' insert = Insert('employees', values={'id': 1, 'name': 'test'}) assert sqlrepr(insert, 'sqlite') == \ "INSERT INTO employees (id, name) VALUES (1, 'test')" update = Update('employees', {'name': 'test'}, where='id=1') assert sqlrepr(update, 'sqlite') == \ "UPDATE employees SET name='test' WHERE id=1" update = Update('employees', {'name': 'test', 'age': 42}, where='id=1') assert sqlrepr(update, 'sqlite') == \ "UPDATE employees SET age=42, name='test' WHERE id=1" delete = Delete('employees', where='id=1') assert sqlrepr(delete, 'sqlite') == \ "DELETE FROM employees WHERE id=1" raises(TypeError, Delete, 'employees') delete = Delete('employees', where=None) assert sqlrepr(delete, 'sqlite') == \ "DELETE FROM employees"
def test_CONCAT(): setupClass(SOTestSQLBuilder) SOTestSQLBuilder(name='test', value=42) assert sqlrepr(CONCAT('a', 'b'), 'mysql') == "CONCAT('a', 'b')" assert sqlrepr(CONCAT('a', 'b'), 'sqlite') == "'a' || 'b'" assert sqlrepr(CONCAT('prefix', SOTestSQLBuilder.q.name), 'mysql') == \ "CONCAT('prefix', so_test_sql_builder.name)" assert sqlrepr(CONCAT('prefix', SOTestSQLBuilder.q.name), 'sqlite') == \ "'prefix' || so_test_sql_builder.name" select = Select([CONCAT(SOTestSQLBuilder.q.name, '-suffix')], staticTables=['so_test_sql_builder']) connection = getConnection() rows = connection.queryAll(connection.sqlrepr(select)) result = rows[0][0] if not PY2 and not isinstance(result, str): result = result.decode('ascii') assert result == "test-suffix"
def test_ANY(): setupClass(SOTestSQLBuilder) select = Select( [SOTestSQLBuilder.q.name], 'value' == ANY(SOTestSQLBuilder.q.so_value), ) assert sqlrepr(select, 'mysql') == \ "SELECT so_test_sql_builder.name FROM so_test_sql_builder " \ "WHERE (('value') = ANY (so_test_sql_builder.so_value))"
def test_CONCAT(): setupClass(SOTestSQLBuilder) SOTestSQLBuilder(name='test', so_value=42) assert sqlrepr(CONCAT('a', 'b'), 'mysql') == "CONCAT('a', 'b')" assert sqlrepr(CONCAT('a', 'b'), 'mssql') == "'a' + 'b'" assert sqlrepr(CONCAT('a', 'b'), 'sqlite') == "'a' || 'b'" assert sqlrepr(CONCAT('prefix', SOTestSQLBuilder.q.name), 'mysql') == \ "CONCAT('prefix', so_test_sql_builder.name)" assert sqlrepr(CONCAT('prefix', SOTestSQLBuilder.q.name), 'sqlite') == \ "'prefix' || so_test_sql_builder.name" select = Select([CONCAT(SOTestSQLBuilder.q.name, '-suffix')], staticTables=['so_test_sql_builder']) connection = getConnection() rows = connection.queryAll(connection.sqlrepr(select)) result = rows[0][0] if not PY2 and not isinstance(result, str): result = result.decode('ascii') assert result == "test-suffix"
def test_Select(): setupClass(SOTestSQLBuilder) select1 = Select([const.id, func.MAX(const.salary)], staticTables=['employees']) assert sqlrepr(select1) == 'SELECT id, MAX(salary) FROM employees' select2 = Select([SOTestSQLBuilder.q.name, SOTestSQLBuilder.q.value]) assert sqlrepr(select2) == \ 'SELECT so_test_sql_builder.name, so_test_sql_builder.value ' \ 'FROM so_test_sql_builder' union = Union(select1, select2) assert sqlrepr(union) == \ 'SELECT id, MAX(salary) FROM employees ' \ 'UNION SELECT so_test_sql_builder.name, so_test_sql_builder.value ' \ 'FROM so_test_sql_builder' union = Union(SOTestSQLBuilder.select().queryForSelect()) assert sqlrepr(union) == \ 'SELECT so_test_sql_builder.id, so_test_sql_builder.name, ' \ 'so_test_sql_builder.value FROM so_test_sql_builder WHERE 1 = 1'
def test_Select(): setupClass(SOTestSQLBuilder) select1 = Select([const.id, func.MAX(const.salary)], staticTables=['employees']) assert sqlrepr(select1) == 'SELECT id, MAX(salary) FROM employees' select2 = Select([SOTestSQLBuilder.q.name, SOTestSQLBuilder.q.so_value]) assert sqlrepr(select2) == \ 'SELECT so_test_sql_builder.name, so_test_sql_builder.so_value ' \ 'FROM so_test_sql_builder' union = Union(select1, select2) assert sqlrepr(union) == \ 'SELECT id, MAX(salary) FROM employees ' \ 'UNION SELECT so_test_sql_builder.name, ' \ 'so_test_sql_builder.so_value ' \ 'FROM so_test_sql_builder' union = Union(SOTestSQLBuilder.select().queryForSelect()) assert sqlrepr(union) == \ 'SELECT so_test_sql_builder.id, so_test_sql_builder.name, ' \ 'so_test_sql_builder.so_value FROM so_test_sql_builder WHERE 1 = 1'
def test_constant(): instance = SQLConstant('test') assert sqlrepr(instance) == repr(instance)
def test_prefix(): instance = SQLPrefix('test', 'test') assert sqlrepr(instance, 'mysql') == "test 'test'"
def test_newstyle(): instance = NewTestClass() assert sqlrepr(instance) == repr(instance)
def test_string_000(): assert sqlrepr('A String\000Another', 'postgres') == "'A String\\0Another'"
def test_division(): SOTestSQLBuilder(name='test', so_value=-11) assert sqlrepr(SOTestSQLBuilder.q.so_value / 4 == -2.75, 'mysql') == \ "(((so_test_sql_builder.so_value) / (4)) = (-2.75))" assert sqlrepr(SOTestSQLBuilder.q.so_value // 4 == -3, 'mysql') == \ "((FLOOR(((so_test_sql_builder.so_value) / (4)))) = (-3))"
def quote(x): r"""Quote a variable ready for inclusion into an SQL statement. Note that you should use quote_like to create a LIKE comparison. Basic SQL quoting works >>> quote(1) '1' >>> quote(1.0) '1.0' >>> quote("hello") "'hello'" >>> quote("'hello'") "'''hello'''" >>> quote(r"\'hello") "'\\\\''hello'" Note that we need to receive a Unicode string back, because our query will be a Unicode string (the entire query will be encoded before sending across the wire to the database). >>> quote(u"\N{TRADE MARK SIGN}") u"'\u2122'" Timezone handling is not implemented, since all timestamps should be UTC anyway. >>> from datetime import datetime, date, time >>> quote(datetime(2003, 12, 4, 13, 45, 50)) "'2003-12-04 13:45:50'" >>> quote(date(2003, 12, 4)) "'2003-12-04'" >>> quote(time(13, 45, 50)) "'13:45:50'" This function special cases datetime objects, due to a bug that has since been fixed in SQLOS (it installed an SQLObject converter that stripped the time component from the value). By itself, the sqlrepr function has the following output: >>> sqlrepr(datetime(2003, 12, 4, 13, 45, 50), 'postgres') "'2003-12-04T13:45:50'" This function also special cases set objects, which SQLObject's sqlrepr() doesn't know how to handle. >>> quote(set([1,2,3])) '(1, 2, 3)' >>> quote(frozenset([1,2,3])) '(1, 2, 3)' """ if isinstance(x, datetime): return "'%s'" % x elif ISQLBase(x, None) is not None: return str(x.id) elif isinstance(x, (set, frozenset)): # SQLObject can't cope with sets, so convert to a list, which it # /does/ know how to handle. x = list(x) return sqlrepr(x, 'postgres')
def test_op(): instance = SQLOp('and', 'this', 'that') assert sqlrepr(instance, 'mysql') == "(('this') AND ('that'))"
def test_simple_string(): assert sqlrepr('A String', 'firebird') == "'A String'"
def test_string_newline(): assert sqlrepr('A String\nAnother', 'postgres') == "'A String\\nAnother'" assert sqlrepr('A String\nAnother', 'sqlite') == "'A String\nAnother'"
def test_integer(): assert sqlrepr(10) == "10"
def test_simple_unicode(): assert sqlrepr(u'A String', 'postgres') == "'A String'"
def test_string_(): assert sqlrepr('A String\tAnother', 'postgres') == "'A String\\tAnother'" assert sqlrepr('A String\'Another', 'firebird') == "'A String''Another'"
def test_modulo(): setupClass(SOTestSQLBuilder) assert sqlrepr(SOTestSQLBuilder.q.value % 2 == 0, 'mysql') == \ "((MOD(so_test_sql_builder.value, 2)) = (0))" assert sqlrepr(SOTestSQLBuilder.q.value % 2 == 0, 'sqlite') == \ "(((so_test_sql_builder.value) % (2)) = (0))"
def test_tuple(): assert sqlrepr(('one', 'two', 'three'), 'postgres') == "('one', 'two', 'three')"
def test_call(): instance = SQLCall('test', ('test',)) assert sqlrepr(instance, 'mysql') == "'test'('test')"
def test_bool(): assert sqlrepr(True, 'postgres') == "'t'" assert sqlrepr(False, 'postgres') == "'f'" assert sqlrepr(True, 'mysql') == "1" assert sqlrepr(False, 'mysql') == "0"
def test_dict(): assert sqlrepr({"key": "value"}, "sqlite") == "('key')"
def test_trueclause(): instance = SQLTrueClauseClass() assert sqlrepr(instance) == repr(instance)
def test_instance(): instance = TestClass() assert sqlrepr(instance) == repr(instance)
def test_float(): assert sqlrepr(10.01) == "10.01"
def test_string_b(): assert sqlrepr('A String\bAnother', 'postgres') == "'A String\\bAnother'"
def test_modulo(): setupClass(SOTestSQLBuilder) assert sqlrepr(SOTestSQLBuilder.q.so_value % 2 == 0, 'mysql') == \ "((MOD(so_test_sql_builder.so_value, 2)) = (0))" assert sqlrepr(SOTestSQLBuilder.q.so_value % 2 == 0, 'sqlite') == \ "(((so_test_sql_builder.so_value) % (2)) = (0))"
def test_none(): assert sqlrepr(None) == "NULL"
def test_list(): assert sqlrepr(['one', 'two', 'three'], 'postgres') == "('one', 'two', 'three')"
def checkCount(q, c, msg=''): print("STRING:", str(q)) print("POSTGR:", sqlrepr(q, 'postgres')) assert sqlrepr(q, 'postgres').count("'t'") == c and \ sqlrepr(q, 'postgres') != str(q), msg