sqlalchemy 学习笔记

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

########## prepare ##########
# install sqlalchemy:
# pip install sqlalchemy

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine,Column,String
from sqlalchemy.orm import sessionmaker

# 创建对象的基类:
Base = declarative_base()
# # 初始化数据库连接:'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
# engine = create_engine('mysql+mysqlconnector://root:******@localhost:3306/test')
# # 创建DBSession类型:
# DBSession = sessionmaker(bind=engine)

# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
#     # 一对多:
#     books = relationship('Book')
#
# class Book(Base):
#     __tablename__ = 'book'
#
#     id = Column(String(20), primary_key=True)
#     name = Column(String(20))
#     # “多”的一方的book表是通过外键关联到user表的:
#     user_id = Column(String(20), ForeignKey('user.id'))

class SqlalchemyOperate():
    """sqlalchemy对数据库的相关操作"""
    def __init__(self,ip="localhost",port="3306",dbname="test",user="root",password="******"):
        """初始化数据库"""
        self.ip = ip
        self.port = port
        self.dbname = dbname
        self.user = user
        self.psw = password
        self.engine = create_engine('mysql+mysqlconnector://'+self.user+':'+self.psw+'@'+self.ip+':'+self.port+'/'+self.dbname)
        self.DBSession = sessionmaker(bind=self.engine)

    def insertOperate(self,insertInfo):
        """数据库插入操作"""
        # 创建session对象:
        session = self.DBSession()
        try:
            # 创建新User对象:
            # insertInfo = User(id='6', name='Bob')
            # 添加到session:
            session.add(insertInfo)
            # 提交即保存到数据库:
            session.commit()
        except:
            print("插入数据异常")
            pass
        finally:
            # 关闭session:
            session.close()

    def queryOperate(self,dbObj=None,condition=""):
        """数据库查询操作queryInfo"""
        user = ""
        # 创建session对象:
        session = self.DBSession()
        try:
            # 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
            # user = session.query(User).filter(User.id=='5').one()
            user = session.query(dbObj).filter(condition).all()
            # 打印类型和对象的name属性:
            print('type:', type(user))
            # print('name:', user.name)
            # for u in user:
            #     print(u.name)
        except:
            print("查询数据异常")
            pass
        finally:
            # 关闭session:
            session.close()
            return user

    def updateOperate(self,dbObj=None,condition="",fieldName="",fieldValue=""):
        """数据库更新操作"""
        user = ""
        print(fieldName)
        print(fieldValue)
        # 创建session对象:
        session = self.DBSession()
        try:
            # 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
            user = session.query(User).filter(condition).first()
            # print(user.fieldName)
            user.name = "hahaa"
            # user.fieldName = fieldValue
            session.commit()
        except:
            print("更新数据异常")
            pass
        finally:
            # 关闭session:
            session.close()

    def deleteOperate(self,dbObj=None,condition=None):
        """数据库删除操作"""
        # 创建session对象:
        session = self.DBSession()
        try:
            # 创建delete查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
            print(condition)
            # user = session.query(dbObj).filter_by(condition).one()
            user = session.query(dbObj).filter(condition).one()
            session.delete(user)
            session.commit()
        except:
            print("删除数据异常")
            pass
        finally:
            # 关闭session:
            session.close()



if __name__ == '__main__':
    dbs = SqlalchemyOperate(ip="localhost",port="3306",dbname="test",user="root",password="******")

    # names = [u"张三",u"李四",u"王五","赵六"]
    # i = 10
    # for name in names:
    #     insertInfo = ""
    #     i = i + 1
    #     insertInfo = User(id=i, name=name.encode("utf-8"))
    #     dbs.insertOperate(insertInfo)

    # delBefore = dbs.queryOperate(User,User.id==5)
    delBefore = dbs.queryOperate(User)
    print(delBefore)
    # users = dbs.queryOperate(User,User.id<=5)
    # for user in users:
    #     print(user.name)

    # dbs.deleteOperate(User,User.id==5)
    dbs.updateOperate(User,User.id==10,fieldName="name",fieldValue="haha")
    delAfter = dbs.queryOperate(User)
    print(delAfter)