ORM_mysql

1.安装sqlalchemy
pip install sqlalchemy

2.创建表-方式1
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

engine = create_engine("mysql+mysqldb://root:123456@localhost/testdb",encoding='utf-8', echo=True)
# engine = create_engine("mysql+mysqldb://root:123456@127.0.0.1/testdb",encoding='utf-8', echo=True)

Base = declarative_base() #生成orm基类
 
class User(Base):
    __tablename__ = 'user' #表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))
 
#通过父类创建子类 
Base.metadata.create_all(engine) #创建表结构

2.创建表-方式2
from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper
 
metadata = MetaData()
 
user = Table('user', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(50)),
            Column('fullname', String(50)),
            Column('password', String(12))
        )
 
class User(object):
    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password
 
mapper(User, user) #the table metadata is created separately with the Table construct, then associated with the User class via the mapper() function

3.创建表后，添加数据
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+mysqldb://root:123456@localhost/testdb",encoding='utf-8', echo=True)
# engine = create_engine("mysql+mysqldb://root:123456@127.0.0.1/testdb",encoding='utf-8', echo=True)

Base = declarative_base() #生成orm基类
 
class User(Base):
    __tablename__ = 'user' #表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))
 
#通过父类创建子类 
Base.metadata.create_all(engine) #创建表结构

Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class() #生成session实例
 
user_obj = User(name="zhangsan",password="123456") #生成你要创建的数据对象
print(user_obj.name,user_obj.id)  #此时还没创建对象呢，不信你打印一下id发现还是None
 
Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建
print(user_obj.name,user_obj.id) #此时也依然还没创建
 
Session.commit() #现此才统一提交，创建数据

#注意：User对象创建的时候，并没有指定id,默认从1开始， 可以自己手动添加user_obj = User(id="3",name="zhangsan",password="123456") #生成你要创建的数据对象

4.查询
4.1数据表和数据都已经添加后，既可以在之前的基础上执行查询：
data = Session.query(User).filter_by(name="zhangsan").all()
print (data[0].id,data[0].name,data[0].password)
输出 1 zhangsan 123456
其中查找到的数据组成一个列表，如果只查到1行，data就是[]
如果查到多行就是[[],[],[],...]
如果直接print data，就是输出一个内存对象地址。如果想知道内存对象的其它信息，可以如下定义User：
class User(Base):
    __tablename__ = 'user' #表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

	def __repr__(self):
	    return "<id = %d ,name =%s>"%(self.id,self.name)

这时候print data就是<id=1,name=zhangsan>
这就能清楚知道返回了哪些数据
4.2 查询命令
data = Session.query(User).filter(User.name="zhangsan").all()
data = Session.query(User).filter(User.id>2).filter(User.id<4).all()
data = Session.query(User).filter(User.id>2).filter(User.id<4).first()
4.3统计命令
data = Session.query(User).filter(User.name.like("Ra%")).count()
4.4分组命令
from sqlalchemy import func
print(Session.query(func.count(User.name),User.name).group_by(User.name).all() )

ret = session.query(User).group_by(User.password).all()
ret = session.query(
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.name).all()

ret = session.query(
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.name).having(func.min(User.id) >2).all()
5.修改
data = Session.query(User).filter_by(name="zhangsan").all()
print (data[0].id,data[0].name,data[0].password)
输出 1 zhangsan 123456
此时：
data.password="123"
Session.commit() #此时就将zhangsan的密码改成了123

6.回滚
Session.rollback() 


7.增：
方法1：
obj = User(name="zhang", password='123')
session.add(obj)
方法2：
session.add_all([
    User(name="wang", password='123'),
    User(name="liu", password='123'),
])
方法3：
obj1 = User(name="zhang", password='123')
obj2 = User(name="zhang", password='123')
session.add_all([obj1,obj2])
session.commit()

8.删：
session.query(User).filter(User.id > 2).delete()
session.commit()

9.改：
session.query(User).filter(User.id > 2).update({"name" : "099"})
session.query(User).filter(User.id > 2).update({User.name: User.name + "099"}, synchronize_session=False)
session.query(User).filter(User.id > 2).update({"num": User.num + 1}, synchronize_session="evaluate")
session.commit()

10.查：
ret = session.query(User).all()
ret = session.query(User.name, User.password).all()
ret = session.query(User).filter_by(name='zhang').all()
ret = session.query(User).filter_by(name='zhang').first()

11.条件查询
ret = session.query(User).filter_by(name='alex').all()
ret = session.query(User).filter(User.id > 1, User.name == 'eric').all()
ret = session.query(User).filter(User.id.between(1, 3), User.name == 'eric').all()
ret = session.query(User).filter(User.id.in_([1,3,4])).all()
ret = session.query(User).filter(~User.id.in_([1,3,4])).all()
ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='eric'))).all()
from sqlalchemy import and_, or_
ret = session.query(User).filter(and_(User.id > 3, User.name == 'eric')).all()
ret = session.query(User).filter(or_(User.id < 2, User.name == 'eric')).all()
ret = session.query(User).filter(
    or_(
        User.id < 2,
        and_(User.name == 'eric', User.id > 3),
        User.extra != ""
    )).all()

12.通配符：
ret = session.query(User).filter(User.name.like('z%')).all()
ret = session.query(User).filter(~User.name.like('z%')).all()

13限制
ret = session.query(User)[1:2]

14.排序
ret = session.query(User).order_by(User.name.desc()).all()
ret = session.query(User).order_by(User.name.desc(), User.id.asc()).all()

15.多表查询
class User(Base):
    __tablename__ = 'user' #表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))
	def __repr__(self):
	    return "<id = %d ,name =%s>"%(self.id,self.name)
		 
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)
    def __repr__(self):
        return "%s-%s" %(self.nid, self.caption)

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))
    # 与生成表结构无关，仅用于查询方便
    favor = relationship("Favor", backref='pers')
	
注意：favor = relationship("Favor", backref='pers')作用：
	Person实例p1中可以直接使用p1.favor来访问Favor对象，例如p1.favor.capation
    Favor实例f1中可以直接使用f1.pers来访问Person对象，例如f1.pers.name
	
	
方法1：User 和 Favor无需外键关联
ret = session.query(User, Favor).filter(User.id == Favor.nid).all()

方法2：Person和 Favor需外键关联favor_id = Column(Integer, ForeignKey("favor.nid"))
ret = session.query(Person).join(Favor).all()

方式3：
ret = session.query(Person).join(Favor, isouter=True).all()

16.组合
q1 = session.query(User.name).filter(User.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

q1 = session.query(User.name).filter(User.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()