python中orm框架学习

时间：2022-01-20 15:42:14

安装sqlalchemy

pip3 install sqlalchemy

创建表结构：

from sqlalchemy import Column,String,create_engine

from sqlalchemy.orm import sessionmaker,relationship

from sqlalchemy.ext.declarative import declarative_base

import 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_base

from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index

from sqlalchemy.orm import sessionmaker,relationship

from sqlalchemy import create_engine

engine = 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_base

from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index

from sqlalchemy.orm import sessionmaker,relationship

from sqlalchemy import create_engine

from 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()

　

相关文章