python数据库操作封装_python 笔记 之 复习-数据库操作-封装的操作

'''

复习

数据库操作-封装的操作

'''

from sqlalchemy import create_engine, Integer, String, Column

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Student(Base):

__tablname__ = "student"

id = Column(Integer,primary_key=True)

name = Column(String(50))

age = Column(Integer)

address = Column(String(50))

# 增

def insert(session):

st = Student(id=1005, name='nnn', age=16,

address='zhangjiang')

session.add(st)

session.commit()

#改

def update(session):

st1 = session.query(Student).filter(Student.id == 1001).one()

st1.name = "test"

session.commit()

#查

def select(session):

stu2 = session.query(Student).filter(Student.id == 1001).one()

print(stu2)

#删

def delete(session):

st1 = session.query(Student).filter(Student.id == 1001).delete()

session.commit()

#统计

def count(session):

number = session.query(Student).filter().count()

print("total Student is {0}".format(number))

#分组

def groupBy(session):

groupByName = session.query(Student).group_by(Student.name).one()

groupByAge = session.query(Student).group_by(Student.age).one()

print(groupByName)

print(groupByAge)

for i in groupByAge:

print(i.id,i.name,i.age,i.address)

#排序

def orderBy(session):

orderBy = session.query(Student).order_by(Student.age).all()

orderByDesc = session.query(Student).order_by(Student.age.desc()).all() #反序

print(orderBy)

for i in orderByDesc:

print(i.id,i.name,i.age,i.address)

#初始化，链接数据库，调用函数

def main():

engine = create_engine('mysql+pymysql://chen:123456@192.168.4.10/sqlalchemy')

DBsession = sessionmaker(bind=engine)

session = DBsession()

select(session)

update(session)

select(session)

delete(session)

count(session)

groupBy(session)

orderBy(session)

if __name__ == '__main__':

main()