1.原生模块 ：pymsql 用于在程序之中编程
2 ORM框架： SQLAchemy
-作用： 1提供简单的规则转化
2自动转化成SQL语句
-此类在企业开发中的好处减少容错率，提升开发效率
- DB first: 手动创建数据库以及表 -> ORM框架 -> 自动生成类
- code first： 手动创建类、和数据库 -> ORM框架 -> 以及表
a. 功能
- 创建数据库表
- 连接数据库（非SQLAlchemy,pymyql,mysqldb,....）
- 类转换SQL语句
- 操作数据行
增
删
改
查
- 便利的功能
-开发自己的web框架 - socket -http协议 -html -sql
练习使用
pip install sqlalchemy
需要引入sqlclchemy库
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
Base = declarative_base()
#类转化为sql语句
class UserType(Base): #Usertype和继承Base
__tablename__ = ‘usertype‘
id = Column(Integer, primary_key=True, autoincrement=True )
title = Column(VARCHAR(32), nullable=True, index=True )
class User(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‘)
#执行建立数据库
def creat_db():
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/day6?charset=utf8", max_overflow=5)
Base.metadata.create_all(engine)
def drop_db():
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/day6?charset=utf8", max_overflow=5)
Base.metadata.drop_all(engine)
#由dbAPI 连接到数据库
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/day6?charset=utf8", max_overflow=5)
creat_db()
#Session取到链接对表操作
Session = sessionmaker(bind=engine)
session = Session()
#类》表
#对象》行
#增加----------------------------------------------------------------------------------------------------
# a = [
# UserType(title =‘白银‘),
# UserType(title =‘黄钻‘),
# UserType(title =‘黑钻‘),
# UserType(title =‘普通‘),
# ]
# session.add_all(a)
# #查-----------------------------------------------
# print(session.query(UserType).all()) #生成sql语句
# user_type_list = session.query(UserType).all()
# 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)
# for row in user_type_list:
# print(row.id, row.title)
#删除----------------------------------------------------------
# session.query(UserType.id, UserType.title).filter(UserType.id==3).delete()
#修改------------------------------------------------------------
# session.query(UserType.id, UserType.title).filter(UserType.id>3).update({"title":"黄钻6"}) #范围性修改
# session.query(UserType.id, UserType.title).filter(UserType.id>3).update({UserType.title:UserType.title+‘099‘} # 条件修改
# , synchronize_session=False)
# session.query(UserType.id, UserType.title).filter(UserType.id>3).update({"number":UserType.number+1}
# , synchronize_session="evaluate")
# -------------------------------------------------------------------------
# 分组，排序，连表，通配符，子查询，limit，union，索引，where，
# 连表操作-----------------------------------------------------------------
# ret = session.query(User, UserType)
# select * from user,usertype;
# ret = session.query(Users, UserType).filter(User.usertype_id==UserType.id)
# select * from user,usertype whre user.usertype_id = usertype.id
# result = session.query(User).join(UserType)
# print(result)
# result = session.query(User).join(UserType,isouter=True)
# print(result)
# 临时表--------------------------------------------------------------
# select id , (select id from b where a.id = b.xx) from a
# a = session.query(User.id, session.query(UserType).filter(User.user_type_id==UserType.id).as_scalar())
# print(a)
# select * from (select * from tb) as b
# a = session.query(UserType).filter(UserType.id>2).subquery()
# r = session.query(a).all()
# print(r)
#relationship的添加 在 django中也同样适应
# 1正向操作
# question1. 获取用户信息以及与其关联的用户类型名称(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.反向操作
#question: 获取用户类型
# type_list = session.query(UserType)
# for i in type_list:
# print(i.id, i.title, session.query(User).filter(User.user_type_id == i.id).all())
# type_list = session.query(UserType)
# for i in type_list:
# print(i.id, i.title, i.xxoo)
#
session.commit()
session.close()