MySQL学习笔记（六）
好耶，七天课程的最后一天！我当然还没精通了，，，之后可能是多练习题目然后再学学其他的东西吧。mysql新的知识点也会在后面补充的。
一、杂七杂八补充
1. 当多个函数共用同样的参数时，可以转变成类进行
面向对象：数据与逻辑组合在一起
函数编程：数据与逻辑分离
#进行意会的伪代码 class SqlHelper: def __init__(self): #需要重复使用的数据， 在__init__里面封装多个函数需要共同使用的东西 self.host = '' self.port ='' self.db ='' self.charset='' def exc1(self,SQL): # 连接 conn(self.host,) execute(SQL) return xx def exc2(self,proc_name): callproc(proc_name) return xxx
2. 类：提取共性
一类事物共同具有的属性和行为。如一张表里面如果只有id与name两列，那么对表的操作一定是对这两列的操作（额，我Python不知道如何对类进行操作，等我学了对类的操作后，我再返回来检查一下这里的代码）
class Userinfo: def __init__(self,id,name): #约束’每个对象中只有两个字段，即：每个行数据都有id和name列 self.id = id self.name= name def add(self,name): pass # row1 = UserInfo(1,'alex') # 第一行 # row2 = UserInfo(2,'alex') # 第二行
3. 每一个对象里面具体的特殊方法（我服了，云里雾里就是我！我还会再回来的！）
class Foo: def __init__(self,name): self.name = name def show(self): print(self.name) def __call__(self): pass def __getitem__(self,key): pass def __setitem__(self,key,value): pass def __delitem__(self,key): pass obj1 = Foo('eric') obj1() #这个是Python中的特殊用法 obj1['k'] obj1['k'] = 123 del obj[k] #删除 obj.__dict__
二、ORM框架：SQLAlchemy
1. 作用
提供简单规则，自动转换成SQL语句。
2. 两类框架
（1）DB first
先手动创建数据库与表，根据数据库里面的表自动生成类
（2）code first
手动创建类与数据库，自动生成与表（ORM所属类别）
3. 功能
（1）创建数据库表
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,CHAR,VARCHAR from sqlalchemy.orm import sessionmaker, relationship Base = declarative_base() # 创建单表——users继承base class UserType(Base): __tablename__ = 'usertype' #下面的三个变量创建好后会自动变到__init__里面，成为类里面的通用变量 id = Column(Integer, primary_key=True, autoincrement=True) title = Column(String(32), nullable=True, index=True) class Users(Base): __tablename__ = 'users' #下面的三个变量创建好后会自动变到__init__里面，成为类里面的通用变量 id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(32), nullable=True, index=True, default='sf') email = Column(String(16), nullable=True, unique=True) #unique=True表示建立唯一索引 #创建主键 user_type_id = Column(Integer,ForeignKey("usertype.id")) #设定规则 __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), #id与name当成联合唯一索引，名字叫'uix_id_name' Index('ix_id_name','name', 'email'), #建立普通索引,名字叫'ix_id_name'，注意，名字要写在前面 ) #将创建表封装成函数 def create_db(): #max_overflow表示最多与数据库建立5个链接，最多可以发五条信息，实际上就是指“connection pool”这个连接池 engine = create_engine("mysql+pymysql://root:@localhost:3306/s7day", max_overflow = 5) #找到py文件中继承了base的类，创建表 Base.metadata.create_all(engine) #将删除表封装成函数 def drop_db(): engine = create_engine("mysql+pymysql://root:@localhost:3306/s7day", max_overflow=5) #默认会把base类对应的表都删掉 Base.metadata.drop_all(engine)
#调用时
create_db()
drop_db()
a.链接数据库（不是SQLAlchemy处理的，是pymysql/mysqlDB处理的）
b.把类转换成sql语句进行操作（SQLAlchemy进行语句的转换，然后提交给给pymysql进行处理）
#SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如： #MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> #pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] #MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> #cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html
（2）操作数据行
a. 增
#创建连接 engine = create_engine("mysql+pymysql://root:@localhost:3306/s7day", max_overflow=5) Session = sessionmaker(bind = engine) #从5个连接中取出一个链接 session = Session() #类代指表，对象代指一行数据 #增加用户——一条增加 obj1 = UserType(title="bbb") session.add(obj1) #转换成sql语句 #增加用户——多条增加 objs = [ UserType(title="普通用户1"), UserType(title="普通用户2"), UserType(title="普通用户3") ] session.add_all(objs) session.commit()
b. 查
#创建连接 engine = create_engine("mysql+pymysql://root:@localhost:3306/s7day", max_overflow=5) Session = sessionmaker(bind = engine) #从5个连接中取出一个链接 session = Session() #查询数据——类是表的含义，所以要去类里面查询 # print(session.query(UserType)) #输出的是SQL语句SELECT usertype.id AS usertype_id, usertype.title AS usertype_title FROM usertype usertype_list = session.query(UserType).all() # print(usertype_list) #输出是列表，列表里面是对象，即数据行 for row in usertype_list: print(row.id, row.title) #得到用户具体信息 #查询筛选 usertype_list = session.query().filter(UserType.id > 2) #全部返回 for row in usertype_list: print(row.id, row.title) usertype_list = session.query(usertype_list.id).filter(UserType.id > 2) #只返回id session.close()
c. 删
#创建连接 engine = create_engine("mysql+pymysql://root:@localhost:3306/s7day", max_overflow=5) Session = sessionmaker(bind = engine) #从5个连接中取出一个链接 session = Session() #删除数据前要先查找到需要删除的数据 usertype_list = session.query(UserType).filter(UserType.id>2).delete() #将查询到的全部删除（可以不赋值给一个变量） # for row in usertype_list: # print(row.id, row.title) print(usertype_list) #返回的是删除的条数 session.commit() session.close()
d. 改——更新
#更新输入的是字典 #整体修改成一样的值 session.query(UserType).filter(UserTpye.id > 2).update({"title" : "099"}) #字符拼接拼接：在原来的title的基础上加“099” session.query(UserType).filter(UserType.id > 2).update({UserType.title: UserType.title + "099"}, synchronize_session=False) #在原来数值的基础上+1 session.query(UserType).filter(UserType.id > 2).update({"num": UserType.num + 1}, synchronize_session="evaluate") session.commit()
e. 其他汇总
# 条件 ret = session.query(Users).filter_by(name='alex').all() #括号内传的是参数，在内部会调用filter ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() #k括号内传的是表达式 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() #~指not in ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() from sqlalchemy import and_, or_ ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all() ret = session.query(Users).filter( or_( Users.id < 2, and_(Users.name == 'eric', Users.id > 3), Users.extra != "" )).all() # 通配符 ret = session.query(Users).filter(Users.name.like('e%')).all() ret = session.query(Users).filter(~Users.name.like('e%')).all() #~表示对立面 # 限制 ret = session.query(Users)[1:2] #其实就是limit # 排序 ret = session.query(Users).order_by(Users.name.desc()).all() ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() # 分组 from sqlalchemy.sql import func ret = session.query(Users).group_by(Users.extra).all() ret = session.query( func.max(Users.id), #聚合函数 func.sum(Users.id), func.min(Users.id)).group_by(Users.name).all() ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() # 连表 #将Users, UserType两张表以笛卡尔积的形式进行链接，返回的是两张表的所有数据，不存在链接关键词 #SELECT users.id AS users_id, users.name AS users_name, users.email AS users_email, users.user_type_id AS users_user_type_id, usertype.num AS usertype_num, usertype.id AS usertype_id, usertype.title AS usertype_title FROM users, usertype ret = session.query(Users, UserType) #相当于inner join，没有空值 #SELECT users.id AS users_id, users.name AS users_name, users.email AS users_email, users.user_type_id AS users_user_type_id, usertype.num AS usertype_num, usertype.id AS usertype_id, usertype.title AS usertype_title FROM users, usertype WHERE users.id = usertype.id ret = session.query(Users, UserType).filter(Users.id == UserType.id).all() #相当于inner join， 在两张表有外键关系的情况下，不用设置链接列 ret = session.query(Users).join(UserType).all() #相当于left join ret = session.query(Users).join(UserType, isouter=True).all() # 组合 q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union(q2).all() q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union_all(q2).all() #不去重 #子查询 #1.select * from usertype where id in (select id from usertype.user_name = 'alex') session.query(UserType).filter( UserType.id.in_(session.query(Users.id).filter(Users.name == 'alex')) ).all() #2. 临时表类型——select * from (select * from usertype where id > 2) as B session.query(session.query(UserType).filter(UserType.id > 2).subquery()) #重点在于这个subquery() #3. select id, (select * from usertype) from users result = session.query(Users.id, Users.name, session.query(UserType.title).filter(Users.user_type_id==UserType.id).as_scalar()) #重点在于这个as_scalar()# 条件 ret = session.query(Users).filter_by(name='alex').all() #括号内传的是参数，在内部会调用filter ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() #k括号内传的是表达式 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() #~指not in ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() from sqlalchemy import and_, or_ ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all() ret = session.query(Users).filter( or_( Users.id < 2, and_(Users.name == 'eric', Users.id > 3), Users.extra != "" )).all() # 通配符 ret = session.query(Users).filter(Users.name.like('e%')).all() ret = session.query(Users).filter(~Users.name.like('e%')).all() #~表示对立面 # 限制 ret = session.query(Users)[1:2] #其实就是limit # 排序 ret = session.query(Users).order_by(Users.name.desc()).all() ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() # 分组 from sqlalchemy.sql import func ret = session.query(Users).group_by(Users.extra).all() ret = session.query( func.max(Users.id), #聚合函数 func.sum(Users.id), func.min(Users.id)).group_by(Users.name).all() ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() # 连表 #将Users, UserType两张表以笛卡尔积的形式进行链接，返回的是两张表的所有数据，不存在链接关键词 #SELECT users.id AS users_id, users.name AS users_name, users.email AS users_email, users.user_type_id AS users_user_type_id, usertype.num AS usertype_num, usertype.id AS usertype_id, usertype.title AS usertype_title FROM users, usertype ret = session.query(Users, UserType) #相当于inner join，没有空值 #SELECT users.id AS users_id, users.name AS users_name, users.email AS users_email, users.user_type_id AS users_user_type_id, usertype.num AS usertype_num, usertype.id AS usertype_id, usertype.title AS usertype_title FROM users, usertype WHERE users.id = usertype.id ret = session.query(Users, UserType).filter(Users.id == UserType.id).all() #相当于inner join， 在两张表有外键关系的情况下，不用设置链接列 ret = session.query(Users).join(UserType).all() #相当于left join ret = session.query(Users).join(UserType, isouter=True).all() # 组合 q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union(q2).all() q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union_all(q2).all() #不去重 #子查询 #1.select * from usertype where id in (select id from usertype.user_name = 'alex') session.query(UserType).filter( UserType.id.in_(session.query(Users.id).filter(Users.name == 'alex')) ).all() #2. 临时表类型——select * from (select * from usertype where id > 2) as B session.query(session.query(UserType).filter(UserType.id > 2).subquery()) #重点在于这个subquery() #3. select id, (select * from usertype) from users result = session.query(Users.id, Users.name, session.query(UserType.title).filter(Users.user_type_id==UserType.id).as_scalar()) #重点在于这个as_scalar()
4. SQLAlchemy中的relationship（***）
relationship写在有foreign key的类中
问题一：获取用户信息以及与其关联的用户类型名称
#解决方法一：使用连表操作 user_list = session.query(Users,UserType).join(UserType,isouter=True) print(user_list) for row in user_list: print(row[0].id,row[0].name,row[0].email,row[0].user_type_id,row[1].title) user_list = session.query(Users.name,UserType.title).join(UserType,isouter=True).all() #不加all也能拿，不加all相当于一个迭代器，一次一次拿，加上all一下子拿完 for row in user_list: print(row)
#解决方案二 #在创建数据表时设立relationship class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(VARCHAR(32), nullable=True, index=True) email = Column(VARCHAR(16), unique=True) user_type_id = Column(Integer,ForeignKey("usertype.id")) user_type = relationship("UserType",backref='xxoo') ) type_list = session.query(Users) for row in type_list: print(row.id,row.name,user_type.title)
问题二：获取不同用户类型下的用户
#解决方法一 type_list = session.query(UserType) for row in type_list: print(row.id,row.title,session.query(Users).filter(Users.user_type_id == row.id).all())
#解决方法二 class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(VARCHAR(32), nullable=True, index=True) email = Column(VARCHAR(16), unique=True) user_type_id = Column(Integer,ForeignKey("usertype.id")) user_type = relationship("UserType",backref='xxoo') "xxoo"是提供给usertype使用的 ) type_list = session.query(UserType) for row in type_list: print(row.id,row.title,row.xxoo)
relationship中的“正向操作”与反向操作
