def test_injection_it_no_reference():
# There was a bug where the second value wasn't escaped and produced:
# (\'valid_value\', "user"."id"); something_bad")
sql = sqlpuzzle.select('id', 'name').from_('user').where({
'something': sqlpuzzle.relations.IN('valid_value', '"user"id"); something_bad'),
})
assert '"something" IN (\'valid_value\', \'"user"id"); something_bad\')' in str(sql)
def testInConditionWithRelationIN(self):
self.tables.set('t1')
self.tables.join('t2').on(
't2.id',
sqlpuzzle.relations.IN(sqlpuzzle.select('id').from_('t3')))
self.assertEqual(
str(self.tables),
'"t1" JOIN "t2" ON "t2"."id" IN (SELECT "id" FROM "t3")')
def article(article_id):
cur = get_db().cursor()
cur.execute(str(sqlpuzzle.select('title', 'text').from_('article').where({'id': article_id})))
row = cur.fetchone()
article = {
'title': row[0],
'text': row[1],
}
return render_template('article/detail.html', article=article)
def test_select_without_columns(self):
sql = sqlpuzzle.select().from_('user')
self.assertEqual(str(sql), 'SELECT * FROM "user"')
def testInConditionWithRelationIN(tables):
tables.set('t1')
tables.join('t2').on('t2.id', sqlpuzzle.relations.IN(sqlpuzzle.select('id').from_('t3')))
assert str(tables) == '"t1" JOIN "t2" ON "t2"."id" IN (SELECT "id" FROM "t3")'
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sqlpuzzle
avg = sqlpuzzle.customsql('AVG(`age`) AS "avgAge"')
table = sqlpuzzle.customsql('`user` LEFT JOIN `country` ON `user`.`country_id`=`country`.`id`')
where = sqlpuzzle.customsql('(`enable` = 1 OR `vip` = 1)')
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)
def test_select_without_columns():
sql = sqlpuzzle.select().from_('user')
assert str(sql) == 'SELECT * FROM "user"'
def test_select_test():
sql = sqlpuzzle.select(1)
assert str(sql) == 'SELECT 1'
#!/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 test_reference(mysql):
sql = sqlpuzzle.select('id').from_('table')
assert str(sql) == 'SELECT `id` FROM `table`'
def test_select_without_columns():
sql = sqlpuzzle.select().from_('user')
assert str(sql) == 'SELECT * FROM "user"'
def test_reference(postgresql):
sql = sqlpuzzle.select('id').from_('table')
assert str(sql) == 'SELECT "id" FROM "table"'
def test_reference(self):
sql = sqlpuzzle.select('id').from_('table')
self.assertEqual(str(sql), 'SELECT `id` FROM `table`')
def test_select_test():
sql = sqlpuzzle.select(1)
assert str(sql) == 'SELECT 1'
def test_select_with_columns():
sql = sqlpuzzle.select('id', 'name').from_('user')
assert str(sql) == 'SELECT "id", "name" FROM "user"'
def test_reference(self):
sql = sqlpuzzle.select("id").from_("table")
self.assertEqual(str(sql), 'SELECT "id" FROM "table"')
def test_injection_it_no_referenc():
sql = sqlpuzzle.select('id', 'name').from_('user').where(
'something',
sqlpuzzle.relations.IN('valid_value', '"user"id"); something_bad'))
assert '"user"id"); something_bad' in str(sql)
def test_reference(postgresql):
sql = sqlpuzzle.select('id').from_('table')
assert str(sql) == 'SELECT "id" FROM "table"'
def test_customsql_as_key_in_dict():
custom = sqlpuzzle.customsql('avg(col)')
sql = sqlpuzzle.select({
custom: 'acol',
}).from_('table')
assert str(sql) == 'SELECT avg(col) AS "acol" FROM "table"'
import sqlpuzzle
import sqlpuzzle.exceptions
#
# Inheritance of SqlPuzzleException:
#
# 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'))
def test_select_with_columns():
sql = sqlpuzzle.select('id', 'name').from_('user')
assert str(sql) == 'SELECT "id", "name" FROM "user"'
def test_select_test(self):
sql = sqlpuzzle.select(1)
self.assertEqual(str(sql), 'SELECT 1')
#!/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)
#!/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:
import sqlpuzzle
import sqlpuzzle.exceptions
#
# Inheritance of SqlPuzzleException:
#
# 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'))
def testInConditionWithRelationIN(tables):
tables.set('t1')
tables.join('t2').on(
't2.id', sqlpuzzle.relations.IN(sqlpuzzle.select('id').from_('t3')))
assert str(
tables) == '"t1" JOIN "t2" ON "t2"."id" IN (SELECT "id" FROM "t3")'
def test_select_without_columns(self):
sql = sqlpuzzle.select().from_('user')
self.assertEqual(str(sql), 'SELECT * FROM "user"')
def testInConditionWithRelationIN(self):
self.tables.set('t1')
self.tables.join('t2').on('t2.id', sqlpuzzle.relations.IN(sqlpuzzle.select('id').from_('t3')))
self.assertEqual(str(self.tables), '"t1" JOIN "t2" ON "t2"."id" IN (SELECT "id" FROM "t3")')
def test_reference(mysql):
sql = sqlpuzzle.select('id').from_('table')
assert str(sql) == 'SELECT `id` FROM `table`'
def test_select_with_columns(self):
sql = sqlpuzzle.select('id', 'name').from_('user')
self.assertEqual(str(sql), 'SELECT "id", "name" FROM "user"')
def test_select_test(self):
sql = sqlpuzzle.select(1)
self.assertEqual(str(sql), 'SELECT 1')
#!/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 test_select_with_columns(self):
sql = sqlpuzzle.select('id', 'name').from_('user')
self.assertEqual(str(sql), 'SELECT "id", "name" FROM "user"')
def articles():
cur = get_db().cursor()
cur.execute(str(sqlpuzzle.select('id', 'title').from_('article')))
rows = cur.fetchall()
articles = [{'id': row[0], 'title': row[1]} for row in rows]
return render_template('article/list.html', articles=articles)
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sqlpuzzle
avg = sqlpuzzle.customsql('AVG(`age`) AS "avgAge"')
table = sqlpuzzle.customsql(
'`user` LEFT JOIN `country` ON `user`.`country_id`=`country`.`id`')
where = sqlpuzzle.customsql('(`enable` = 1 OR `vip` = 1)')
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:
def test_reference(self):
sql = sqlpuzzle.select('id').from_('table')
self.assertEqual(str(sql), 'SELECT "id" FROM "table"')