Python sqlalchemy.sql.text() Examples

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 

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 

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

def upgrade():
    conn = op.get_bind()
    results = conn.execute("select service_id, organisation_id from organisation_to_service")
    org_to_service = results.fetchall()
    for x in org_to_service:
        sql = """
            UPDATE services
               SET organisation_id = :organisation_id
            WHERE id = :service_id
        """
        conn.execute(text(sql), service_id=str(x.service_id), organisation_id=str(x.organisation_id))
        history_sql = """
            UPDATE services_history
               SET organisation_id = :organisation_id
            WHERE id = :service_id
              AND version = (select max(version) from services_history sh2 where id = services_history.id); 
        """
        conn.execute(text(history_sql), service_id=str(x.service_id), organisation_id=str(x.organisation_id)) 

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 

def downgrade():
    conn = op.get_bind()

    results = conn.execute("select id, organisation_id from services where organisation_id is not null")
    services = results.fetchall()
    results_2 = conn.execute("select service_id, organisation_id from organisation_to_service")
    org_to_service = results_2.fetchall()

    for x in services:
        os = [y for y in org_to_service if y.service_id == x.id]
        if len(os) == 1:
            update_sql = """
                UPDATE organisation_to_service
                   SET organisation_id = :organisation_id
                 WHERE service_id = :service_id
            """
            conn.execute(text(update_sql), service_id=str(x.id), organisation_id=str(x.organisation_id))
        elif len(os) == 0:
            insert_sql = """
                INSERT INTO organisation_to_service(service_id, organisation_id) VALUES(:service_id, :organisation_id)
            """
            conn.execute(text(insert_sql), service_id=str(x.id), organisation_id=str(x.organisation_id))
        else:
            raise Exception("should only have 1 row. Service_id {},  orgid: {}".format(x.id, x.organisation_id)) 

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 

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 

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 

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] 

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

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] 

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

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 

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
        } 

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 

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) 

def set_task_status_text(self, task, text):
        task.status_text = text
        self.session.commit() 

def test_add_ck_constraint(self):
        with self.op.batch_alter_table("foo", recreate="always") as batch_op:
            batch_op.create_check_constraint("newck", text("x > 0"))

        # we dont support reflection of CHECK constraints
        # so test this by just running invalid data in
        foo = self.metadata.tables["foo"]

        assert_raises_message(
            exc.IntegrityError,
            "newck",
            self.conn.execute,
            foo.insert(),
            {"id": 6, "data": 5, "x": -2},
        ) 

def get_table_comment(self, connection, table_name, schema=None, **kwargs):
        schema = schema or self.default_schema_name

        result = connection.execute(
            sql.text(
                "SELECT COMMENTS FROM SYS.TABLES WHERE SCHEMA_NAME=:schema AND TABLE_NAME=:table"
            ).bindparams(
                schema=self.denormalize_name(schema),
                table=self.denormalize_name(table_name),
            )
        )

        return {"text" : result.scalar()} 

def get_table_oid(self, connection, table_name, schema=None, **kwargs):
        schema = schema or self.default_schema_name

        result = connection.execute(
            sql.text(
                "SELECT TABLE_OID FROM SYS.TABLES "
                "WHERE SCHEMA_NAME=:schema AND TABLE_NAME=:table"
            ).bindparams(
                schema=self.denormalize_name(schema),
                table=self.denormalize_name(table_name)
            )
        )
        return result.scalar() 

def test_add_server_default_int(self):
        self._run_alter_col({"type": Integer}, {"server_default": text("5")}) 

def get_unique_constraints(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_UNIQUE_KEY='TRUE' AND IS_PRIMARY_KEY='FALSE'"
                "ORDER BY CONSTRAINT_NAME, POSITION"
            ).bindparams(
                schema=self.denormalize_name(schema),
                table=self.denormalize_name(table_name)
            )
        )

        constraints = []
        parsing_constraint = None
        for constraint_name, column_name in result.fetchall():
            if parsing_constraint != constraint_name:
                # Start with new constraint
                parsing_constraint = constraint_name

                constraint = {'name': None, 'column_names': [], 'duplicates_index': None}
                if not constraint_name.startswith('_SYS'):
                    # Constraint has user-defined name
                    constraint['name'] = self.normalize_name(constraint_name)
                    constraint['duplicates_index'] = self.normalize_name(constraint_name)
                constraints.append(constraint)
            constraint['column_names'].append(self.normalize_name(column_name))

        return constraints 

def test_modify_server_default_int(self):
        self._run_alter_col(
            {"type": Integer, "server_default": text("2")},
            {"server_default": text("5")},
        ) 

def upgrade():
    conn = op.get_bind()
    conn.execute(text(
        """UPDATE letter_rates SET start_date = :start_date WHERE post_class = 'first'"""
    ), start_date=START_DATE) 

def get_foreign_keys(self, connection, table_name, schema=None, **kwargs):
        lookup_schema = schema or self.default_schema_name

        result = connection.execute(
            sql.text(
                "SELECT  CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_SCHEMA_NAME, "
                "REFERENCED_TABLE_NAME,  REFERENCED_COLUMN_NAME, UPDATE_RULE, DELETE_RULE "
                "FROM SYS.REFERENTIAL_CONSTRAINTS "
                "WHERE SCHEMA_NAME=:schema AND TABLE_NAME=:table "
                "ORDER BY CONSTRAINT_NAME, POSITION"
            ).bindparams(
                schema=self.denormalize_name(lookup_schema),
                table=self.denormalize_name(table_name)
            )
        )
        foreign_keys = []

        for row in result:

            foreign_key = {
                "name": self.normalize_name(row[0]),
                "constrained_columns": [self.normalize_name(row[1])],
                "referred_schema": schema,
                "referred_table": self.normalize_name(row[3]),
                "referred_columns": [self.normalize_name(row[4])],
                "options": {"onupdate": row[5],
                            "ondelete": row[6]}
            }

            if row[2] != self.denormalize_name(self.default_schema_name):
                foreign_key["referred_schema"] = self.normalize_name(row[2])

            foreign_keys.append(foreign_key)

        return foreign_keys 

def get_view_definition(self, connection, view_name, schema=None, **kwargs):
        schema = schema or self.default_schema_name

        return connection.execute(
            sql.text(
                "SELECT DEFINITION FROM SYS.VIEWS WHERE VIEW_NAME=:view_name AND SCHEMA_NAME=:schema LIMIT 1",
            ).bindparams(
                view_name=self.denormalize_name(view_name),
                schema=self.denormalize_name(schema),
            )
        ).scalar() 

def get_view_names(self, connection, schema=None, **kwargs):
        schema = schema or self.default_schema_name

        result = connection.execute(
            sql.text(
                "SELECT VIEW_NAME FROM SYS.VIEWS WHERE SCHEMA_NAME=:schema",
            ).bindparams(
                schema=self.denormalize_name(schema),
            )
        )

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

def has_table(self, connection, table_name, schema=None):
        schema = schema or self.default_schema_name

        result = connection.execute(
            sql.text(
                "SELECT 1 FROM SYS.TABLES "
                "WHERE SCHEMA_NAME=:schema AND TABLE_NAME=:table",
            ).bindparams(
                schema=self.denormalize_name(schema),
                table=self.denormalize_name(table_name)
            )
        )
        return bool(result.first()) 

def normalize(text):
    response = text.lower()
    response = unidecode(unicode(response))
    response = clean_html(response)  # has to be before remove_punctuation
    response = remove_punctuation(response)
    response = re.sub(ur"\b(a|an|the)\b", u"", response)
    response = re.sub(ur"\b(and)\b", u"", response)
    response = re.sub(u"\s+", u"", response)
    return response