sql = sqlpuzzle.select('country.name', avg).from_(table) sql.where(where).where(planet='Earth') sql.group_by('user.country_id') print(sql) # # output: # (for better reading splited to more lines) # # SELECT `country`.`name`, AVG(`age`) AS "avgAge" # FROM `user` LEFT JOIN `country` ON `user`.`country_id`=`country`.`id` # WHERE (`enable` = 1 OR `vip` = 1) AND `planet` = 'Earth' # GROUP BY `user`.`country_id` # table = sqlpuzzle.customsql('`user`') set_ = sqlpuzzle.customsql('`age` = `age` + 1') sql = sqlpuzzle.update(table).set(set_).where(where) print(sql) # output: # UPDATE `user` SET `age` = `age` + 1 WHERE (`enable` = 1 OR `vip` = 1) print(sqlpuzzle.delete_from(table).where(where)) # output: # DELETE FROM `user` WHERE (`enable` = 1 OR `vip` = 1) print(sqlpuzzle.insert_into(table).values(name='Alan')) # output: # INSERT INTO `user` (`name`) VALUES ("Alan")
print(sql) # # output: # (for better reading splited to more lines) # # SELECT `country`.`name`, AVG(`age`) AS "avgAge" # FROM `user` LEFT JOIN `country` ON `user`.`country_id`=`country`.`id` # WHERE (`enable` = 1 OR `vip` = 1) AND `planet` = 'Earth' # GROUP BY `user`.`country_id` # table = sqlpuzzle.customsql('`user`') set_ = sqlpuzzle.customsql('`age` = `age` + 1') sql = sqlpuzzle.update(table).set(set_).where(where) print(sql) # output: # UPDATE `user` SET `age` = `age` + 1 WHERE (`enable` = 1 OR `vip` = 1) print(sqlpuzzle.delete_from(table).where(where)) # output: # DELETE FROM `user` WHERE (`enable` = 1 OR `vip` = 1) print(sqlpuzzle.insert_into(table).values(name='Alan')) # output: # INSERT INTO `user` (`name`) VALUES ("Alan")
def test_delete_from(self): sql = sqlpuzzle.delete_from('user').where(id=42) self.assertEqual(str(sql), 'DELETE FROM "user" WHERE "id" = 42')
def test_delete_from(): sql = sqlpuzzle.delete_from('user').where(id=42) assert str(sql) == 'DELETE FROM "user" WHERE "id" = 42'
#!/usr/bin/env python # -*- coding: utf-8 -*- import sqlpuzzle import sqlpuzzle.exceptions sql = sqlpuzzle.delete().from_('table') try: print(sql) except sqlpuzzle.exceptions.ConfirmDeleteAllException: pass # delete all records is not enabled by default sql.allow_delete_all() print(sql) # output: DELETE FROM `table` try: sql.forbid_delete_all() print(sql) except sqlpuzzle.exceptions.ConfirmDeleteAllException: pass # protected of delete all records can be turned on again sql.where(id=42) print(sql) # output: DELETE FROM `table` WHERE `id` = 42 print(sqlpuzzle.delete_from('table').where(id=42)) # same output as previous command