SQLAlchemy基础操作一
用前安装
pip3 install sqlalchemy
ORM
ORM就是运用面向对象的知识,将数据库中的每个表对应一个类,将数据库表中的记录对应一个类的对象。将复杂的sql语句转换成类和对象的操作。
执行源生SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | import time import threading import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.engine.base import Engine engine = create_engine( "mysql+pymysql://root:123@47.93.4.198:3306/s6?charset=utf8" , max_overflow = 0 , # 超过连接池大小外最多创建的连接 pool_size = 5 , # 连接池大小 pool_timeout = 30 , # 池中没有线程最多等待的时间,否则报错 pool_recycle = - 1 # 多久之后对线程池中的线程进行一次连接的回收(重置) ) from sqlalchemy.pool import _ConnectionFairy def task(arg): conn = engine.raw_connection() print ( '你到了' ,conn.connection) cursor = conn.cursor() cursor.execute( "select * from users" ) result = cursor.fetchall() import time time.sleep( 5 ) cursor.close() conn.close()<br> for i in range ( 20 ): t = threading.Thread(target = task, args = (i,)) t.start() |
注意: 查看连接 show status like 'Threads%';
ORM表操作
一创建数据库表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | import datetime from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index Base = declarative_base() class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key = True ) name = Column(String( 32 ), index = True , nullable = False ) # email = Column(String(32), unique=True) # ctime = Column(DateTime, default=datetime.datetime.now) # extra = Column(Text, nullable=True) __table_args__ = ( # UniqueConstraint('id', 'name', name='uix_id_name'), # Index('ix_id_name', 'name', 'email'), ) def init_db(): """ 根据类创建数据库表 :return: """ engine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8" , max_overflow = 0 , # 超过连接池大小外最多创建的连接 pool_size = 5 , # 连接池大小 pool_timeout = 30 , # 池中没有线程最多等待的时间,否则报错 pool_recycle = - 1 # 多久之后对线程池中的线程进行一次连接的回收(重置) ) Base.metadata.create_all(engine) def drop_db(): """ 根据类删除数据库表 :return: """ engine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8" , max_overflow = 0 , # 超过连接池大小外最多创建的连接 pool_size = 5 , # 连接池大小 pool_timeout = 30 , # 池中没有线程最多等待的时间,否则报错 pool_recycle = - 1 # 多久之后对线程池中的线程进行一次连接的回收(重置) ) Base.metadata.drop_all(engine) if __name__ = = '__main__' : drop_db() init_db() |
二含FK与M2M的表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | import datetime from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index from sqlalchemy.orm import relationship Base = declarative_base() # ------------ 单表示例 -----------# class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key = True ) name = Column(String( 32 ), index = True ) age = Column(Integer, default = 18 ) email = Column(String( 32 ), unique = True ) ctime = Column(DateTime, default = datetime.datetime.now) extra = Column(Text, nullable = True ) __table_args__ = ( # UniqueConstraint('id', 'name', name='uix_id_name'), # Index('ix_id_name', 'name', 'extra'), ) class Hosts(Base): __tablename__ = 'hosts' id = Column(Integer, primary_key = True ) name = Column(String( 32 ), index = True ) ctime = Column(DateTime, default = datetime.datetime.now) # ##################### 一对多示例 ######################### class Hobby(Base): __tablename__ = 'hobby' id = Column(Integer, primary_key = True ) caption = Column(String( 50 ), default = '篮球' ) class Person(Base): __tablename__ = 'person' nid = Column(Integer, primary_key = True ) name = Column(String( 32 ), index = True , nullable = True ) hobby_id = Column(Integer, ForeignKey( "hobby.id" )) # 与生成表结构无关,仅用于查询方便 hobby = relationship( "Hobby" , backref = 'pers' ) # ##################### 多对多示例 ######################### class Server2Group(Base): __tablename__ = 'server2group' id = Column(Integer, primary_key = True , autoincrement = True ) server_id = Column(Integer, ForeignKey( 'server.id' )) group_id = Column(Integer, ForeignKey( 'group.id' )) class Group(Base): __tablename__ = 'group' id = Column(Integer, primary_key = True ) name = Column(String( 64 ), unique = True , nullable = False ) # 与生成表结构无关,仅用于查询方便 servers = relationship( 'Server' , secondary = 'server2group' , backref = 'groups' ) class Server(Base): __tablename__ = 'server' id = Column(Integer, primary_key = True , autoincrement = True ) hostname = Column(String( 64 ), unique = True , nullable = False ) def init_db(): """ 根据类创建数据库表 :return: """ engine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8" , max_overflow = 0 , # 超过连接池大小外最多创建的连接 pool_size = 5 , # 连接池大小 pool_timeout = 30 , # 池中没有线程最多等待的时间,否则报错 pool_recycle = - 1 # 多久之后对线程池中的线程进行一次连接的回收(重置) ) Base.metadata.create_all(engine) def drop_db(): """ 根据类删除数据库表 :return: """ engine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8" , max_overflow = 0 , # 超过连接池大小外最多创建的连接 pool_size = 5 , # 连接池大小 pool_timeout = 30 , # 池中没有线程最多等待的时间,否则报错 pool_recycle = - 1 # 多久之后对线程池中的线程进行一次连接的回收(重置) ) Base.metadata.drop_all(engine) if __name__ = = '__main__' : drop_db() init_db() |
三 操作数据库表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from models import Users engine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/s6" , max_overflow = 0 , pool_size = 5 ) Session = sessionmaker(bind = engine) # 每次执行数据库操作时,都需要创建一个session session = Session() # ############# 执行ORM操作 ############# obj1 = Users(name = "alex1" ) session.add(obj1) # 提交事务 session.commit() # 关闭session session.close() |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· 趁着过年的时候手搓了一个低代码框架
· 本地部署DeepSeek后,没有好看的交互界面怎么行!
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· 乌龟冬眠箱湿度监控系统和AI辅助建议功能的实现