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(''sqlite:///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 applied 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 alias 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)