Python SQLAlchemy demo

"""
Create 操作
"""

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

faker = Factory.create()

engine = create_engine('mysql+pymysql://root:root@localhost:3306/exam')
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()


class User(Base):
    __tablename__ = "User"
    id = Column(Integer, primary_key=True,autoincrement=True)
    cname = Column(String(64))
    caddress = Column(String(512))

    def __init__(self,name,address):
        self.cname = name
        self.caddress = address

    def __str__(self):
        return "cname:"+self.cname +" caddress:"+self.caddress

    def __repr__(self):
        return "cname:"+self.cname +" caddress:"+self.caddress


class Dog(Base):
    __tablename__ = "Dog"

    id = Column(Integer,primary_key=True,autoincrement=True)
    dname = Column(String(64))
    ddesc = Column(String(1024))

    def __init__(self,name,desc):
        self.dname = name
        self.ddesc = desc

    def __str__(self):
        return "dname:"+self.dname + " ddesc:"+self.ddesc


# 生成多条Users数据
Users = [User(name= faker.name(),address = faker.address()) for i in range(10)]

# 生成一条Dog数据
dog = Dog(name=faker.name(),desc=faker.email())

# 把描述的表创建出来
Base.metadata.create_all(engine)

# 把多个表数据添加到会话
session.add_all(Users)

# 把一个表数据添加到会话
session.add(dog)

# 提交会话
session.commit()




"""
Retrieve查询操作
"""

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

engine = create_engine('mysql+pymysql://root:root@localhost:3306/exam')

# 实例它一个sessionmaker类
Session = sessionmaker(bind=engine)

# 数据库会话
session = Session()

# 基类
Base = declarative_base()

# ORM描述User表
class User(Base):
    __tablename__ = "User"
    id = Column(Integer, primary_key=True,autoincrement=True)
    cname = Column(String(64))
    caddress = Column(String(512))

    def __init__(self,name,address):
        self.cname = name
        self.caddress = address

    def __str__(self):
        return "cname:"+self.cname +"\ncaddress:"+self.caddress

    def __repr__(self):
        return "cname:"+self.cname +"\ncaddress:"+self.caddress


# 查询会返回所有的结果
users = session.query(User).all()

# 遍历输出看所有结果
for user in users:
    print(user.__str__() +"\n--------------------------\n")

# 条件查询(我数据库里只有一个ElizabethBass)，这里也是为了方便展示.first()获取第一个对象的作用
elizabeth = session.query(User).filter_by(cname='Elizabeth Bass').first()
print(elizabeth)
print("\n------------------------\n")
# 条件查询的另一种形式
elizabeth = session.query(User).filter(User.cname == 'Elizabeth Bass').first()
print(elizabeth)



"""
Update更新操作
"""

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

engine = create_engine('mysql+pymysql://root:root@localhost:3306/exam')

# 实例它一个sessionmaker类
Session = sessionmaker(bind=engine)

# 数据库会话
session = Session()

# 基类
Base = declarative_base()


# ORM描述Dog表
class Dog(Base):
    __tablename__ = "Dog"

    id = Column(Integer,primary_key=True,autoincrement=True)
    dname = Column(String(64))
    ddesc = Column(String(1024))

    def __init__(self,name,desc):
        self.dname = name
        self.ddesc = desc

    def __str__(self):
        return "dname:"+self.dname + " ddesc:"+self.ddesc

# 还记得吗，我们的数据库里只有一条Dog的数据
# 现在就把它拿出来，改一下名字，再放回去
dog = session.query(Dog).first()
print(dog)
print("\n---------------------\n")
# 给小狗改名
dog.dname = "little white"

# 提交更改
session.commit()

# 改完再看看
dog = session.query(Dog).first()
print(dog)



"""
删除Delete操作
"""

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

engine = create_engine('mysql+pymysql://root:root@localhost:3306/exam')

# 实例它一个sessionmaker类
Session = sessionmaker(bind=engine)

# 数据库会话
session = Session()

# 基类
Base = declarative_base()


# ORM描述Dog表
class Dog(Base):
    __tablename__ = "Dog"

    id = Column(Integer,primary_key=True,autoincrement=True)
    dname = Column(String(64))
    ddesc = Column(String(1024))

    def __init__(self,name,desc):
        self.dname = name
        self.ddesc = desc

    def __str__(self):
        return "dname:"+self.dname + " ddesc:"+self.ddesc

# 把唯一的一条狗数据删掉
dog = session.query(Dog).first()
session.delete(dog)
session.commit()

# 删了之后一条数据都没了 = =
dogs = session.query(Dog).all()
for dog in dogs:
    print(dog)
    print("\n------------------\n")