sqlalchemy.select

Here are the examples of the python api sqlalchemy.select taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

159 Examples 7

Example 1

Project: cubes Source File: test_expression.py
    def assertExpressionEqual(self, left, right):
        """Asserts that the `left` and `right` statement expressions are equal
        by pulling out the data from the table and testing whether the
        returned sequences are equal."""

        stmt = sa.select([left.label("value")], from_obj=self.table)
        result = self.engine.execute(stmt)
        left_result = [row["value"] for row in result]


        stmt = sa.select([right.label("value")], from_obj=self.table)
        result = self.engine.execute(stmt)
        right_result = [row["value"] for row in result]

        self.assertCountEqual(left_result, right_result)

Example 2

Project: Flexget Source File: digest.py
Function: upgrade
@db_schema.upgrade('digest')
def upgrade(ver, session):
    if None is ver:
        ver = 0
    if ver == 0:
        table = table_schema('digest_entries', session)
        table_add_column(table, 'json', Unicode, session)
        # Make sure we get the new schema with the added column
        table = table_schema('digest_entries', session)
        for row in session.execute(select([table.c.id, table.c.entry])):
            try:
                p = pickle.loads(row['entry'])
                session.execute(table.update().where(table.c.id == row['id']).values(
                    json=json.dumps(p, encode_datetime=True)))
            except KeyError as e:
                log.error('Unable error upgrading backlog pickle object due to %s' % str(e))

        ver = 1
    return ver

Example 3

Project: blaze Source File: sql.py
Function: compute_up
@dispatch(Projection, Selectable)
def compute_up(expr, data, **kwargs):
    return compute(
        expr,
        sa.select([data]),
        post_compute=False,
        return_type='native',
    )

Example 4

Project: ibis Source File: test_sqlalchemy.py
Function: test_count_distinct
    def test_count_distinct(self):
        t = self.alltypes
        sat = self.sa_alltypes.alias('t0')

        cases = [
            (t.int_col.nunique().name('nunique'),
             sa.select([F.count(sat.c.int_col.distinct())
                        .label('nunique')])),
            (t.group_by('string_col')
             .aggregate(t.int_col.nunique().name('nunique')),
             sa.select([sat.c.string_col,
                        F.count(sat.c.int_col.distinct())
                        .label('nunique')])
             .group_by(sat.c.string_col)),
        ]
        for case, ex in cases:
            self._compare_sqla(case, ex)

Example 5

Project: odo Source File: test_sql.py
def test_copy_one_table_to_a_foreign_engine():
    data = [(1, 1), (2, 4), (3, 9)]
    ds = dshape('var * {x: int, y: int}')
    with tmpfile('db') as fn1:
        with tmpfile('db') as fn2:
            src = into('sqlite:///%s::points' % fn1, data, dshape=ds)
            tgt = into('sqlite:///%s::points' % fn2,
                       sa.select([src]), dshape=ds)

            assert into(set, src) == into(set, tgt)
            assert into(set, data) == into(set, tgt)

Example 6

Project: SickRage Source File: test_insert.py
    def test_last_inserted_id_implicit_returning(self):

        r = config.db.execute(
            self.tables.autoinc_pk.insert(),
            data="some data"
        )
        pk = config.db.scalar(select([self.tables.autoinc_pk.c.id]))
        eq_(
            r.inserted_primary_key,
            [pk]
        )

Example 7

Project: bottle-cork Source File: sqlalchemy_backend.py
Function: iter_items
    def iteritems(self):
        """Iterate over table rows"""
        query = select([self._table])
        result = self._engine.execute(query)
        for row in result:
            key = row[0]
            d = self._row_to_value(row)[1]
            yield (key, d)

Example 8

Project: SickGear Source File: test_types.py
Function: test_text_round_trip
    def test_text_roundtrip(self):
        text_table = self.tables.text_table

        config.db.execute(
            text_table.insert(),
            {"text_data": 'some text'}
        )
        row = config.db.execute(
                    select([text_table.c.text_data])
                ).first()
        eq_(row, ('some text',))

Example 9

Project: ocf Source File: db.py
Function: get_rspec
  def getRspec (self, sliver_urn):
    s = select([slivers], slivers.c.sliver_urn==sliver_urn)
    conn = self.connection()
    result = conn.execute(s)
    row = result.first()
    return row[slivers.c.req_rspec]

Example 10

Project: SickGear Source File: test_types.py
Function: test_round_trip
    def test_round_trip(self):
        date_table = self.tables.date_table

        config.db.execute(
            date_table.insert(),
            {'date_data': self.data}
        )

        row = config.db.execute(
                    select([
                            date_table.c.date_data,
                    ])
                ).first()

        compare = self.compare or self.data
        eq_(row,
            (compare, ))
        assert isinstance(row[0], type(compare))

Example 11

Project: ibis Source File: test_sqlalchemy.py
    def test_where_uncorrelated_subquery(self):
        expr = self._case_where_uncorrelated_subquery()

        foo = self._to_sqla(self.foo).alias('t0')
        bar = self._to_sqla(self.bar)

        subq = sa.select([bar.c.job])
        stmt = sa.select([foo]).where(foo.c.job.in_(subq))
        self._compare_sqla(expr, stmt)

Example 12

Project: odo Source File: test_sql.py
def test_empty_select_to_empty_frame():
    # data = [(1, 1), (2, 4), (3, 9)]
    ds = dshape('var * {x: int, y: int}')
    with tmpfile('db') as fn1:
        points = resource('sqlite:///%s::points' % fn1, dshape=ds)
        sel = sa.select([points])
        df = odo(sel, pd.DataFrame)
    assert df.empty
    assert df.columns.tolist() == ['x', 'y']

Example 13

Project: SickRage Source File: test_insert.py
    @requirements.fetch_rows_post_commit
    def test_explicit_returning_pk_autocommit(self):
        engine = config.db
        table = self.tables.autoinc_pk
        r = engine.execute(
            table.insert().returning(
                            table.c.id),
            data="some data"
        )
        pk = r.first()[0]
        fetched_pk = config.db.scalar(select([table.c.id]))
        eq_(fetched_pk, pk)

Example 14

Project: cubes Source File: demo.py
Function: rows
    def rows(self, table_name, columns=None):
        """Return an interable of rows from table `table_name`. If `columns`
        is specified then yield only those columns."""

        table = self.table(table_name)
        if columns:
            selection = [table.c[name] for name in columns]
        else:
            selection = table.columns

        select = sa.select(selection)

        return self.engine.execute(select)

Example 15

Project: SickGear Source File: test_select.py
    def test_composed_int_desc(self):
        table = self.tables.some_table
        lx = (table.c.x + table.c.y).label('lx')
        self._assert_result(
            select([lx]).order_by(lx.desc()),
            [(7, ), (5, ), (3, )]
        )

Example 16

Project: bottle-cork Source File: sqlalchemy_backend.py
Function: get_item
    def __getitem__(self, key):
        query = select([self._table], self._key_col == key)
        row = self._engine.execute(query).fetchone()
        if row is None:
            raise KeyError(key)
        return self._row_to_value(row)[1]

Example 17

Project: plenario Source File: point.py
def _null_malformed_geoms(existing):
    # We decide to set the geom to NULL when the given lon/lat is (0,0)
    # (off the coast of Africa).
    upd = existing.update().values(geom=None).\
        where(existing.c.geom == select([func.ST_SetSRID(func.ST_MakePoint(0, 0), 4326)]))
    engine.execute(upd)

Example 18

Project: fas Source File: json_request.py
Function: user_id
    @identity.require(turbogears.identity.not_anonymous())
    @expose("json", allow_json=True)
    def user_id(self):
        people = {}
        peoplesql = sqlalchemy.select([People.id, People.username])
        persons = peoplesql.execute()
        for person in persons:
            people[person[0]] = person[1]
        return dict(people=people)

Example 19

Project: SickGear Source File: test_select.py
Function: test_plain
    def test_plain(self):
        table = self.tables.some_table
        lx = table.c.x.label('lx')
        self._assert_result(
            select([lx]).order_by(lx),
            [(1, ), (2, ), (3, )]
        )

Example 20

Project: ocf Source File: db.py
Function: get_manifest
  def getManifest (self, sliver_urn):
    s = select([slivers], and_(slivers.c.sliver_urn==sliver_urn, slivers.c.deleted==False))
    conn = self.connection()
    result = conn.execute(s)
    row = result.first()
    return row[slivers.c.manifest_rspec]

Example 21

Project: odo Source File: test_mysql.py
Function: test_sql_select_to_csv
def test_sql_select_to_csv(sql, csv):
    sql = odo(csv, sql)
    query = sa.select([sql.c.a])
    with tmpfile('.csv') as fn:
        csv = odo(query, fn)
        assert odo(csv, list) == [(x,) for x, _ in data]

Example 22

Project: odo Source File: test_sql.py
def test_select_to_iterator():
    engine, t = single_table_engine()
    append(t, [('Alice', 100), ('Bob', 200)])

    sel = sa.select([t.c.amount + 1])

    assert convert(list, sel) == [(101,), (201,)]
    assert convert(list, sel, dshape=dshape('var * int')) == [101, 201]

    sel2 = sa.select([sa.sql.func.sum(t.c.amount)])

    assert convert(int, sel2, dshape=dshape('int')) == 300

    sel3 = sa.select([t])

    result = convert(list, sel3, dshape=discover(t))
    assert type(result[0]) is tuple
    for res in result:
        assert isinstance(res[0], string_types)

Example 23

Project: SickGear Source File: test_select.py
    def test_composed_int(self):
        table = self.tables.some_table
        lx = (table.c.x + table.c.y).label('lx')
        self._assert_result(
            select([lx]).order_by(lx),
            [(3, ), (5, ), (7, )]
        )

Example 24

Project: odo Source File: test_sql.py
def test_select_to_series_retains_name():
    data = [(1, 1), (2, 4), (3, 9)]
    ds = dshape('var * {x: int, y: int}')
    with tmpfile('db') as fn1:
        points = odo(data, 'sqlite:///%s::points' % fn1, dshape=ds)
        sel = sa.select([(points.c.x + 1).label('x')])
        series = odo(sel, pd.Series)
    assert series.name == 'x'
    assert odo(series, list) == [x + 1 for x, _ in data]

Example 25

Project: SickGear Source File: test_types.py
Function: test_null
    def test_null(self):
        date_table = self.tables.date_table

        config.db.execute(
            date_table.insert(),
            {'date_data': None}
        )

        row = config.db.execute(
                    select([
                            date_table.c.date_data,
                    ])
                ).first()
        eq_(row, (None,))

Example 26

Project: ibis Source File: test_sqlalchemy.py
Function: test_sort_by
    def test_sort_by(self):
        st = self.sa_star1.alias('t0')
        cases = self._case_sort_by()

        base = sa.select([st])
        expected = [
            base.order_by(st.c.f),
            base.order_by(st.c.f.desc()),
            base.order_by(st.c.c, st.c.f.desc()),
        ]
        for case, ex_sqla in zip(cases, expected):
            self._compare_sqla(case, ex_sqla)

Example 27

Project: ibis Source File: alchemy.py
def _table_column(t, expr):
    op = expr.op()
    ctx = t.context
    table = op.table

    sa_table = _get_sqla_table(ctx, table)
    out_expr = getattr(sa_table.c, op.name)

    # If the column does not originate from the table set in the current SELECT
    # context, we should format as a subquery
    if t.permit_subquery and ctx.is_foreign_expr(table):
        return sa.select([out_expr])

    return out_expr

Example 28

Project: SickRage Source File: test_insert.py
    @requirements.dbapi_lastrowid
    def test_native_lastrowid_autoinc(self):
        r = config.db.execute(
            self.tables.autoinc_pk.insert(),
            data="some data"
        )
        lastrowid = r.lastrowid
        pk = config.db.scalar(select([self.tables.autoinc_pk.c.id]))
        eq_(
            lastrowid, pk
        )

Example 29

Project: ibis Source File: test_sqlalchemy.py
    def test_array_distinct(self):
        t = self.alltypes
        sat = self.sa_alltypes.alias('t0')

        cases = [
            (t.string_col.distinct(),
             sa.select([sat.c.string_col.distinct()]))
        ]
        for case, ex in cases:
            self._compare_sqla(case, ex)

Example 30

Project: SickGear Source File: test_select.py
    def test_plain_desc(self):
        table = self.tables.some_table
        lx = table.c.x.label('lx')
        self._assert_result(
            select([lx]).order_by(lx.desc()),
            [(3, ), (2, ), (1, )]
        )

Example 31

Project: mindpark Source File: reader.py
    def _collect_columns(self, engine, table):
        result = engine.execute(sql.select([table]))
        columns = np.array([x for x in result]).T
        if not len(columns) or not columns.shape[1]:
            return None
        columns = Metric(
            id=np.array([int(x, 16) for x in columns[0]]),
            timestamp=columns[1],
            step=columns[2].astype(int),
            epoch=columns[3].astype(int),
            training=columns[4].astype(bool),
            episode=columns[5].astype(int),
            data=columns[6:].T.astype(float))
        columns = self._sort_columns(columns)
        return columns

Example 32

Project: SickRage Source File: test_insert.py
    def test_explicit_returning_pk_no_autocommit(self):
        engine = config.db
        table = self.tables.autoinc_pk
        with engine.begin() as conn:
            r = conn.execute(
                table.insert().returning(
                                table.c.id),
                data="some data"
            )
            pk = r.first()[0]
        fetched_pk = config.db.scalar(select([table.c.id]))
        eq_(fetched_pk, pk)

Example 33

Project: cubes Source File: test_browser.py
Function: select
    def select(self, attrs, whereclause=None):
        """Returns a select statement from the star view"""
        columns = [self.star.column(attr) for attr in attrs]

        return sa.select(columns,
                         from_obj=self.context.star,
                         whereclause=whereclause)

Example 34

Project: pgcontents Source File: query.py
def list_remote_checkpoints(db, user_id, api_path):
    db_path = from_api_filename(api_path)
    fields = _remote_checkpoint_default_fields()
    results = db.execute(
        select(fields).where(
            and_(
                remote_checkpoints.c.user_id == user_id,
                remote_checkpoints.c.path == db_path,
            ),
        ).order_by(
            desc(remote_checkpoints.c.last_modified),
        ),
    )

    return [to_dict_no_content(fields, row) for row in results]

Example 35

Project: sqlakeyset Source File: test_paging.py
def do_core_tests(dburl):
    spec = ['b', 'd', 'id', 'c']

    cols = [column(each) for each in spec]
    ob = [OC(x).uo for x in spec]

    with S(dburl, echo=ECHO) as s:
        selectable = select(
            cols,
            from_obj=[table('t_Book')],
            whereclause=column('d') == 99,
            order_by=ob)

        check_paging(selectable=selectable, s=s)

Example 36

Project: sqlalchemy-redshift Source File: test_delete_stmt.py
def test_delete_stmt_on_subquerycomma():
    del_stmt = sa.delete(
        ham
    ).where(
        ham.c.id.in_(
            sa.select(
                [hammy_spam.c.ham_id]
            )
        )
    )
    expected = """
        DELETE FROM ham
        WHERE ham.id IN
        (SELECT "ham, spam".ham_id
        FROM "ham, spam")"""
    assert clean(compile_query(del_stmt)) == clean(expected)

Example 37

Project: bottle-cork Source File: sqlalchemy_backend.py
Function: iter
    def __iter__(self):
        """Iterate over table index key values"""
        query = select([self._key_col])
        result = self._engine.execute(query)
        for row in result:
            key = row[0]
            yield key

Example 38

Project: SickGear Source File: test_types.py
Function: test_empty_strings
    def _test_empty_strings(self):
        unicode_table = self.tables.unicode_table

        config.db.execute(
            unicode_table.insert(),
            {"unicode_data": u('')}
        )
        row = config.db.execute(
                    select([unicode_table.c.unicode_data])
                ).first()
        eq_(row, (u(''),))

Example 39

Project: bottle-cork Source File: sqlalchemy_backend.py
Function: pop
    def pop(self, key):
        query = select([self._table], self._key_col == key)
        row = self._engine.execute(query).fetchone()
        if row is None:
            raise KeyError

        query = delete(self._table, self._key_col == key)
        self._engine.execute(query)
        return row

Example 40

Project: plenario Source File: ShapeMetadata.py
    def _get_num_shapes(self):
        table = self.shape_table
        # Arbitrarily select the first column of the table to count against
        count_query = select([func.count(table.c.geom)])
        # Should return only one row.
        # And we want the 0th and only attribute of that row (the count).
        return session.execute(count_query).fetchone()[0]

Example 41

Project: Flexget Source File: entry_list.py
Function: upgrade
@db_schema.upgrade('entry_list')
def upgrade(ver, session):
    if None is ver:
        ver = 0
    if ver == 0:
        table = table_schema('entry_list_entries', session)
        table_add_column(table, 'json', Unicode, session)
        # Make sure we get the new schema with the added column
        table = table_schema('entry_list_entries', session)
        for row in session.execute(select([table.c.id, table.c.entry])):
            try:
                p = pickle.loads(row['entry'])
                session.execute(table.update().where(table.c.id == row['id']).values(
                    json=json.dumps(p, encode_datetime=True)))
            except KeyError as e:
                log.error('Unable error upgrading entry_list pickle object due to %s' % str(e))

        ver = 1
    return ver

Example 42

Project: pychess Source File: database.py
    def get_movetext(self, gameno):
        selection = select([game.c.movelist, game.c.comments],
                           game.c.id == self.games[gameno][0])
        result = self.engine.execute(selection).first()
        self.comments = result[1].split("|")
        arr = array("H")
        arr.fromstring(result[0])
        return arr

Example 43

Project: Flexget Source File: cached_input.py
Function: upgrade
@db_schema.upgrade('input_cache')
def upgrade(ver, session):
    if ver == 0:
        table = table_schema('input_cache_entry', session)
        table_add_column(table, 'json', Unicode, session)
        # Make sure we get the new schema with the added column
        table = table_schema('input_cache_entry', session)
        for row in session.execute(select([table.c.id, table.c.entry])):
            try:
                p = pickle.loads(row['entry'])
                session.execute(table.update().where(table.c.id == row['id']).values(
                    json=json.dumps(p, encode_datetime=True)))
            except KeyError as e:
                log.error('Unable error upgrading input_cache pickle object due to %s' % str(e))
        ver = 1
    return ver

Example 44

Project: blaze Source File: sql.py
Function: compute_up
@dispatch(Summary, ClauseElement)
def compute_up(t, s, **kwargs):
    scope = {t._child: s}
    return sa.select(
        compute(
            value,
            scope,
            post_compute=None,
            return_type='native',
        ).label(name)
        for value, name in zip(t.values, t.fields)
    )

Example 45

Project: SickGear Source File: test_types.py
Function: test_text_empty_strings
    def test_text_empty_strings(self):
        text_table = self.tables.text_table

        config.db.execute(
            text_table.insert(),
            {"text_data": ''}
        )
        row = config.db.execute(
                    select([text_table.c.text_data])
                ).first()
        eq_(row, ('',))

Example 46

Project: regenesis Source File: dimension.py
Function: get_statistics
def get_statistics(dimension_name=None):
    ct = cube_table.table.alias('cube')
    st = statistic_table.table.alias('statistic')
    rt = reference_table.table.alias('reference')
    tables = [ct, st, rt]
    wheres = [
      st.c.name==ct.c.statistic_name,
      rt.c.cube_name==ct.c.name,
      rt.c.dimension_name==dimension_name
      ]
    q = select([st], and_(*wheres), tables, distinct=True)
    return list(engine.query(q))

Example 47

Project: pymssql Source File: test_sqlalchemy.py
    def test_pickle_type(self):
        s = SAObj(name='foobar', data=['one'])
        sess.add(s)
        sess.commit()
        res = sess.execute(sa.select([saotbl.c.data]))
        row = res.fetchone()
        eq_(row['data'], ['one'])

Example 48

Project: SickGear Source File: test_select.py
    def test_composed_multiple(self):
        table = self.tables.some_table
        lx = (table.c.x + table.c.y).label('lx')
        ly = (func.lower(table.c.q) + table.c.p).label('ly')
        self._assert_result(
            select([lx, ly]).order_by(lx, ly.desc()),
            [(3, util.u('q1p3')), (5, util.u('q2p2')), (7, util.u('q3p1'))]
        )

Example 49

Project: SickRage Source File: test_insert.py
    def test_last_inserted_id(self):

        r = config.db.execute(
            self.tables.autoinc_pk.insert(),
            data="some data"
        )
        pk = config.db.scalar(select([self.tables.autoinc_pk.c.id]))
        eq_(
            r.inserted_primary_key,
            [pk]
        )

Example 50

Project: ibis Source File: test_sqlalchemy.py
    def test_join_just_materialized(self):
        joined = self._case_join_just_materialized()

        rt, nt, ct = self._sqla_tables(['tpch_region', 'tpch_nation',
                                        'tpch_customer'])
        nt = nt.alias('t0')
        rt = rt.alias('t1')
        ct = ct.alias('t2')

        sqla_joined = (nt.join(rt, nt.c.n_regionkey == rt.c.r_regionkey)
                       .join(ct, nt.c.n_nationkey == ct.c.c_nationkey))

        expected = sa.select([sqla_joined])

        self._compare_sqla(joined, expected)
See More Examples - Go to Next Page
Page 1 Selected Page 2 Page 3 Page 4