Python【day 11】：Python学习(sqlalchemy）
sqlalchemy 建表
__author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey #导入模块 metadata = MetaData() #新建实例 user = Table('user1', metadata, #创建表1，表名是user1 #这里metadata类似于类的继承（Table继承metadata） Column('id', Integer, primary_key=True), #字段1 字段名字是id 整数 主键（默认自增） Column('name', String(20)), #字段2 字段名字name 字符串类型 20个字节长度 ) #这个表配置了一下（表进行了对象的映射），这些最终会转换成sql语句取调mysql的api # metadata定义了怎么讲上述对象转换成sql语句 color = Table('color1', metadata, #创建表2，表名是color1 Column('id', Integer, primary_key=True), #字段1 字段名字是id 整数 主键（默认自增） Column('name', String(20)), #字段2 字段名字name 字符串类型 20个字节长度 ) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5) #创建数据库连接 mysql用户名：root mysql密码：123123 mysql主机ip：192.168.19.130 数据库实例名字：test metadata.create_all(engine) #如果表已经存在了，这句代码（创建引擎）可以不写，写了也没关系，只是不执行了 #创建上述2个表的结构
增加记录
__author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey,select #导入模块 metadata = MetaData() #新建实例 user = Table('user2', metadata, #创建表1，表名是user2 Column('id', Integer, primary_key=True), #字段1 字段名字是id 整数 主键 Column('name', String(20)), #字段2 字段名字name 字符串类型 20个字节长度 ) # color = Table('color2', metadata, #创建表2，表名是color1 # Column('id', Integer, primary_key=True), #字段1 字段名字是id 整数 主键 # Column('name', String(20)), #字段2 字段名字name 字符串类型 20个字节长度 # ) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5) #创建数据库连接 mysql用户名：root mysql密码：123123 mysql主机ip：192.168.19.130 数据库实例名字：test conn = engine.connect() #创建一个游标（增删改查必须先创建游标） metadata.create_all(engine) ##如果表已经存在了，这句代码（创建引擎）可以不写，写了也没关系，只是不执行了 #如果不存在，就是创建表 sql = select([user, ]) result = conn.execute(sql) print(result.fetchall()) #[(7, 'seven'), (9, 'jack')] # 创建SQL语句，INSERT INTO "user" (id, name) VALUES (:id, :name) #原生sql result=conn.execute(user.insert(),{'id':12,'name':'seven'}) #往表"user2"中新增一条记录 # result=conn.execute(user.insert(),{'name':'seven'}) #主键是自增的，id可以不写，默认从1开始 # sql= user.insert(),{'id':9,'name':'seven'} #分开执行 ，会报错 # conn.execute(sql) # print(result.fetchall()) sql = select([user, ]) result = conn.execute(sql) print(result.fetchall()) #[(7, 'seven'), (9, 'jack'), (12, 'seven')] conn.close() #关闭游标
删除记录
__author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey,select #导入模块 metadata = MetaData() #新建实例 user = Table('user2', metadata, #创建表1，表名是user1 Column('id', Integer, primary_key=True), #字段1 字段名字是id 整数 主键 Column('name', String(20)), #字段2 字段名字name 字符串类型 20个字节长度 ) # color = Table('color2', metadata, #创建表2，表名是color1 # Column('id', Integer, primary_key=True), #字段1 字段名字是id 整数 主键 # Column('name', String(20)), #字段2 字段名字name 字符串类型 20个字节长度 # ) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5) #创建数据库连接 mysql用户名：root mysql密码：123123 mysql主机ip：192.168.19.130 数据库实例名字：test conn = engine.connect() #创建一个游标 metadata.create_all(engine) # 创建SQL语句，INSERT INTO "user" (id, name) VALUES (:id, :name) #原生sql # conn.execute(user.insert(),{'id':8,'name':'seven'}) #往表"user2"中新增一条记录 sql = select([user, ]) result = conn.execute(sql) print(result.fetchall()) #[(7, 'seven'), (9, 'jack'), (10, 'seven')] sql = user.delete().where(user.c.id > 9) #从表"user2"中删除id>7的记录 这里user.c.id中的c是固定写法，记下 conn.execute(sql) #通过游标执行sql语句 sql = select([user, ]) result = conn.execute(sql) print(result.fetchall()) #[(7, 'seven'), (9, 'jack')] conn.close() #关闭游标
修改记录
__author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey,select #导入模块 metadata = MetaData() #新建实例 user = Table('user2', metadata, #创建表1，表名是user1 Column('id', Integer, primary_key=True), #字段1 字段名字是id 整数 主键 Column('name', String(20)), #字段2 字段名字name 字符串类型 20个字节长度 ) # color = Table('color2', metadata, #创建表2，表名是color1 # Column('id', Integer, primary_key=True), #字段1 字段名字是id 整数 主键 # Column('name', String(20)), #字段2 字段名字name 字符串类型 20个字节长度 # ) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5) #创建数据库连接 mysql用户名：root mysql密码：123123 mysql主机ip：192.168.19.130 数据库实例名字：test conn = engine.connect() #创建一个游标 metadata.create_all(engine) sql = select([user, ]) result = conn.execute(sql) print(result.fetchall()) #[(7, 'seven'), (9, 'jack'), (12, 'seven')] # 创建SQL语句，INSERT INTO "user" (id, name) VALUES (:id, :name) #原生sql # conn.execute(user.insert(),{'id':8,'name':'seven'}) #往表"user2"中新增一条记录 # sql = user.delete().where(user.c.id > 7) #从表"user2"中删除id>7的记录 这里user.c.id中的c是固定写法，记下 sql = user.update().where(user.c.id == 9).values(name='jack1') #将id=9的这一条记录的name字段改成"jack" result = conn.execute(sql) #通过游标执行sql语句 sql = select([user, ]) result = conn.execute(sql) print(result.fetchall()) #[(7, 'seven'), (9, 'jack1'), (12, 'seven')] conn.close() #关闭游标
查询记录
__author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey,select #导入模块 metadata = MetaData() #新建实例 user = Table('user2', metadata, #创建表1，表名是user1 Column('id', Integer, primary_key=True), #字段1 字段名字是id 整数 主键 Column('name', String(20)), #字段2 字段名字name 字符串类型 20个字节长度 ) # color = Table('color2', metadata, #创建表2，表名是color1 # Column('id', Integer, primary_key=True), #字段1 字段名字是id 整数 主键 # Column('name', String(20)), #字段2 字段名字name 字符串类型 20个字节长度 # ) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5) #创建数据库连接 mysql用户名：root mysql密码：123123 mysql主机ip：192.168.19.130 数据库实例名字：test conn = engine.connect() #创建一个游标 metadata.create_all(engine) # 创建SQL语句，INSERT INTO "user" (id, name) VALUES (:id, :name) #原生sql # conn.execute(user.insert(),{'id':8,'name':'seven'}) #往表"user2"中新增一条记录 # sql = user.delete().where(user.c.id > 7) #从表"user2"中删除id>7的记录 这里user.c.id中的c是固定写法，记下 # sql = user.update().where(user.c.id == 9).values(name='jack') # #将id=9的这一条记录的name字段改成"jack" #1查询所有记录 sql = select([user, ]) #查询表"user2"的所有记录 原生sql：select * from user2 需要先导入select模块 result = conn.execute(sql) #通过游标执行sql语句 print(result.fetchall()) #[(7, 'seven'), (9, 'jack')] 列表中元组的形式(id,name) #输出所有的记录 sql = select([user.c.id, ]) #查询表"user2"的id字段所有记录 原生sql：select id from user2 result = conn.execute(sql) #通过游标执行sql语句 print(result.fetchall()) #[(7,), (9,), (12,)] #2排序 sql = select([user,]).order_by(user.c.id) #按照id顺序排列 #原生sql：select id from user2 oeder by id result = conn.execute(sql) #通过游标执行sql语句 print(result.fetchall()) #[(7, 'seven'), (9, 'jack1'), (12, 'seven')] #聚合统计 group by sql = select([user,]).group_by(user.c.name) #按照name进行聚合统计 #原生sql：select count(*) from user2 group by name result = conn.execute(sql) #通过游标执行sql语句 print(result.fetchall()) #[(9, 'jack1'), (7, 'seven')] #3多表查询 # sql = select([user.c.name, color.c.name]).where(user.c.id==color.c.id) conn.close() #关闭游标
建表2-新插入记录
__author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker Base = declarative_base() #生成一个SqlORM 基类（相当于前面的metedata） # engine = create_engine("mysql+mysqldb://root@localhost:3306/test",echo=False) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", # max_overflow=5,echo=True) max_overflow=5,echo=False) #这里的echo=True可以显示建表过程 #这里的echo=False不会显示建表过程 class Host(Base): #继承基类 （创建一个表的映射） __tablename__ = 'hosts' #定义表名"hosts" id = Column(Integer,primary_key=True,autoincrement=True) #自增 主键 hostname = Column(String(64),unique=True,nullable=False) ip_addr = Column(String(128),unique=True,nullable=False) port = Column(Integer,default=22) Base.metadata.create_all(engine) #创建所有表结构（如果表以及存在，这句话就不执行了） if __name__ == '__main__': SessionCls = sessionmaker(bind=engine) #创建与数据库的会话session class , # 注意,这里返回给session的是个class,不是实例 session = SessionCls() # h1 = Host(hostname='localhost',ip_addr='127.0.0.1') #id是主键，不写就是自增；port不写就是默认22 h2 = Host(hostname='ubuntu',ip_addr='192.168.2.243',port=20000) #插入记录1 h3 = Host(hostname='ubuntu2',ip_addr='192.168.2.244',port=20000) #插入记录2 #session.add(h3) session.add_all( [h2,h3]) #执行插入操作 #h2.hostname = 'ubuntu_test' #只要没提交,此时修改也没问题 #session.rollback() session.commit() #提交 必须commit才能生效 res = session.query(Host).filter(Host.hostname.in_(['ubuntu','ubuntu2'])).all() #原生sql select * from hosts where hostname in("ubuntu","ubuntu2"); print(res) #[<__main__.Host object at 0x000000000361BDD8>, <__main__.Host object at 0x000000000361B898>]
建表2-回滚
__author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker Base = declarative_base() #生成一个SqlORM 基类 # engine = create_engine("mysql+mysqldb://root@localhost:3306/test",echo=False) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", # max_overflow=5,echo=True) max_overflow=5,echo=False) #这里的echo=True可以显示建表过程 #这里的echo=False不会显示建表过程 class Host(Base): #继承基类 __tablename__ = 'hosts' #定义表名"hosts" id = Column(Integer,primary_key=True,autoincrement=True) #自增 主键 hostname = Column(String(64),unique=True,nullable=False) ip_addr = Column(String(128),unique=True,nullable=False) port = Column(Integer,default=22) Base.metadata.create_all(engine) #创建所有表结构 if __name__ == '__main__': SessionCls = sessionmaker(bind=engine) #创建与数据库的会话session class , # 注意,这里返回给session的是个class,不是实例 session = SessionCls() # h1 = Host(hostname='localhost',ip_addr='127.0.0.1') h2 = Host(hostname='ubuntu3',ip_addr='192.168.2.243',port=20000) #插入记录1 h3 = Host(hostname='ubuntu4',ip_addr='192.168.2.244',port=20000) #插入记录2 #session.add(h3) session.add_all( [h2,h3]) #执行插入操作 #h2.hostname = 'ubuntu_test' #只要没提交,此时修改也没问题 session.rollback() #提交之前先回滚了，插入操作失效 session.commit() #提交 必须commit才能生效 res = session.query(Host).filter(Host.hostname.in_(['ubuntu','ubuntu2'])).all() #原生sql select * from hosts where hostname in("ubuntu","ubuntu2"); print(res) #[<__main__.Host object at 0x000000000361BDD8>, <__main__.Host object at 0x000000000361B898>]
建表03
__author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine # engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5,echo=True) # max_overflow=5,echo=False) Base = declarative_base() class User(Base): __tablename__ = 'users1' id = Column(Integer, primary_key=True) name = Column(String(50)) # 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息 Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() # # ########## 增 ########## # u = User(id=2, name='sb') # session.add(u) # session.add_all([ # User(id=3, name='sb'), # User(id=4, name='sb') # ]) session.commit()
新建记录
__author__ = 'Administrator' #-*- coding:utf-8 -*- __author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine # engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5,echo=True) # max_overflow=5,echo=False) Base = declarative_base() class User(Base): #继承类Base __tablename__ = 'users1' #定义表名 id = Column(Integer, primary_key=True) name = Column(String(50)) # 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息 Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() # # ########## 增 ########## u = User(id=2, name='sb') #先插入一条记录 session.add(u) session.add_all([ User(id=3, name='sb'), ##再插入2条记录 User(id=4, name='sb') ]) session.commit() #必须提交才能生效 ##原生sql insert into users1("id","name") values (2,"sb"); ##原生sql insert into users1("id","name") values (3,"sb"); ##原生sql insert into users1("id","name") values (4,"sb");
删除记录
__author__ = 'Administrator' #-*- coding:utf-8 -*- __author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine # engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5,echo=True) # max_overflow=5,echo=False) Base = declarative_base() class User(Base): #继承类Base __tablename__ = 'users1' #定义表名 id = Column(Integer, primary_key=True) name = Column(String(50)) # 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息 Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() # # # ########## 增 ########## # u = User(id=2, name='sb') #先插入一条记录 # session.add(u) # session.add_all([ # User(id=3, name='sb'), ##再插入2条记录 # User(id=4, name='sb') # ]) ##原生sql insert into users1("id","name") values (2,"sb"); ##原生sql insert into users1("id","name") values (3,"sb"); ##原生sql insert into users1("id","name") values (4,"sb"); # session.commit() #必须提交才能生效 # ########## 删除 方法1########## session.query(User).filter(User.id > 2).delete() #将id大于2的记录删除 # 原生sql delete from users1 where id >2; session.commit() #必须提交才能生效 # ########## 删除 方法2########## obj = session.query(User).filter(User.id > 2).all() #删除所有符合条件的 # obj = session.query(User).filter(User.id > 2).first() #删除所有符合条件的第一条 # obj = session.query(User).filter(User.id > 2).last() #删除所有符合条件的最后一条 #1先将id大于2的记录查出来 # 原生sql delete from users1 where id >2; session.delete(obj) #2再将查出来的记录删除 session.commit() #必须提交才能生效
修改记录
__author__ = 'Administrator' #-*- coding:utf-8 -*- __author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine # engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5,echo=True) # max_overflow=5,echo=False) Base = declarative_base() class User(Base): #继承类Base __tablename__ = 'users1' #定义表名 id = Column(Integer, primary_key=True) name = Column(String(50)) # 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息 Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() # # # ########## 增 ########## # u = User(id=2, name='sb') #先插入一条记录 # session.add(u) # session.add_all([ # User(id=3, name='sb'), ##再插入2条记录 # User(id=4, name='sb') # ]) ##原生sql insert into users1("id","name") values (2,"sb"); ##原生sql insert into users1("id","name") values (3,"sb"); ##原生sql insert into users1("id","name") values (4,"sb"); # session.commit() #必须提交才能生效 # # ########## 删除 ########## # session.query(User).filter(User.id > 2).delete() # #将id大于2的记录删除 # # 原生sql delete from users1 where id >2; # session.commit() #必须提交才能生效 # # ########## 修改 方法1 ########## # # session.query(User).filter(User.id >1 ).update({'name' : "jack3"}) #可以一次修改多条 # session.query(User).filter(User.id == 2 ).update({'name' : "jack3"}) #一次修改一条 # session.commit() # ########## 修改 方法2 ########## obj = session.query(User).filter(User.id == 2 ).all() #先把要修改的记录查出来 obj.name = "jack4" #再进行修改 session.commit()
查询记录
__author__ = 'Administrator' #-*- coding:utf-8 -*- __author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine # engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5,echo=True) # max_overflow=5,echo=False) Base = declarative_base() class User(Base): #继承类Base __tablename__ = 'users1' #定义表名 id = Column(Integer, primary_key=True) name = Column(String(50)) def __repr__(self): #查询的时候，可以显示查询的结果记录 return "<id=%s,name=%s>" %(self.id,self.name) # 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息 Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() # # # ########## 增 ########## # u = User(id=2, name='sb') #先插入一条记录 # session.add(u) # session.add_all([ # User(id=3, name='sb'), ##再插入2条记录 # User(id=4, name='sb') # ]) ##原生sql insert into users1("id","name") values (2,"sb"); ##原生sql insert into users1("id","name") values (3,"sb"); ##原生sql insert into users1("id","name") values (4,"sb"); # session.commit() #必须提交才能生效 # # ########## 删除 ########## # session.query(User).filter(User.id > 2).delete() # #将id大于2的记录删除 # # 原生sql delete from users1 where id >2; # session.commit() #必须提交才能生效 # # ########## 修改 ########## # # session.query(User).filter(User.id >1 ).update({'name' : "jack3"}) #可以一次修改多条 # session.query(User).filter(User.id == 2 ).update({'name' : "jack3"}) #一次修改一条 #原生sql update users1 set name = "jack3" where id =2; # session.commit() # ########## 查 ########## # ret = session.query(User).filter_by(name='jack2').first() #显示第一条符合要求的 # print(ret) #<__main__.User object at 0x000000000361C5F8> # ret = session.query(User).filter_by(name='jack2').all() #显示所有符合要求的 # print(ret) #[<__main__.User object at 0x00000000035FCE10>, <__main__.User object at 0x000000000361D0B8>] #查到2条 #原生sql select * from users1 where name ="jack2"; # #3 in关键字 # ret = session.query(User).filter(User.name.in_(['jack2','jack3'])).all() # print(ret) #查到3条 # #原生sql select * from users1 where name in ("jack2","jack3"); # #4 排序 order by ret = session.query(User).order_by(User.id).all() print(ret) #[<id=2,name=jack3>, <id=3,name=jack2>, <id=4,name=jack2>] # #原生sql select * from users1 order by id; 默认升序 # #5 排序 order by 区间 # ret = session.query(User).order_by(User.id)[2:3] # print(ret) #原生sql select * from users1 where id between 2 and 3 order by id; 默认升序 # session.commit()
外键
__author__ = 'Administrator' #-*- coding:utf-8 -*- from sqlalchemy import Table, Column, Integer, ForeignKey,create_engine from sqlalchemy.orm import relationship from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5,echo=True) Base = declarative_base() class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) # children = relationship("Child") children = relationship("Child", backref="parent") class Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey('parent.id')) #定义外键 child表的parent_id是外键，对应parent表的主键id # 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息 Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() ret = session.query(Child).filter_by(name='jack2').all() #显示所有符合要求的 print(ret)
外键——一对多
__author__ = 'Administrator' #-*- coding:utf-8 -*- # 外键引用relationship() #!/usr/bin/env python3 #coding:utf8 #导入所需模块 from sqlalchemy import create_engine,func from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String,ForeignKey from sqlalchemy.orm import sessionmaker,relationship #生成sqlorm基类 Base = declarative_base() #创建数据库连接 # engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/liuyao", max_overflow=5) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5,echo=True) #目的是一个人可以拥有多本书，那么在数据库里的一对多关系 class User(Base): #表名 __tablename__ = 'user' #id字段 id = Column(String(20), primary_key=True) #名字字段 name = Column(String(20)) # 一对多:#内容不是表名而是定义的表结构名字 books = relationship('Book') class Book(Base): #表名 __tablename__ = 'book' #id字段 id = Column(String(20), primary_key=True) #名字字段 name = Column(String(20)) # “多”的一方的book表是通过外键关联到user表的: #ForeignKey是外键 关联user表的id字段 #"book"表的"user_id"是外键，他对应"user"表的主键"id" user_id = Column(String(20), ForeignKey('user.id')) #定义外键 #创建所需表 Base.metadata.create_all(engine) if __name__ == '__main__': #绑定,生成会话 SessionCls = sessionmaker(bind=engine) #返回的是类 session = SessionCls() #实例 #创建用户--新建2条记录 liuyao = User(id='1',name='liuyao') ali=User(id='2',name='ali') #添加字段--将字段添加到session session.add_all([liuyao,ali]) #提交 session.commit() #创建白鹿原这本书，指定谁是拥有者 Whitedeer = Book(id='1',name='White_deer',user_id = '1') #创建三体这本书，指定谁是拥有者 Threebody = Book(id='2',name='Three_body',user_id = '2') #添加字段 session.add_all([Whitedeer,Threebody]) #提交 session.commit()
外键-多对多
__author__ = 'Administrator' #-*- coding:utf-8 -*- # # 多对多 # # 建立一个双向一对多关系,“反向”是一个许多人,指定一个额外的relationship()函数 # 并连接两个使用relationship.back_populates参数 简单来说, relationship函数是sqlalchemy对关系之间提供的 # 一种便利的调用方式, backref参数则对关系提供反向引用的声明。 # 在最新版本的sqlalchemy中对relationship引进了back_populates参数。 from sqlalchemy import Column, Sequence, String, Integer, ForeignKey from sqlalchemy import create_engine # 导入创建连接驱动 from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker from sqlalchemy.orm import relationship, backref # 这个url可以用urlparse解析, 其中echo=True表示执行时显示sql语句 # engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/liuyao", max_overflow=5) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5,echo=True) #生成了declarative基类, 以后的model继承此类 Base = declarative_base() class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) name = Column(String(64),unique=True,nullable=False) children = relationship("Child", back_populates="parent") #双向关联，成对出现 class Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True) name = Column(String(64),unique=True,nullable=False) parent_id = Column(Integer, ForeignKey('parent.id')) parent = relationship("Parent", back_populates="children") #双向关联，成对出现 Base.metadata.create_all(engine) #创建所有表结构 if __name__ == '__main__': SessionCls = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 session = SessionCls() mama = Parent(id='1',name='mamaxx') #添加2条记录 baba = Parent(id='2',name='babaoo') session.add_all([mama,baba]) session.commit() #提交 onesb = Child(id='1',name='onesb',parent_id='2') twosb = Child(id='2',name='twosb',parent_id='2') session.add_all([onesb,twosb]) session.commit() #提交
外键-多对多中间表
__author__ = 'Administrator' #-*- coding:utf-8 -*- # from sqlalchemy import create_engine,func,Table,select from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String,ForeignKey from sqlalchemy.orm import sessionmaker,relationship Base = declarative_base() ##生成了declarative基类, 以后的model继承此类 #中间关系表（中间关系表的2个字段都是外键，分别对应其他2个表的主键，主键关系表本身由2个外键一同组成主键） Host2Group = Table('host_2_group',Base.metadata, Column('host_id',ForeignKey('hosts.id'),primary_key=True), Column('group_id',ForeignKey('group1.id'),primary_key=True), ) # engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/liuyao", max_overflow=5) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test1", max_overflow=5,echo=True) conn = engine.connect() class Host(Base): __tablename__ = 'hosts' id = Column(Integer,primary_key=True,autoincrement=True) hostname = Column(String(64),unique=True,nullable=False) ip_addr = Column(String(128),unique=True,nullable=False) port = Column(Integer,default=22) groups = relationship('Group', #关联group表，这里是类名 secondary= Host2Group, #关联中间关系表，这里也是类名 backref = 'host_list') #想要查看一个组包含多少主机，通过host_list调用，单向即可 #想要看一个主机属于几个组，通过host_list调用 #把表"group"作为一个对象，通过host_list来调用 def __repr__(self): return "<id=%s,hostname=%s, ip_addr=%s>" %(self.id, self.hostname, self.ip_addr) class Group(Base): __tablename__ = 'group1' id = Column(Integer,primary_key=True) name = Column(String(64),unique=True,nullable=False) Base.metadata.create_all(engine) #创建所有表结构 (这句代码至关重要) if __name__ == '__main__': SessionCls = sessionmaker(bind=engine) session = SessionCls() h1 = Host(hostname='h3',ip_addr="1.23.22.11") h2 = Host(hostname='h4',ip_addr="1.23.22.111") session.add_all([h1,h2]) #创建2个主机 session.commit() #提交 g1 = Group(name='g3') g2 = Group(name='g4') # g3 = Group(name='g3') # g4 = Group(name='g4') session.add_all([g1,g2]) #创建2个组 session.commit() #提交 sql = select([Host2Group, ]) result = conn.execute(sql) print(result.fetchall()) #[] # # 创建SQL语句，INSERT INTO "user" (host_id, group_id) VALUES (:host_id, :group_id) #原生sql result=conn.execute(Host2Group.insert(),{'host_id':1,'group_id':'1'}) #往表"Host2Group"中新增一条记录 result=conn.execute(Host2Group.insert(),{'host_id':2,'group_id':'1'}) sql = select([Host2Group, ]) result = conn.execute(sql) print(result.fetchall()) #[(1, 2), (2, 2)] conn.close() #关闭游标
外键-backref
__author__ = 'Administrator' #-*- coding:utf-8 -*- # from sqlalchemy import Integer, ForeignKey, String, Column,create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship,sessionmaker engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5,echo=True) Base = declarative_base() class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) name = Column(String(50)) addresses = relationship("Address", backref="user") def __repr__(self): #查询的时候，可以显示查询的结果记录 return "<id=%s,name=%s>" %(self.id,self.name) class Address(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True) email = Column(String(50)) user_id = Column(Integer, ForeignKey('user.id')) def __repr__(self): #查询的时候，可以显示查询的结果记录 return "<id=%s,email=%s,user_id=%s>" %(self.id,self.email,self.user_id) # 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息 Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() # session.add_all([ # User(id=3, name='sb3'), ##再插入2条记录 # User(id=4, name='sb4') # ]) # # session.add_all([ # Address(id=1, email='123@163.com',user_id=3), ##再插入2条记录 # Address(id=2, email='124@163.com',user_id=4) # ]) #1如果没有19行的话，需要2条sql # user = session.query(User).filter_by(name="sb3").first() # print(user) #<id=3,name=sb3> # # 假如没有relationship，我们只能像下面这样调用关系数据。 # ---先通过名字找到用户id 再通过用户id去另外一张表找email # # addresses = session.query(Address).filter_by(user_id=3).all()#再通过用户id去另外一张表找email # print(addresses) #[<id=1,email=123@163.com,user_id=3>] #2 加上19行 addresses = relationship("Address")#, backref="user")后 # 就只需要1步（一个sql），就可以查到另外一张表的所有字段--外键关联(相当于通过外键进行多表查询) sql = session.query(User).filter_by(name="sb3").first() print(sql.addresses) #19行 #[<id=1,email=123@163.com,user_id=3>] #这里的addresses指的是19行addresses = relationship("Address", backref="user") 最前面的addresses #通过表A的字段(用户名字)，查表B的字段（邮箱） 这里的addresses代指表B（地址表） #3 注意，在上面的addresses属性中我们并没有定义backref属性,所以我们可以通过User对象获取所拥有的地址， # 但是不能通过Address对象获取到所属的用户. #我们当我们有从Address对象获取所属用户的需求时，backref参数就派上用场了。 # 大致原理应该就是sqlalchemy在运行时对Address对象动态的设置了一个指向所属User对象的属性， # 这样就能在实际开发中使逻辑关系更加清晰，代码更加简洁了。 sql = session.query(Address).filter_by(email="124@163.com").first() print(sql.user) #19行 <id=4,name=sb4> #这里的"user"指的是19行backref="user"后的user 这里的user代指表A（用户表） # 就只需要1步，就可以查到另外一张表的所有字段--外键关联(相当于通过外键进行多表查询) # 通过表b的邮箱，查到表b的user_id，通过user_id查到表A的id（就是表B的user_id）和name #通过表B的字段(邮箱)，查表A的字段（用户名字） # http://www.zhihu.com/question/38456789
左连接
# __author__ = 'Administrator' # #-*- coding:utf-8 -*- # # # http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins # # #1左外连接 # # A B # # - - # # 1 3 # # 2 4 # # 3 5 # # 4 6 # # # # select * from A LEFT OUTER JOIN B on A.a = B.b; 原生sql # # # # a | b # # --+----- # # 1 | null # # 2 | null # # 3 | 3 # # 4 | 4 # # # session.query(Host).join(Host.host_groups).filter(HostGroup.name=='t1').group_by("Host").all() # # # # Examples # # # # Suppose you have two tables, with a single column each, and data as follows: # # # # A B # # - - # # 1 3 # # 2 4 # # 3 5 # # 4 6 # # Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B. # # # # Inner join # # # # An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common. # # # # select * from a INNER JOIN b on a.a = b.b; # # select a.*,b.* from a,b where a.a = b.b; # # # # a | b # # --+-- # # 3 | 3 # # 4 | 4 # # Left outer join # # # # A left outer join will give all rows in A, plus any common rows in B. # # # # select * from a LEFT OUTER JOIN b on a.a = b.b; # # select a.*,b.* from a,b where a.a = b.b(+); # # # # a | b # # --+----- # # 1 | null # # 2 | null # # 3 | 3 # # 4 | 4 # # Right outer join # # # # A right outer join will give all rows in B, plus any common rows in A. # # # # select * from a RIGHT OUTER JOIN b on a.a = b.b; # # select a.*,b.* from a,b where a.a(+) = b.b; # # # # a | b # # -----+---- # # 3 | 3 # # 4 | 4 # # null | 5 # # null | 6 # # Full outer join # # # # A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa. # # # # select * from a FULL OUTER JOIN b on a.a = b.b; # # # # a | b # # -----+----- # # 1 | null # # 2 | null # # 3 | 3 # # 4 | 4 # # null | 6 # # null | 5 # # # 如何连接表？ # # # # 复制代码 代码如下: # # from sqlalchemy import distinct # from sqlalchemy.orm import aliased # # # Friend = aliased(User, name='Friend') # # print session.query(User.id).join(Friendship, User.id == Friendship.user_id1).all() # # 所有有朋友的用户 # print session.query(distinct(User.id)).join(Friendship, User.id == Friendship.user_id1).all() # # 所有有朋友的用户（去掉重复的） # print session.query(User.id).join(Friendship, User.id == Friendship.user_id1).distinct().all() # # 同上 # print session.query(Friendship.user_id2).join(User, User.id == Friendship.user_id1).order_by(Friendship.user_id2).distinct().all() # # 所有被别人当成朋友的用户 # print session.query(Friendship.user_id2).select_from(User).join(Friendship, User.id == Friendship.user_id1).order_by(Friendship.user_id2).distinct().all() # # 同上，join 的方向相反，但因为不是 STRAIGHT_JOIN，所以 MySQL 可以自己选择顺序 # print session.query(User.id, Friendship.user_id2).join(Friendship, User.id == Friendship.user_id1).all() # # 用户及其朋友 # print session.query(User.id, Friendship.user_id2).join(Friendship, User.id == Friendship.user_id1).filter(User.id < 10).all() # # id 小于 10 的用户及其朋友 # print session.query(User.id, Friend.id).join(Friendship, User.id == Friendship.user_id1).join(Friend, Friend.id == Friendship.user_id2).all() # # 两次 join，由于使用到相同的表，因此需要别名 # print session.query(User.id, Friendship.user_id2).outerjoin(Friendship, User.id == Friendship.user_id1).all() # # 用户及其朋友（无朋友则为 None，使用左连接） # http://www.jb51.net/article/49789.htm
左右连接、自连接
__author__ = 'Administrator' #-*- coding:utf-8 -*- __author__ = 'Administrator' #-*- coding:utf-8 -*- # from sqlalchemy import create_engine,func,Table from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String,ForeignKey from sqlalchemy.orm import sessionmaker,relationship Base = declarative_base() ##生成了declarative基类, 以后的model继承此类 # engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/liuyao", max_overflow=5) engine = create_engine("mysql+pymysql://root:123456@192.168.19.130:3306/test", max_overflow=5,echo=True) class Host(Base): __tablename__ = 'hosts5' id = Column(Integer,primary_key=True,autoincrement=True) hostname = Column(String(64),unique=True,nullable=False) ip_addr = Column(String(128),unique=True,nullable=False) port = Column(Integer,default=22) # groups = relationship('Group', #关联group表，这里是类名 # backref = 'host_list') #想要查看一个组包含多少主机，通过host_list调用，单向即可 #想要看一个主机属于几个组，通过host_list调用 #把表"group"作为一个对象，通过host_list来调用 def __repr__(self): return "<id=%s,hostname=%s, ip_addr=%s>" %(self.id, self.hostname, self.ip_addr) class Group(Base): __tablename__ = 'group5' id = Column(Integer,primary_key=True) name = Column(String(64),unique=True,nullable=False) def __repr__(self): return "<id=%s,name=%s>" %(self.id,self.name) Base.metadata.create_all(engine) #创建所有表结构 (这句代码至关重要) if __name__ == '__main__': SessionCls = sessionmaker(bind=engine) session = SessionCls() # h1 = Host(hostname='h1',ip_addr="1.23.22.1") # h2 = Host(hostname='h2',ip_addr="1.23.22.113") # h3 = Host(hostname='h3',ip_addr="1.23.22.12") # h4 = Host(hostname='h4',ip_addr="1.23.22.123") # session.add_all([h1,h2,h3,h4]) #创建4个主机 # session.commit() #提交 # # g3 = Group(name='g3') # g4 = Group(name='g4') # session.add_all([g3,g4]) #创建2个组 # session.commit() #提交 #1内连接（自连接） sql=session.query(Host).join(Group,Host.id == Group.id).all() #显示一个表的字段 # trans_details.query.outerjoin(Uses).filter(Users.username.like('%xx%')) print(sql) #[<id=1,hostname=h1, ip_addr=1.23.22.1>, <id=2,hostname=h2, ip_addr=1.23.22.113>] sql=session.query(Host,Group).join(Group,Host.id == Group.id).all() #显示2个表的字段 # trans_details.query.outerjoin(Uses).filter(Users.username.like('%xx%')) print(sql) #2左连接 sql=session.query(Host).outerjoin(Group,Host.id == Group.id).all() #显示一个表的字段 # trans_details.query.outerjoin(Uses).filter(Users.username.like('%xx%')) print(sql) #[<id=1,hostname=h1, ip_addr=1.23.22.1>, <id=2,hostname=h2, ip_addr=1.23.22.113>, # <id=3,hostname=h3, ip_addr=1.23.22.12>, <id=4,hostname=h4, ip_addr=1.23.22.123>] sql=session.query(Host,Group).outerjoin(Group,Host.id == Group.id).all() #显示2个表的字段 # trans_details.query.outerjoin(Uses).filter(Users.username.like('%xx%')) print(sql) #1自连接（内连接） # select * from hosts5 INNER JOIN group5 on hosts5.id = group5.id; #相当于求2个表的交集 # mysql> select * from group5; # +----+------+ # | id | name | # +----+------+ # | 1 | g3 | # | 2 | g4 | # +----+------+ # 2 rows in set (0.00 sec) # # mysql> select * from hosts5; # +----+----------+-------------+------+ # | id | hostname | ip_addr | port | # +----+----------+-------------+------+ # | 1 | h1 | 1.23.22.1 | 22 | # | 2 | h2 | 1.23.22.113 | 22 | # | 3 | h3 | 1.23.22.12 | 22 | # | 4 | h4 | 1.23.22.123 | 22 | # +----+----------+-------------+------+ # 4 rows in set (0.00 sec) # # mysql> select * from host5 INNER JOIN group5 on host5.id = group5.id; # ERROR 1146 (42S02): Table 'test.host5' doesn't exist # mysql> select * from hosts5 INNER JOIN group5 on hosts5.id = group5.id; # +----+----------+-------------+------+----+------+ # | id | hostname | ip_addr | port | id | name | # +----+----------+-------------+------+----+------+ # | 1 | h1 | 1.23.22.1 | 22 | 1 | g3 | # | 2 | h2 | 1.23.22.113 | 22 | 2 | g4 | # +----+----------+-------------+------+----+------+ # 2 rows in set (0.00 sec) #2 左连接 # mysql> select * from hosts5 left outer JOIN group5 on hosts5.id = group5.id; # +----+----------+-------------+------+------+------+ # | id | hostname | ip_addr | port | id | name | # +----+----------+-------------+------+------+------+ # | 1 | h1 | 1.23.22.1 | 22 | 1 | g3 | # | 2 | h2 | 1.23.22.113 | 22 | 2 | g4 | # | 3 | h3 | 1.23.22.12 | 22 | NULL | NULL | # | 4 | h4 | 1.23.22.123 | 22 | NULL | NULL | # +----+----------+-------------+------+------+------+ # 4 rows in set (0.01 sec) # #3 右连接 # mysql> select * from hosts5 right outer JOIN group5 on hosts5.id = group5.id; # +------+----------+-------------+------+----+------+ # | id | hostname | ip_addr | port | id | name | # +------+----------+-------------+------+----+------+ # | 1 | h1 | 1.23.22.1 | 22 | 1 | g3 | # | 2 | h2 | 1.23.22.113 | 22 | 2 | g4 | # +------+----------+-------------+------+----+------+ # 2 rows in set (0.00 sec)
浙公网安备 33010602011771号