def testInJoin(self): self.tables.set('t1') self.tables.join({ sqlpuzzle.select_from('t2'): 't' }).on('t1.id', 't.id') self.assertEqual( str(self.tables), '"t1" JOIN (SELECT * FROM "t2") AS "t" ON "t1"."id" = "t"."id"')
#!/usr/bin/env python # -*- coding: utf-8 -*- import sqlpuzzle sql = sqlpuzzle.select_from('table') sql.order_by('name') print(sql) # output: SELECT * FROM `table` ORDER BY `name` sql.order_by(('name', 'desc'), 'id') print(sql) # output: SELECT * FROM `table` ORDER BY `name` DESC, `id` sql.order_by(('name', 'asc')) # or only 'name' print(sql) # output: SELECT * FROM `table` ORDER BY `name`, `id` # same as before: sql.order_by({'name': 'asc'}) # dict looks better sql.order_by(name='asc') # also possible
def test_copy_with_custom(): query1 = sqlpuzzle.select_from('t').where(sqlpuzzle.customsql('x')) query2 = query1.copy() assert str(query1) == str(query2)
def test_select_from_with_more(): sql = sqlpuzzle.select_from('user', 'country') assert str(sql) == 'SELECT * FROM "user", "country"'
#!/usr/bin/env python # -*- coding: utf-8 -*- import sqlpuzzle ########################################################################### # !!! Use only if you must, because it can be changed without notice. !!! # ########################################################################### sql = sqlpuzzle.select_from('t').where(name='Alan').limit(5).offset(10).order_by('id') print(sql._limit) # output: LIMIT 5 OFFSET 10 print(sql._where) # output: WHERE `name` = "Alan" print(sql._order_by) # output: ORDER BY `id` # All possible parts: # * _columns # * _group_by # * _having # * _into_outfile # * _limit # * _on_duplicate_key_update # * _order_by # * _tables
def test_select_from_with_more(self): sql = sqlpuzzle.select_from('user', 'country') self.assertEqual(str(sql), 'SELECT * FROM "user", "country"')
#!/usr/bin/env python # -*- coding: utf-8 -*- import sqlpuzzle ########################################################################### # !!! Use only if you must, because it can be changed without notice. !!! # ########################################################################### sql = sqlpuzzle.select_from('t').where( name='Alan').limit(5).offset(10).order_by('id') print(sql._limit) # output: LIMIT 5 OFFSET 10 print(sql._where) # output: WHERE `name` = "Alan" print(sql._order_by) # output: ORDER BY `id` # All possible parts: # * _columns # * _group_by # * _having # * _into_outfile # * _limit # * _on_duplicate_key_update # * _order_by # * _tables # * _values
#!/usr/bin/env python # -*- coding: utf-8 -*- import sqlpuzzle sql = sqlpuzzle.select_from('table') sql.group_by('name') print(sql) # output: SELECT * FROM `table` GROUP BY `name` sql.group_by(('name', 'desc'), 'id') print(sql) # output: SELECT * FROM `table` GROUP BY `name` DESC, `id` sql.group_by(('name', 'asc')) # or only 'name' print(sql) # output: SELECT * FROM `table` GROUP BY `name`, `id` # Same as before: sql.group_by({'name': 'asc'}) # dict looks better sql.group_by(name='asc') # also possible
def test_copy1(): query1 = sqlpuzzle.select_from('t').where('c', sqlpuzzle.relations.GT(1)) query2 = query1.copy() assert str(query1) == str(query2)
def test_select_from_as_kwds(): sql = sqlpuzzle.select_from(user='******') assert str(sql) == 'SELECT * FROM "user" AS "u"'
def test_select_from(): sql = sqlpuzzle.select_from('user') assert str(sql) == 'SELECT * FROM "user"'
def testInJoin(self): self.tables.set('t1') self.tables.join({sqlpuzzle.select_from('t2'): 't'}).on('t1.id', 't.id') self.assertEqual(str(self.tables), '"t1" JOIN (SELECT * FROM "t2") AS "t" ON "t1"."id" = "t"."id"')
def test_boolean(self): sql = sqlpuzzle.select_from("table").where({"flag": True}) self.assertEqual(str(sql), 'SELECT * FROM "table" WHERE "flag" = 1')
def test_security_subselect(): select = sqlpuzzle.select_from('table') assert str(SqlReference(select)) == '(SELECT * FROM "table")'
#!/usr/bin/env python # -*- coding: utf-8 -*- import sqlpuzzle print(sqlpuzzle.select_from('table').limit(10)) # output: SELECT * FROM `table` LIMIT 10 print(sqlpuzzle.select_from('table').limit(10).offset(5)) # output: SELECT * FROM `table` LIMIT 10 OFFSET 5 print(sqlpuzzle.select_from('table').limit(10, 50)) # output: SELECT * FROM `table` LIMIT 10 OFFSET 50
# # SqlPuzzleException # - ConfirmException # - - ConfirmUpdateAllException # - - ConfirmDeleteAllException # - InvalidArgumentException # - - InvalidQueryException # try: sqlpuzzle.select(True) except sqlpuzzle.exceptions.InvalidArgumentException, e: print('catched:', e) try: print(sqlpuzzle.select_from('t').on('t2')) except sqlpuzzle.exceptions.InvalidQueryException, e: print('catched:', e) try: print(sqlpuzzle.update('table').set(name='Alan')) except sqlpuzzle.exceptions.ConfirmUpdateAllException, e: print('catched:', e) try: print(sqlpuzzle.delete().from_('table')) except sqlpuzzle.exceptions.ConfirmDeleteAllException, e: print('catched:', e) # All exceptions are inherited from SqlPuzzleException. try:
#!/usr/bin/env python # -*- coding: utf-8 -*- import sqlpuzzle import sqlpuzzle.relations subselect = sqlpuzzle.select('name').from_('user').where('id', 42) print(sqlpuzzle.select(subselect).from_('table')) # output: # SELECT (SELECT `name` FROM `user` WHERE `id` = 42) FROM `table` subselect = sqlpuzzle.select_from('user') print(sqlpuzzle.select_from(subselect)) # ouput: # SELECT * FROM (SELECT * FROM `user`) subselect = sqlpuzzle.select('id').from_('user').where('name', 'Alan').limit(1) print(sqlpuzzle.select_from('table').where(subselect, sqlpuzzle.relations.GE(50))) # ouput: # SELECT * FROM `table` WHERE (SELECT `id` FROM `user` WHERE `name` = "Alan" LIMIT 1) >= 50 # if you need column reference in value of condition, just add back quotes (first block is enought) subselect = sqlpuzzle.select('name').from_(('user', 'parent')).where('parent.id', '`user`.parent_id') print(sqlpuzzle.select('user.*', (subselect, 'parentName')).from_('user')) # output: # SELECT `user`.*, (SELECT `name` FROM `user` AS `parent` WHERE `parent`.`id` = `user`.`parent_id`) AS "parentName" FROM `user` subselect = sqlpuzzle.select(sqlpuzzle.count()).from_table('table') print(sqlpuzzle.select({subselect: 'foo'}).from_table('table2')) # output:
def test_subselect(self): select = sqlpuzzle.select_from('table') self.assertEqual(str(SqlReference(select)), '(SELECT * FROM "table")')
def test_select_from(self): sql = sqlpuzzle.select_from('user') self.assertEqual(str(sql), 'SELECT * FROM "user"')
def test_boolean(postgresql): sql = sqlpuzzle.select_from('table').where({'flag': True}) assert str(sql) == 'SELECT * FROM "table" WHERE "flag" = true'
def test_copy_with_custom(self): query1 = sqlpuzzle.select_from('t').where(sqlpuzzle.customsql('x')) query2 = query1.copy() self.assertEquals(str(query1), str(query2))
def test_boolean(self): sql = sqlpuzzle.select_from('table').where({'flag': True}) self.assertEqual(str(sql), 'SELECT * FROM `table` WHERE `flag` = 1')
def test_subselect(): select = sqlpuzzle.select_from('table') assert str(SqlValue(select)) == '(SELECT * FROM "table")'
def test_boolean(mysql): sql = sqlpuzzle.select_from('table').where({'flag': True}) assert str(sql) == 'SELECT * FROM `table` WHERE `flag` = 1'
#!/usr/bin/env python # -*- coding: utf-8 -*- import sqlpuzzle sql = sqlpuzzle.select_from("table") sql.group_by("name") print(sql) # output: SELECT * FROM `table` GROUP BY `name` sql.group_by(("name", "desc"), "id") print(sql) # output: SELECT * FROM `table` GROUP BY `name` DESC, `id` sql.group_by(("name", "asc")) # or only 'name' print(sql) # output: SELECT * FROM `table` GROUP BY `name`, `id` # Same as before: sql.group_by({"name": "asc"}) # dict looks better sql.group_by(name="asc") # also possible
#!/usr/bin/env python # -*- coding: utf-8 -*- import sqlpuzzle print(sqlpuzzle.select().from_('table')) # output: SELECT * FROM `table` print(sqlpuzzle.select_from('table')) # same output as previous command sql = sqlpuzzle.select('id', 'name') sql.from_('table') sql.columns(('first_name', 'firstName')) # tuple is for AS sql.from_('table2') print(sql) # output: # SELECT `id`, `name`, `first_name` AS "firstName" FROM `table`, `table2` # You can also use dictionary for AS in columns and tables. sql = sqlpuzzle.select({'user_id': 'userId'}).from_({'some_table': 'someTable'}) print(sql) # output: # SELECT `user_id` AS "userId" FROM `some_table` AS `someTable` # Named parameter is also possible. sql = sqlpuzzle.select(user_id='userId').from_(some_table='someTable') print(sql)
def test_exists(where): subquery = sqlpuzzle.select_from('t2').where(col=sqlpuzzle.R('t1.col')) where.where(Exists(subquery)) assert str(where) == 'WHERE EXISTS(SELECT * FROM "t2" WHERE "col" = "t1"."col")'
#!/usr/bin/env python # -*- coding: utf-8 -*- import sqlpuzzle sql1 = sqlpuzzle.select_from('t1') sql2 = sqlpuzzle.select_from('t2') print(sql1 & sql2) # output: SELECT * FROM `t1` UNION ALL SELECT * FROM `t2` print(sql1 | sql2) # output: SELECT * FROM `t1` UNION SELECT * FROM `t2` print(sql1 & sql2 | sql1) # output: SELECT * FROM `t1` UNION ALL SELECT * FROM `t2` UNION SELECT * FROM `t1` print(sqlpuzzle.select(sql1 | sql2).from_('t')) # output: SELECT (SELECT * FROM `t1` UNION SELECT * FROM `t2`) FROM `t` print(sqlpuzzle.select_from(sql1 & sql2)) # output: SELECT * FROM (SELECT * FROM `t1` UNION ALL SELECT * FROM `t2`)
# # SqlPuzzleException # - ConfirmException # - - ConfirmUpdateAllException # - - ConfirmDeleteAllException # - InvalidArgumentException # - - InvalidQueryException # try: sqlpuzzle.select(True) except sqlpuzzle.exceptions.InvalidArgumentException, e: print('catched:', e) try: print(sqlpuzzle.select_from('t').on('t2')) except sqlpuzzle.exceptions.InvalidQueryException, e: print('catched:', e) try: print(sqlpuzzle.update('table').set(name='Alan')) except sqlpuzzle.exceptions.ConfirmUpdateAllException, e: print('catched:', e) try: print(sqlpuzzle.delete().from_('table')) except sqlpuzzle.exceptions.ConfirmDeleteAllException, e: print('catched:', e) # All exceptions are inherited from SqlPuzzleException.
#!/usr/bin/env python # -*- coding: utf-8 -*- import datetime import sqlpuzzle sql = sqlpuzzle.select_from('user') sql.where(id=42) sql.where(age=20, name='Michael', enabled=True) sql.where({ 'last_modify': datetime.datetime(2011, 6, 15, 22, 11, 00), 'right': 'admin', }) sql.where('column', sqlpuzzle.relations.LE(10)).where('column', sqlpuzzle.relations.GE(10)) sql.where( ('id', sqlpuzzle.relations.NOT_IN(range(10, 20, 2))), ('name', 'Alan'), ) conditions = (('salary', sqlpuzzle.relations.GRATHER_THAN(10000)), ('lastname', sqlpuzzle.relations.LIKE('%ar%'))) sql.where(conditions)
def test_select_from_as_kwds(self): sql = sqlpuzzle.select_from(user='******') self.assertEqual(str(sql), 'SELECT * FROM "user" AS "u"')
def test_copy1(self): query1 = sqlpuzzle.select_from('t').where('c', sqlpuzzle.relations.GT(1)) query2 = query1.copy() self.assertEquals(str(query1), str(query2))
def testInJoin(tables): tables.set('t1') tables.join({sqlpuzzle.select_from('t2'): 't'}).on('t1.id', 't.id') assert str( tables ) == '"t1" JOIN (SELECT * FROM "t2") AS "t" ON "t1"."id" = "t"."id"'
#!/usr/bin/env python # -*- coding: utf-8 -*- import sqlpuzzle sql1 = sqlpuzzle.select_from("t1") sql2 = sqlpuzzle.select_from("t2") print(sql1 & sql2) # output: SELECT * FROM `t1` UNION ALL SELECT * FROM `t2` print(sql1 | sql2) # output: SELECT * FROM `t1` UNION SELECT * FROM `t2` print(sql1 & sql2 | sql1) # output: SELECT * FROM `t1` UNION ALL SELECT * FROM `t2` UNION SELECT * FROM `t1` print(sqlpuzzle.select(sql1 | sql2).from_("t")) # output: SELECT (SELECT * FROM `t1` UNION SELECT * FROM `t2`) FROM `t` print(sqlpuzzle.select_from(sql1 & sql2)) # output: SELECT * FROM (SELECT * FROM `t1` UNION ALL SELECT * FROM `t2`)
def testInJoin(tables): tables.set('t1') tables.join({sqlpuzzle.select_from('t2'): 't'}).on('t1.id', 't.id') assert str(tables) == '"t1" JOIN (SELECT * FROM "t2") AS "t" ON "t1"."id" = "t"."id"'
def test_boolean(self): sql = sqlpuzzle.select_from('table').where({'flag': True}) self.assertEqual(str(sql), 'SELECT * FROM "table" WHERE "flag" = true')