SQLAlchemy helps you to just use strings, for those cases while the SQL is already acknowledged and there isn’t a strong need for the declaration to support dynamic capabilities. The textual content() assemble is used to compose a textual assertion this is exceeded to the database mostly unchanged.
It constructs a new TextClause, representing a textual SQL string directly as proven within the underneath code −
from sqlalchemy import text
t = text("SELECT * FROM students")
result = connection.execute(t)
The benefits textual content() presents over a undeniable string are −
- backend-impartial aid for bind parameters
- in step with-announcement execution alternatives
- result-column typing behaviour
The text()feature calls for Bound parameters within the named colon layout. They are consistent irrespective of database backend. To ship values in for the parameters, we skip them into the execute() method as extra arguments.
The following example uses bound 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() function 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 handed as parameters. Result is a listing of rows with names between ‘A’ and ‘L’ −
[('Komal', 'Bhandari'), ('Abdul', 'Sattar')]
The text() construct supports pre-set up sure values the use of the TextClause.Bindparams() technique. The parameters can also 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 can also use and_() characteristic to combine a couple of conditions in WHERE clause created with the help 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 id extra than 2. The output of the code is given below −
[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar')]