数据库之sqlalchemy

1.安装sqlalchemy

pip3 install sqlalchemy
pip3 install pymysql

2.设置数据库信息

dialect+driver://username:password@host:port/database

解释：

    dialect：数据库，如：sqlite、mysql、oracle等
    driver：数据库驱动，用于连接数据库的，本文使用pymysql
    username：用户名
    password：密码
    host：IP地址
    port：端口
    database：数据库

配置：
新建DB_Url.py文件，

内容：
HOST = 'localhost'
PORT = 3306
USERNAME = 'root'
PASSWORD = '123456'
DB = 'learn'

# dialect + driver://username:passwor@host:port/database
DB_URI = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DB}'

3.创建引擎，连接数据库并测试：

from sqlalchemy import create_engine
from config import DB_URI


engine = create_engine(DB_URI)  # 创建引擎
conn = engine.connect()  # 连接
result = conn.execute('SELECT 1')  # 执行SQL
print(result.fetchone())  
conn.close()  # 关闭连接

4.创建ORM模型并创建一个student表映射到数据库

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

engine = create_engine(DB_URI)
Base = declarative_base(engine)  # SQLORM基类
session = sessionmaker(engine)()  # 构建session对象


class Student(Base):
    __tablename__ = 'student'  # 表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    sex = Column(String(10))

#创建student表
Base.metadata.create_all()  # 将模型映射到数据库中

 5.新增数据

添加单条数据：
student = Student(name='Tony', age=18, sex='male')  # 创建一个student对象
session.add(student)  # 添加到session
session.commit()  # 提交到数据库

添加多条数据：
session.add_all([
    Student(name='Jane', age=16, sex='female'),
    Student(name='Ben', age=20, sex='male')
])
session.commit()

 

6.获取数据：

sqlalchemy提供了query()方法来查询数据

item_list = session.query(Student).all()
print(item_list)
for item in item_list:
    print(item.name, item.age)

7.指定查询列

item0 = session.query(Student.name).all()
print('session.query(列名).all()的结果：',item0)

 

8.获取返回数据的第一行

item0 = session.query(Student.name).first()
print('session.query().first()的结果：',item0)

 

9.使用filter()方法进行筛选过滤

item_list1= session.query(Student.age).filter(Student.id>=8).all()
print('session.query(目标).filter(条件).all()的结果：',item_list1)

 

10.使用order_by()进行排序

item_list2 = session.query(Student.name,Student.age).order_by(Student.age.desc()).all()
print('session.query(目标).order_by(排序关键词,desc->倒序).all()的结果：',item_list2)

 

11.多个查询条件（and和or）

#多个条件查询（and 和 or）,默认and
# 默认为and, 在filter()中用,分隔多个条件表示and

item_list = session.query(Student.name,Student.id,Student.age).filter(
    Student.age >= 13,Student.id > 8
).all()
print('session.query(目标).filter(条件1,条件2,...).all()的结果:',item_list)

or_的用法：

from sqlalchemy import or_
item_list1 = session.query(Student.name).filter(
    or_(Student.id>5,Student.age<10)
).all()
print('session.query(目标).filter(or_(条件1,条件2,...)).all()的结果:',item_list1)

 

12.缺省查值：

#等于的用法：
item_list1 = session.query(Student.name,Student.age,Student.id).filter(
    Student.age == 22
).all()
print(item_list1)

#不等于的用法：
item_list1 = session.query(Student.name,Student.age,Student.id).filter(
    Student.age != 22
).all()
print(item_list1)

#like的用法：
item_list2 = session.query(Student.name,Student.age,Student.id).filter(
    Student.name.like('%小%')
).all()
print(item_list2)

#in的用法：注意---in方法的范围边界都包含
item_list1 = session.query(Student.name,Student.age,Student.id).filter(
    Student.age.in_([10,20])
).all()
print(item_list1)

 

13.计数--count()

count = session.query(Student).count()
print(count)

 

14.切片：

#切片：session.query(目标).all()[切片方法]
item_list = session.query(Student.name).all()[1:5]
print(item_list)

 

15.修改数据：

#使用update()方法，update完成后记得执行session.commit()

session.query(Student).filter(Student.id == 1).update({'name':'toney'})
session.commit()

item = session.query(Student.name).first()
print(item)

 

16.删除数据：

#删除数据使用delete()方法，同样也需要执行session.commit()提交事务

session.query(Student).filter(Student.name == 'toney').delete()
session.commit()
item_list = session.query(Student.name).all()
print(item_list)