Python sql.text方法代碼示例

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def tabularasa(self):
        """Truncate all tables.

        Used for testing to truncate all tables so the database is clean.
        """
        table_names = [
            'tasks',
            'result_message',
            'active_instance',
            'boot_action',
            'boot_action_status',
            'build_data',
        ]

        with self.db_engine.connect() as conn:
            for t in table_names:
                query_text = sql.text(
                    "TRUNCATE TABLE %s" % t).execution_options(autocommit=True)
                conn.execute(query_text) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def get_now(self):
        """Query the database for now() from dual.
        """
        try:
            with self.db_engine.connect() as conn:
                query = sql.text("SELECT now()")
                rs = conn.execute(query)

                r = rs.first()

                if r is not None and r.now:
                    return r.now
                else:
                    return None
        except Exception as ex:
            self.logger.error(str(ex))
            self.logger.error("Error querying for now()", exc_info=True)
            return None 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def define_constraint_cascades(self, constraint):
        text = ""
        if constraint.ondelete is not None:
            text += " ON DELETE %s" % constraint.ondelete

        # oracle has no ON UPDATE CASCADE -
        # its only available via triggers
        # http://asktom.oracle.com/tkyte/update_cascade/index.html
        if constraint.onupdate is not None:
            util.warn(
                "Oracle does not contain native UPDATE CASCADE "
                "functionality - onupdates will not be rendered for foreign "
                "keys.  Consider using deferrable=True, initially='deferred' "
                "or triggers.")

        return text 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def get_view_definition(self, connection, view_name, schema=None,
                            resolve_synonyms=False, dblink='', **kw):
        info_cache = kw.get('info_cache')
        (view_name, schema, dblink, synonym) = \
            self._prepare_reflection_args(connection, view_name, schema,
                                          resolve_synonyms, dblink,
                                          info_cache=info_cache)

        params = {'view_name': view_name}
        text = "SELECT text FROM all_views WHERE view_name=:view_name"

        if schema is not None:
            text += " AND owner = :schema"
            params['schema'] = schema

        rp = connection.execute(sql.text(text), **params).scalar()
        if rp:
            if util.py2k:
                rp = rp.decode(self.encoding)
            return rp
        else:
            return None 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def get_table_names(self, connection, schema=None, **kw):
        if schema is None:
            schema = self.default_schema_name

        TABLE_SQL = text("""
          SELECT o.name AS name
          FROM sysobjects o JOIN sysusers u ON o.uid = u.uid
          WHERE u.name = :schema_name
            AND o.type = 'U'
        """)

        if util.py2k:
            if isinstance(schema, unicode):
                schema = schema.encode("ascii")

        tables = connection.execute(TABLE_SQL, schema_name=schema)

        return [t["name"] for t in tables] 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def get_view_definition(self, connection, view_name, schema=None, **kw):
        if schema is None:
            schema = self.default_schema_name

        VIEW_DEF_SQL = text("""
          SELECT c.text
          FROM syscomments c JOIN sysobjects o ON c.id = o.id
          WHERE o.name = :view_name
            AND o.type = 'V'
        """)

        if util.py2k:
            if isinstance(view_name, unicode):
                view_name = view_name.encode("ascii")

        view = connection.execute(VIEW_DEF_SQL, view_name=view_name)

        return view.scalar() 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def get_view_names(self, connection, schema=None, **kw):
        if schema is None:
            schema = self.default_schema_name

        VIEW_SQL = text("""
          SELECT o.name AS name
          FROM sysobjects o JOIN sysusers u ON o.uid = u.uid
          WHERE u.name = :schema_name
            AND o.type = 'V'
        """)

        if util.py2k:
            if isinstance(schema, unicode):
                schema = schema.encode("ascii")
        views = connection.execute(VIEW_SQL, schema_name=schema)

        return [v["name"] for v in views] 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def _literal_as_text(element, warn=False):
    if isinstance(element, Visitable):
        return element
    elif hasattr(element, '__clause_element__'):
        return element.__clause_element__()
    elif isinstance(element, util.string_types):
        if warn:
            util.warn_limited(
                "Textual SQL expression %(expr)r should be "
                "explicitly declared as text(%(expr)r)",
                {"expr": util.ellipses_string(element)})

        return TextClause(util.text_type(element))
    elif isinstance(element, (util.NoneType, bool)):
        return _const_expr(element)
    else:
        raise exc.ArgumentError(
            "SQL expression object or string expected, got object of type %r "
            "instead" % type(element)
        ) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def get_scheduled_tasks(session):

	scheduled = session.execute(text("""SELECT id, next_run_time, job_state FROM apscheduler_jobs;"""))
	ret = list(scheduled)


	now = datetime.datetime.now(get_localzone())
	now_utc = datetime_to_utc_timestamp(now)

	ret = [(name, ts-now_utc, pickle.loads(value)) for name, ts, value in ret]


	for name, ts, value in ret:
		then = value['next_run_time'].astimezone(tz=None)
		# print((ts, now_utc, then, type(then)))
		now = datetime.datetime.now(datetime.timezone.utc)
		tgt = then - now
		value['time_til_job'] = tgt

	return ret 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def get_temp_table_names(self, connection, schema=None, **kwargs):
        schema = schema or self.default_schema_name

        result = connection.execute(
            sql.text(
                "SELECT TABLE_NAME FROM SYS.TABLES WHERE SCHEMA_NAME=:schema AND "
                "IS_TEMPORARY='TRUE' ORDER BY TABLE_NAME",
            ).bindparams(
                schema=self.denormalize_name(schema),
            )
        )

        temp_table_names = list([
            self.normalize_name(row[0]) for row in result.fetchall()
        ])
        return temp_table_names 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def get_pk_constraint(self, connection, table_name, schema=None, **kwargs):
        schema = schema or self.default_schema_name

        result = connection.execute(
            sql.text(
                "SELECT CONSTRAINT_NAME, COLUMN_NAME FROM SYS.CONSTRAINTS "
                "WHERE SCHEMA_NAME=:schema AND TABLE_NAME=:table AND "
                "IS_PRIMARY_KEY='TRUE' "
                "ORDER BY POSITION"
            ).bindparams(
                schema=self.denormalize_name(schema),
                table=self.denormalize_name(table_name)
            )
        )

        constraint_name = None
        constrained_columns = []
        for row in result.fetchall():
            constraint_name = row[0]
            constrained_columns.append(self.normalize_name(row[1]))

        return {
            "name": self.normalize_name(constraint_name),
            "constrained_columns": constrained_columns
        } 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def get_check_constraints(self, connection, table_name, schema=None, **kwargs):
        schema = schema or self.default_schema_name

        result = connection.execute(
            sql.text(
                "SELECT CONSTRAINT_NAME, CHECK_CONDITION FROM SYS.CONSTRAINTS "
                "WHERE SCHEMA_NAME=:schema AND TABLE_NAME=:table AND "
                "CHECK_CONDITION IS NOT NULL"
            ).bindparams(
                schema=self.denormalize_name(schema),
                table=self.denormalize_name(table_name)
            )
        )

        check_conditions = []

        for row in result.fetchall():
            check_condition = {
                "name": self.normalize_name(row[0]),
                "sqltext": self.normalize_name(row[1])
            }
            check_conditions.append(check_condition)

        return check_conditions 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def filtered_locations(self):
        locations = self.open_locations

        # now remove noncompliant ones
        compliant_locations = [location for location in locations if not location.is_reported_noncompliant]

        validate_pdf_urls(compliant_locations)
        valid_locations = [
            x for x in compliant_locations
            if x.pdf_url_valid
            and not (self.has_bad_doi_url and x.best_url == self.url)
            and x.endpoint_id != '01b84da34b861aa938d'  # lots of abstracts presented as full text. find a better way to do this.
            and x.endpoint_id != '58e562cef9eb07c3c1d'  # garbage PDFs in identifier tags
        ]

        return valid_locations 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def fulltext_search_title(query):
    query_statement = sql.text("""
      SELECT id, ts_headline('english', title, query), ts_rank_cd(to_tsvector('english', title), query, 32) AS rank
        FROM pub_2018, plainto_tsquery('english', :search_str) query  -- or try plainto_tsquery, phraseto_tsquery, to_tsquery
        WHERE to_tsvector('english', title) @@ query
        ORDER BY rank DESC
        LIMIT 50;""")

    rows = db.engine.execute(query_statement.bindparams(search_str=query)).fetchall()
    ids = [row[0] for row in rows]
    my_pubs = db.session.query(Pub).filter(Pub.id.in_(ids)).all()
    for row in rows:
        my_id = row[0]
        for my_pub in my_pubs:
            if my_id == my_pub.id:
                my_pub.snippet = row[1]
                my_pub.score = row[2]
    return my_pubs 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import text [as 別名]
def test_auto_literals(self):
        context = op_fixture(as_sql=True, literal_binds=True)
        from sqlalchemy.sql import table, column
        from sqlalchemy import String, Integer

        account = table(
            "account", column("name", String), column("id", Integer)
        )
        op.execute(
            account.update()
            .where(account.c.name == op.inline_literal("account 1"))
            .values({"name": op.inline_literal("account 2")})
        )
        op.execute(text("update table set foo=:bar").bindparams(bar="bat"))
        context.assert_(
            "UPDATE account SET name='account 2' "
            "WHERE account.name = 'account 1'",
            "update table set foo='bat'",
        )