SQLAlchemy helps you to simply use strings, for the ones instances when the SQL is already recognized and there isn’t a robust want for the declaration to help dynamic features. The textual content() construct is used to compose a textual statement this is surpassed to the database in the main unchanged.
It constructs a new TextClause, representing a textual SQL string immediately as proven inside the below code −
from sqlalchemy import text
t = text("SELECT * FROM students")
result = connection.execute(t)
The benefits text() affords over a plain string are −
- backend-impartial support for bind parameters
- per-assertion execution alternatives
- result-column typing behaviour
The textual content()characteristic requires Bound parameters inside the named colon format. They are constant irrespective of database backend. To send values in for the parameters, we bypass them into the execute() method as extra arguments.
The following instance makes use of sure parameters in textual SQL −
from sqlalchemy.sql import text
s = text("select students.name, students.lastname from students where students.name between :x and :y")
conn.execute(s, x = 'A', y = 'L').fetchall()
The text() characteristic constructs SQL expression as follows −
select students.name, students.lastname from students where students.name between ? and ?
The values of x = ’A’ and y = ’L’ are surpassed as parameters. Result is a list of rows with names among ‘A’ and ‘L’ −
[('Komal', 'Bhandari'), ('Abdul', 'Sattar')]
The text() assemble helps pre-installed bound values the usage of the TextClause.Bindparams() method. The parameters also can be explicitly typed as follows −
stmt = text("SELECT * FROM students WHERE students.name BETWEEN :x AND :y")
stmt = stmt.bindparams(
bindparam("x", type_= String),
bindparam("y", type_= String)
)
result = conn.execute(stmt, {"x": "A", "y": "L"})
The text() function also be produces fragments of SQL within a select() object that
accepts text() objects as an arguments. The “geometry” of the statement is provided by
select() construct , and the textual content by text() construct. We can build a statement
without the need to refer to any pre-established Table metadata.
from sqlalchemy.sql import select
s = select([text("students.name, students.lastname from students")]).where(text("students.name between :x and :y"))
conn.execute(s, x = 'A', y = 'L').fetchall()
You also can use and_() characteristic to mix more than one conditions in WHERE clause created with the assist of text() function.
from sqlalchemy import and_
from sqlalchemy.sql import select
s = select([text("* from students")]) \
.where(
and_(
text("students.name between :x and :y"),
text("students.id>2")
)
)
conn.execute(s, x = 'A', y = 'L').fetchall()
Above code fetches rows with names between “A” and “L” with identity more than 2. The output of the code is given under −
[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar')]