安装sqlalchemy
pip3 install sqlalchemy
创建表结构：
from sqlalchemy import Column,String,create_enginefrom sqlalchemy.orm import sessionmaker,relationshipfrom sqlalchemy.ext.declarative import declarative_baseimport pymysql#创建对象的基类Base = declarative_base()#定义user对象class User(Base): #表的名字 __tablename__ = 'user' #表的结构 id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(20))#初始化数据库连接#数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名engine = create_engine('mysql+pymysql://root:123456@192.168.170.129:3306/learn_orm',max_overflow=5)Base.metadata.create_all(engine)#创建DBSession类型DBSession = sessionmaker(bind=engine) 一对多核多对多
from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Indexfrom sqlalchemy.orm import sessionmaker,relationshipfrom sqlalchemy import create_engineengine = create_engine("mysql+pymysql://root:123456@192.168.180.129:3306/test",max_overflow=5)Base = declarative_base()#创建单表class Users(Base): __tablename__ = 'users' id = Column(Integer,primary_key=True) name = Column(String(32)) extra = Column(String(16))#一对多class Favor(Base): __tablename__ = 'favor' nid = Column(Integer,primary_key=True) caption = Column(String(50),default='red',unique=True)class Person(Base): __tablename__ = 'person' nid = Column(Integer,primary_key=True) name = Column(String(32),index=True,nullable=True) favor_id = Column(Integer,ForeignKey('favor.nid'))#多对多class Group(Base): __tablename__ = 'group' nid = Column(Integer,primary_key=True) name = Column(String(64),unique=True,nullable=False) port = Column(Integer,default=22)class Server(Base): __tablename__ = 'server' id = Column(Integer,primary_key=True,autoincrement=True) hostname = Column(String(64),unique=True,nullable=False)class ServerToGroup(Base): __tablename__ = 'servertogroup' nid = Column(Integer,primary_key=True,autoincrement=True) server_id = Column(Integer, ForeignKey('server.id')) group_id = Column(Integer, ForeignKey('group.nid'))# Base.metadata.create_all(engine) #生成表Base.metadata.drop_all(engine) #生成表 #添加数据(往Users表增加数据)
from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Indexfrom sqlalchemy.orm import sessionmaker,relationshipfrom sqlalchemy import create_enginefrom orm一对多和多对多 import Users #从orm一对多和对多程序中导入Users类engine = create_engine("mysql+pymysql://root:123456@192.168.180.129:3306/test",max_overflow=5)Base = declarative_base()Base.metadata.create_all(engine) #生成表# Base.metadata.drop_all(engine) #生成表Session = sessionmaker(bind=engine)session = Session()#增加数据obj = Users(name="parktrick",extra="Devops")session.add(obj)session.commit() #不执行这步，数据是写入不了数据库的 #删除User表某条数据
#删除数据session.query(Users).filter(Users.id > 2).delete() #把Users.id大于2的数据删除掉session.commit()
#修改数据
#修改数据session.query(Users).filter(Users.id > 1).update({"name":"bob"}) #修改User.id大于1的name为"bob"session.commit()