Python Sqlalchemy数据库连接

一、连接数据

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 1. 连接数据库
dbHost = 'postgres://用户名:密码@主机:端口/数据库名'
engine = create_engine(dbHost)
# create_engine() 参数说明：
# a. dbHost, # 数据库连接信息
# b. echo=False,  # 是否打印SQL
# c. pool_size=10,  # 连接池的大小，指定同时在连接池中保持的数据库连接数，默认:5
# d. max_overflow=20,  # 超出连接池大小的连接数，超过这个数量的连接将被丢弃,默认: 5

# 2. 创建会话
Session = sessionmaker(bind=engine)
session = Session()

二、模型

from sqlalchemy import Column, String, TIMESTAMP
from sqlalchemy.dialects.postgresql import INTEGER, TEXT, VARCHAR
from sqlalchemy.ext.declarative import declarative_base

# 模型父类
Base = declarative_base()
class User(Base):
    __tablename__ = 'auth_user'
    id = Column(INTEGER, primary_key=True)
    active = Column(INTEGER)
    name = Column(TEXT)

class Size(Base):
    __tablename__ = 'master_size'
    id = Column(INTEGER, primary_key=True)
    active = Column(INTEGER)
    size = Column(TEXT)
    user_id = Column(INTEGER)

三、ORM操作

from sqlalchemy import func

# 1. 查询
# func.lower(User.name)  转换为小写
user_objs = session.query(User).filter(func.lower(User.name) == 'jack', User.active == 0)
user_obj = session.query(User).get(5)   # 获取id=5
user_obj = session.query(User).get({"id": 5, "active": 0}) # 获取id=5 and active=0

# 2. 添加 
# 添加User
user_obj = User(name='张三'， active=0)
session.add(user_obj)   # 添加
session.commit()   # 提交
# 添加Size
size_obj = Size(size='XXL'， active=0, user_id=user_obj.id)
session.add(size_obj)   # 添加
session.commit()   # 提交

# 3. 删除
user_objs = session.query(User).filter(func.lower(User.name) == 'jack', User.active == 0)
session.delete(user_objs)
session.commit()

# 修改
user_obj = session.query(User).get({"id": 5, "active": 0})
user_obj.name = '李四'
session.commit()