Python select_from示例

示例#1

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

示例#2

文件： order_by.py 项目： fm4d/python-sqlpuzzle

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

示例#3

文件： test_sqlpuzzle.py 项目： vikipha/python-sqlpuzzle

def test_copy_with_custom():
    query1 = sqlpuzzle.select_from('t').where(sqlpuzzle.customsql('x'))
    query2 = query1.copy()
    assert str(query1) == str(query2)

示例#4

文件： test_sqlpuzzle.py 项目： vikipha/python-sqlpuzzle

def test_select_from_with_more():
    sql = sqlpuzzle.select_from('user', 'country')
    assert str(sql) == 'SELECT * FROM "user", "country"'

示例#5

文件： parts_of_query.py 项目： fm4d/python-sqlpuzzle

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

示例#6

文件： sqlpuzzletest.py 项目： lgsunnyvale/python-sqlpuzzle

 def test_select_from_with_more(self):
     sql = sqlpuzzle.select_from('user', 'country')
     self.assertEqual(str(sql), 'SELECT * FROM "user", "country"')

示例#7

文件： parts_of_query.py 项目： lgsunnyvale/python-sqlpuzzle

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

示例#8

文件： group_by.py 项目： lgsunnyvale/python-sqlpuzzle

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

示例#9

文件： test_sqlpuzzle.py 项目： horejsek/python-sqlpuzzle

def test_copy1():
    query1 = sqlpuzzle.select_from('t').where('c', sqlpuzzle.relations.GT(1))
    query2 = query1.copy()
    assert str(query1) == str(query2)

示例#10

文件： test_sqlpuzzle.py 项目： horejsek/python-sqlpuzzle

def test_select_from_with_more():
    sql = sqlpuzzle.select_from('user', 'country')
    assert str(sql) == 'SELECT * FROM "user", "country"'

示例#11

文件： test_sqlpuzzle.py 项目： horejsek/python-sqlpuzzle

def test_select_from_as_kwds():
    sql = sqlpuzzle.select_from(user='******')
    assert str(sql) == 'SELECT * FROM "user" AS "u"'

示例#12

文件： test_sqlpuzzle.py 项目： horejsek/python-sqlpuzzle

def test_select_from():
    sql = sqlpuzzle.select_from('user')
    assert str(sql) == 'SELECT * FROM "user"'

示例#13

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

示例#14

文件： sqlite.py 项目： horejsek/python-sqlpuzzle

 def test_boolean(self):
     sql = sqlpuzzle.select_from("table").where({"flag": True})
     self.assertEqual(str(sql), 'SELECT * FROM "table" WHERE "flag" = 1')

示例#15

文件： test_sqlvalue.py 项目： vikipha/python-sqlpuzzle

def test_security_subselect():
    select = sqlpuzzle.select_from('table')
    assert str(SqlReference(select)) == '(SELECT * FROM "table")'

示例#16

文件： limit.py 项目： fm4d/python-sqlpuzzle

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

示例#17

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

示例#18

文件： test_sqlpuzzle.py 项目： horejsek/python-sqlpuzzle

def test_copy_with_custom():
    query1 = sqlpuzzle.select_from('t').where(sqlpuzzle.customsql('x'))
    query2 = query1.copy()
    assert str(query1) == str(query2)

示例#19

文件： subselect.py 项目： 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:

示例#20

 def test_subselect(self):
     select = sqlpuzzle.select_from('table')
     self.assertEqual(str(SqlReference(select)), '(SELECT * FROM "table")')

示例#21

文件： sqlpuzzletest.py 项目： lgsunnyvale/python-sqlpuzzle

 def test_select_from(self):
     sql = sqlpuzzle.select_from('user')
     self.assertEqual(str(sql), 'SELECT * FROM "user"')

示例#22

文件： test_postgresql.py 项目： horejsek/python-sqlpuzzle

def test_boolean(postgresql):
    sql = sqlpuzzle.select_from('table').where({'flag': True})
    assert str(sql) == 'SELECT * FROM "table" WHERE "flag" = true'

示例#23

文件： sqlpuzzletest.py 项目： lgsunnyvale/python-sqlpuzzle

 def test_copy_with_custom(self):
     query1 = sqlpuzzle.select_from('t').where(sqlpuzzle.customsql('x'))
     query2 = query1.copy()
     self.assertEquals(str(query1), str(query2))

示例#24

文件： mysql.py 项目： lgsunnyvale/python-sqlpuzzle

 def test_boolean(self):
     sql = sqlpuzzle.select_from('table').where({'flag': True})
     self.assertEqual(str(sql), 'SELECT * FROM `table` WHERE `flag` = 1')

示例#25

文件： test_sqlvalue.py 项目： vikipha/python-sqlpuzzle

def test_subselect():
    select = sqlpuzzle.select_from('table')
    assert str(SqlValue(select)) == '(SELECT * FROM "table")'

示例#26

文件： test_mysql.py 项目： vikipha/python-sqlpuzzle

def test_boolean(mysql):
    sql = sqlpuzzle.select_from('table').where({'flag': True})
    assert str(sql) == 'SELECT * FROM `table` WHERE `flag` = 1'

示例#27

文件： test_sqlpuzzle.py 项目： vikipha/python-sqlpuzzle

def test_copy1():
    query1 = sqlpuzzle.select_from('t').where('c', sqlpuzzle.relations.GT(1))
    query2 = query1.copy()
    assert str(query1) == str(query2)

示例#28

文件： group_by.py 项目： vidyacraghav/python-sqlpuzzle

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

示例#29

文件： limit.py 项目： lgsunnyvale/python-sqlpuzzle

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

示例#30

def test_boolean(postgresql):
    sql = sqlpuzzle.select_from('table').where({'flag': True})
    assert str(sql) == 'SELECT * FROM "table" WHERE "flag" = true'

示例#31

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

示例#32

文件： test_where.py 项目： vikipha/python-sqlpuzzle

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

示例#33

文件： union.py 项目： 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`)

示例#34

文件： exceptions.py 项目： fm4d/python-sqlpuzzle

#
# 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.

示例#35

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

示例#36

文件： sqlpuzzletest.py 项目： fm4d/python-sqlpuzzle

 def test_select_from(self):
     sql = sqlpuzzle.select_from('user')
     self.assertEqual(str(sql), 'SELECT * FROM "user"')

示例#37

文件： sqlpuzzletest.py 项目： fm4d/python-sqlpuzzle

 def test_select_from_as_kwds(self):
     sql = sqlpuzzle.select_from(user='******')
     self.assertEqual(str(sql), 'SELECT * FROM "user" AS "u"')

示例#38

文件： sqlpuzzletest.py 项目： fm4d/python-sqlpuzzle

 def test_select_from_with_more(self):
     sql = sqlpuzzle.select_from('user', 'country')
     self.assertEqual(str(sql), 'SELECT * FROM "user", "country"')

示例#39

文件： test_mysql.py 项目： horejsek/python-sqlpuzzle

def test_boolean(mysql):
    sql = sqlpuzzle.select_from('table').where({'flag': True})
    assert str(sql) == 'SELECT * FROM `table` WHERE `flag` = 1'

示例#40

文件： sqlpuzzletest.py 项目： fm4d/python-sqlpuzzle

 def test_copy1(self):
     query1 = sqlpuzzle.select_from('t').where('c', sqlpuzzle.relations.GT(1))
     query2 = query1.copy()
     self.assertEquals(str(query1), str(query2))

示例#41

文件： test_tables.py 项目： vikipha/python-sqlpuzzle

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

示例#42

文件： sqlpuzzletest.py 项目： fm4d/python-sqlpuzzle

 def test_copy_with_custom(self):
     query1 = sqlpuzzle.select_from('t').where(sqlpuzzle.customsql('x'))
     query2 = query1.copy()
     self.assertEquals(str(query1), str(query2))

示例#43

文件： sqlpuzzletest.py 项目： lgsunnyvale/python-sqlpuzzle

 def test_select_from_as_kwds(self):
     sql = sqlpuzzle.select_from(user='******')
     self.assertEqual(str(sql), 'SELECT * FROM "user" AS "u"')

示例#44

文件： union.py 项目： 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`)

示例#45

文件： sqlpuzzletest.py 项目： lgsunnyvale/python-sqlpuzzle

 def test_copy1(self):
     query1 = sqlpuzzle.select_from('t').where('c',
                                               sqlpuzzle.relations.GT(1))
     query2 = query1.copy()
     self.assertEquals(str(query1), str(query2))

示例#46

文件： test_sqlpuzzle.py 项目： vikipha/python-sqlpuzzle

def test_select_from():
    sql = sqlpuzzle.select_from('user')
    assert str(sql) == 'SELECT * FROM "user"'

示例#47

文件： sqlvaluetest.py 项目： fm4d/python-sqlpuzzle

 def test_subselect(self):
     select = sqlpuzzle.select_from('table')
     self.assertEqual(str(SqlReference(select)), '(SELECT * FROM "table")')

示例#48

文件： test_sqlpuzzle.py 项目： vikipha/python-sqlpuzzle

def test_select_from_as_kwds():
    sql = sqlpuzzle.select_from(user='******')
    assert str(sql) == 'SELECT * FROM "user" AS "u"'

示例#49

文件： test_tables.py 项目： horejsek/python-sqlpuzzle

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

示例#50

 def test_boolean(self):
     sql = sqlpuzzle.select_from('table').where({'flag': True})
     self.assertEqual(str(sql), 'SELECT * FROM "table" WHERE "flag" = true')