【数据库篇|ORM】一篇速通SQLALchemy必备知识

一.SQLALchemy概述1.1 定义SQLAlchemy 是 Python 生态中最强大、最成熟的数据库工具库它既是一个SQL 工具包也是一个对象关系映射ORM框架。ORM 让你用操作对象的方式操作数据库。1.2 安装命令行cmd或者pyCharm终端输入pip install sqlalchemy二.数据库的连接和表的创建2.1 SQLAlchemy连接mysql数据库2.1.1 使用mysql创建表DROP TABLE IF EXISTS student; CREATE TABLE student ( id int NOT NULL AUTO_INCREMENT, name varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, PRIMARY KEY (id) USING BTREE ) ENGINE InnoDB AUTO_INCREMENT 5 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic; SET FOREIGN_KEY_CHECKS 1;结果ps:如果表保存过sql预览还是为空可以右键点击表名选择转储SQL文件的仅结构之后选择保持位置后会生成一个.sql文件打开即为建表代码。2.1.2 连接 MySQL 数据库1.导包import sqlalchemy2.创建引擎engine sqlalchemy.create_engine(mysqlpymysql://用户名:密码localhost:端口号/数据库名)数据库类型mysql驱动pymysqlps:pymysql安装pip install pymysql3.连接conn engine.connect()4.查询query sqlalchemy.text(SELECT * FROM student) result conn.execute(query)5.输出结果for row in result: print(row)针对以上所创建的数据库而言会输出6.清理conn.close() engine.dispose()2.2SQLAlchemy Core创建表1.导包import sqlalchemy2.创建引擎engine sqlalchemy.create_engine(mysqlpymysql://用户名:密码localhost:端口号/数据库名,echoTrue)3.定义元数据metadata sqlalchemy.MetaData()4.表goods sqlalchemy.Table( goods, metadata, sqlalchemy.Column(id, sqlalchemy.Integer, primary_keyTrue), sqlalchemy.Column(name, sqlalchemy.String(128), uniqueTrue, nullableFalse), sqlalchemy.Column(price, sqlalchemy.Integer, nullableFalse), ) metadata.create_all(engine)三.增删改查3.1 增单增insert_apple goods.insert().values(id1,nameapple,price100) with engine.connect() as conn: result conn.execute(insert_apple) conn.commit() print(插入成功受影响行数, result.rowcount)多增goods_insert goods.insert() with engine.connect() as conn: conn.execute(goods_insert,[ {name: apple, price: 100}, {name: book, price: 200}, {name: peer, price: 300}, ]) conn.commit()3.2 查with engine.connect() as connection: query goods.select().where(goods.c.id 1) results connection.execute(query)3.3 删with engine.connect() as conn: delete_query goods.delete().where(goods.id 1) conn.execute(delete_query) conn.commit()3.4 改with engine.connect() as conn: update_query goods.update().values(price10).where(goods.id1) conn.execute(update_query) conn.commit()ps:代码非完整代码直接粘贴会报错。缺乏if __name__ __main__保护建议加上。