接着我上篇文章继续：MySQL数据库操作

在上篇文章中，我们创建了数据表。
现在我们需要向数据表增加、删除、查询、修改数据

一、创建会话

创建一个会话对象;类似pymysql中的游标,检测我们的sql语句位置

from sqlalchemy.orm import sessionmaker
base.metadata.create_all(db)
s = sessionmaker(bind = db)
session = s()

单条语句插入

# 二、单条语句插入
user = User(id = 1,name = "zhangsan",addree = "beijing",age = 20)
session.add(user)
session.commit()

多条插入

    session.add_all([User(id = 2,name = "lisi",addree = "chaoyangqu",age = 23),User(id = 3,name = "lisi",addree = "chaoyangqu",age = 23),User(id = 4,name = "lihua",addree = "chaoyangqu",age = 26)])session.commit()

查询全部数据

    result = session.query(User).all()for r in result:print(r.name,r.age)

查询单条数据

    result = session.query(User).get(1)print(result.name,result.age)

条件查询

    result = session.query(User).filter_by(name = "zhangsan")for i in result:print(i.name)print(i.age)

修改数据

    result = session.query(User).get(1)print(result.age)result.age = 20session.commit()print(result.age)

删除数据

    result = session.query(User).get(1)session.delete(result)re = session.query(User).all()

完整的代码：

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
#创建链接
#mysql://scott:tiger@hostname/dbname
db = sqlalchemy.create_engine("mysql://root:root@localhost/sqlorm")#创建基类
base = declarative_base((db))#创建数据表
class User(base):__tablename__ = 'user'id = sqlalchemy.Column(sqlalchemy.Integer,primary_key = True)name = sqlalchemy.Column(sqlalchemy.String(32))addree = sqlalchemy.Column(sqlalchemy.String(32))age = sqlalchemy.Column(sqlalchemy.Integer)class UserInfo(base):__tablename__ = 'userinfo'id = sqlalchemy.Column(sqlalchemy.Integer,primary_key = True)phone = sqlalchemy.Column(sqlalchemy.String(11))user = sqlalchemy.Column(sqlalchemy.Integer,sqlalchemy.ForeignKey('user.id'))if __name__=="__main__":# 一、创建会话#创建一个会话对象;类似pymysql中的游标,检测我们的sql语句位置base.metadata.create_all(db)s = sessionmaker(bind = db)session = s()# 二、单条语句插入# user = User(id = 1,name = "zhangsan",addree = "beijing",age = 20)# session.add(user)# session.commit()# 三、多条语句插入# 多条语句的插入,使用add_all()方法，数据用列表形式# session.add_all([# User(id = 2,name = "lisi",addree = "chaoyangqu",age = 23),# User(id = 3,name = "lisi",addree = "chaoyangqu",age = 23),# User(id = 4,name = "lihua",addree = "chaoyangqu",age = 26)]# )# session.commit()#四、查询所有的数据#惰性特质# result = session.query(User).all()# for r in result:#     print(r.name,r.age)# 五、查询单条数据# get()方法使用主键查询,参数输入id# result = session.query(User).get(1)# print(result.name,result.age)#六、条件查询# result = session.query(User).filter_by(name = "zhangsan")# for i in result:#     print(i.name)#     print(i.age)# 七、修改数据,首先使用get()获取数据,然后直接使用.加字段名的方法修改# result = session.query(User).get(1)# print(result.age)# result.age = 20# session.commit()# print(result.age)# 八、删除数据# result = session.query(User).get(1)# session.delete(result)# re = session.query(User).all()