SQLAlchemy Core - Selecting Rows

Advertisements

Previous Page
Next Page  

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