Esempi in Python per select

Esempio n. 1

File: test_sqlpuzzle.py Progetto: horejsek/python-sqlpuzzle

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)

Esempio n. 2

 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")')

Esempio n. 3

File: views.py Progetto: horejsek/helloworld

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)

Esempio n. 4

File: sqlpuzzletest.py Progetto: lgsunnyvale/python-sqlpuzzle

 def test_select_without_columns(self):
     sql = sqlpuzzle.select().from_('user')
     self.assertEqual(str(sql), 'SELECT * FROM "user"')

Esempio n. 5

File: test_tables.py Progetto: horejsek/python-sqlpuzzle

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")'

Esempio n. 6

File: customsql.py Progetto: fm4d/python-sqlpuzzle

#!/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)

Esempio n. 7

File: test_sqlpuzzle.py Progetto: horejsek/python-sqlpuzzle

def test_select_without_columns():
    sql = sqlpuzzle.select().from_('user')
    assert str(sql) == 'SELECT * FROM "user"'

Esempio n. 8

File: test_sqlpuzzle.py Progetto: vikipha/python-sqlpuzzle

def test_select_test():
    sql = sqlpuzzle.select(1)
    assert str(sql) == 'SELECT 1'

Esempio n. 9

File: union.py Progetto: lgsunnyvale/python-sqlpuzzle

#!/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`)

Esempio n. 10

File: test_mysql.py Progetto: vikipha/python-sqlpuzzle

def test_reference(mysql):
    sql = sqlpuzzle.select('id').from_('table')
    assert str(sql) == 'SELECT `id` FROM `table`'

Esempio n. 11

File: test_sqlpuzzle.py Progetto: vikipha/python-sqlpuzzle

def test_select_without_columns():
    sql = sqlpuzzle.select().from_('user')
    assert str(sql) == 'SELECT * FROM "user"'

Esempio n. 12

File: test_postgresql.py Progetto: horejsek/python-sqlpuzzle

def test_reference(postgresql):
    sql = sqlpuzzle.select('id').from_('table')
    assert str(sql) == 'SELECT "id" FROM "table"'

Esempio n. 13

File: mysql.py Progetto: lgsunnyvale/python-sqlpuzzle

 def test_reference(self):
     sql = sqlpuzzle.select('id').from_('table')
     self.assertEqual(str(sql), 'SELECT `id` FROM `table`')

Esempio n. 14

File: test_sqlpuzzle.py Progetto: horejsek/python-sqlpuzzle

def test_select_test():
    sql = sqlpuzzle.select(1)
    assert str(sql) == 'SELECT 1'

Esempio n. 15

File: test_sqlpuzzle.py Progetto: horejsek/python-sqlpuzzle

def test_select_with_columns():
    sql = sqlpuzzle.select('id', 'name').from_('user')
    assert str(sql) == 'SELECT "id", "name" FROM "user"'

Esempio n. 16

File: sqlite.py Progetto: horejsek/python-sqlpuzzle

 def test_reference(self):
     sql = sqlpuzzle.select("id").from_("table")
     self.assertEqual(str(sql), 'SELECT "id" FROM "table"')

Esempio n. 17

File: test_sqlpuzzle.py Progetto: tepek2/python-sqlpuzzle

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)

Esempio n. 18

def test_reference(postgresql):
    sql = sqlpuzzle.select('id').from_('table')
    assert str(sql) == 'SELECT "id" FROM "table"'

Esempio n. 19

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"'

Esempio n. 20

File: exceptions.py Progetto: fm4d/python-sqlpuzzle

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'))

Esempio n. 21

File: test_sqlpuzzle.py Progetto: vikipha/python-sqlpuzzle

def test_select_with_columns():
    sql = sqlpuzzle.select('id', 'name').from_('user')
    assert str(sql) == 'SELECT "id", "name" FROM "user"'

Esempio n. 22

File: sqlpuzzletest.py Progetto: fm4d/python-sqlpuzzle

 def test_select_test(self):
     sql = sqlpuzzle.select(1)
     self.assertEqual(str(sql), 'SELECT 1')

Esempio n. 23

#!/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)

Esempio n. 24

File: subselect.py Progetto: lgsunnyvale/python-sqlpuzzle

#!/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:

Esempio n. 25

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'))

Esempio n. 26

File: test_tables.py Progetto: vikipha/python-sqlpuzzle

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")'

Esempio n. 27

File: sqlpuzzletest.py Progetto: fm4d/python-sqlpuzzle

 def test_select_without_columns(self):
     sql = sqlpuzzle.select().from_('user')
     self.assertEqual(str(sql), 'SELECT * FROM "user"')

Esempio n. 28

 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")')

Esempio n. 29

File: test_mysql.py Progetto: horejsek/python-sqlpuzzle

def test_reference(mysql):
    sql = sqlpuzzle.select('id').from_('table')
    assert str(sql) == 'SELECT `id` FROM `table`'

Esempio n. 30

File: sqlpuzzletest.py Progetto: fm4d/python-sqlpuzzle

 def test_select_with_columns(self):
     sql = sqlpuzzle.select('id', 'name').from_('user')
     self.assertEqual(str(sql), 'SELECT "id", "name" FROM "user"')

Esempio n. 31

File: sqlpuzzletest.py Progetto: lgsunnyvale/python-sqlpuzzle

 def test_select_test(self):
     sql = sqlpuzzle.select(1)
     self.assertEqual(str(sql), 'SELECT 1')

Esempio n. 32

File: union.py Progetto: vidyacraghav/python-sqlpuzzle

#!/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`)

Esempio n. 33

File: sqlpuzzletest.py Progetto: lgsunnyvale/python-sqlpuzzle

 def test_select_with_columns(self):
     sql = sqlpuzzle.select('id', 'name').from_('user')
     self.assertEqual(str(sql), 'SELECT "id", "name" FROM "user"')

Esempio n. 34

File: views.py Progetto: horejsek/helloworld

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)

Esempio n. 35

#!/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:

Esempio n. 36

 def test_reference(self):
     sql = sqlpuzzle.select('id').from_('table')
     self.assertEqual(str(sql), 'SELECT "id" FROM "table"')