s4 mysql的py操作 笔记

MySQL三

____oldboy fullstack4

Python开发【第十九篇】:Python操作MySQL        
    https://www.cnblogs.com/wupeiqi/articles/5713330.html    
    
    
- pymysql模块:

    pip3 install pymysql -i https://pypi.douban.com/simple
    Python模块:对数据库进行操作(SQL语句)
    python2: mysqldb,pymysql
    
    操作步骤:
        - 连接、关闭(游标)
        - execute()   # SQL注入
        - 增删改: conn.commit()
        - fetchone fetchall
        - 受影响行数
        - 获取插入数据自增ID
        
    示例一:
        import pymysql

        user = input('username:')
        pwd = input('password:')

        conn = pymysql.connect(host='localhost',user='root',password='',database='db66')
        cursor =conn.cursor() # 游标

        # sql ='select * from userinfo where username = "%s" and password ="%s"' %(user,pwd,)  # sql注入,不推荐!!!!!!
        # sql ='select * from userinfo where username = %s and password =%s'
        sql ='select * from userinfo where username = %(u)s and password =%(p)s'
        # cursor.execute(sql)  # sql注入,不推荐!!!!!!
        # cursor.execute(sql,user,pwd)
        # cursor.execute(sql,[user,pwd])
        cursor.execute(sql,{'u':user,'p':pwd})
        result = cursor.fetchone()

        cursor.close()
        conn.close()

        if result:
            print('sucess!')
        else:
            print('wrong!!!')

        # alex'--
        # uuuu' or 1=1 --
        # sql 注入        
    
    示例二:
    
        import pymysql
        # 增、删、改
        # conn = pymysql.connect(host='localhost',user='root',password='',database='db66')
        # cursor =conn.cursor()
        # sql ="INSERT INTO userinfo(username,password) VALUES ('root','123123')"
        # r = cursor.execute(sql)   # r:受影响行数
        # conn.commit()   # 增、删、改 必须!!!
        # cursor.close()
        # conn.close()

        # # user = 'eric'
        # # pwd = '123123'
        # conn = pymysql.connect(host='localhost',user='root',password='',database='db66')
        # cursor =conn.cursor()
        # # sql ="INSERT INTO userinfo(username,password) VALUES (%s,%s)"
        # # cursor.execute(sql,(user,pwd,))
        # sql ="INSERT INTO userinfo(username,password) VALUES (%s,%s)"
        # r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')])
        # # r:受影响行数,executemany只适用于insert
        # conn.commit()   # 增、删、改 必须!!!
        # cursor.close()
        # conn.close()

        # 查
        # conn = pymysql.connect(host='localhost',user='root',password='',database='db66')
        # cursor =conn.cursor(cursor=pymysql.cursors.DictCursor)
        # sql ='select * from userinfo'
        # cursor.execute(sql)
        # # cursor.ecroll(1,mode='relative') #相对当前位置移动
        # # cursor.ecroll(2,mode='absolute') #相对绝对位置移动
        # # result = cursor.fetchone()
        # # result = cursor.fetchmany(4)  #一般不用
        # result = cursor.fetchall()  # 通过sql语句限制数量
        # print(result)   # 默认得到元组,里面小元组;通过cursor =conn.cursor(参数修改)
        # cursor.close()
        # conn.close()

        # 新插入数据的自增ID
        conn = pymysql.connect(host='localhost',user='root',password='',database='db66')
        cursor =conn.cursor()
        sql ="INSERT INTO userinfo(username,password) VALUES ('eeeee','123123')"
        cursor.execute(sql)
        conn.commit()
        print(cursor.lastrowid) # 新增数据ID
        cursor.close()
        conn.close()    
    

- 面向对象回顾:
    1. 函数编程:数据和逻辑分离
        a = 123
        b = 456
        c = 789
        def exc3(proc_name):
            callproc(xxx)
            return xxx
        def exc4(proc_name):
            callproc(xxx)
            return xxx
        
    2. 面向对象:数据和逻辑(属性和行为)组合在一起
        class SqlHelper:
            def __init__(self):
                self.host = ''
                self.port =''
                self.db =''
                self.charset=''
                    
            def exc1(self,SQL):
                conn(self.host,)
                execute("inser")
                return xx

            def exc2(self,proc_name):
                callproc(xxx)
                return xxx
            
        一类事物共同具有:属性和行为
            class Person:
                def __init__(self,name):
                    self.name = name
                def speak(self):
                    pass
        
            1. 提取共性
            2. 进行分类
            3. 模板“约束”    
            4. 当一类函数公用同样参数时,可以转变成类进行分类

    3. 分类示例:
        类 = 表,对象=行
        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') # 第二行
        # UserInfo.add('eric)

    4. 特殊方法:
        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()                ==> call    # python特有
        obj1['k']            ==> getitem
        obj1['k'] = 123        ==> setitem
        del obj[k]            ==> delitem
        obj.__dict__
        


- ORM框架(关系、对象映射)
    - SQLAlchemy
        # pip3 install SQLAlchemy -i https://pypi.douban.com/simple
        # 默认没有 DB first
        
    - 作用:
        - 提供简单的规则
        - 自动转换成SQL语句
    
    - 分类:
        - DB first:    手动创建数据库以及表,ORM框架自动生成类
        - code first: 手动创建类、和数据库,ORM框架生成表
    
    - 功能:
        - 创建数据库表
            - 连接数据库
            # SQLAlchemy不接数据库,而是通过DBAPI:pymyql,mysqldb..
            # 使用前安装DBAPI
            - 类转换SQL语句
        - 操作数据行
            增
            删
            改
            查
        - 便利的功能Relationship
        

- 代码:

    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,CHAR,VARCHAR
    from sqlalchemy.orm import sessionmaker, relationship
    from sqlalchemy import create_engine
    # engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/db62?charset=utf8", max_overflow=5)
    # max-voerflow:最大连接池
    Base =declarative_base()

    # 创建表单
    class UserType(Base):
        __tablename__ ='usertype'
        id = Column(Integer,primary_key=True,autoincrement=True)
        title = Column(String(32),nullable=True,index=True)
    """
    1   白金
    2   黑金
    obj.xx ==> [obj,obj...]
    """
    class Users(Base):
        __tablename__ ='users'
        id = Column(Integer,primary_key=True,autoincrement=True)
        name = Column(String(32),nullable=True,index=True)
        email = Column(String(16),unique=True)
        user_type_id =Column(Integer,ForeignKey('usertype.id'))

        user_type = relationship("UserType",backref='xxoo')
        # __table_args__ = (
        #     UniqueConstraint('id','name',name='uix_id_name'),
        #     Index('ixx','name','email',),
        # )
    """
    1   方少伟   1
    2   成套     1
    3   小白     2
    # 正向
    ut = relationship(backref='xx')
    obj.ut ==> 1   白金
    """
    def init_db():
        Base.metadata.create_all(engine)
    def drop_db():
        Base.metadata.drop_all(engine)
    engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/db62?charset=utf8", max_overflow=5)
    Session =sessionmaker(bind=engine)
    session = Session()
    # 类 -> 表,对象 -> 行

    # -------- 增加 --------
    # obj1 = UserType(title='普通用户')
    # session.add(obj1)

    # objs =[
    #     UserType(title='超级用户'),
    #     UserType(title='白金用户'),
    #     UserType(title='黑金用户'),
    # ]
    # session.add_all(objs)

    # -------- 查 --------
    # print(session.query(UserType))    # SQL 语句
    # user_type_list =session.query(UserType).all()
    # print(type(user_type_list))     # <class 'list'>
    # print(type(user_type_list[0]))  # <class '__main__.UserType'>
    # print(user_type_list[0])        # <__main__.UserType object at 0x000000000413D668>
    # for row in user_type_list:
    #     print(row.id,row.title)

    # select xxx UserType where ..
    # user_type_list =session.query(UserType).filter(UserType.id > 2)
    # user_type_list =session.query(UserType.id,UserType.title).filter(UserType.id > 2)
    # for row in user_type_list:
    #     print(row.id,row.title)

    # 分组、排序、连表、通配符、子查询、limit、union、where、原生SQL

    # 条件
    # ret = session.query(Users).filter_by(name='alex').all()   # filter_by传字典,filter传表达式
    # ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
    # 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()
    # 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).filter(~Users.name.like('e%')).all()


    # 限制
    ret = session.query(Users)[1:2]

    # 排序
    # 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()

    # 连表

    # ret = session.query(Users, Favor)
    # ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()
    #
    # ret = session.query(Person).join(Favor).all()
    # 默认找到ForeignKey关联,相当于 inner join
    # ret = session.query(Person).join(Favor, isouter=True).all()
    # left join

    # # 组合
    # 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 b where id in (select id from tb2)

    # 2 select * from (select * from tb) as B
    # q1 = session.query(UserType).filter(UserType.id > 0).subquery()
    # result = session.query(q1).all()
    # print(result)

    # 3
    # select
    #   id ,
    #   (select * from users where users.user_type_id=usertype.id)
    # from usertype;

    # session.query(UserType,session.query(Users).filter(Users.id == 1).subquery()) # 笛卡尔积
    # session.query(UserType,Users) # 笛卡尔积
    # result = session.query(UserType.id,session.query(Users).as_scalar())

    # result = session.query(UserType.id,session.query(Users).filter(Users.user_type_id==UserType.id).as_scalar())
    # print(result)


    # 问题1. 获取用户信息以及与其关联的用户类型名称(FK,Relationship=>正向操作)
    # 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()
    # for row in user_list:
    #     print(row[0],row[1],row.name,row.title)

    # user_list = session.query(Users)
    # for row in user_list:
    #     print(row.name,row.id,row.user_type.title)


    # 问题2. 获取用户类型
    # 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())

    # type_list = session.query(UserType)
    # for row in type_list:
    #     print(row.id,row.title,row.xxoo)


    # -------- 删除 --------
    # session.query(UserType.id,UserType.title).filter(UserType.id > 2).delete()

    # -------- 修改 --------
    # usession.query(UserType.id,UserType.title).filter(UserType.id > 0).update({'title':'黑金'})
    # session.query(UserType.id,UserType.title).filter(UserType.id > 0).update(
    #     {UserType.title:UserType.title + 'x'},
    #     synchronize_session=False)  # 字符串相加
    # session.query(UserType.id,UserType.title).filter(UserType.id > 0).update(
    #     {'num':Users.num +1},
    #     synchronize_session='evaluate') # 数字

    session.commit()
    session.close()    

posted @ 2019-12-23 22:06  badweather  阅读(105)  评论(0编辑  收藏  举报