SQLalchemy的初步学习

创建表，创建引擎

create_table.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text
from sqlalchemy import create_engine
#创建ORM模型基类
Base = declarative_base() # 相当于django中的Model

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), index=True)

engine = create_engine("mysql+pymysql://root:kkd*33251.@127.0.0.1:3306/db103?charset=utf8")
Base.metadata.create_all(engine)

sqlalchemy的CRUD之–插入数据

from create_table import User, engine
from sqlalchemy.orm import sessionmaker


# 打开一个查询窗口
Session = sessionmaker(engine)
db_session = Session()

# 增加单行数据
# user_obj = User(name='Ywb')
# #通过db_session 已经打开会话窗口 提交数据
# db_session.add(user_obj)
# #执行CRUD
# db_session.commit()
# db_session.close()

# 批量增加数据
db_session.add_all([
    User(name='叶芷'),
    User(name='杨思思'),
    User(name='米高')
])
db_session.commit()
db_session.close()

sqlalchemy的CRUD之–查询数据

#查询
#会话窗口
from sqlalchemy.orm import  sessionmaker
from create_table import  engine, User

Session = sessionmaker(engine)
db_session = Session()

# 1、简单查询
select * from table
user_list = db_session.query(User).all()
print(user_list) # 此处返回的是对象列表
#[<create_table.User object at 0x037B93F0>, <create_table.User object at 0x037B9570>, <create_table.User object at 0x037B95B0>, <create_table.User object at 0x037B9590>]
for row in user_list:
    print(row.id, row.name)
# 1 Ywb
# 2 叶芷
# 3 杨思思
# 4 米高

# 2、带条件的查询
user_list = db_session.query(User).filter(user.id == 3).all()
print(user_list)

user_list = db_session.query(User).filter(user.id <= 3).all()
print(user_list)

user_list = db_session.query(User).filter(user.id >= 3).all()
print(user_list)

#查看sql语句
sql = db_session.query(User).filter(User.id >=4)
print(sql)

sqlalchemy的CRUD之–修改数据

#更新数据
from sqlalchemy.orm import sessionmaker
from create_table import engine, User
Session = sessionmaker()
db_session = Session()
# sql：update "User" set 'name' = '666' where id =1

#1、修改数据
res = db_session.query(User).filter(User.name == 'Ywb').update({"name": "谢天笑"})
print(res)
db_session.commit()
db_session.close()
# 批量修改
res = db_session.query(User).filter(User.id >= 5).update({"name": "嘻嘻哈哈"})

sqlalchemy的CRUD之–删除数据

#删除数据
from sqlalchemy.orm import sessionmaker
from create_table import engine, User

Session = sessionmaker(engine)
db_session = Session()

#sql:delete from User where id = 1

# 1、删除
res = db_session.query(User).filter(User.id == 2).delete()
print(res)# 返回的是删除的个数
db_session.commit()
db_session.close()

# 2、删除多条
res = db_session.query(User).filter(User.name == "叶芷").delete()
print(res)
db_session.commit()
db_session.close()