python 使用sqlalchemy的ORM操作mysql（1）

ORM（Object-Relational Mapping），就是把关系数据库的表结构映射到对象上。

跟数据库创建链接

# -*- coding: utf-8 -*-
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

# 创建连接
engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/test", encoding='utf-8', echo=True)
# 生成orm基类
base = declarative_base()

映射到数据库表

class User(base):
    __tablename__ = 'user'
    user_id = Column(Integer, primary_key=True)
    name = Column(String(32))
    nick = Column(String(32))
    mobile_phone = Column(String(32))
    password = Column(String(64))

Session_class = sessionmaker(bind=engine)
Session = Session_class()

查询name为001的一条记录

my_user = Session.query(User).filter_by(name="001").one()
print(my_user.user_id)
print(my_user.mobile_phone)

查询name为11%的多条记录

users = Session.query(User).filter(User.name.like('11%')).all()
print(len(users))
print(users[0].name)

查询name为11%的多条记录 根据id排序

users = Session.query(User).filter(User.name.like('11%')).order_by(User.name.asc()).all()
print(users[0].name)

添加一条记录

obj = User(name="zp_test_1", mobile_phone='13847504112', password='111111')
Session.add(obj)
Session.commit()

添加多条记录

Session.add_all([
    User(name="zp_test_3", mobile_phone='13847504113', password='111112'),
    User(name="zp_test_4", mobile_phone='13847504114', password='111113'),
])

my_user = Session.query(User).filter_by(mobile_phone="13847504113").one()
print(my_user.user_id)

删除一条记录

Session.query(User).filter_by(user_id=24250440).delete()
Session.commit()

删除多条记录

Session.query(User).filter(User.user_id > 24250440).delete()
Session.commit()

修改一条记录

Session.query(User).filter_by(user_id=24250439).update({"nick": "new_nick"})
Session.commit()

修改多条记录

Session.query(User).filter(User.user_id > 24250437).update({User.name: User.name + "_new"}, synchronize_session=False)
Session.commit()

完整代码

# -*- coding: utf-8 -*-
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

# 创建连接
engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/zhenmuwang", encoding='utf-8', echo=True)
# 生成orm基类
base = declarative_base()

class User(base):
    __tablename__ = 'stpc_user'
    user_id = Column(Integer, primary_key=True)
    name = Column(String(32))
    nick = Column(String(32))
    mobile_phone = Column(String(32))
    password = Column(String(64))

Session_class = sessionmaker(bind=engine)
Session = Session_class()

# 查询name为001的一条记录
my_user = Session.query(User).filter_by(name="001").one()
print(my_user.user_id)
print(my_user.mobile_phone)

# 查询name为11%的多条记录
users = Session.query(User).filter(User.name.like('11%')).all()
print(len(users))
print(users[0].name)

# 查询name为11%的多条记录 根据id排序
users = Session.query(User).filter(User.name.like('11%')).order_by(User.name.asc()).all()
print(users[0].name)

# 添加一条记录
obj = User(name="zp_test_1", mobile_phone='13847504112', password='111111')
Session.add(obj)
Session.commit()

# 添加多条记录
Session.add_all([
    User(name="zp_test_3", mobile_phone='13847504113', password='111112'),
    User(name="zp_test_4", mobile_phone='13847504114', password='111113'),
])

my_user = Session.query(User).filter_by(mobile_phone="13847504113").one()
print(my_user.user_id)

# 删除一条记录
Session.query(User).filter_by(user_id=24250440).delete()
Session.commit()

# 删除多条记录
Session.query(User).filter(User.user_id > 24250440).delete()
Session.commit()

# 修改一条记录
Session.query(User).filter_by(user_id=24250439).update({"nick": "new_nick"})
Session.commit()

# 修改多条记录
Session.query(User).filter(User.user_id > 24250437).update({User.name: User.name + "_new"}, synchronize_session=False)
Session.commit()