实习Day 1 记录 - SQLAlchemy

2024年07月01日 895 字大概 4 分钟

SQLAlchemy

author: zzy
date: 2024-7-1

数据库结构

结构树

MetaData(元数据:可看做Table对象目录)
- Table
  - column
  - key
  - constraint
  - index

MetaData

保存数据库结构, 把数据库结构结合在一起

1 2	`from sqlalchemy import MetaData metadata = MetaData()`

Table

通过调用Table构造函数来初始化Table对象.

在构造函数中提供MetaData对象和表名, 任何其他参数都被认为是列对象, 列对象通过调用Column函数创建.

from sqlalchemy import Table, Column, Integer, Numeric, String, ForeignKey

cookies = Table('cookies', metadata,
    Column('cookie_id', Interger(), primary_key=True),
    Column('cookie_name', String(50), index=True),
    Column('cookie_recipe_url', String(255)),
    Column('cookie_sku', String(55)),
    Column('quantity', Integer()),
    Column('unit_cost', Numeric(12, 2)),
)

Column

Column(<键名>, <数据类型>, <约束>)

key and constraint

1
2
3

from sqlalchemy import PrimaryKeyConsteraint, UniqueConstraint, CheckConstraint

PrimaryKeyConstraint('user_id', name='user_pk')

连接到数据库

from sqlalchemy import create_engine, text

engine = create_engine("sqlite://")# 建立连接, 具体URL留空则连接python内存, 并不会真的连接内容.
connection = engine.connect()# 实例化连接对象

# execute
wd = text("select 'word' as greeting")
result = connection.execute(wd)# 执行命令 返回值为对应数据库返回值

# row(记录)
for row in result:
    print(row)

engine使用

# 从engine中获取一个连接
with engine.connect() as connection:
    # 开启一个事务块
    with connect.begin():
        connection.execute(
        	insert_stmt,
            {"name":"", "fullname": ""}
        )
    # end of inner block: commits transaction, or rollback if exception
# end of outer block: releases connection to th connection pool

代码和注释摘自视频https://www.youtube.com/watch?v=Uym2DHnUEno的20:00处

处理数据

增 `insert`

作为Table对象的实例方法:

ins = cookies.insert().values(
    cookie_name="chocolate chip",
    cookie_recipe_url="example.com",
    cooki_sku="CC01",
    quantity="12",
    unit_cost="0.50" 
)
print(str(ins))# 实际要执行的SQL语句
ins.compile().params# 编译语句, 但不执行, 得到执行后的结果
result = connection.execute(ins)# 执行插入语句

作为独立的函数:

from sqlalchemy import insert
ins = insert(cookies).values(
	cookie_name="chocolate chip",
    cookie_recipe_url="example.com",
	cookie_sku="CC01",
    quantity="12",
	unit_cost="0.50"
)

使用关键字参数

ins = cookies.insert()
result = connection.executte(
	ins,
    cookie_name='dark chocolate chip',
    cookie_recipe_url='example.com',
    cookie_sku='CC01',
    quantity='12',
	unit_cost='0.50'
)

插入多条记录

inventory_list = [
    {
        'key1': 'value1',
        'key2': 'value2',
    },
	{
        'key3': 'value3',
        'key4': 'value4',
    },
    {
        'key5': 'value5',
        'key6': 'value6',
    },
]
result = connection.execute(ins, inventory_list)

删 `delete`

from sqlalchemy import delete

u = delete(cookies).where(cookies.c.cookie_name == "dark chocolate chip")
result = connection.execute(u)
print(result.rowcount)

s = select([cookies]),where(cookies.c.cookie_name == "dark chocolate chip")
result = connection.execute(s).fetchall()
print(len(result))

改 `update`

from sqlalchemy import update

u = update(cookie).where(cookies.c.cpploe_name == "chocolate chip")
u = u.values(quantity=(cookie.c.quantity+120))
result = connection.execute(u)
print(result.rowcount)
s = select([cookies]).where(cookies.c.cpploe_name == "chocolate chip")
result =  connection.execute(s).first()
for key in result.keys():
    print('{:>20}':{}'.format(key,result[key]))

查 `select`

from sqlalchemy import select
s = select([cookies])
rp = connection.execute(s)
result = rp.fetchall()

ORM

Object-Relational Mapping, ORM）

from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table  
from sqlalchemy.orm import mapper, sessionmaker  
from sqlalchemy.ext.declarative import declarative_base  
  
# 基类 
Base = declarative_base()  
  
class User(Base):  
    __tablename__ = 'users'  # 指定这个类映射到的数据库表名  
    id = Column(Integer, primary_key=True)  # 主键  
    name = Column(String)  # 定义键名 数据类型 
    fullname = Column(String)  # 定义键名 数据类型
  
engine = create_engine('sqlite:///example.db')# 建立连接
Base.metadata.create_all(engine)# 建表

# 实例化会话类
Session = sessionmaker(bind=engine)  
session = Session()

# 创建 User 对象, 相当于SQL的一条记录
new_user = User(name='newuser', fullname='New User')  
session.add(new_user)  
  
# 提交
session.commit()  
  
# 查询 User 对象  
users = session.query(User).all()  
for user in users:  
    print(user.name, user.fullname)  
  
# 关闭会话  
session.close()

在视频中提到, 不建议使用close()方法

同样地优化框架为:

with Session() as session:  
    # 数据库操作  
    pass  
# 会话关闭