ORMmysql

创建表，需要手动先创建数据库

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy import create_engine

Base = declarative_base()
class userinfo(Base):
    __tablename__ = "user_test"
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),nullable=True)
    age = Column(Integer,default=12,index=True)
    work = Column(String(32))


engine = create_engine("mysql+pymysql://xiao:123123@42.193.197.149:3306/userinfo?charset=utf8")
Base.metadata.create_all(engine)

 添加外键

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy import create_engine

Base = declarative_base()

class teacher(Base):
    __tablename__ = "teacher"
    tid = Column(Integer,primary_key=True)
    tname = Column(String(32))
class course(Base):
    __tablename__ = "course"
    cid = Column(Integer,primary_key=True)
    cname = Column(String(32))
    tearch_id = Column(Integer,ForeignKey("teacher.tid"))

engine = create_engine("mysql+pymysql://xiao:123123@42.193.197.149:3306/userinfo?charset=utf8")
Base.metadata.create_all(engine)

 删除表

 对数据的操作：

插入数据

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker,relationship
Base = declarative_base()
class userinfo(Base):
    __tablename__ = "user_test"
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),nullable=True)
    age = Column(Integer,default=12,index=True)
    work = Column(String(32))


engine = create_engine("mysql+pymysql://xiao:123123@42.193.197.149:3306/userinfo?charset=utf8")
Session = sessionmaker(bind=engine)
session = Session() 
obj1 = [userinfo(name="xiao",age=20,work="IT"),userinfo(name="liucui",age=18,work="docker")]
session.add_all(obj1)  # 添加多行数据，  add 表示添加一条数据
session.commit()       # 提交
session.close()

 查看数据

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker,relationship
Base = declarative_base()
class userinfo(Base):
    __tablename__ = "user_test"
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),nullable=True)
    age = Column(Integer,default=12,index=True)
    work = Column(String(32))


engine = create_engine("mysql+pymysql://xiao:123123@42.193.197.149:3306/userinfo?charset=utf8")
Session = sessionmaker(bind=engine)
session = Session()
#obj1 = [userinfo(name="xiao",age=20,work="IT"),userinfo(name="liucui",age=18,work="docker")]
#session.add_all(obj1)  # 添加多行数据，  add 表示添加一条数据
ret = session.query(userinfo).all()
print(ret) # 返回每列为一个对象[<__main__.userinfo object at 0x000001F5D7EC9D30>, <__main__.userinfo object at 0x000001F5D7EC9580>]
for row in ret:
    print(row.id,row.name,row.age,row.work)

#session.commit()       # 提交
session.close()

 where 条件查看

ret = session.query(userinfo.id,userinfo.name).filter(userinfo.id > 1).all()
print(ret) # 返回每列为一个对象[<__main__.userinfo object at 0x000001F5D7EC9D30>, <__main__.userinfo object at 0x000001F5D7EC9580>]
for row in ret:
    print(row.id,row.name)