sqlalchemy数据库模块

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
 5 from sqlalchemy.orm import sessionmaker, relationship
 6 from sqlalchemy import create_engine
 7 engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/sig") #engine 引擎
 8 Base = declarative_base()  #declarative 声明的   base 基地
 9 # 创建单表
10 class Users(Base):
11     __tablename__ = 'users' #表名
12     id = Column(Integer, primary_key=True)
13     name = Column(String(32))
14     extra = Column(String(16))
15     #args 传感器
16     __table_args__ = (
17     UniqueConstraint('id', 'name', name='uix_id_name'),  #Constraint 约束   unique 唯一
18         Index('ix_id_name', 'name', 'extra'),
19     )
20 # 创建一对多
21 class Favor(Base):
22     __tablename__ = 'favor'
23     nid = Column(Integer, primary_key=True)
24     caption = Column(String(50), default='red', unique=True)
25 
26 class Person(Base):
27     __tablename__ = 'person'
28     nid = Column(Integer, primary_key=True)
29     name = Column(String(32), index=True, nullable=True)
30     favor_id = Column(Integer, ForeignKey("favor.nid"))
31 
32 # 创建多对多
33 class Group(Base):
34     __tablename__ = 'group'
35     id = Column(Integer, primary_key=True)
36     name = Column(String(64), unique=True, nullable=False)
37     port = Column(Integer, default=22)
38 
39 
40 class Server(Base):
41     __tablename__ = 'server'
42     id = Column(Integer, primary_key=True, autoincrement=True)
43     hostname = Column(String(64), unique=True, nullable=False)
44 
45 
46 class ServerToGroup(Base):
47     __tablename__ = 'servertogroup'
48     nid = Column(Integer, primary_key=True, autoincrement=True)
49     server_id = Column(Integer, ForeignKey('server.id'))
50     group_id = Column(Integer, ForeignKey('group.id'))
51 
52 Base.metadata.create_all(engine)  #metadata元数据   把创建的表格发送給数据库
53 
54 
55 
56 Session = sessionmaker(bind=engine) #session  会话  maker制造者   sessionmaker会话者
57 session = Session()  #创建一个session对象
58 
59 obj = Users(name="alex0", extra='sb')
60 session.add(obj)
61 session.add_all([
62     Users(name="alex1", extra='sb'),
63     Users(name="alex2", extra='sb'),
64 ])
65 session.commit()  #commit 把。。。托付給    把写入的内容发送給数据库

创建

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
 5 from sqlalchemy.orm import sessionmaker, relationship
 6 from sqlalchemy import create_engine
 7 engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/sig") #engine 引擎
 8 Base = declarative_base()  #declarative 声明的   base 基地
 9 # 创建单表
10 class Users(Base):
11     __tablename__ = 'users' #表名
12     id = Column(Integer, primary_key=True)
13     name = Column(String(32))
14     extra = Column(String(16))
15     num = Column(Integer)
16     #args 传感器
17     __table_args__ = (
18     UniqueConstraint('id', 'name', name='uix_id_name'),  #Constraint 约束   unique 唯一
19         Index('ix_id_name', 'name', 'extra'),
20     )
21 
22     def __repr__(self):
23         return "%s-%s" %(self.id, self.name)
24 # 创建一对多
25 class Favor(Base):
26     __tablename__ = 'favor'
27     nid = Column(Integer, primary_key=True)
28     caption = Column(String(50), default='red', unique=True)
29     def __repr__(self):
30         return "%s-%s" %(self.id, self.name)
31 
32 class Person(Base):
33     __tablename__ = 'person'
34     nid = Column(Integer, primary_key=True)
35     name = Column(String(32), index=True, nullable=True)
36     favor_id = Column(Integer, ForeignKey("favor.nid"))
37 
38 # 创建多对多
39 class Group(Base):
40     __tablename__ = 'group'
41     id = Column(Integer, primary_key=True)
42     name = Column(String(64), unique=True, nullable=False)
43     port = Column(Integer, default=22)
44 
45 class Server(Base):
46     __tablename__ = 'server'
47     id = Column(Integer, primary_key=True, autoincrement=True)
48     hostname = Column(String(64), unique=True, nullable=False)
49 
50 class ServerToGroup(Base):
51     __tablename__ = 'servertogroup'
52     nid = Column(Integer, primary_key=True, autoincrement=True)
53     server_id = Column(Integer, ForeignKey('server.id'))
54     group_id = Column(Integer, ForeignKey('group.id'))
55 
56 Base.metadata.create_all(engine)  #metadata元数据   把创建的表格发送給数据库
57 
58 
59 
60 Session = sessionmaker(bind=engine) #session  会话  maker制造者   sessionmaker会话者
61 session = Session()  #创建一个session对象
62 # 写入数据
63 # obj = Users(name="alex0", extra='sb')
64 # session.add(obj)
65 # session.add_all([
66 #     Users(name="alex1", extra='sb'),
67 #     Users(name="alex2", extra='sb'),
68 # ])
69 
70 #删除数据
71 # session.query(Users).filter(Users.id > 2).delete()  #query查询  filter过滤
72 # session.commit()
73 
74 # 改数据
75 # session.query(Users).filter(Users.id < 3).update({"name" : "alxe" })
76 # session.query(Users).filter(Users.id < 3).update({Users.name: Users.name + "199"}, synchronize_session=False)  #synchronize使用同步
77 # session.query(Users).filter(Users.id < 3).update({"num": Users.num + 1}, synchronize_session="evaluate") #evaluate评价 估价 求的值
78 # session.commit()
79 
80 # 查看数据
81 # ret = session.query(Users).all()
82 # ret = session.query(Users.name, Users.extra).all()
83 # ret = session.query(Users).filter_by(name='alxe199').all()
84 # ret = session.query(Users).filter_by(name='alxe199').first()
85 # print(ret)
86 session.commit()  #commit 把。。。托付給    把写入的内容发送給数据库

增删改查

#!/usr/bin/env python
# -*- coding:utf-8 -*-
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@127.0.0.1:3306/sig") #engine 引擎
Base = declarative_base()  #declarative 声明的   base 基地
# 创建单表
class Users(Base):
    __tablename__ = 'users' #表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))
    num = Column(Integer)
    #args 传感器
    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),  #Constraint 约束   unique 唯一
        Index('ix_id_name', 'name', 'extra'),
    )

    def __repr__(self):
        return "%s-%s-%s_%s" %(self.id, self.name,self.extra,self.num)
# 创建一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)
    def __repr__(self):
        return "%s-%s" %(self.nid, self.caption)

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"))
    def __repr__(self):
        return "%s-%s-%s" %(self.nid, self.name,self.favor_id)
# 创建多对多
class Group(Base):
    __tablename__ = 'group'
    id = 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.id'))

Base.metadata.create_all(engine)  #metadata元数据   把创建的表格发送給数据库



Session = sessionmaker(bind=engine) #session  会话  maker制造者   sessionmaker会话者
session = Session()  #创建一个session对象
# 写入数据
# obj = Users(name="alex0", extra='sb')
# session.add(obj)
# session.add_all([
#     Users(name="alex1", extra='sb'),
#     Users(name="alex2", extra='sb'),
# ])

#删除数据
# session.query(Users).filter(Users.id > 2).delete()  #query查询  filter过滤
# session.commit()

# 改数据
# session.query(Users).filter(Users.id < 3).update({"name" : "alxe" })
# session.query(Users).filter(Users.id < 3).update({Users.name: Users.name + "199"}, synchronize_session=False)  #synchronize使用同步
# session.query(Users).filter(Users.id < 3).update({"num": Users.num + 1}, synchronize_session="evaluate") #evaluate评价 估价 求的值
# session.commit()

# 查看数据
# ret = session.query(Users).all()
# ret = session.query(Users.name, Users.extra).all()
# ret = session.query(Users).filter_by(name='alxe199').all()
# ret = session.query(Users).filter_by(name='alxe199').first()

# 条件
# ret = session.query(Users).filter_by(name='alxe199').all()
# ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
# ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() #between 在什么 之间
# ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
# ret = session.query(Users.id).filter_by(name='eric').all()
# ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()

# from sqlalchemy import and_, or_  #模块 and并且  or或者
# ret = session.query(Users).filter(and_(Users.id > 2, Users.name == 'eric')).all()
# # ret = session.query(Users).filter(or_(Users.id < 4, Users.name == 'eric')).all()
# ret = session.query(Users).filter(
#     or_(
#         Users.id < 2,
#         and_(Users.name == 'eric', Users.id > 3),
#         Users.extra != ""
#     )).all()

# 通配符
# ret = session.query(Users).filter(Users.name.like('e%')).all()
# ret = session.query(Users).filter(~Users.name.like('e%')).all()   # ~ 符号是相反的意思， 除了这个条件 其他都成立

# 限制
# ret = session.query(Users)[1:5]  #取 1-5

# 排序
# ret = session.query(Users).order_by(Users.id.desc()).all()
# ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组
from sqlalchemy.sql import func
# ret = session.query(Users).group_by(Users.name).all()
# ret = session.query(
#     func.max(Users.id),
#     func.sum(Users.id),
#     func.min(Users.id)).group_by(Users.name).all()

# ret = session.query(
#     func.max(Users.id),
#     func.sum(Users.id),
#     func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

# 连表

# ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

# ret = session.query(Person).join(Favor).all()
#
# ret = session.query(Person).join(Favor, isouter=True).all()

# 组合
# q1 = session.query(Users.name).filter(Users.id > 2)
# q2 = session.query(Favor.caption).filter(Favor.nid < 2)
# ret = q1.union(q2).all()

# q1 = session.query(Users.name).filter(Users.id > 2)
# q2 = session.query(Favor.caption).filter(Favor.nid < 2)
# ret = q1.union_all(q2).all()
# print(ret)



session.commit()  #commit 把。。。托付給    把写入的内容发送給数据库

条件

#!/usr/bin/env python
# -*- coding:utf-8 -*-
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@127.0.0.1:3306/sig") #engine 引擎
Base = declarative_base()  #declarative 声明的   base 基地
# 创建单表
class Users(Base):
    __tablename__ = 'users' #表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))
    num = Column(Integer)
    #args 传感器
    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),  #Constraint 约束   unique 唯一
        Index('ix_id_name', 'name', 'extra'),
    )

    def __repr__(self):
        return "%s-%s-%s_%s" %(self.id, self.name,self.extra,self.num)
# 创建一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)
    def __repr__(self):
        return "%s-%s" %(self.nid, self.caption)

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"))
    def __repr__(self):
        return "%s-%s-%s" %(self.nid, self.name,self.favor_id)
# 创建多对多
class Group(Base):
    __tablename__ = 'group'
    id = 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.id'))

Base.metadata.create_all(engine)  #metadata元数据   把创建的表格发送給数据库



Session = sessionmaker(bind=engine) #session  会话  maker制造者   sessionmaker会话者
session = Session()  #创建一个session对象
# 写入数据
# obj = Users(name="alex0", extra='sb')
# session.add(obj)
# session.add_all([
#     Users(name="alex1", extra='sb'),
#     Users(name="alex2", extra='sb'),
# ])

#删除数据
# session.query(Users).filter(Users.id > 2).delete()  #query查询  filter过滤
# session.commit()

# 改数据
# session.query(Users).filter(Users.id < 3).update({"name" : "alxe" })
# session.query(Users).filter(Users.id < 3).update({Users.name: Users.name + "199"}, synchronize_session=False)  #synchronize使用同步
# session.query(Users).filter(Users.id < 3).update({"num": Users.num + 1}, synchronize_session="evaluate") #evaluate评价 估价 求的值
# session.commit()

# 查看数据
# ret = session.query(Users).all()
# ret = session.query(Users.name, Users.extra).all()
# ret = session.query(Users).filter_by(name='alxe199').all()
# ret = session.query(Users).filter_by(name='alxe199').first()

# 条件
# ret = session.query(Users).filter_by(name='alxe199').all()
# ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
# ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() #between 在什么 之间
# ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
# ret = session.query(Users.id).filter_by(name='eric').all()
# ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()

# from sqlalchemy import and_, or_  #模块 and并且  or或者
# ret = session.query(Users).filter(and_(Users.id > 2, Users.name == 'eric')).all()
# # ret = session.query(Users).filter(or_(Users.id < 4, Users.name == 'eric')).all()
# ret = session.query(Users).filter(
#     or_(
#         Users.id < 2,
#         and_(Users.name == 'eric', Users.id > 3),
#         Users.extra != ""
#     )).all()

# 通配符
# ret = session.query(Users).filter(Users.name.like('e%')).all()
# ret = session.query(Users).filter(~Users.name.like('e%')).all()   # ~ 符号是相反的意思， 除了这个条件 其他都成立

# 限制
# ret = session.query(Users)[1:5]  #取 1-5

# 排序
# ret = session.query(Users).order_by(Users.id.desc()).all()
# ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组
from sqlalchemy.sql import func
# ret = session.query(Users).group_by(Users.name).all()
# ret = session.query(
#     func.max(Users.id),
#     func.sum(Users.id),
#     func.min(Users.id)).group_by(Users.name).all()

# ret = session.query(
#     func.max(Users.id),
#     func.sum(Users.id),
#     func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

# 连表

# ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()
# ret = session.query(Person).join(Favor).all()
# ret = session.query(Person,Favor).join(Favor).all()
# ret = session.query(Person.name('aaa'),Favor.('caption')).join(Favor).all() # 添加一个别名
#
# ret = session.query(Person).join(Favor, isouter=True).all()

# 组合
# q1 = session.query(Users.name).filter(Users.id > 2)
# q2 = session.query(Favor.caption).filter(Favor.nid < 2)
# ret = q1.union(q2).all()

# q1 = session.query(Users.name).filter(Users.id > 2)
# q2 = session.query(Favor.caption).filter(Favor.nid < 2)
# ret = q1.union_all(q2).all()
# print(ret)



session.commit()  #commit 把。。。托付給    把写入的内容发送給数据库

条件1

查看数据

键值对用 filter_by 查询
ret = session.query(Users).filter_by(name='alxe199').all()

条件判断用 filter
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()

小知识

 1 from sqlalchemy.ext.declarative import declarative_base
 2 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
 3 from sqlalchemy.orm import sessionmaker, relationship
 4 from sqlalchemy import create_engine
 5 engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/pro") #engine 引擎
 6 Base = declarative_base()  #declarative 声明的   base 基地
 7 
 8 # 一对多
 9 class Son(Base):
10     __tablename__ = 'son'
11     nid = Column(Integer, primary_key=True)
12     name = Column(String(32), index=True, nullable=True)
13     favor_id = Column(Integer, ForeignKey("father.nid"))
14     # 与生成表结构无关，仅用于查询方便
15     father = relationship('Father')
16 
17 
18 class Father(Base):
19     __tablename__ = 'father'
20     nid = Column(Integer, primary_key=True)
21     caption = Column(String(50), default='red', unique=True)
22     son = relationship('Son')
23 
24 Base.metadata.create_all(engine)  #metadata元数据   把创建的表格发送給数据库
25 
26 Session = sessionmaker(bind=engine) #session  会话  maker制造者   sessionmaker会话者
27 session = Session()  #创建一个session对象
28 
29 # father类通过relationship 查son类对应的数据
30 ret = session.query(Father).filter(Father.nid<2).first()
31 print(ret.son)
32 for i in ret.son:
33     print(i.name)
34     
35 # son类通过relationship 查 father类对应的数据
36 rets = session.query(Son).filter_by(nid=3).first()
37 print(rets.name)
38 print(rets.father.caption)
39 
40 
41 session.commit()  #commit 把。。。托付給    把写入的内容发送給数据库
42 
43 
44 详解参照下图

relationship 关系

 1 from sqlalchemy.ext.declarative import declarative_base
 2 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
 3 from sqlalchemy.orm import sessionmaker, relationship
 4 from sqlalchemy import create_engine
 5 engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/cole") #engine 引擎
 6 Base = declarative_base()  #declarative 声明的   base 基地
 7 class M_W_Group(Base):
 8     __tablename__ = 'm_w_group'
 9     nid = Column(Integer, primary_key=True, autoincrement=True)
10     man_gid = Column(Integer, ForeignKey('man.man_id'))
11     woman_gid = Column(Integer, ForeignKey('woman.woman_id'))
12 
13 class Man(Base):
14     __tablename__ = 'man'
15     man_id = Column(Integer, primary_key=True, autoincrement=True)
16     man_name = Column(String(64), unique=True, nullable=False)
17     man_age = Column(Integer)
18 
19 class Woman(Base):
20     __tablename__ = 'woman'
21     woman_id = Column(Integer, primary_key=True)
22     woman_name = Column(String(64), unique=True, nullable=False)
23     woman_age = Column(Integer)
24     m_w_group = relationship('Man',secondary=M_W_Group.__table__,backref='gf') #erlationship关系 secondary第二的 backref后面的参考
25 Base.metadata.create_all(engine)
26 
27 Session = sessionmaker(bind=engine) #session  会话  maker制造者   sessionmaker会话者
28 session = Session()  #创建一个session对象
29 
30 m1 = session.query(Man).filter_by(man_id=2).first()
31 w1 = session.query(Woman).all()
32 m1.gf=w1
33 session.add(m1)
34 
35 
36 session.commit()  #commit 把。。。托付給    把写入的内容发送給数据库

erlationship关系多对多

相关阅读:
HTML5/CSS3滑块动画菜单
 基于HTML5手机登录注册表单代码
 基于HTML5手机上下滑动翻页特效
 基于jQuery+HTML5页面整屏滑动切换代码
 基于html5可拖拽图片循环滚动切换
 基于html5背景图片自适应代码是一款背景不随滚动条滚动，会根据分辨率不同自动匹配对应的背景图片
 HTML5实现摇一摇
 html5桌面通知,notification的使用,右下角出现通知框
 html5全局属性
 HTML5 QQ登录背景动态图片
原文地址：https://www.cnblogs.com/cloniu/p/6405450.html