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()

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中的“正向操作”与反向操作

巴特西

MySQL学习笔记（六）

最新文章

热门文章