SQLalchemy基础
一、ORM 概述
- ORM 框架：
- orm：关系对象映射，
- DBAPI：
- SQLAlchemy只是将类、对象转换为sql语句，交由DBAPI去执行
- 一大堆模块集合，包含pymysql， mysqldb即各种数据库client端api插件
- Dialect：
- 连接数据库的配置文件，记录了连接db要使用的api模块，db主机名、
- ConnectionPooling：
- 数据库连接池
- Engine：
- orm核心
- 连接方式：
- SQLAlchemy 本身无法链接数据库，是其中的Dialect使用第三方插件（pymysql等）而调用不同数据库的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...]
补充 ：数据类型
from sqlalchemy.dialects.mysql import \
BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, DATE, \
DATETIME, DECIMAL, DECIMAL, DOUBLE, ENUM, FLOAT, INTEGER, \
LONGBLOB, LONGTEXT, MEDIUMBLOB, MEDIUMINT, MEDIUMTEXT, NCHAR, \
NUMERIC, NVARCHAR, REAL, SET, SMALLINT, TEXT, TIME, TIMESTAMP, \
TINYBLOB, TINYINT, TINYTEXT, VARBINARY, VARCHAR, YEAR
二、基本及操作
- 安装：
- pymyql 、sqlalchemy 模块
- 导入的模块
import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker
- 创建链接
engine = create_engine("mysql+pymysql://root:redhat@192.168.10.202/db01?charset=utf8", echo=True)# echo 表示是否将执行过程生成的语句打印出来; charset 要在?后面写，可以输入中文，外面写不行
- 创建表结构
Base = declarative_base() # 声明基础类 class User(Base): __tablename__ = 'user' # 表名称 id = Column(Integer, primary_key=True) u_id = Column(Integer, nullable=False) u_name = Column(String(32), nullable=False) u_pass = Column(String(32), nullable=False) # Base.metadata.create_all(engine) # Base.metadata.drop_all(engine)注意：多次执行，不会重复创建，若DB中有该表，那么只是将该表的“表结构”映射给 class；（对表的操作，必须有该表的表结构）
- 插入数据：
Session_cls = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 Session = Session_cls() #生成session实例 # 声明要创建的数据对象，并添加到session中，此时没有进行创建 obj = User(name='user1', age=18, favo='black') # 增加单条数据 session.add_all([ User(name='user2', age=20, favo='green'), User(name='user3', age=30, favo='blue') ]) ## 增加多条数据，[obj1, obj2, obj3]Session.commit() ## 提交修改，此时才是真正的创建数据 ## 批量创建 for i in range(10): user_obj = User(u_id=i, u_name='user-' + str(i), u_pass='passwd-' + str(i)) Session.add(user_obj) Session.commit()
三、查询中的注意问题
- 注意问题：
- query（类名），查询的结果为对象，每个对象对应表中的一行条目，通过obj.字段 来获取该条目相应字段的值
- all（），讲所有查询到的对象放在一个列表中
- first（），只取回对象列表中的第一个对象
- __repr__（）方法，
- 默认情况下，没有该方法时，print(获取到的对象)，则得到的是对象的内存地址
- 若有该方法，则print(获取到的对象)，则显示的是该方法的返回值，更加人性化的区别开了各个对象（条目）
class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) u_id = Column(Integer, nullable=False) u_name = Column(String(32), nullable=False) u_pass = Column(String(32), nullable=False) def __repr__(self): obj_desc = '<id:{}, name:{}, passwd:{}>'.format(self.u_id, self.u_name, self.u_pass) return obj_desc Session_cls = sessionmaker(bind=engine) Session = Session_cls() obj_lst = Session.query(User).filter(User.u_name == 'user-1').all() obj_first = Session.query(User).filter(User.u_name == 'user-1').first() print(obj_lst) print(obj_first.name) # 获取该对象的u_name字段值 # 没有__repr__方法：[<__main__.User object at 0x0000000003EAA8D0>] print得到的是内存地址 # 有__repr__方法： [<id:1, name:user-1, passwd:passwd-1>] print得到该方法返回值
- 多条件查询：
obj_lst = Session.query(User).filter(User.id > 3).filter(User.u_id < 5).all() for obj in obj_lst: print(obj.u_name)
- 数据修改
#### 对象赋值方式obj = Session.query(User).filter(User.id > 3).filter(User.u_id < 5).first() obj.u_name = 'qiaogy' # 修改即是对该对象字段重新赋值 Session.commit()#### sql语句方式Session.query(User).filter(User.u_name == 'user-1').update({User.u_name: 'est'}, synchronize_session=False) Session.query(User).filter(User.u_name == '关羽').update({User.u_id: User.u_id - 100}, synchronize_session="evaluate") Session.commit()evaluate：默认值，会同时修改当前session会话中的对象属性；False 表示不修改当前会话中的对象属性
- 删除数据
Session.query(User).filter(User.u_id > 20).delete() Session.commit()
- 数据回滚
fake_user = User(u_id=10, u_name='Rain', u_pass='12345') Session.add(fake_user) print(Session.query(User).filter(User.u_name.in_(['Jack', 'rain'])).all()) # 这时看session里有你刚添加和修改的数据 Session.rollback() print(Session.query(User).filter(User.u_name.in_(['Jack', 'rain'])).all()) # 再查就发现刚才添加的数据没有了。 # 没有什么暖用，因为不提交，照样不会生效，一提交，回滚也救不了
- 统计
from sqlalchemy import func ret = Session.query(User).filter(User.u_id > 9).count() print(ret)
- 分组：
from sqlalchemy import func ret = Session.query(User.u_name, func.count(User.u_name)).group_by(User.u_name).all() print(ret)
四、补充 查询问题：
- 映射：query(table.cloumn)
obj_lst = Session.query(User.u_name, User.u_pass).filter(User.u_id > 5).all() print(obj_lst) ## 查询结果为：[('user-6', 'passwd-6'), ('user-7', 'passwd-7'), ('user-8', 'passwd-8')]， 每个对象的各个字段被组织成元组
- 过滤
## filter obj = Session.query(User).filter(User.id == 3).first() # 条件为表达式格式，可以使用 > < 来表示范围，不能走索引 obj = Session.query(User).filter_by(id=3).first() # 条件为K=V格式，不能取范围，但是可以走索引 ## filter 范围之 in_, between obj_lst = Session.query(User.u_name).filter(User.id.between(3,5)).all() # 范围为前闭后闭 obj_lst = Session.query(User.u_name).filter(~User.id.between(3,5)).all() # 取反 obj_lst = Session.query(User.u_name).filter(User.id.in_([3,4,6])).all() # 值在指定列表中的一个 obj_lst = Session.query(User.u_name).filter(~User.id.in_([3,4,6])).all() # 取反
- 逻辑之 and 、or
from sqlalchemy import or_, and_ ret = Session.query(User).filter(and_(User.id > 3, User.u_name == 'user-8')).all() ret = session.query(User).filter( or_(User.id > 1, # 或者id大于1 and_(User.name == 'user3', User.id < 4), # 或者名称为user3 且id小于4 User.favo == 'blue', # 或者颜色为蓝色 ) ).all()
- 通配符 %多个字符，_单个字符
ret = Session.query(User).filter(User.u_name.like('qiaog_')).all() ret = Session.query(User.u_name).filter(User.u_name.like('user%')).all() print(ret)
- 排序、限制：
ret = Session.query(User).order_by(User.u_id.desc()).all() ret2 = Session.query(User).order_by(User.u_id.desc()).all()[1:3]
注意：
sqlalchemy创建的表不能修改表结构，只能重新创建
unique 唯一约束：即该字段值不能重复
作业：
1、利用rabbitmq实现rpc
2、SQLAchemy+paramiko 主机管理
最少2个表：
主机表，业务类型，验证
mysql保存主机对应关系
sqlachmy获取或设置数据
