lab7 ORM介绍和基础查询练习¶

1. ORM的介绍¶

ORM：Object Relation Mapping，最初主要描述的是程序中的Object对象和关系型数据库中Relation关系(表)之间的映射关系，目前来说也是描述程序中对象和数据库中数据记录之间的映射关系的统称，是一种进行程序和数据库之间数据持久化的一种编程思想。

特点是操纵Python对象而不是SQL查询，也就是在代码层面考虑的是对象，而不是SQL，体现的是一种程序化思维，这样使得Python程序更加简洁易读。

增删改操作¶

常规情况下，软件程序中的ORM操作主要有四个操作场景：增、删、改、查. 核心操作一般会区分为：增删改、查询

增加操作：程序中存在的一个对象Object数据，通过[ORM]核心模块进行增加的函数定义将对象保存到数据库的操作过程；
如：注册操作中，通过用户输入的账号密码等信息创建了一个独立的对象，通过add()函数将对象增加保存到数据库中，数据库中就存在用户这个对象数据了。

修改操作：程序中存在的一个对象Object数据，有自己的id编号(可以是程序中自行赋值定义、更多的操作是从数据库中查询出来存在的一个对象)，通过[ORM]核心模块进行修改函数的定义将对象改变的数据更新到数据库中已经存在的记录中的过程；
如:用户更改登录密码操作时，根据程序中查询得到的一个用户[id编号、账号、密码、..]，在程序中通过改变其密码属性数据，然后通过update()函数将改变的数据更新保存到数据库中，数据库中原来的数据就发生了新的改变。

删除操作：程序中存在的一个对象或者已知的id编号，通过主键编号或者对象的任意属性进行数据库中数据记录的删除的操作过程；
如：管理员删除某个会员账号的操作，通过获取要删除会员的账号，然后通过delete()函数将要删除的会员信息告知数据库执行删除操作，数据库中的某条存在的数据记录就被删除掉了。

2.sqlalchemy¶

2.1 sqlalchemy的介绍和安装¶

SQLAlchemy 是一个Python 的SQL 工具包以及数据库对象映射框架。它包含整套企业级持久化模式，专门为高效和高性能的数据库访问。

如果想在本地安装，可使用以下语句：

pip install SQLAlchemy
pip install psycopg2

在该水杉环境中已经安装完成，直接导入即可：

In [1]:

Copied!

import sqlalchemy
import sqlalchemy

2.2 sqlalchemy 的简单操作¶

In [2]:

Copied!





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

2.2.1 建立连接¶

在网址postgresql://ecnu学号:ECNU学号@172.16.253.154:5432/ecnu学号中填入自己的学号

In [3]:

Copied!





from sqlalchemy import create_engine
engine = create_engine("postgresql://ecnu10211900416:ECNU10211900416@172.16.253.154:5432/ecnu10211900416",
    echo=True,
    pool_size=8, 
    pool_recycle=60*30
)
from sqlalchemy import create_engine
engine = create_engine("postgresql://ecnu10211900416:ECNU10211900416@172.16.253.154:5432/ecnu10211900416",
    echo=True,
    pool_size=8, 
    pool_recycle=60*30
)

2.2.2 建立会话（session）¶

session 用于创建程序与数据库之间的对话.

In [4]:

Copied!





from sqlalchemy.orm import sessionmaker
# 创建session
DbSession = sessionmaker(bind=engine)
session = DbSession()
from sqlalchemy.orm import sessionmaker
# 创建session
DbSession = sessionmaker(bind=engine)
session = DbSession()

session 的常见用法:

commit：提交了一个事务
rollback：回滚
close：关闭

2.2.3 创建表格¶

declarative_base()是sqlalchemy内部封装的一个方法，通过其构造一个基类，这个基类和它的子类，可以将Python类和数据库表关联映射起来。

数据库表模型类通过tablename和表关联起来，Column表示数据表的列。

In [5]:

Copied!

from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()

/tmp/ipykernel_57/3927807320.py:4: MovedIn20Warning: The ``declarative_base()`` function is now available as sqlalchemy.orm.declarative_base(). (deprecated since: 2.0) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  Base = declarative_base()

In [6]:

Copied!





# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'users'
    
    # 表的结构:
    id = Column(Integer, autoincrement=True, primary_key=True, unique=True, nullable=False)
    name = Column(String(50), nullable=False)
    sex = Column(String(4), nullable=False)
    nation = Column(String(20), nullable=False)
    birth = Column(String(8), nullable=False)
    id_address = Column(Text, nullable=False)
    id_number = Column(String(18), nullable=False)
    creater = Column(String(32))
    create_time = Column(String(20), nullable=False)
    updater = Column(String(32))
    update_time = Column(String(20), nullable=False,
    default=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
    onupdate=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime()))
    comment = Column(String(200))
    
def createTable():
    # 创建所有继承于Base的类对应的表
    Base.metadata.create_all(engine)
    
createTable()
# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'users'
    
    # 表的结构:
    id = Column(Integer, autoincrement=True, primary_key=True, unique=True, nullable=False)
    name = Column(String(50), nullable=False)
    sex = Column(String(4), nullable=False)
    nation = Column(String(20), nullable=False)
    birth = Column(String(8), nullable=False)
    id_address = Column(Text, nullable=False)
    id_number = Column(String(18), nullable=False)
    creater = Column(String(32))
    create_time = Column(String(20), nullable=False)
    updater = Column(String(32))
    update_time = Column(String(20), nullable=False,
    default=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
    onupdate=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime()))
    comment = Column(String(200))
    
def createTable():
    # 创建所有继承于Base的类对应的表
    Base.metadata.create_all(engine)
    
createTable()

2023-11-16 05:54:50,816 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2023-11-16 05:54:50,816 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-11-16 05:54:50,870 INFO sqlalchemy.engine.Engine select current_schema()
2023-11-16 05:54:50,871 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-11-16 05:54:50,904 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2023-11-16 05:54:50,904 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-11-16 05:54:50,906 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:54:50,910 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-16 05:54:50,911 INFO sqlalchemy.engine.Engine [generated in 0.00078s] {'table_name': 'users', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-16 05:54:51,048 INFO sqlalchemy.engine.Engine 
CREATE TABLE users (
	id SERIAL NOT NULL, 
	name VARCHAR(50) NOT NULL, 
	sex VARCHAR(4) NOT NULL, 
	nation VARCHAR(20) NOT NULL, 
	birth VARCHAR(8) NOT NULL, 
	id_address TEXT NOT NULL, 
	id_number VARCHAR(18) NOT NULL, 
	creater VARCHAR(32), 
	create_time VARCHAR(20) NOT NULL, 
	updater VARCHAR(32), 
	update_time VARCHAR(20) NOT NULL, 
	comment VARCHAR(200), 
	PRIMARY KEY (id), 
	UNIQUE (id)
)


2023-11-16 05:54:51,048 INFO sqlalchemy.engine.Engine [no key 0.00086s] {}
2023-11-16 05:54:52,070 INFO sqlalchemy.engine.Engine COMMIT

2.2.4 插入数据¶

In [7]:

Copied!





# 插入操作
def insertData():
    
    # 创建会话 
    session = DbSession()
    
    # 创建新User对象:
    local_time = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
    new_user = User(name='mdotdot', sex='女', nation='汉',
    birth='19981021', id_address='ECNU', id_number='441242142142',
    create_time=local_time)
    new_user1 = User(name='xdot', sex='男', nation='汉',
    birth='19990110', id_address='ECNU', id_number='451242142142',
    create_time=local_time)
    
    # 添加到session:
    session.add(new_user)
    session.add(new_user1)
    # 提交即保存到数据库:
    session.commit()
    
    # 关闭session: 
    session.close()
    
insertData()
# 插入操作
def insertData():
    
    # 创建会话 
    session = DbSession()
    
    # 创建新User对象:
    local_time = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
    new_user = User(name='mdotdot', sex='女', nation='汉',
    birth='19981021', id_address='ECNU', id_number='441242142142',
    create_time=local_time)
    new_user1 = User(name='xdot', sex='男', nation='汉',
    birth='19990110', id_address='ECNU', id_number='451242142142',
    create_time=local_time)
    
    # 添加到session:
    session.add(new_user)
    session.add(new_user1)
    # 提交即保存到数据库:
    session.commit()
    
    # 关闭session: 
    session.close()
    
insertData()

2023-11-16 05:55:07,315 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:55:07,318 INFO sqlalchemy.engine.Engine INSERT INTO users (name, sex, nation, birth, id_address, id_number, creater, create_time, updater, update_time, comment) VALUES (%(name__0)s, %(sex__0)s, %(nation__0)s, %(birth__0)s, %(id_address__0)s, %(id_number__0)s, %(creater__0)s, %(create_time_ ... 157 characters truncated ... ter__1)s, %(create_time__1)s, %(updater__1)s, %(update_time__1)s, %(comment__1)s) RETURNING users.id
2023-11-16 05:55:07,319 INFO sqlalchemy.engine.Engine [generated in 0.00013s (insertmanyvalues)] {'updater__0': None, 'birth__0': '19981021', 'id_address__0': 'ECNU', 'sex__0': '女', 'create_time__0': '2023-11-16 05:55:07', 'id_number__0': '441242142142', 'update_time__0': '2023-11-16 05:54:50', 'creater__0': None, 'name__0': 'mdotdot', 'nation__0': '汉', 'comment__0': None, 'updater__1': None, 'birth__1': '19990110', 'id_address__1': 'ECNU', 'sex__1': '男', 'create_time__1': '2023-11-16 05:55:07', 'id_number__1': '451242142142', 'update_time__1': '2023-11-16 05:54:50', 'creater__1': None, 'name__1': 'xdot', 'nation__1': '汉', 'comment__1': None}
2023-11-16 05:55:07,341 INFO sqlalchemy.engine.Engine COMMIT

2.2.5 查询数据¶

SQL 与 SQLalchemy 的写法区别为：

query ：对应 SELECT xxx FROM xxx
filter/filter_by ：对应 WHERE ，fillter 可以进行比较运算(==, >, < ...)来对条件进行灵活的运用，不同的条件用逗号分割，fillter_by 只能指定参数传参来获取查询结果。
limit ：对应 limit()
order by ：对应 order_by()
group by ：对应 group_by()

返回结果数量可以有以下两种方式：

all()

查询所有
返回一个列表对象

first()

查询第一个符合条件的对象
返回一个对象

在ORM中，查询也有和SQL类似的关键字

In [8]:

Copied!

from sqlalchemy import and_,or_
from sqlalchemy import and_,or_

like	session.query(Person).filter(Person.desc.like("活%")).all()
not like	session.query(Person).filter(Person.desc.notlike("活%")).all()
is(等价于==)	session.query(Person).filter(Person.username.is_(None)).all()，session.query(Person).filter(Person.username == None).all()
isnot(等价于 !=)	session.query(Person).filter(Person.username.isnot(None)).all()，session.query(Person).filter(Person.username != None).all()
正则查询	session.query(Person).filter(Person.password.op("regexp")(r"^[\u4e00-\u9fa5]+")).all()
count	session.query(Person).filter(Person.desc.like("活%")).count()
in	session.query(Person).filter(Person.username.in_(['Mark', 'Tony'])).all()
not in(等价于~in)	session.query(Person).filter(Person.username.notin_(['Mark', 'Tony'])).all()，session.query(Person).filter(~Person.username.in_(['Mark', 'Tony'])).all()
AND(导入and_)	more_person = session.query(Person).filter(and_(Person.password=='123456',Person.desc=="可爱'")).all()
OR(导入or_)	session.query(Person).filter(or_(Person.password=='123456',Person.desc=="活泼'")).all()
limit	session.query(Person).filter(Person.desc.notlike("活%")).limit(1).all()
offset	session.query(Person).filter(Person.desc.like("活%")).offset(1).all()
order_by（asc正序）	session.query(Person).order_by(Person.username.desc()).all()
group_by	session.query(Person).group_by(Person.desc).all()
between	session.query(Protocols.protocolName).filter(Protocols.id.between(1, 3)).all()

聚合函数

In [9]:

Copied!

from sqlalchemy import func, extract
from sqlalchemy import func, extract

关键字	示例
count	session.query(Person.password, func.count(Person.id)).group_by(Person.password).all()
sum	session.query(Person.password, func.sum(Person.id)).group_by(Person.password).all()
max	session.query(Person.password, func.max(Person.id)).group_by(Person.password).all()
min	session.query(Person.password, func.min(Person.id)).group_by(Person.password).all()
having	session.query(Person.password, func.count(Person.id)).group_by(Person.password).having(func.count(Person.id) > 1).all()

In [10]:

Copied!





#查询所有数据
def find_all():
    # 创建Session
    session = DbSession()
    
    user = session.query(User).all()
    for i in user:
        print('id:',i.id)
        print('name:', i.name)
        print('id_address:', i.id_address)
        print('id_number:', i.id_number)
        
    session.close() # 关闭Session
    
find_all()
#查询所有数据
def find_all():
    # 创建Session
    session = DbSession()
    
    user = session.query(User).all()
    for i in user:
        print('id:',i.id)
        print('name:', i.name)
        print('id_address:', i.id_address)
        print('id_number:', i.id_number)
        
    session.close() # 关闭Session
    
find_all()

2023-11-16 05:55:23,922 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:55:23,925 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.sex AS users_sex, users.nation AS users_nation, users.birth AS users_birth, users.id_address AS users_id_address, users.id_number AS users_id_number, users.creater AS users_creater, users.create_time AS users_create_time, users.updater AS users_updater, users.update_time AS users_update_time, users.comment AS users_comment 
FROM users
2023-11-16 05:55:23,926 INFO sqlalchemy.engine.Engine [generated in 0.00081s] {}
id: 1
name: mdotdot
id_address: ECNU
id_number: 441242142142
id: 2
name: xdot
id_address: ECNU
id_number: 451242142142
2023-11-16 05:55:23,930 INFO sqlalchemy.engine.Engine ROLLBACK

In [11]:

Copied!





# 查询操作
def selectData():
    # 创建Session
    session = DbSession()

    # 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
    user = session.query(User).filter(User.id == '1' and User.name == 'mdotdot').first()
    
    if user:
        print('name:', user.name)
        print('id_address:', user.id_address)
    
    session.close() # 关闭Session
    
selectData()
# 查询操作
def selectData():
    # 创建Session
    session = DbSession()

    # 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
    user = session.query(User).filter(User.id == '1' and User.name == 'mdotdot').first()
    
    if user:
        print('name:', user.name)
        print('id_address:', user.id_address)
    
    session.close() # 关闭Session
    
selectData()

2023-11-16 05:56:03,646 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:56:03,649 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.sex AS users_sex, users.nation AS users_nation, users.birth AS users_birth, users.id_address AS users_id_address, users.id_number AS users_id_number, users.creater AS users_creater, users.create_time AS users_create_time, users.updater AS users_updater, users.update_time AS users_update_time, users.comment AS users_comment 
FROM users 
WHERE users.id = %(id_1)s 
 LIMIT %(param_1)s
2023-11-16 05:56:03,650 INFO sqlalchemy.engine.Engine [generated in 0.00076s] {'id_1': '1', 'param_1': 1}
name: mdotdot
id_address: ECNU
2023-11-16 05:56:03,659 INFO sqlalchemy.engine.Engine ROLLBACK

还可以将查询的参数单独写：

In [12]:

Copied!





# 创建Session
session = DbSession()

filter = (User.name=='mdotdot')
user = session.query(User).filter(filter).first()
print(user.name)

session.close()
# 创建Session
session = DbSession()

filter = (User.name=='mdotdot')
user = session.query(User).filter(filter).first()
print(user.name)

session.close()

2023-11-16 05:56:06,732 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:56:06,740 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.sex AS users_sex, users.nation AS users_nation, users.birth AS users_birth, users.id_address AS users_id_address, users.id_number AS users_id_number, users.creater AS users_creater, users.create_time AS users_create_time, users.updater AS users_updater, users.update_time AS users_update_time, users.comment AS users_comment 
FROM users 
WHERE users.name = %(name_1)s 
 LIMIT %(param_1)s
2023-11-16 05:56:06,740 INFO sqlalchemy.engine.Engine [generated in 0.00066s] {'name_1': 'mdotdot', 'param_1': 1}
mdotdot
2023-11-16 05:56:06,805 INFO sqlalchemy.engine.Engine ROLLBACK

2.2.6 修改数据¶

适用于批量修改

In [13]:

Copied!

session.query(User).filter_by(name = "mdotdot").update({'name':"Jack"})
session.commit() # 提交即保存到数据库
session.query(User).filter_by(name = "mdotdot").update({'name':"Jack"})
session.commit() # 提交即保存到数据库

2023-11-16 05:56:09,642 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:56:09,646 INFO sqlalchemy.engine.Engine UPDATE users SET name=%(name)s, update_time=%(update_time)s WHERE users.name = %(name_1)s
2023-11-16 05:56:09,647 INFO sqlalchemy.engine.Engine [generated in 0.00081s] {'name': 'Jack', 'update_time': '2023-11-16 05:54:50', 'name_1': 'mdotdot'}
2023-11-16 05:56:09,699 INFO sqlalchemy.engine.Engine COMMIT

In [14]:

Copied!

find_all()
find_all()

2023-11-16 05:56:12,309 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:56:12,310 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.sex AS users_sex, users.nation AS users_nation, users.birth AS users_birth, users.id_address AS users_id_address, users.id_number AS users_id_number, users.creater AS users_creater, users.create_time AS users_create_time, users.updater AS users_updater, users.update_time AS users_update_time, users.comment AS users_comment 
FROM users
2023-11-16 05:56:12,311 INFO sqlalchemy.engine.Engine [cached since 48.39s ago] {}
id: 2
name: xdot
id_address: ECNU
id_number: 451242142142
id: 1
name: Jack
id_address: ECNU
id_number: 441242142142
2023-11-16 05:56:12,313 INFO sqlalchemy.engine.Engine ROLLBACK

修改成功

适用于获取对象的值,进行操作之后修改

In [15]:

Copied!





# 更新操作
def updateData():
    session = DbSession() # 创建会话
    
    users = session.query(User).filter(User.name=="Jack").first()# 查询条件
    
    if users:
        users.id_number = "abcd" # 更新操作
        session.add(users) # 添加到会话
        session.commit() # 提交即保存到数据库
    
    session.close() # 关闭会话
    
updateData()
# 更新操作
def updateData():
    session = DbSession() # 创建会话
    
    users = session.query(User).filter(User.name=="Jack").first()# 查询条件
    
    if users:
        users.id_number = "abcd" # 更新操作
        session.add(users) # 添加到会话
        session.commit() # 提交即保存到数据库
    
    session.close() # 关闭会话
    
updateData()

2023-11-16 05:56:15,880 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:56:15,881 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.sex AS users_sex, users.nation AS users_nation, users.birth AS users_birth, users.id_address AS users_id_address, users.id_number AS users_id_number, users.creater AS users_creater, users.create_time AS users_create_time, users.updater AS users_updater, users.update_time AS users_update_time, users.comment AS users_comment 
FROM users 
WHERE users.name = %(name_1)s 
 LIMIT %(param_1)s
2023-11-16 05:56:15,882 INFO sqlalchemy.engine.Engine [cached since 9.143s ago] {'name_1': 'Jack', 'param_1': 1}
2023-11-16 05:56:15,886 INFO sqlalchemy.engine.Engine UPDATE users SET id_number=%(id_number)s, update_time=%(update_time)s WHERE users.id = %(users_id)s
2023-11-16 05:56:15,886 INFO sqlalchemy.engine.Engine [generated in 0.00067s] {'id_number': 'abcd', 'update_time': '2023-11-16 05:54:50', 'users_id': 1}
2023-11-16 05:56:15,888 INFO sqlalchemy.engine.Engine COMMIT

In [16]:

Copied!

find_all()
find_all()

2023-11-16 05:56:18,375 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:56:18,376 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.sex AS users_sex, users.nation AS users_nation, users.birth AS users_birth, users.id_address AS users_id_address, users.id_number AS users_id_number, users.creater AS users_creater, users.create_time AS users_create_time, users.updater AS users_updater, users.update_time AS users_update_time, users.comment AS users_comment 
FROM users
2023-11-16 05:56:18,377 INFO sqlalchemy.engine.Engine [cached since 54.45s ago] {}
id: 2
name: xdot
id_address: ECNU
id_number: 451242142142
id: 1
name: Jack
id_address: ECNU
id_number: abcd
2023-11-16 05:56:18,382 INFO sqlalchemy.engine.Engine ROLLBACK

已修改成功

2.2.7 删除数据¶

直接将删除语句写成一行

In [17]:

Copied!

delete_query = session.query(User).filter(User.name=='xdot').delete()
session.commit() # 提交会话
delete_query = session.query(User).filter(User.name=='xdot').delete()
session.commit() # 提交会话

2023-11-16 05:56:21,267 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:56:21,272 INFO sqlalchemy.engine.Engine DELETE FROM users WHERE users.name = %(name_1)s
2023-11-16 05:56:21,273 INFO sqlalchemy.engine.Engine [generated in 0.00125s] {'name_1': 'xdot'}
2023-11-16 05:56:21,275 INFO sqlalchemy.engine.Engine COMMIT

In [18]:

Copied!

find_all()
find_all()

2023-11-16 05:56:23,500 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:56:23,502 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.sex AS users_sex, users.nation AS users_nation, users.birth AS users_birth, users.id_address AS users_id_address, users.id_number AS users_id_number, users.creater AS users_creater, users.create_time AS users_create_time, users.updater AS users_updater, users.update_time AS users_update_time, users.comment AS users_comment 
FROM users
2023-11-16 05:56:23,503 INFO sqlalchemy.engine.Engine [cached since 59.58s ago] {}
id: 1
name: Jack
id_address: ECNU
id_number: abcd
2023-11-16 05:56:23,505 INFO sqlalchemy.engine.Engine ROLLBACK

查找到数据后再删除

In [19]:

Copied!





# 删除操作
def deleteData():
    session = DbSession() # 创建会话
    
    delete_users = session.query(User).filter(User.id == "1").first()
    if delete_users:
        session.delete(delete_users)
        session.commit()
        
    session.close() # 关闭会话
    
deleteData()
# 删除操作
def deleteData():
    session = DbSession() # 创建会话
    
    delete_users = session.query(User).filter(User.id == "1").first()
    if delete_users:
        session.delete(delete_users)
        session.commit()
        
    session.close() # 关闭会话
    
deleteData()

2023-11-16 05:56:25,666 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:56:25,667 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.sex AS users_sex, users.nation AS users_nation, users.birth AS users_birth, users.id_address AS users_id_address, users.id_number AS users_id_number, users.creater AS users_creater, users.create_time AS users_create_time, users.updater AS users_updater, users.update_time AS users_update_time, users.comment AS users_comment 
FROM users 
WHERE users.id = %(id_1)s 
 LIMIT %(param_1)s
2023-11-16 05:56:25,668 INFO sqlalchemy.engine.Engine [cached since 22.02s ago] {'id_1': '1', 'param_1': 1}
2023-11-16 05:56:25,670 INFO sqlalchemy.engine.Engine DELETE FROM users WHERE users.id = %(id)s
2023-11-16 05:56:25,671 INFO sqlalchemy.engine.Engine [generated in 0.00076s] {'id': 1}
2023-11-16 05:56:25,673 INFO sqlalchemy.engine.Engine COMMIT

In [20]:

Copied!

find_all()
find_all()

2023-11-16 05:56:28,480 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:56:28,482 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.sex AS users_sex, users.nation AS users_nation, users.birth AS users_birth, users.id_address AS users_id_address, users.id_number AS users_id_number, users.creater AS users_creater, users.create_time AS users_create_time, users.updater AS users_updater, users.update_time AS users_update_time, users.comment AS users_comment 
FROM users
2023-11-16 05:56:28,483 INFO sqlalchemy.engine.Engine [cached since 64.56s ago] {}
2023-11-16 05:56:28,489 INFO sqlalchemy.engine.Engine ROLLBACK

数据都成功删除

2.2.8 删除表格¶

In [21]:

Copied!

def dropTable():
    sql = 'DROP TABLE IF EXISTS users;'
    result = engine.execute(sql)
    
dropTable()
def dropTable():
    sql = 'DROP TABLE IF EXISTS users;'
    result = engine.execute(sql)
    
dropTable()

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[21], line 5
      2     sql = 'DROP TABLE IF EXISTS users;'
      3     result = engine.execute(sql)
----> 5 dropTable()

Cell In[21], line 3, in dropTable()
      1 def dropTable():
      2     sql = 'DROP TABLE IF EXISTS users;'
----> 3     result = engine.execute(sql)

AttributeError: 'Engine' object has no attribute 'execute'

删除所有表

In [22]:

Copied!

# all tables are deleted
Base.metadata.drop_all(engine)
# all tables are deleted
Base.metadata.drop_all(engine)

2023-11-16 05:56:41,017 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 05:56:41,018 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-16 05:56:41,019 INFO sqlalchemy.engine.Engine [cached since 110.1s ago] {'table_name': 'users', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-16 05:56:41,022 INFO sqlalchemy.engine.Engine 
DROP TABLE users
2023-11-16 05:56:41,022 INFO sqlalchemy.engine.Engine [no key 0.00077s] {}
2023-11-16 05:56:41,187 INFO sqlalchemy.engine.Engine COMMIT

3. ORM练习¶

注意：本次练习中的数据为3.4中数据一次插入的结果。如果不小心多次插入，可使用删除数据或者删除表，再重新插入。

3.1 建立连接¶

In [23]:

Copied!





from sqlalchemy import create_engine
engine = create_engine("postgresql://ecnu10211900416:ECNU10211900416@172.16.253.154:5432/ecnu10211900416",
    echo=True,
    pool_size=8, 
    pool_recycle=60*30
)
from sqlalchemy import create_engine
engine = create_engine("postgresql://ecnu10211900416:ECNU10211900416@172.16.253.154:5432/ecnu10211900416",
    echo=True,
    pool_size=8, 
    pool_recycle=60*30
)

3.2 建立会话¶

In [24]:

Copied!





from sqlalchemy.orm import sessionmaker
# 创建session
DbSession = sessionmaker(bind=engine)
session = DbSession()
from sqlalchemy.orm import sessionmaker
# 创建session
DbSession = sessionmaker(bind=engine)
session = DbSession()

3.3 表格创建¶

四个表格分别是 student, course, teacher, score.

create table student(
s_id varchar(10),
s_name varchar(20),
s_age date,
s_sex varchar(10)
);

create table course(
c_id varchar(10),
c_name varchar(20),
t_id varchar(10)
);

create table teacher (
t_id varchar(10),
t_name varchar(20)
);

create table score (
s_id varchar(10),
c_id varchar(10),
score integer );

In [25]:

Copied!

from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()

/tmp/ipykernel_57/3927807320.py:4: MovedIn20Warning: The ``declarative_base()`` function is now available as sqlalchemy.orm.declarative_base(). (deprecated since: 2.0) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  Base = declarative_base()

In [26]:

Copied!





# 定义Student对象:
class Student(Base):
    # 表的名字:
    __tablename__ = 'student'
    
    # 表的结构:
    s_id = Column(String(10), primary_key=True, unique=True, nullable=False)
    s_name = Column(String(20), nullable=False)
    s_age = Column(Date, nullable=False)
    s_sex = Column(String(10), nullable=False)
# 定义Student对象:
class Student(Base):
    # 表的名字:
    __tablename__ = 'student'
    
    # 表的结构:
    s_id = Column(String(10), primary_key=True, unique=True, nullable=False)
    s_name = Column(String(20), nullable=False)
    s_age = Column(Date, nullable=False)
    s_sex = Column(String(10), nullable=False)

In [27]:

Copied!





# 定义Course对象:
class Course(Base):
    # 表的名字:
    __tablename__ = 'course'
    
    # 表的结构:
    c_id = Column(String(10), primary_key=True, unique=True, nullable=False)
    c_name = Column(String(20), nullable=False)
    t_id = Column(String(10), nullable=False)
# 定义Course对象:
class Course(Base):
    # 表的名字:
    __tablename__ = 'course'
    
    # 表的结构:
    c_id = Column(String(10), primary_key=True, unique=True, nullable=False)
    c_name = Column(String(20), nullable=False)
    t_id = Column(String(10), nullable=False)

In [28]:

Copied!





# 定义Teacher对象:
class Teacher(Base):
    # 表的名字:
    __tablename__ = 'teacher'
    
    # 表的结构:
    t_id = Column(String(10), primary_key=True, unique=True, nullable=False)
    t_name = Column(String(20), nullable=False)
# 定义Teacher对象:
class Teacher(Base):
    # 表的名字:
    __tablename__ = 'teacher'
    
    # 表的结构:
    t_id = Column(String(10), primary_key=True, unique=True, nullable=False)
    t_name = Column(String(20), nullable=False)

In [29]:

Copied!





# 定义Score对象:
class Score(Base):
    # 表的名字:
    __tablename__ = 'score'
    
    # 表的结构:
    id = Column(Integer, autoincrement=True, primary_key=True, unique=True, nullable=False)
    s_id = Column(String(10), nullable=False)
    c_id = Column(String(10), nullable=False)
    score = Column(Integer, nullable=False)
# 定义Score对象:
class Score(Base):
    # 表的名字:
    __tablename__ = 'score'
    
    # 表的结构:
    id = Column(Integer, autoincrement=True, primary_key=True, unique=True, nullable=False)
    s_id = Column(String(10), nullable=False)
    c_id = Column(String(10), nullable=False)
    score = Column(Integer, nullable=False)

In [30]:

Copied!

def createTable():
    # 创建所有继承于Base的类对应的表
    Base.metadata.create_all(engine)
    
createTable()
def createTable():
    # 创建所有继承于Base的类对应的表
    Base.metadata.create_all(engine)
    
createTable()

2023-11-16 06:00:39,848 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2023-11-16 06:00:39,849 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-11-16 06:00:39,850 INFO sqlalchemy.engine.Engine select current_schema()
2023-11-16 06:00:39,851 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-11-16 06:00:39,852 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2023-11-16 06:00:39,853 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-11-16 06:00:39,855 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 06:00:39,857 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-16 06:00:39,858 INFO sqlalchemy.engine.Engine [generated in 0.00124s] {'table_name': 'student', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-16 06:00:39,872 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-16 06:00:39,873 INFO sqlalchemy.engine.Engine [cached since 0.0157s ago] {'table_name': 'course', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-16 06:00:39,874 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-16 06:00:39,875 INFO sqlalchemy.engine.Engine [cached since 0.01769s ago] {'table_name': 'teacher', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-16 06:00:39,876 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-16 06:00:39,877 INFO sqlalchemy.engine.Engine [cached since 0.0201s ago] {'table_name': 'score', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-16 06:00:39,879 INFO sqlalchemy.engine.Engine 
CREATE TABLE student (
	s_id VARCHAR(10) NOT NULL, 
	s_name VARCHAR(20) NOT NULL, 
	s_age DATE NOT NULL, 
	s_sex VARCHAR(10) NOT NULL, 
	PRIMARY KEY (s_id), 
	UNIQUE (s_id)
)


2023-11-16 06:00:39,880 INFO sqlalchemy.engine.Engine [no key 0.00060s] {}
2023-11-16 06:00:39,945 INFO sqlalchemy.engine.Engine 
CREATE TABLE course (
	c_id VARCHAR(10) NOT NULL, 
	c_name VARCHAR(20) NOT NULL, 
	t_id VARCHAR(10) NOT NULL, 
	PRIMARY KEY (c_id), 
	UNIQUE (c_id)
)


2023-11-16 06:00:39,946 INFO sqlalchemy.engine.Engine [no key 0.00120s] {}
2023-11-16 06:00:39,984 INFO sqlalchemy.engine.Engine 
CREATE TABLE teacher (
	t_id VARCHAR(10) NOT NULL, 
	t_name VARCHAR(20) NOT NULL, 
	PRIMARY KEY (t_id), 
	UNIQUE (t_id)
)


2023-11-16 06:00:39,985 INFO sqlalchemy.engine.Engine [no key 0.00108s] {}
2023-11-16 06:00:40,029 INFO sqlalchemy.engine.Engine 
CREATE TABLE score (
	id SERIAL NOT NULL, 
	s_id VARCHAR(10) NOT NULL, 
	c_id VARCHAR(10) NOT NULL, 
	score INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (id)
)


2023-11-16 06:00:40,030 INFO sqlalchemy.engine.Engine [no key 0.00106s] {}
2023-11-16 06:00:40,103 INFO sqlalchemy.engine.Engine COMMIT

3.4 插入数据¶

insert into student (s_id, s_name, s_age, s_sex)
values 
('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1989-07-01' , '女'),
('08' , '王菊' , '1990-01-20' , '女');

insert into course (c_id, c_name, t_id)
values 
('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');

insert into teacher (t_id, t_name)
values 
('01' , '张三'),
('02' , '李四'),
('03' , '王五');

insert into score (s_id, c_id, score)
values 
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);

思考：怎样写代码可以批量插入数据

In [31]:

Copied!





student_data = [
    ('01' , '赵雷' , '1990-01-01' , '男'),
    ('02' , '钱电' , '1990-12-21' , '男'),
    ('03' , '孙风' , '1990-05-20' , '男'),
    ('04' , '李云' , '1990-08-06' , '男'),
    ('05' , '周梅' , '1991-12-01' , '女'),
    ('06' , '吴兰' , '1992-03-01' , '女'),
    ('07' , '郑竹' , '1989-07-01' , '女'),
    ('08' , '王菊' , '1990-01-20' , '女'),
]
course_data = [
    ('01' , '语文' , '02'),
    ('02' , '数学' , '01'),
    ('03' , '英语' , '03'),
]
teacher_data = [
    ('01' , '张三'),
    ('02' , '李四'),
    ('03' , '王五'),
]
score_data = [
    ('01' , '01' , 80),
    ('01' , '02' , 90),
    ('01' , '03' , 99),
    ('02' , '01' , 70),
    ('02' , '02' , 60),
    ('02' , '03' , 80),
    ('03' , '01' , 80),
    ('03' , '02' , 80),
    ('03' , '03' , 80),
    ('04' , '01' , 50),
    ('04' , '02' , 30),
    ('04' , '03' , 20),
    ('05' , '01' , 76),
    ('05' , '02' , 87),
    ('06' , '01' , 31),
    ('06' , '03' , 34),
    ('07' , '02' , 89),
    ('07' , '03' , 98),
]

'''
另一种写法：
students = [Student(s_id=s_id, s_name=s_name, s_age=s_age, s_sex=s_sex)
            for s_id, s_name, s_age, s_sex in student_data]
courses = [Course(c_id=c_id, c_name=c_name, t_id=t_id)
           for c_id, c_name, t_id in course_data]
teachers = [Teacher(t_id=t_id, t_name=t_name)
            for t_id, t_name in teacher_data]
scores = [Score(s_id=s_id, c_id=c_id, score=score)
          for s_id, c_id, score in score_data]

with Session() as session:
    # 插入数据
    session.add_all(students)
    session.add_all(courses)
    session.add_all(teachers)
    session.add_all(scores)
    # 结束事务并提交
    session.commit()

'''


# 为每个表构造一个批量对象生成并插入数据函数
def student_insert_many(data):
    session = DbSession()
    
    for s in data:
        stu = Student(s_id=s[0], s_name=s[1], s_age=s[2], s_sex=s[3])
        session.add(stu)
        
    session.commit()
    session.close()
    
def course_insert_many(data):
    session = DbSession()
    
    for c in data:
        course = Course(c_id=c[0], c_name=c[1], t_id=c[2])
        session.add(course)
        
    session.commit()
    session.close()
    
def teacher_insert_many(data):
    session = DbSession()
    
    for t in data:
        teacher = Teacher(t_id=t[0], t_name=t[1])
        session.add(teacher)
        
    session.commit()
    session.close()
    
def score_insert_many(data):
    session = DbSession()
    
    for s in data:
        score = Score(s_id=s[0], c_id=s[1], score=s[2])
        session.add(score)
        
    session.commit()
    session.close()
student_data = [
    ('01' , '赵雷' , '1990-01-01' , '男'),
    ('02' , '钱电' , '1990-12-21' , '男'),
    ('03' , '孙风' , '1990-05-20' , '男'),
    ('04' , '李云' , '1990-08-06' , '男'),
    ('05' , '周梅' , '1991-12-01' , '女'),
    ('06' , '吴兰' , '1992-03-01' , '女'),
    ('07' , '郑竹' , '1989-07-01' , '女'),
    ('08' , '王菊' , '1990-01-20' , '女'),
]
course_data = [
    ('01' , '语文' , '02'),
    ('02' , '数学' , '01'),
    ('03' , '英语' , '03'),
]
teacher_data = [
    ('01' , '张三'),
    ('02' , '李四'),
    ('03' , '王五'),
]
score_data = [
    ('01' , '01' , 80),
    ('01' , '02' , 90),
    ('01' , '03' , 99),
    ('02' , '01' , 70),
    ('02' , '02' , 60),
    ('02' , '03' , 80),
    ('03' , '01' , 80),
    ('03' , '02' , 80),
    ('03' , '03' , 80),
    ('04' , '01' , 50),
    ('04' , '02' , 30),
    ('04' , '03' , 20),
    ('05' , '01' , 76),
    ('05' , '02' , 87),
    ('06' , '01' , 31),
    ('06' , '03' , 34),
    ('07' , '02' , 89),
    ('07' , '03' , 98),
]

'''
另一种写法：
students = [Student(s_id=s_id, s_name=s_name, s_age=s_age, s_sex=s_sex)
            for s_id, s_name, s_age, s_sex in student_data]
courses = [Course(c_id=c_id, c_name=c_name, t_id=t_id)
           for c_id, c_name, t_id in course_data]
teachers = [Teacher(t_id=t_id, t_name=t_name)
            for t_id, t_name in teacher_data]
scores = [Score(s_id=s_id, c_id=c_id, score=score)
          for s_id, c_id, score in score_data]

with Session() as session:
    # 插入数据
    session.add_all(students)
    session.add_all(courses)
    session.add_all(teachers)
    session.add_all(scores)
    # 结束事务并提交
    session.commit()

'''


# 为每个表构造一个批量对象生成并插入数据函数
def student_insert_many(data):
    session = DbSession()
    
    for s in data:
        stu = Student(s_id=s[0], s_name=s[1], s_age=s[2], s_sex=s[3])
        session.add(stu)
        
    session.commit()
    session.close()
    
def course_insert_many(data):
    session = DbSession()
    
    for c in data:
        course = Course(c_id=c[0], c_name=c[1], t_id=c[2])
        session.add(course)
        
    session.commit()
    session.close()
    
def teacher_insert_many(data):
    session = DbSession()
    
    for t in data:
        teacher = Teacher(t_id=t[0], t_name=t[1])
        session.add(teacher)
        
    session.commit()
    session.close()
    
def score_insert_many(data):
    session = DbSession()
    
    for s in data:
        score = Score(s_id=s[0], c_id=s[1], score=s[2])
        session.add(score)
        
    session.commit()
    session.close()

In [32]:

Copied!





student_insert_many(student_data)
course_insert_many(course_data)
teacher_insert_many(teacher_data)
score_insert_many(score_data)
student_insert_many(student_data)
course_insert_many(course_data)
teacher_insert_many(teacher_data)
score_insert_many(score_data)

2023-11-16 06:02:05,478 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 06:02:05,482 INFO sqlalchemy.engine.Engine INSERT INTO student (s_id, s_name, s_age, s_sex) VALUES (%(s_id__0)s, %(s_name__0)s, %(s_age__0)s, %(s_sex__0)s), (%(s_id__1)s, %(s_name__1)s, %(s_age__1)s, %(s_sex__1)s), (%(s_id__2)s, %(s_name__2)s, %(s_age__2)s, %(s_sex__2)s), (%(s_id__3)s, %(s_na ... 168 characters truncated ... %(s_name__6)s, %(s_age__6)s, %(s_sex__6)s), (%(s_id__7)s, %(s_name__7)s, %(s_age__7)s, %(s_sex__7)s)
2023-11-16 06:02:05,482 INFO sqlalchemy.engine.Engine [generated in 0.00014s (insertmanyvalues)] {'s_name__0': '赵雷', 's_id__0': '01', 's_sex__0': '男', 's_age__0': '1990-01-01', 's_name__1': '钱电', 's_id__1': '02', 's_sex__1': '男', 's_age__1': '1990-12-21', 's_name__2': '孙风', 's_id__2': '03', 's_sex__2': '男', 's_age__2': '1990-05-20', 's_name__3': '李云', 's_id__3': '04', 's_sex__3': '男', 's_age__3': '1990-08-06', 's_name__4': '周梅', 's_id__4': '05', 's_sex__4': '女', 's_age__4': '1991-12-01', 's_name__5': '吴兰', 's_id__5': '06', 's_sex__5': '女', 's_age__5': '1992-03-01', 's_name__6': '郑竹', 's_id__6': '07', 's_sex__6': '女', 's_age__6': '1989-07-01', 's_name__7': '王菊', 's_id__7': '08', 's_sex__7': '女', 's_age__7': '1990-01-20'}
2023-11-16 06:02:05,505 INFO sqlalchemy.engine.Engine COMMIT
2023-11-16 06:02:05,513 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 06:02:05,515 INFO sqlalchemy.engine.Engine INSERT INTO course (c_id, c_name, t_id) VALUES (%(c_id__0)s, %(c_name__0)s, %(t_id__0)s), (%(c_id__1)s, %(c_name__1)s, %(t_id__1)s), (%(c_id__2)s, %(c_name__2)s, %(t_id__2)s)
2023-11-16 06:02:05,516 INFO sqlalchemy.engine.Engine [generated in 0.00009s (insertmanyvalues)] {'c_id__0': '01', 'c_name__0': '语文', 't_id__0': '02', 'c_id__1': '02', 'c_name__1': '数学', 't_id__1': '01', 'c_id__2': '03', 'c_name__2': '英语', 't_id__2': '03'}
2023-11-16 06:02:05,534 INFO sqlalchemy.engine.Engine COMMIT
2023-11-16 06:02:05,541 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 06:02:05,542 INFO sqlalchemy.engine.Engine INSERT INTO teacher (t_id, t_name) VALUES (%(t_id__0)s, %(t_name__0)s), (%(t_id__1)s, %(t_name__1)s), (%(t_id__2)s, %(t_name__2)s)
2023-11-16 06:02:05,543 INFO sqlalchemy.engine.Engine [generated in 0.00008s (insertmanyvalues)] {'t_id__0': '01', 't_name__0': '张三', 't_id__1': '02', 't_name__1': '李四', 't_id__2': '03', 't_name__2': '王五'}
2023-11-16 06:02:05,561 INFO sqlalchemy.engine.Engine COMMIT
2023-11-16 06:02:05,569 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 06:02:05,571 INFO sqlalchemy.engine.Engine INSERT INTO score (s_id, c_id, score) VALUES (%(s_id__0)s, %(c_id__0)s, %(score__0)s), (%(s_id__1)s, %(c_id__1)s, %(score__1)s), (%(s_id__2)s, %(c_id__2)s, %(score__2)s), (%(s_id__3)s, %(c_id__3)s, %(score__3)s), (%(s_id__4)s, %(c_id__4)s, %(score__4 ... 492 characters truncated ... __16)s, %(c_id__16)s, %(score__16)s), (%(s_id__17)s, %(c_id__17)s, %(score__17)s) RETURNING score.id
2023-11-16 06:02:05,572 INFO sqlalchemy.engine.Engine [generated in 0.00011s (insertmanyvalues)] {'s_id__0': '01', 'c_id__0': '01', 'score__0': 80, 's_id__1': '01', 'c_id__1': '02', 'score__1': 90, 's_id__2': '01', 'c_id__2': '03', 'score__2': 99, 's_id__3': '02', 'c_id__3': '01', 'score__3': 70, 's_id__4': '02', 'c_id__4': '02', 'score__4': 60, 's_id__5': '02', 'c_id__5': '03', 'score__5': 80, 's_id__6': '03', 'c_id__6': '01', 'score__6': 80, 's_id__7': '03', 'c_id__7': '02', 'score__7': 80, 's_id__8': '03', 'c_id__8': '03', 'score__8': 80, 's_id__9': '04', 'c_id__9': '01', 'score__9': 50, 's_id__10': '04', 'c_id__10': '02', 'score__10': 30, 's_id__11': '04', 'c_id__11': '03', 'score__11': 20, 's_id__12': '05', 'c_id__12': '01', 'score__12': 76, 's_id__13': '05', 'c_id__13': '02', 'score__13': 87, 's_id__14': '06', 'c_id__14': '01', 'score__14': 31, 's_id__15': '06', 'c_id__15': '03', 'score__15': 34, 's_id__16': '07', 'c_id__16': '02', 'score__16': 89, 's_id__17': '07', 'c_id__17': '03', 'score__17': 98}
2023-11-16 06:02:05,590 INFO sqlalchemy.engine.Engine COMMIT

3.5 习题¶

提示：如果写的代码运行出现断开连接，可以通过下面的语句重新连接。（由于代码的不正确导致的）

In [ ]:

Copied!

session = DbSession()
session = DbSession()

1.查询学生中的所有女生，并将名字按降序排序

In [33]:

Copied!





def task1():
    stu = session.query(Student).filter(Student.s_sex == '女').order_by(Student.s_name.desc()).all()
    for g in stu:
        print('name:', g.s_name)
task1()
def task1():
    stu = session.query(Student).filter(Student.s_sex == '女').order_by(Student.s_name.desc()).all()
    for g in stu:
        print('name:', g.s_name)
task1()

2023-11-16 06:02:30,920 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-16 06:02:30,923 INFO sqlalchemy.engine.Engine SELECT student.s_id AS student_s_id, student.s_name AS student_s_name, student.s_age AS student_s_age, student.s_sex AS student_s_sex 
FROM student 
WHERE student.s_sex = %(s_sex_1)s ORDER BY student.s_name DESC
2023-11-16 06:02:30,924 INFO sqlalchemy.engine.Engine [generated in 0.00118s] {'s_sex_1': '女'}
name: 郑竹
name: 王菊
name: 周梅
name: 吴兰

2.查询" 01 "课程中成绩最高的5位同学的id和成绩

In [38]:

Copied!





def task2():
    result = session.query(Score).filter(Score.c_id == '01').order_by(Score.score.desc()).limit(5).all()
    for r in result:
        print(f's_id:{r.s_id} score:{r.score}')
task2()
def task2():
    result = session.query(Score).filter(Score.c_id == '01').order_by(Score.score.desc()).limit(5).all()
    for r in result:
        print(f's_id:{r.s_id} score:{r.score}')
task2()

2023-11-16 06:08:07,666 INFO sqlalchemy.engine.Engine SELECT score.id AS score_id, score.s_id AS score_s_id, score.c_id AS score_c_id, score.score AS score_score 
FROM score 
WHERE score.c_id = %(c_id_1)s ORDER BY score.score DESC 
 LIMIT %(param_1)s
2023-11-16 06:08:07,667 INFO sqlalchemy.engine.Engine [cached since 322.6s ago] {'c_id_1': '01', 'param_1': 5}
s_id:01 score:80
s_id:03 score:80
s_id:05 score:76
s_id:02 score:70
s_id:04 score:50

3.查询出生年份在1990年的同学(注意：s_age的类型为date）

In [40]:

Copied!





from sqlalchemy import func, extract, and_, or_

def task3():
    result = session.query(Student).filter(extract('Y', Student.s_age) == 1990).all()
    for r in result:
        print(f's_id:{r.s_id} s_name:{r.s_name} s_age:{r.s_age} s_sex:{r.s_sex}')
task3()
from sqlalchemy import func, extract, and_, or_

def task3():
    result = session.query(Student).filter(extract('Y', Student.s_age) == 1990).all()
    for r in result:
        print(f's_id:{r.s_id} s_name:{r.s_name} s_age:{r.s_age} s_sex:{r.s_sex}')
task3()

2023-11-16 06:10:36,570 INFO sqlalchemy.engine.Engine SELECT student.s_id AS student_s_id, student.s_name AS student_s_name, student.s_age AS student_s_age, student.s_sex AS student_s_sex 
FROM student 
WHERE EXTRACT(Y FROM student.s_age) = %(param_1)s
2023-11-16 06:10:36,571 INFO sqlalchemy.engine.Engine [cached since 461.5s ago] {'param_1': 1990}
s_id:01 s_name:赵雷 s_age:1990-01-01 s_sex:男
s_id:02 s_name:钱电 s_age:1990-12-21 s_sex:男
s_id:03 s_name:孙风 s_age:1990-05-20 s_sex:男
s_id:04 s_name:李云 s_age:1990-08-06 s_sex:男
s_id:08 s_name:王菊 s_age:1990-01-20 s_sex:女

4.查询每位同学一共选择了几门课和总成绩

In [43]:

Copied!





def task4():
    result = session.query(Score.s_id, func.count(Score.c_id), func.sum(Score.score)).group_by(Score.s_id).all()
    for r in result:
        print(f's_id:{r.s_id} course count:{r[1]} score sum:{r[2]}')
        
task4()
def task4():
    result = session.query(Score.s_id, func.count(Score.c_id), func.sum(Score.score)).group_by(Score.s_id).all()
    for r in result:
        print(f's_id:{r.s_id} course count:{r[1]} score sum:{r[2]}')
        
task4()

2023-11-16 06:12:38,871 INFO sqlalchemy.engine.Engine SELECT score.s_id AS score_s_id, count(score.c_id) AS count_1, sum(score.score) AS sum_1 
FROM score GROUP BY score.s_id
2023-11-16 06:12:38,872 INFO sqlalchemy.engine.Engine [cached since 572.3s ago] {}
s_id:06 course count:2 score sum:65
s_id:03 course count:3 score sum:240
s_id:04 course count:3 score sum:100
s_id:01 course count:3 score sum:269
s_id:02 course count:3 score sum:210
s_id:07 course count:2 score sum:187
s_id:05 course count:2 score sum:163

5.查询01课程或02课程成绩大于85的同学id

In [44]:

Copied!





def task5():
    # 投影自带去重
    result = session.query(Score.s_id).filter(and_(or_(Score.c_id=='01', Score.c_id=='02'), Score.score>85)).all()
    for r in result:
        print('s_id:', r.s_id)
    
task5()
def task5():
    # 投影自带去重
    result = session.query(Score.s_id).filter(and_(or_(Score.c_id=='01', Score.c_id=='02'), Score.score>85)).all()
    for r in result:
        print('s_id:', r.s_id)
    
task5()

2023-11-16 06:13:23,153 INFO sqlalchemy.engine.Engine SELECT score.s_id AS score_s_id 
FROM score 
WHERE (score.c_id = %(c_id_1)s OR score.c_id = %(c_id_2)s) AND score.score > %(score_1)s
2023-11-16 06:13:23,154 INFO sqlalchemy.engine.Engine [cached since 596.1s ago] {'c_id_1': '01', 'c_id_2': '02', 'score_1': 85}
s_id: 01
s_id: 05
s_id: 07

In [ ]: