SQLAlchemy（二)：SQLAlchemy对数据的增删改查操作、属性常用数据类型详解

SQLAlchemy02 /SQLAlchemy对数据的增删改查操作、属性常用数据类型详解

1、用session做数据的增删改查操作：

1. 构建session对象：所有和数据库的ORM操作都必须通过一个叫做session的会话对象来实现，通过以下代码来获取会话对象：

   from sqlalchemy.orm import sessionmaker
   
   engine = create_engine(DB_URI)
   session = sessionmaker(engine)()

2. 添加对象：

   • 创建对象，也即创建一条数据：

     p = Person(name=‘zhangsan‘,age=18,country=‘china‘)

   • 将这个对象添加到session会话对象中：

     session.add(p)

   • 将session中的对象做commit操作（提交）：

     session.commit()

   • 一次性添加多条数据：

     p1 = Person(name=‘zhangsan‘,age=19,country=‘china‘)
     p2 = Person(name=‘lisi‘,age=20,country=‘china‘)
     session.add_all([p1,p2])
     session.commit()

3. 查找对象：

   # 查找某个模型对应的那个表中所有的数据：
   all_person = session.query(Person).all()
   
   # 使用filter_by来做条件查询
   all_person = session.query(Person).filter_by(name=‘zhangsan‘).all()
   
   # 使用filter来做条件查询
   all_person = session.query(Person).filter(Person.name==‘zhangsan‘).all()
   
   # 使用get方法查找数据，get方法是根据id来查找的，只会返回一条数据或者None
   person = session.query(Person).get(primary_key)
   
   # 使用first方法获取结果集中的第一条数据
   person = session.query(Person).first()

4. 修改对象：首先从数据库中查找对象，然后将这条数据修改为你想要的数据，最后做commit操作就可以修改数据了。

   person = session.query(Person).first()
   person.name = ‘wangwu‘
   session.commit()

5. 删除对象：将需要删除的数据从数据库中查找出来，然后使用session.delete方法将这条数据从session中删除，最后做commit操作就可以了。

   person = session.query(Person).first()
   session.delete(person)
   session.commit()

2、SQLAlchemy常用数据类型：

1. Integer：整形，映射到数据库中是int类型。

2. Float：浮点类型，映射到数据库中是float类型。他占据的32位。

3. Double：双精度浮点类型，映射到数据库中是double类型，占据64位。

4. String：可变字符类型，映射到数据库中是varchar类型.

5. Boolean：布尔类型，映射到数据库中的是tinyint类型。

6. DECIMAL：定点类型。是专门为了解决浮点类型精度丢失的问题的。在存储钱相关的字段的时候建议大家都使用这个数据类型。并且这个类型使用的时候需要传递两个参数，第一个参数是用来标记这个字段总能能存储多少个数字，第二个参数表示小数点后有多少位。

7. Enum：枚举类型。指定某个字段只能是枚举中指定的几个值，不能为其他值。在ORM模型中，使用Enum来作为枚举，示例代码如下：

   class Article(Base):
       __tablename__ = ‘article‘
       id = Column(Integer,primary_key=True,autoincrement=True)
       tag = Column(Enum("python",‘flask‘,‘django‘))

   在Python3中，已经内置了enum这个枚举的模块，我们也可以使用这个模块去定义相关的字段。示例代码如下：

   class TagEnum(enum.Enum):
       python = "python"
       flask = "flask"
       django = "django"
   
   class Article(Base):
       __tablename__ = ‘article‘
       id = Column(Integer,primary_key=True,autoincrement=True)
       tag = Column(Enum(TagEnum))
   
   article = Article(tag=TagEnum.flask)

8. Date：存储时间，只能存储年月日。映射到数据库中是date类型。在Python代码中，可以使用datetime.date来指定。示例代码如下：

   class Article(Base):
       __tablename__ = ‘article‘
       id = Column(Integer,primary_key=True,autoincrement=True)
       create_time = Column(Date)
   
   article = Article(create_time=date(2017,10,10))

9. DateTime：存储时间，可以存储年月日时分秒毫秒等。映射到数据库中也是datetime类型。在Python代码中，可以使用datetime.datetime来指定。示例代码如下：

   class Article(Base):
       __tablename__ = ‘article‘
       id = Column(Integer,primary_key=True,autoincrement=True)
       create_time = Column(DateTime)
   
   article = Article(create_time=datetime(2011,11,11,11,11,11))

10. Time：存储时间，可以存储时分秒。映射到数据库中也是time类型。在Python代码中，可以使用datetime.time来至此那个。示例代码如下：

    class Article(Base):
        __tablename__ = ‘article‘
        id = Column(Integer,primary_key=True,autoincrement=True)
        create_time = Column(Time)
    
    article = Article(create_time=time(hour=11,minute=11,second=11))

11. Text：存储长字符串。一般可以存储6W多个字符。如果超出了这个范围，可以使用LONGTEXT类型。映射到数据库中就是text类型。

12. LONGTEXT：长文本类型，映射到数据库中是longtext类型。

• 示例代码：

  #encoding: utf-8
  
  from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text
  from sqlalchemy.dialects.mysql import LONGTEXT
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker
  
  import enum   # 在Python3中才有这个enum模块，在python2中没有
  
  HOSTNAME = ‘127.0.0.1‘
  PORT = ‘3306‘
  DATABASE = ‘first_sqlalchemy‘
  USERNAME = ‘root‘
  PASSWORD = ‘root‘
  
  # dialect+driver://username::port/database
  DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
  
  engine = create_engine(DB_URI)
  
  Base = declarative_base(engine)
  
  session = sessionmaker(engine)()
  
  class TagEnum(enum.Enum):
      python = "python"
      flask = "flask"
      django = "django"
  
  class Article(Base):
      __tablename__ = ‘article‘
      id = Column(Integer,primary_key=True,autoincrement=True)
      rice = Column(Float)
      is_delete = Column(Boolean)
      price = Column(DECIMAL(10,4))  # 100000.0001
      # Enum方式一：
      tag = Column(Enum(‘python‘,‘flask‘,‘django‘))
      # Enum方式二：
      tag = Column(Enum(TagEnum))
      create_time = Column(Date)
      # create_time = Column(DateTime)
      # create_time = Column(Time)
      title = Column(String(50))
      content = Column(Text)
      # content = Column(LONGTEXT)
  
  Base.metadata.drop_all()
  Base.metadata.create_all()
  
  from datetime import date
  from datetime import datetime
  from datetime import time
  
  article = Article(price=100000.99999)   # 小数点位数超出会报错
  session.add(article)
  session.commit()

3、Column常用参数：

1. primary_key：设置某个字段为主键。

2. autoincrement：设置这个字段为自动增长的。

3. default：设置某个字段的默认值。在发表时间这些字段上面经常用。

4. nullable：指定某个字段是否为空。默认值是True，就是可以为空。

5. unique：指定某个字段的值是否唯一。默认是False。

6. onupdate：在数据更新的时候会调用这个参数指定的值或者函数。在第一次插入这条数据的时候，不会用onupdate的值，只会使用default的值。常用的就是update_time（每次更新数据的时候都要更新的值）。

7. name：指定ORM模型中某个属性映射到表中的字段名。如果不指定，那么会使用这个属性的名字来作为字段名。如果指定了，就会使用指定的这个值作为参数。这个参数也可以当作位置参数，在第1个参数来指定。

   title = Column(String(50),name=‘title‘,nullable=False)
   title = Column(‘my_title‘,String(50),nullable=False)

• 示例代码：

  #encoding: utf-8
  
  from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text
  from sqlalchemy.dialects.mysql import LONGTEXT
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker
  
  from datetime import datetime
  
  HOSTNAME = ‘127.0.0.1‘
  PORT = ‘3306‘
  DATABASE = ‘first_sqlalchemy‘
  USERNAME = ‘root‘
  PASSWORD = ‘root‘
  
  # dialect+driver://username::port/database
  DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
  
  engine = create_engine(DB_URI)
  
  Base = declarative_base(engine)
  
  session = sessionmaker(engine)()
  
  class Article(Base):
      __tablename__ = ‘article‘
      id = Column(Integer,primary_key=True,autoincrement=True)
      create_time = Column(DateTime,default=datetime.now)
      read_count = Column(Integer,default=11)
      title = Column(String(50),name=‘my_title‘,nullable=False)
      telephone = Column(String(11),unique=True)
      update_time = Column(DateTime,onupdate=datetime.now,default=datetime.now)
  
  Base.metadata.create_all()

4、query可用参数：

1. 模型对象：指定查找这个模型中所有的对象。
2. 模型中的属性：可以指定只查找某个模型的其中几个属性。
3. 聚合函数：
   • func.count：统计行的数量。
   • func.avg：求平均值。
   • func.max：求最大值。
   • func.min：求最小值。
   • func.sum：求和。
     func上，其实没有任何聚合函数。但是因为他底层做了一些魔术，只要mysql中有的聚合函数，都可以通过func调用。

• 示例代码：

  #encoding: utf-8
  
  from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func
  from sqlalchemy.dialects.mysql import LONGTEXT
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker
  # 在Python3中才有这个enum模块，在python2中没有
  import enum
  from datetime import datetime
  import random
  
  HOSTNAME = ‘127.0.0.1‘
  PORT = ‘3306‘
  DATABASE = ‘first_sqlalchemy‘
  USERNAME = ‘root‘
  PASSWORD = ‘root‘
  
  # dialect+driver://username::port/database
  DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
  
  engine = create_engine(DB_URI)
  
  Base = declarative_base(engine)
  
  session = sessionmaker(engine)()
  
  class Article(Base):
      __tablename__ = ‘article‘
      id = Column(Integer,primary_key=True,autoincrement=True)
      title = Column(String(50),nullable=False)
      price = Column(Float,nullable=False)
  
      def __repr__(self):
          return "<Article(title:%s)>" % self.title
  
  # 创建一些测试数据
  Base.metadata.drop_all()
  Base.metadata.create_all()
  
  
  for x in range(6):
      article = Article(title=‘title%s‘%x,price=random.randint(50,100))
      session.add(article)
  session.commit()
  
  # 模型对象
  articles = session.query(Article).all()
  print(articles)
  
  # 模型中的属性
  articles = session.query(Article.title,Article.price).all()
  print(articles)
  
  # 聚合函数
  # count
  result = session.query(func.count(Article.id)).first()
  print(result)
  # avg
  result = session.query(func.avg(Article.price)).first()
  print(result)
  # max
  result = session.query(func.max(Article.price)).first()
  print(result)
  # min
  result = session.query(func.min(Article.price)).first()
  print(result)
  # sum
  result = session.query(func.sum(Article.price)).first()
  print(result)
  
  # print(func.sum(Article.price))
  # select sum(price) from article;

5、filter过滤条件：

过滤是数据提取的一个很重要的功能，以下对一些常用的过滤条件进行解释，并且这些过滤条件都是只能通过filter方法实现

1. equals：

   article = session.query(Article).filter(Article.title == "title0").first()
   print(article)

2. not equals:

   query.filter(User.name != ‘ed‘)

3. like：

   query.filter(User.name.like(‘%ed%‘))

4. in：

   query.filter(User.name.in_([‘ed‘,‘wendy‘,‘jack‘]))
   
   # 同时，in也可以作用于一个Query
   query.filter(User.name.in_(session.query(User.name).filter(User.name.like(‘%ed%‘))))

5. not in：

   query.filter(~User.name.in_([‘ed‘,‘wendy‘,‘jack‘]))

6. is null：

   # 方式一：
   query.filter(User.name==None)
   # 方式二：
   query.filter(User.name.is_(None))

7. is not null:

   # 方式一:
   query.filter(User.name != None)
   # 方式二：
   query.filter(User.name.isnot(None))

8. and：

   from sqlalchemy import and_
   query.filter(and_(User.name==‘ed‘,User.fullname==‘Ed Jones‘))
   # 或者是传递多个参数
   query.filter(User.name==‘ed‘,User.fullname==‘Ed Jones‘)
   # 或者是通过多次filter操作
   query.filter(User.name==‘ed‘).filter(User.fullname==‘Ed Jones‘)

9. or：

   from sqlalchemy import or_  
   query.filter(or_(User.name==‘ed‘,User.name==‘wendy‘))

10. 如果想要查看orm底层转换的sql语句，可以在filter方法后面不要再执行任何方法直接打印就可以看到了。比如：

    articles = session.query(Article).filter(or_(Article.title==‘abc‘,Article.content==‘abc‘))
    print(articles)