MySQL数据库的增删查改

叁歲伎倆 2022-12-11 06:24 207阅读 0赞

接着我上篇文章继续：[MySQL数据库操作][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 = 20
        session.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()

[MySQL]: https://blog.csdn.net/qq_17802895/article/details/108839205