python sqlalchemy orm_sqlalchemy orm auto_increment

ORM 和 Core相比：ORM主要是关注用户定义的对象，而Core更关注的是Schema，创建一个metadata容器，然后声明一个表对象和该容器进行关联。在ORM中我们会定义一个类去继承一个父类：

declarative_base,它主要是有一个medatada容器和一个mapper（将类映射成表）

 

一 通过ORM类定义Tables

必须要做以下几件事情：

# 继承父类declarative_base父类

# 包含一个__tablename__,它是数据库需要使用的表名

# 包含一个或者多个Column属性

# 确保一个或者属性组成一个primary key

from sqlalchemy import Table,Column,Integer,String,MetaData,create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import mapper
'''
第一种方式：
'''
'''
首先创建数据库表:
CREATE TABLE t_user(
    uid INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(32),
    passwd VARCHAR(64),
    gender VARCHAR(10),
    phone VARCHAR(20),
    PRIMARY KEY(uid)
)
'''

'''
创建引擎
'''
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/sqlalchemy",encoding='utf-8', echo=True)

'''
创建一个declarative_base实例
'''
Base = declarative_base()

'''
定义自己的类去继承Base
'''
class User(Base):
    __tablename__ = 't_user'
    uid = Column(Integer(),primary_key=True,autoincrement=True)
    username = Column(String(32))
    passwd = Column(String(64))
    gender = Column(String(10))
    phone = Column(String(20))

'''
创建表结构
'''
Base.metadata.create_all(engine)

'''
第二种方法: 第一种方式就是基于第二种方式的进行的封装
'''
metadata = MetaData()
user = Table('t_user', metadata,
    Column('uid', Integer, primary_key=True,autoincrement=True),
    Column('username', String(32)),
    Column('passwd', String(64)),
    Column('gender', String(10)),
    Column('phone', String(20))
)

class User(object):
    def __init__(self,username,passwd,gender,phone):
        self.username = username
        self.passwd = passwd
        self.gender = gender
        self.phone = phone

mapper(User,user)

 

二 主键、约束和索引

我们可以在class中使用__table_args__

class Recipes(Base):
    __tablename__ = 'recipes' # 定义表名

    # 定义列属性等
    recipe_id = Column(Integer(),primary_key=True,autoincrement=True)
    recipe_name = Column(String(50),index=True)
    recipe_url = Column(String(255))
    quantity = Column(Integer())
    unit_cost = Column(Numeric(5,2))

    __table_args__ = (ForeignKeyConstraint(['id'], ['other table.id']),
                  CheckConstraint(unit_cost >= 0.0, name='unit_cost_positive'))

 

三 关系Relationships

Relationships 是SQLAlchemy Core和ORM其他不同的一个地方

3.1one-to-many

要点：

# many需要持有one的外键, 通过one的表名.属性表示关联的外键

# 如果one想访问many的列，或者many想访问one的列，需要使用relationship去关联many或者one的实体

# 如果你只希望在一对多的任何一方进行关联，剩余的一方就不用进行关联，我们可以在某一方的relationship里面指定一个backref值

from sqlalchemy import Column,Integer,String,create_engine,Numeric,ForeignKey,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,backref,relationship

Base = declarative_base()
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/sqlalchemy?charset=utf8",encoding='utf-8', echo=True)
class Customer(Base):
    __tablename__ = 'customer'
    customerno = Column(String(20),primary_key=True,nullable=False)
    cname = Column(String(20),nullable=False)
    cage = Column(Integer(),nullable=False)
    csalary = Column(Integer())
    cgender = Column(String(10))
    # 我们可以通过Customer 访问到他所有的地址信息
    # addresses = relationship('Address') #
    addressList = relationship('Address',backref = backref('user'))
class Address(Base):
    __tablename__ = 'address'
    addressno = Column(String(20), primary_key=True, nullable=False)
    country = Column(String(20))
    state = Column(String(20))
    city = Column(String(30))
    street = Column(String(100))
    zipcode = Column(String(10))
    # 关联表的表名.需要关联的属性,另外创建数据时，吧IXUS确保这个数据已经真实存在数据库
    customerno = Column(String(20),ForeignKey("customer.customerno"))
    # 我们可以通过Address 访问到Customer的属性值
    # customer = relationship("Customer")
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()


query = session.query(Customer.cname,Customer.cgender,Customer.cage,Address.country,Address.state,Address.city)
query = query.outerjoin(Address)
results = query.filter(Customer.cname == '洪七公').all()
print(results)

'''Group分组'''
query = session.query(Customer.cname,func.count(Customer.cname),func.sum(Customer.csalary))
query = query.outerjoin(Address).group_by(Customer.cname)
query = query.filter(Address.city.in_(['成都','南京']))
results = query.all()
print(results)
records = session.query(Customer).filter(Customer.customerno == 'c10000').all()
for r in records:
    for add in r.addressList:
        print('[姓名] => %s [地址]：%s %s %s %s' %(r.cname,add.country,add.state,add.city,add.street))

records = session.query(Address).all()
for r in records:
    print(r.country,r.state,r.user.cname)

 

3.2many-to-many

多对多需要通过一个关联表来维护，在relationship，第二参数secondary就可以制定关联表

from sqlalchemy import Table,Column,Integer,String,create_engine,Numeric,ForeignKey,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,backref,relationship

Base = declarative_base()
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/sqlalchemy?charset=utf8",encoding='utf-8', echo=True)

'''
必须通过Table对象建立中间表
'''
stu2course = Table('stu_course',Base.metadata,
    Column('stuid',Integer(),ForeignKey('student.sid')),
    Column('courseid',Integer(),ForeignKey('course.cid'))
)

class Student(Base):
    __tablename__ = 'student'
    sid = Column(Integer(),primary_key=True,nullable=False)
    sname = Column(String(20),nullable=False)
    grade = Column(String(30),nullable=False)
    major = Column(String(40), nullable=False)
    courses = relationship('Course',secondary=stu2course,backref = backref('students'))

class Course(Base):
    __tablename__ = 'course'
    cid = Column(Integer(),primary_key=True,nullable=False)
    cname = Column(String(20),nullable=False)
    classroom = Column(String(30),nullable=False)
    point = Column(Integer(), nullable=False)

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
'''
多对多各自表中添加数据
'''
s1 = Student(sid=1001,sname='李筱寒',grade='2006',major='市场营销')
s2 = Student(sid=1002,sname='王怡彤',grade='2007',major='国际贸易')
s3 = Student(sid=1003,sname='李默涵',grade='2005',major='人力资源')
s4 = Student(sid=1004,sname='张诗琪',grade='2005',major='工商管理')
s5 = Student(sid=1005,sname='许诗涵',grade='2006',major='金融')
session.add_all([s1,s2,s3,s4,s5])

c1 = Course(cid=11,cname='生物技术',classroom='生物学院阶梯教室',point=2)
c2 = Course(cid=12,cname='算法与数据结构',classroom='软件学院301',point=1)
c3 = Course(cid=13,cname='宏观经济学',classroom='经管学院202',point=2)
c4 = Course(cid=14,cname='国际美学',classroom='美术学院120',point=1)
c5 = Course(cid=15,cname='民法总论',classroom='政法学院201',point=2)
session.add_all([c1,c2,c3,c4,c5])
session.commit()
session.close()
'''
多对多添加关系
'''
session = Session()
s2 = session.query(Student).filter(Student.sname == '王怡彤').first()
s2_courses = session.query(Course).filter(Course.cname.in_(['算法与数据结构','国际美学'])).all()
s2.courses = s2_courses

s2 = session.query(Student).filter(Student.sname == '李默涵').first()
s2_courses = session.query(Course).filter(Course.cname.in_(['民法总论','国际美学'])).all()
s2.courses = s2_courses

s2 = session.query(Student).filter(Student.sname == '张诗琪').first()
s2_courses = session.query(Course).filter(Course.cname.in_(['民法总论','国际美学'])).all()
s2.courses = s2_courses

s2 = session.query(Student).filter(Student.sname == '许诗涵').first()
s2_courses = session.query(Course).filter(Course.cname.in_(['生物技术','宏观经济学','生物技术'])).all()
s2.courses = s2_courses
session.commit()
session.close()

'''
多对多的查询
'''
s2 = session.query(Student).filter(Student.sname == '王怡彤').first()
for c in s2.courses:
    print(s2.sname,s2.major,c.cname,c.classroom)

cs = session.query(Course).filter(Course.cname == '民法总论').first()
for s in cs.students:
    print(cs.cname,cs.classroom,cs.point,s.sname,s.grade,s.major)

 

四 持久化schema，创建表结构

engine = create_engine("mysql+pymysql://root:123456@localhost:3306/sqlalchemy",encoding='utf8')
Base.metadata.create_all(engine)

 

五Session的管理

Session是SQLAlchemy ORM 和数据库进行交互的一种方式，通过engine包装了数据库Connection；应该使用sessionmarker来创建一个新的session

from sqlalchemy.orm import  sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
'''
flush预提交，等于提交到数据库内存，还未写入数据库文件；
commit就是把内存里面的东西直接写入，可以提供查询了；
'''
# 此时还没有创建该对象，需要在session提交之后才会创建
user1 = User(username='jerry',passwd='jerry123456',gender='female',phone='13422345678')
user2 = User(username='kitty',passwd='kitty123456',gender='female',phone='15712349087')
# 把要创建的数据放在session里
session.add(user1)
session.add(user2)
session.flush()
address1 = Address(country='America',state='CA',city='Rosemead',street='9368 Valley Blvd., #103')
address2 = Address(country='America',state='CA',city='Azusa',street='1151 W. 5th Street, #N100')
session.add(address1)
session.add(address2)
session.flush()
session.commit()
session.close()

 

六 增删改查

6.1 查询数据

from sqlalchemy import Column,Integer,String,create_engine,func,desc,or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/sqlalchemy",encoding='utf-8', echo=True)
Base = declarative_base()
class User(Base):
    __tablename__ = 't_user'
    uid = Column(Integer(),primary_key=True,autoincrement=True)
    username = Column(String(32))
    passwd = Column(String(64))
    gender = Column(String(10))
    phone = Column(String(20))

def foreach(elements):
    for user in elements:
        print("%s == %s == %s" % (user.username, user.passwd, user.phone))
'''
普通查询：
'''
Session = sessionmaker(bind=engine)
session = Session()
# 生成一个Query对象
query = session.query(User)
'''
对Query进行过滤，有两种过滤方式：
第一种：filter(Class.property == | > | <|like 值)
第二种：filter_by(property == 值)
区别：
filter需要使用类名.属性名进行过滤，而且可以像where条件那样进行比较操作等
filter_by:不需要使用类名，直接使用属性名，但是只能进行相等比较操作

'''
query = query.filter(User.username.like('%nicky'))
query.filter_by(username = 'nicky')

# 获取表中有多少记录
c = query.count()

# 查询所有记录，返回一个List<User>
userList = query.all()
foreach(userList)

# 返回第一个记录
user = query.first()

# limit 对结果集进行限制
query = query.limit(2)
userList = query.all()
# foreach(userList)

# 控制查询那些列
user = session.query(User.username,User.passwd,User.phone).first()

# 对查询进行排序
users_1 = session.query(User.username,User.passwd,User.phone).order_by(User.phone).all()
users_2 = session.query(User.username,User.passwd,User.phone).order_by(desc(User.phone)).all()

# 内置的函数使用以及别名
session.query(func.sum(User.uid))
session.query(func.count(User.username).label('user_count')).first()

# 连词
session.query(User).filter(
    or_(
        User.username == 'nicky',
        User.phone == '17089768888'
    )
)

# 返回一个select语句
sel = query.from_self()

 

6.2 添加数据

'''
插入数据
'''
from sqlalchemy.orm import  sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
'''
flush预提交，等于提交到数据库内存，还未写入数据库文件；
commit就是把内存里面的东西直接写入，可以提供查询了；
'''
# 此时还没有创建该对象，需要在session提交之后才会创建
user1 = User(username='jerry',passwd='jerry123456',gender='female',phone='13422345678')
user2 = User(username='kitty',passwd='kitty123456',gender='female',phone='15712349087')
# 把要创建的数据放在session里
session.add(user1)
session.add(user2)
session.flush()
address1 = Address(country='America',state='CA',city='Rosemead',street='9368 Valley Blvd., #103')
address2 = Address(country='America',state='CA',city='Azusa',street='1151 W. 5th Street, #N100')
session.add(address1)
session.add(address2)
session.flush()
session.commit()
session.close()

 

6.3 修改数据

'''
修改数据
'''
from sqlalchemy.orm import  sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

user = session.query(User).filter_by(username='belly').first()
user.phone = '17089768888'
user.passwd = 'belly_qazwsx'

session.commit()

 

6. 4删除数据

'''
删除数据
'''
user = session.query(User).filter_by(username='belly').first()
session.delete(user)
session.commit()

 

 

6.5 回滚事务

'''
回滚事务
'''
user = session.query(User).filter_by(username='nicky').first()
user.phone = '18080171436'

roll_user = User(username='katherine',passwd='katherine_88',gender='female',phone='13600963456')
session.add(roll_user)
session.rollback()

 

七 join 操作

from sqlalchemy import Column,Integer,String,create_engine,Numeric,ForeignKey,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,backref,relationship

Base = declarative_base()
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/sqlalchemy?charset=utf8",encoding='utf-8', echo=True)
class Customer(Base):
    __tablename__ = 'customer'
    customerno = Column(String(20),primary_key=True,nullable=False)
    cname = Column(String(20),nullable=False)
    cage = Column(Integer(),nullable=False)
    csalary = Column(Integer())
    cgender = Column(String(10))
    # 我们可以通过Customer 访问到他所有的地址信息
    # addresses = relationship('Address')#
    addressList = relationship('Address',backref = backref('user'))
class Address(Base):
    __tablename__ = 'address'
    addressno = Column(String(20), primary_key=True, nullable=False)
    country = Column(String(20))
    state = Column(String(20))
    city = Column(String(30))
    street = Column(String(100))
    zipcode = Column(String(10))
    # 关联表的表名.需要关联的属性,另外创建数据时，吧IXUS确保这个数据已经真实存在数据库
    customerno = Column(String(20),ForeignKey("customer.customerno"))
    # 我们可以通过Address 访问到Customer的属性值
    # customer =relationship("Customer")
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()


query = session.query(Customer.cname,Customer.cgender,Customer.cage,Address.country,Address.state,Address.city)
query = query.outerjoin(Address)
results = query.filter(Customer.cname== '洪七公').all()
print(results)

八 分组
query = session.query(Customer.cname,func.count(Customer.cname),func.sum(Customer.csalary))
query = query.outerjoin(Address).group_by(Customer.cname)
query = query.filter(Address.city.in_(['成都','南京']))
results = query.all()
print(results)