Python Table.select方法代码示例

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
class OOQuery(object):
    def __init__(self, table, foreign_key=None):
        self.fields = None
        self.table = Table(table)
        self._select = self.table.select()
        self.parser = Parser(self.table, foreign_key)

    @property
    def select_on(self):
        if self.parser.joins:
            return self.parser.joins[-1]
        else:
            return self.table

    def select(self, fields=None):
        if fields:
            self.fields = [getattr(self.table, arg) for arg in fields]
            self._select = self.table.select(*self.fields)
        return self

    def where(self, domain):
        where = self.parser.parse(domain)
        self._select = self.select_on.select(*self.fields)
        self._select.where = where
        return self._select

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
 def test_update_subselect(self):
     t1 = Table('t1')
     t2 = Table('t2')
     query_list = t1.update([t1.c], [t2.select(t2.c, where=t2.i == t1.i)])
     query_nolist = t1.update([t1.c], t2.select(t2.c, where=t2.i == t1.i))
     for query in [query_list, query_nolist]:
         self.assertEqual(str(query),
             'UPDATE "t1" SET "c" = ('
             'SELECT "b"."c" FROM "t2" AS "b" WHERE ("b"."i" = "t1"."i"))')
         self.assertEqual(query.params, ())

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
def login_from_id(user_id):
    with Transaction().new_transaction(readonly=True) as t:
        cursor = t.connection.cursor()
        table = Table('res_user')
        cursor.execute(*table.select(table.login,
                where=table.id == user_id))
        return cursor.fetchone()[0]

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
 def test_insert_subselect(self):
     t1 = Table("t1")
     t2 = Table("t2")
     subquery = t2.select(t2.c1, t2.c2)
     query = t1.insert([t1.c1, t1.c2], subquery)
     self.assertEqual(str(query), 'INSERT INTO "t1" ("c1", "c2") ' 'SELECT "a"."c1", "a"."c2" FROM "t2" AS "a"')
     self.assertEqual(query.params, ())

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
    def __register__(cls, module_name):
        cursor = Transaction().cursor
        model_data = Table('ir_model_data')

        # Migration from 3.0: new tax rates
        if module_name == 'account_nl':
            for old_id, new_id in (
                    ('tva_vente_19_6', 'tva_vente_taux_normal'),
                    ('tva_vente_7', 'tva_vente_taux_intermediaire'),
                    ('tva_vente_intracommunautaire_19_6',
                        'tva_vente_intracommunautaire_taux_normal'),
                    ('tva_vente_intracommunautaire_7',
                        'tva_vente_intracommunautaire_taux_intermediaire'),
                    ('tva_achat_19_6', 'tva_achat_taux_normal'),
                    ('tva_achat_7', 'tva_achat_taux_intermediaire'),
                    ('tva_achat_intracommunautaire_19_6',
                        'tva_achat_intracommunautaire_taux_normal'),
                    ):
                cursor.execute(*model_data.select(model_data.id,
                        where=(model_data.fs_id == new_id)
                        & (model_data.module == module_name)))
                if cursor.fetchone():
                    continue
                cursor.execute(*model_data.update(
                        columns=[model_data.fs_id],
                        values=[new_id],
                        where=(model_data.fs_id == old_id)
                        & (model_data.module == module_name)))

        super(TaxTemplate, cls).__register__(module_name)

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
    def __register__(cls, module_name):
        TableHandler = backend.get('TableHandler')
        cursor = Transaction().cursor

        super(ProductMedia, cls).__register__(module_name)

        media_table = cls.__table__()

        if TableHandler.table_exist(cursor, 'product_product_imageset'):
            # Migrate data from ProductImageSet table to ProductMedia table
            imageset_table = Table('product_product_imageset')

            cursor.execute(*media_table.insert(
                columns=[
                    media_table.sequence,
                    media_table.product, media_table.template,
                    media_table.static_file,
                ],
                values=imageset_table.select(
                    Literal(10),
                    imageset_table.product, imageset_table.template,
                    imageset_table.image
                )
            ))

            TableHandler.drop_table(
                cursor, 'product.product.imageset', 'product_product_imageset',
                cascade=True
            )

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
    def test_0050_query_pagination(self):
        """
        Test pagination via `nereid.contrib.QueryPagination.`
        """
        with Transaction().start(DB_NAME, USER, CONTEXT):
            self.setup_defaults()

            # Create a 100 addresses
            for id in xrange(0, 100):
                self.address_obj.create([{
                    'party': self.guest_party,
                    'name': 'User %s' % id,
                }])

            table = Table('party_address')
            select_query = table.select()

            pagination = QueryPagination(
                self.address_obj, select_query, table, page=1, per_page=10
            )

            self.assertEqual(pagination.count, 100)
            self.assertEqual(pagination.pages, 10)
            self.assertEqual(pagination.begin_count, 1)
            self.assertEqual(pagination.end_count, 10)

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
    def test_in(self):
        in_ = In(self.table.c1, [self.table.c2, 1, None])
        self.assertEqual(str(in_), '("c1" IN ("c2", %s, %s))')
        self.assertEqual(in_.params, (1, None))

        t2 = Table('t2')
        in_ = In(self.table.c1, t2.select(t2.c2))
        self.assertEqual(str(in_),
            '("c1" IN (SELECT "a"."c2" FROM "t2" AS "a"))')
        self.assertEqual(in_.params, ())

        in_ = In(self.table.c1, t2.select(t2.c2) | t2.select(t2.c3))
        self.assertEqual(str(in_),
            '("c1" IN (SELECT "a"."c2" FROM "t2" AS "a" '
            'UNION SELECT "a"."c3" FROM "t2" AS "a"))')
        self.assertEqual(in_.params, ())

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
 def test_order_query(self):
     table = Table('t')
     column = Column(table, 'c')
     query = table.select(column)
     self.assertEqual(str(Asc(query)),
         '(SELECT "a"."c" FROM "t" AS "a") ASC')
     self.assertEqual(str(Desc(query)),
         '(SELECT "a"."c" FROM "t" AS "a") DESC')

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
 def test_delete3(self):
     t1 = Table('t1')
     t2 = Table('t2')
     query = t1.delete(where=(t1.c.in_(t2.select(t2.c))))
     self.assertEqual(str(query),
         'DELETE FROM "t1" WHERE ("c" IN ('
         'SELECT "a"."c" FROM "t2" AS "a"))')
     self.assertEqual(query.params, ())

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
    def test_with(self):
        t1 = Table('t1')
        w = With(query=t1.select(t1.c1))

        query = self.table.delete(with_=[w],
            where=self.table.c2.in_(w.select(w.c3)))
        self.assertEqual(str(query),
            'WITH a AS (SELECT "b"."c1" FROM "t1" AS "b") '
            'DELETE FROM "t" WHERE ("c2" IN (SELECT "a"."c3" FROM a AS "a"))')
        self.assertEqual(query.params, ())

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
    def test_lateral_select(self):
        t1 = Table('t1')
        t2 = Table('t2')
        lateral = Lateral(t2.select(where=t2.id == t1.t2))
        query = From([t1, lateral]).select()

        self.assertEqual(str(query),
            'SELECT * FROM "t1" AS "a", LATERAL '
            '(SELECT * FROM "t2" AS "c" WHERE ("c"."id" = "a"."t2")) AS "b"')
        self.assertEqual(query.params, ())

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
 def test_join_subselect(self):
     t1 = Table('t1')
     t2 = Table('t2')
     select = t2.select()
     join = Join(t1, select)
     join.condition = t1.c == select.c
     with AliasManager():
         self.assertEqual(str(join),
             '"t1" AS "a" INNER JOIN (SELECT * FROM "t2" AS "c") AS "b" '
             'ON ("a"."c" = "b"."c")')
         self.assertEqual(join.params, ())

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
    def test_with(self):
        t1 = Table('t1')
        w = With(query=t1.select())

        query = self.table.insert(
            [self.table.c1],
            with_=[w],
            values=w.select())
        self.assertEqual(str(query),
            'WITH "a" AS (SELECT * FROM "t1" AS "b") '
            'INSERT INTO "t" ("c1") SELECT * FROM "a" AS "a"')
        self.assertEqual(query.params, ())

# 需要导入模块: from sql import Table [as 别名]
# 或者: from sql.Table import select [as 别名]
    def test_with(self):
        t1 = Table('t1')
        w = With(query=t1.select(t1.c1))

        query = self.table.update(
            [self.table.c2],
            with_=[w],
            values=[w.select(w.c3, where=w.c4 == 2)])
        self.assertEqual(str(query),
            'WITH b AS (SELECT "c"."c1" FROM "t1" AS "c") '
            'UPDATE "t" SET "c2" = (SELECT "b"."c3" FROM b AS "b" '
            'WHERE ("b"."c4" = %s))')
        self.assertEqual(query.params, (2,))