SQLAlchemy Core - Selecting Rows

In this chapter, we will discuss about the concept of selecting rows in the table object.

The select() method of table object enables us to construct SELECT expression.

s = students.select()

The select object translates to SELECT query by str(s) function as shown below −

 SELECT students.id, students.name, students.lastname FROM students 

We can use this select object as a parameter to execute() method of connection object as shown in the code below −

result = conn.execute(s)

When the above statement is executed, Python shell echoes following equivalent SQL expression −

SELECT students.id, students.name, students.lastname
FROM students

The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method.

row = result.fetchone()

All selected rows in the table can be printed by a for loop as given below −

for row in result:
   print (row)

The complete code to print all rows from students table is shown below −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine( sqpte:///college.db , echo = True)
meta = MetaData()

students = Table(
    students , meta, 
   Column( id , Integer, primary_key = True), 
   Column( name , String), 
   Column( lastname , String), 
)

s = students.select()
conn = engine.connect()
result = conn.execute(s)

for row in result:
   print (row)

The output shown in Python shell is as follows −

(1,  Ravi ,  Kapoor )
(2,  Rajiv ,  Khanna )
(3,  Komal ,  Bhandari )
(4,  Abdul ,  Sattar )
(5,  Priya ,  Rajhans )

The WHERE clause of SELECT query can be appped by using Select.where(). For example, if we want to display rows with id >2

s = students.select().where(students.c.id>2)
result = conn.execute(s)

for row in result:
   print (row)

Here c attribute is an apas for column. Following output will be displayed on the shell −

(3,  Komal ,  Bhandari )
(4,  Abdul ,  Sattar )
(5,  Priya ,  Rajhans )

Here, we have to note that select object can also be obtained by select() function in sqlalchemy.sql module. The select() function requires the table object as argument.

from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)