SQLAlchemy查询相关操作
首先建立数据库表student,表中有sid字段(数值型、主键、自增),sname字段(字符串类型)、age字段(字符串类型)、sex字段(枚举型,“男”和“女”)。代码如下,
from sqlalchemy import create_engine, Column, String, Integer, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
HOSTNAME = '127.0.0.1'
PORT = '3306'
USERNAME = 'root'
PASSWORD = '******'
DATABASE = 'sqlalchemy'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()
class Student(Base):
__tablename__ = 'student'
sid = Column(Integer, primary_key=True, autoincrement=True)
sname = Column(String(10))
age = Column(Integer)
sex = Column(Enum('男', '女'))
def __repr__(self):
return "Student(sname:{}, sid:{}, age:{}, sex:{})".format(self.sname, self.sid, self.age, self.sex)
Base.metadata.drop_all()
Base.metadata.create_all()
插入下列数据,
student1 = Student(sname='zhao', age=22, sex='男')
student2 = Student(sname='qian', age=20, sex='男')
student3 = Student(sname='sun', age=23, sex='女')
student4 = Student(sname='li', age=21, sex='男')
student5 = Student(sname='zhou', age=26, sex='男')
student6 = Student(sname='wu', age=25, sex='女')
student7 = Student(sname='zheng', age=24, sex='男')
student8 = Student(sname='wang', age=32, sex='女')
student9 = Student(sname=