SQLAlchemy 第三篇

使用insert语句

from sqlalchemy import Table, Column, Integer, String, MetaDatametadata_obj = MetaData()
user_table = Table("user_account",metadata_obj,Column("id", Integer, primary_key=True),Column("name", String(255)),Column("fullname", String(255)),
)
from sqlalchemy import insert
stmt = insert(user_table).values(name="new user", fullname="New User Name")
print(stmt)
compiled = stmt.compile()
compiled.params

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname){'name': 'new user', 'fullname': 'New User Name'}

执行语句

from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:root@localhost/test?charset=utf8mb4",echo=True,echo_pool=True,pool_size=20)
with engine.connect() as conn:result = conn.execute(stmt)conn.commit()

2023-12-14 13:54:07,384 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2023-12-14 13:54:07,385 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-12-14 13:54:07,386 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2023-12-14 13:54:07,387 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-12-14 13:54:07,387 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2023-12-14 13:54:07,388 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-12-14 13:54:07,390 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 13:54:07,391 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s)
2023-12-14 13:54:07,392 INFO sqlalchemy.engine.Engine [generated in 0.00121s] {'name': 'new user', 'fullname': 'New User Name'}
2023-12-14 13:54:07,392 INFO sqlalchemy.engine.Engine COMMIT

插入多条

with engine.connect() as conn:result = conn.execute(insert(user_table),[{"name": "user1", "fullname": "User One"},{"name": "user2", "fullname": "User Two"},])conn.commit()

2023-12-14 14:05:54,902 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 14:05:54,904 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s)
2023-12-14 14:05:54,904 INFO sqlalchemy.engine.Engine [cached since 92.92s ago] [{'name': 'user1', 'fullname': 'User One'}, {'name': 'user2', 'fullname': 'User Two'}]
2023-12-14 14:05:54,906 INFO sqlalchemy.engine.Engine COMMIT

# 查询SELECT

from sqlalchemy import select
stmt = select(user_table).where(user_table.c.name == "user1")
print(stmt)

SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = :name_1

from sqlalchemy.orm import Session
with Session(engine) as conn:result = conn.execute(stmt)for row in result:print(row)

2023-12-14 14:10:13,515 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 14:10:13,517 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = %(name_1)s
2023-12-14 14:10:13,519 INFO sqlalchemy.engine.Engine [cached since 82.49s ago] {'name_1': 'user1'}
(2, 'user1', 'User One')
(4, 'user1', 'User One')
(8, 'user1', 'User One')
2023-12-14 14:10:13,521 INFO sqlalchemy.engine.Engine ROLLBACK

指定查询字段column

print(select(user_table.c.name,user_table.c.id))

SELECT user_account.name, user_account.id 
FROM user_account

from typing import List, Optional
from sqlalchemy.orm import Mapped, mapped_column, relationshipfrom sqlalchemy.orm import DeclarativeBaseclass Base(DeclarativeBase):passclass User(Base):__tablename__ = "user_account"id: Mapped[int] = mapped_column(primary_key=True)name: Mapped[str] = mapped_column(String(20))fullname: Mapped[Optional[str]]def __repr__(self) -> str:return f"User(id={self.id},name={self.name})"with Session(engine) as conn:result = conn.execute(select(User.id,User.name)).first()print(result)

2023-12-14 14:22:09,242 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 14:22:09,244 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name 
FROM user_account
2023-12-14 14:22:09,246 INFO sqlalchemy.engine.Engine [generated in 0.00123s] {}
(1, 'new user')
2023-12-14 14:22:09,248 INFO sqlalchemy.engine.Engine ROLLBACK

字段别名

stmt = select(("tmpName:"+user_table.c.name).label("realname")).order_by(user_table.c.id)
with engine.connect() as conn:for row in conn.execute(stmt):print(row.realname)

2023-12-14 14:31:05,943 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 14:31:05,944 INFO sqlalchemy.engine.Engine SELECT concat(%(name_1)s, user_account.name) AS realname 
FROM user_account ORDER BY user_account.id
2023-12-14 14:31:05,945 INFO sqlalchemy.engine.Engine [cached since 43.98s ago] {'name_1': 'tmpName:'}
tmpName:new user
tmpName:user1
tmpName:user2
tmpName:user1
tmpName:user2
tmpName:user1
tmpName:user2
2023-12-14 14:31:05,947 INFO sqlalchemy.engine.Engine ROLLBACK

## 字段表达式

from sqlalchemy import text
stmt = select(user_table.c.id,text("id+100"),text("'id+100'"),)
with engine.connect() as conn:result = conn.execute(stmt)for row in result:print(row)

2023-12-14 14:34:46,915 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 14:34:46,916 INFO sqlalchemy.engine.Engine SELECT user_account.id, id+100, 'id+100' 
FROM user_account
2023-12-14 14:34:46,917 INFO sqlalchemy.engine.Engine [generated in 0.00169s] {}
(1, 101, 'id+100')
(2, 102, 'id+100')
(3, 103, 'id+100')
(4, 104, 'id+100')
(5, 105, 'id+100')
(8, 108, 'id+100')
(9, 109, 'id+100')
2023-12-14 14:34:46,919 INFO sqlalchemy.engine.Engine ROLLBACK

使用orm进行增删改查

增加

squidward = User(name="squidward", fullname="Squidward Tentacles")
krabs = User(name="ehkrabs", fullname="Eugene H. Krabs")
with Session(engine) as session:session.add(squidward)session.add(krabs)session.commit()print("*"*50)# 自动生成的主键属性print(squidward.id)print(krabs.id)

2023-12-14 16:57:42,724 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 16:57:42,725 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s)
2023-12-14 16:57:42,726 INFO sqlalchemy.engine.Engine [cached since 261.2s ago] {'name': 'squidward', 'fullname': 'Squidward Tentacles'}
2023-12-14 16:57:42,728 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s)
2023-12-14 16:57:42,728 INFO sqlalchemy.engine.Engine [cached since 261.2s ago] {'name': 'ehkrabs', 'fullname': 'Eugene H. Krabs'}
2023-12-14 16:57:42,730 INFO sqlalchemy.engine.Engine COMMIT
**************************************************
2023-12-14 16:57:42,732 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 16:57:42,733 INFO sqlalchemy.engine.Engine SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname 
FROM user_account 
WHERE user_account.id = %(pk_1)s
2023-12-14 16:57:42,733 INFO sqlalchemy.engine.Engine [cached since 84.44s ago] {'pk_1': 26}
26
2023-12-14 16:57:42,735 INFO sqlalchemy.engine.Engine SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname 
FROM user_account 
WHERE user_account.id = %(pk_1)s
2023-12-14 16:57:42,736 INFO sqlalchemy.engine.Engine [cached since 84.44s ago] {'pk_1': 27}
27
2023-12-14 16:57:42,737 INFO sqlalchemy.engine.Engine ROLLBACK

删除

session = Session(engine)
patrick = session.get(User, 3)
session.delete(patrick)
session.commit()

2023-12-14 17:01:14,641 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 17:01:14,642 INFO sqlalchemy.engine.Engine SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname 
FROM user_account 
WHERE user_account.id = %(pk_1)s
2023-12-14 17:01:14,642 INFO sqlalchemy.engine.Engine [cached since 39.91s ago] {'pk_1': 3}
2023-12-14 17:01:14,646 INFO sqlalchemy.engine.Engine DELETE FROM user_account WHERE user_account.id = %(id)s
2023-12-14 17:01:14,647 INFO sqlalchemy.engine.Engine [generated in 0.00076s] {'id': 3}
2023-12-14 17:01:14,650 INFO sqlalchemy.engine.Engine COMMIT

from sqlalchemy import delete
stmt = delete(User).where(User.name.in_(["squidward", "sandy"]))
session.execute(stmt)
session.commit()

2023-12-14 17:04:21,096 INFO sqlalchemy.engine.Engine DELETE FROM user_account WHERE user_account.name IN (%(name_1_1)s, %(name_1_2)s)
2023-12-14 17:04:21,098 INFO sqlalchemy.engine.Engine [cached since 15.71s ago] {'name_1_1': 'squidward', 'name_1_2': 'sandy'}
2023-12-14 17:04:21,099 INFO sqlalchemy.engine.Engine COMMIT

修改

from sqlalchemy import update
stmt = (update(User).where(User.name.in_(["squidward", "sandy"])).values(fullname="Name starts with S")
)
session.execute(stmt)
session.commit()    

2023-12-14 17:04:58,832 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 17:04:58,835 INFO sqlalchemy.engine.Engine UPDATE user_account SET fullname=%(fullname)s WHERE user_account.name IN (%(name_1_1)s, %(name_1_2)s)
2023-12-14 17:04:58,836 INFO sqlalchemy.engine.Engine [generated in 0.00123s] {'fullname': 'Name starts with S', 'name_1_1': 'squidward', 'name_1_2': 'sandy'}
2023-12-14 17:04:58,840 INFO sqlalchemy.engine.Engine COMMIT