Python sql.Table类代码示例

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

    def init(self):
        from trytond.modules import get_module_info
        with self.get_connection() as conn:
            cursor = conn.cursor()
            sql_file = os.path.join(os.path.dirname(__file__), 'init.sql')
            with open(sql_file) as fp:
                for line in fp.read().split(';'):
                    if (len(line) > 0) and (not line.isspace()):
                        cursor.execute(line)

            ir_module = Table('ir_module')
            ir_module_dependency = Table('ir_module_dependency')
            for module in ('ir', 'res'):
                state = 'uninstalled'
                if module in ('ir', 'res'):
                    state = 'to install'
                info = get_module_info(module)
                insert = ir_module.insert(
                    [ir_module.create_uid, ir_module.create_date,
                        ir_module.name, ir_module.state],
                    [[0, CurrentTimestamp(), module, state]])
                cursor.execute(*insert)
                cursor.execute('SELECT last_insert_rowid()')
                module_id, = cursor.fetchone()
                for dependency in info.get('depends', []):
                    insert = ir_module_dependency.insert(
                        [ir_module_dependency.create_uid,
                            ir_module_dependency.create_date,
                            ir_module_dependency.module,
                            ir_module_dependency.name,
                            ],
                        [[0, CurrentTimestamp(), module_id, dependency]])
                    cursor.execute(*insert)
            conn.commit()

    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)

    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)

    def restore_default_party_lang_from_4_2(cls):
        from trytond.transaction import Transaction
        from sql import Null, Table, Cast
        from sql.operators import Concat
        from trytond.pool import Pool

        TableHandler = backend.get('TableHandler')
        if not TableHandler.table_exist('ir_property'):
            return

        pool = Pool()
        property = Table('ir_property')
        Lang = pool.get('ir.lang')
        field = pool.get('ir.model.field').__table__()
        lang = Lang.__table__()
        cursor = Transaction().connection.cursor()

        query_table = property.join(lang, condition=(
                property.value == Concat('ir.lang,', Cast(lang.id, 'VARCHAR'))
                )).join(field, condition=((property.field == field.id) &
                        (field.name == 'lang')))

        cursor.execute(
            *query_table.select(lang.id, where=property.res == Null))
        result = cursor.fetchone()
        if result:
            result = list(result)
            default_lang = Lang(result[0])
            print('Default Language restored [%s]' % default_lang.rec_name)
            pool.get('party.configuration.party_lang'
                ).create([{'party_lang': default_lang}])
        else:
            print('No default language on party configuration found')

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]

 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, ())

    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
            )

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

 def test_update2(self):
     t1 = Table('t1')
     t2 = Table('t2')
     query = t1.update([t1.c], ['foo'], from_=[t2], where=(t1.c == t2.c))
     self.assertEqual(str(query),
         'UPDATE "t1" AS "b" SET "c" = %s FROM "t2" AS "a" '
         'WHERE ("b"."c" = "a"."c")')
     self.assertEqual(query.params, ('foo',))

 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, ())

    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, ())

    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, ())

 def __register__(cls, module_name):
     cursor = Transaction().cursor
     model_data = Table('ir_model_data')
     # Migration from 1.6: corrected misspelling of ounce (was once)
     cursor.execute(*model_data.update(
             columns=[model_data.fs_id],
             values=['uom_ounce'],
             where=(model_data.fs_id == 'uom_once')
             & (model_data.module == 'product')))
     super(Uom, cls).__register__(module_name)

    def migrate(self):
        obj = objectify.fromstring(self.content)
        t = Table('ir_model_data')
        for xml_record in obj.iter(tag='record'):
            record = self._record(xml_record)
            self.records.append(record)
            sp = []
            for field in self.search_params.get(record.model, record.vals.keys()):
                sp.append((field, '=', record.vals[field]))
            logger.info('Trying to find existing record with query: {}'.format(
                sp
            ))
            table = record.model.replace('.', '_')
            q = OOQuery(table)
            sql = q.select(['id']).where(sp)
            logger.debug(tuple(sql))
            self.cursor.execute(*sql)
            res_id = self.cursor.fetchone()
            if res_id:
                res_id = res_id[0]
                logger.info('Record {}.{} found! ({} id:{})'.format(
                    self.module, record.id, record.model, res_id
                ))
            else:
                logger.info('Record {}.{} not found!'.format(
                    self.module, record.id
                ))
                # We have to create the model
                table_model = Table(record.model.replace('.', '_'))
                columns = []
                values = []
                for col, value in record.vals.items():
                    columns.append(getattr(table_model, col))
                    values.append(value)

                sql = table_model.insert(
                    columns=columns, values=[values], returning=[table_model.id]
                )
                logger.debug(tuple(sql))
                self.cursor.execute(*sql)
                res_id = self.cursor.fetchone()[0]
                logger.info('Creating record {}.{} ({} id:{})'.format(
                    self.module, record.id, record.model, res_id
                ))

            sql = t.insert(
                columns=[t.name, t.model, t.noupdate, t.res_id, t.module],
                values=[(record.id, record.model, record.noupdate, res_id,
                         self.module)]
            )
            logger.debug(tuple(sql))
            logger.info('Linking model data {}.{} -> record {} id:{}'.format(
                self.module, record.id, record.model, res_id
            ))
            self.cursor.execute(*sql)