Python:SQLAlchemy Example

 import sqlalchemy as db

from sqlalchemy.orm import sessionmaker

engine = db.create_engine("mysql+mysqlconnector://root:sangram#81@localhost/typeorm")

conn = engine.connect()

metadata = db.MetaData()

Student = db.Table('Student', metadata,

db.Column('Id', db.Integer(),primary_key=True),

db.Column('Name', db.String(255), nullable=False),

db.Column('Major', db.String(255), default="Math"),

db.Column('Pass', db.Boolean(), default=True)

)

StudentCity = db.Table('StudentCity', metadata,

db.Column('Id', db.Integer(),primary_key=True),

db.Column('City', db.String(255), nullable=False),

db.Column('StudentId',db.ForeignKey("Student.Id"))

)

# metadata.create_all(engine)

# query = db.insert(StudentCity)

# StudentCity_values_list = [

# {'Id':'1', 'City':'Mumbai',"StudentId":1},

# {'Id':'2', 'City':'Delhi',"StudentId":2},

# {'Id':'3', 'City':'Pune',"StudentId":3},

# {'Id':'4', 'City':'Banglore',"StudentId":4},

# ]

# StudentCity_Result = conn.execute(query,StudentCity_values_list)

# conn.commit()

# query = db.insert(Student).values(Id=1, Name='Matthew', Major="English", Pass=True)

# Result = conn.execute(query)

# query = db.insert(Student)

# values_list = [{'Id':'2', 'Name':'Nisha', 'Major':"Science", 'Pass':False},

# {'Id':'3', 'Name':'Natasha', 'Major':"Math", 'Pass':True},

# {'Id':'4', 'Name':'Ben', 'Major':"English", 'Pass':False}]

# Result = conn.execute(query,values_list)

# conn.commit()

# output = conn.execute(db.select(Student))

# rows = output.fetchall()

# output = conn.execute(db.text("SELECT * FROM Student"))

# rows = output.fetchall()

# query = Student.select().where(Student.columns.Major == 'English')

# output = conn.execute(query)

# rows = output.fetchall()

# query = Student.select().where(db.and_(Student.columns.Major == 'English', Student.columns.Pass != True))

# output = conn.execute(query)

# rows = output.fetchall()

# output = conn.execute(db.select(Student.columns.Major.distinct()))

# rows = output.fetchall()

# output = conn.execute(db.select(db.func.sum(Student.columns.Id),Student.columns.Major).group_by(Student.columns.Major))

# rows = output.fetchall()

# rows = output.fetchall()

# headers= output.keys()

# for header in headers:

# print(header + " ",end="")

# print()

# for row in rows:

# for col in row:

# print(str(col) + " ",end="")

# print()

Session = sessionmaker(bind=engine)

session = Session()

inner_join_query = session.query(Student.columns.Name, StudentCity.columns.City,Student.columns.Id).select_from(Student).join(StudentCity, Student.columns.Id == StudentCity.columns.StudentId)

header_ = inner_join_query.statement.columns.keys()

for header in header_:

print(header + " ",end="")

print()

for row in inner_join_query:

for col in row:

print(str(col) + " ",end="")

print()

session.close()