SQLAlchemy
24
conn.execute(students.insert(), [
{'name':'Ravi', 'lastname':'Kapoor'},
{'name':'Rajiv', 'lastname' : 'Khanna'},
{'name':'Komal','lastname' : 'Bhandari'},
{'name':'Abdul','lastname' : 'Sattar'},
{'name':'Priya','lastname' : 'Rajhans'},
])
Rows are added in addresses table with the help of the following code:
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo=True)
meta=MetaData()
conn = engine.connect()
addresses=Table('addresses', meta, Column('id', Integer, primary_key=True),
Column('st_id', Integer), Column('postal_add', String), Column('email_add',
String))
conn.execute(addresses.insert(), [
{'st_id':1, 'postal_add':'Shivajinagar Pune',
{'st_id':1, 'postal_add':'ChurchGate Mumbai',
'email_add':'kapoor@gmail.com'},
{'st_id':3, 'postal_add':'Jubilee Hills Hyderabad',
'email_add':'komal@gmail.com'},
{'st_id':5, 'postal_add':'MG Road Bangaluru',
{'st_id':2, 'postal_add':'Cannought Place new Delhi',
'email_add':'admin@khanna.com'},
])
Note that the st_id column in addresses table refers to id column in students table. We
can now use this relation to fetch data from both the tables. We want to fetch name and
lastname from students table corresponding to st_id in the addresses table.
from sqlalchemy.sql import select
s = select([students, addresses]).where(students.c.id == addresses.c.st_id)
result=conn.execute(s)
for row in result:
print (row)
The select objects will effectively translate into following SQL expression joining two tables
on common relation: