python玩转SQLAlchemy

安装SQLAlchemy

pip install sqlalchemy

实例

#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
 #  @file       py_sqlalchemy.py
 #  @brief      SQLAlchemy操作数据库
 #  @version    1.0.0
 #  @author     LindenTao(lindentao@qq.com)
 #  @date       17/8/27 上午09:47
 #  @history    <author>   <time>  <desc>
"""

# 创建数据库test_sql

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 初始化数据库连接，数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名/编码
engine = create_engine("mysql+mysqlconnector://root:123456@localhost:13306/test_sql?charset=utf8", encoding='utf-8',
                       echo=False)
# 创建对象的基类:
Base = declarative_base()


# 定义SysUser对象:
class SysUser(Base):
    # 表的名字:
    __tablename__ = 't_sys_user'

    # 表的结构:
    id = Column(BIGINT, nullable=False, autoincrement=True, primary_key=True)
    user_name = Column(VARCHAR(20), nullable=False, default='', unique=True)
    password = Column(CHAR(32), nullable=False, default='')
    real_name = Column(VARCHAR(30), nullable=False, default='')
    mobile = Column(VARCHAR(15), nullable=False, default='')
    status = Column(CHAR(1), nullable=False, default='y')
    agent_id = Column(BIGINT, nullable=False, default=0)
    role_list = Column(VARCHAR(50), nullable=False, default='')
    business = Column(VARCHAR(20), nullable=False, default='')
    login_amount = Column(BIGINT, nullable=False, default=0)
    last_login_time = Column(TIMESTAMP, nullable=False, default='0000-00-00 00:00:00')
    last_login_ip = Column(VARCHAR(20), nullable=False, default='')
    remark = Column(VARCHAR(150), nullable=False, default='')
    mtime = Column(TIMESTAMP, nullable=False, default='0000-00-00 00:00:00')
    ctime = Column(TIMESTAMP, nullable=False, default='0000-00-00 00:00:00')

    def __repr__(self):
        return "<SysUser(id='%s', user_name='%s', password='%s', real_name='%s')>" % (
            self.id, self.user_name, self.password, self.real_name)


class SysLogs(Base):
    # 表的名字:
    __tablename__ = 't_sys_logs'

    # 表的结构:
    id = Column(BIGINT, nullable=False, autoincrement=True, primary_key=True)
    action = Column(VARCHAR(20), nullable=False, default='')
    content = Column(TEXT, default='')
    user_name = Column(VARCHAR(20), nullable=False, default='')
    ctime = Column(TIMESTAMP, nullable=False, default='0000-00-00 00:00:00', index=True)

    def __repr__(self):
        return "<SysUser(id='%s', action='%s', content='%s', user_name='%s', ctime='%s')>" % (
            self.id, self.action, self.content, self.user_name, self.ctime)


# 创建表，如果表存在则忽视
Base.metadata.create_all(engine)

# 创建Session类型:
Session = sessionmaker(bind=engine)
# 创建session对象:
session = Session()

# 增Insert
# res = session.execute("INSERT INTO t_sys_user (user_name, password) VALUES ('lindentao', 'xxxxxx')")

# ouser = SysUser(user_name='lindentao')
# session.add(ouser)
# session.commit()

# 删Delete
# user = session.query(SysUser).filter_by(user_name='lindentao').first()
# session.delete(user)
# session.commit()

# 改Update
# session.query(SysUser).filter_by(user_name='lindentao').update({SysUser.password: 'xxxxxx'})
# session.commit()

# 查Select
# fetchall返回list
# res = session.execute("SELECT * FROM t_sys_user ORDER BY id DESC").fetchall()
# res = session.execute("SELECT * FROM t_sys_user WHERE user_name=:user_name limit 1", {"user_name": "lindentao"}).fetchone()
# print(res)

# ouser = session.query(SysUser).order_by(SysUser.id.desc()).all()      # 调用all()则返回所有行
# ouser = session.query(SysUser).order_by(SysUser.id)[0:3]      # [0:3],返回前3行
# for o in ouser:
#     print(o, o.id)

# ouser = session.query(SysUser).order_by(SysUser.id).first()       # first()返回第一行
# ouser = session.query(SysUser).filter_by(user_name='lindentao').first()  # filter是where条件
# ouser = session.query(SysUser).filter(SysUser.user_name == 'lindentao').first()
# print(ouser, ouser.user_name)


# 关闭Session:
session.close()

链接

• http://docs.sqlalchemy.org/en/latest/orm/tutorial.html
• http://docs.sqlalchemy.org/en/latest/core/tutorial.html

赞助

如果您认为以上内容对您有所帮助或者您心情好，不妨支持我一下，谢谢。