Python3中sqlalchemy模块学习之增加操作

起步

#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""增"""

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

engine = create_engine('mysql+pymysql://root:123@localhost:3306/sqlalchemy_db?charset=utf8mb4', max_overflow=10, echo=True)

Base = declarative_base()


# 多对一：假设多个员工可以属于一个部门，而多个部门不能有同一个员工


class Dep(Base):
    __tablename__ = 'dep'
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)


class Emp(Base):
    __tablename__ = 'emp'
    id = Column(Integer, primary_key=True, autoincrement=True)
    ename = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey(column='dep.id'))


def init_db():
    Base.metadata.create_all(bind=engine)


def drop_db():
    Base.metadata.drop_all(bind=engine)


# 创建表
# init_db()

# 删除表
# drop_db()

# 拿连接
session = sessionmaker(bind=engine)()

增加一个

相当于 insert into Dep(dname) values('销售');

row = Dep(dname='销售')
session.add(row)  # 返回None

增加多个

相当于 insert into Dep(dname) values('技术'), ('运营'), ('人事');

session.add_all([
    Dep(dname='技术'),
    Dep(dname='运营'),
    Dep(dname='人事')
])

提交

特别的： 增删改需要 <commit>

session.commit()

关闭连接

session.close()