做医疗竞价网站,网站开发系统架构图,怎么做的网站怎么放上网,站长百科 wordpress摘自网上一个经典的例子#xff1a;大哥和小弟一、外键约束MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。外键的使用条件#xff1a;1.两个表必须是InnoDB表#xff0c;MyISAM表暂时不支持外键(据说以后的版本有可能支持#xff0c;但至少目前不支持)#x…摘自网上一个经典的例子大哥和小弟一、外键约束MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。外键的使用条件1.两个表必须是InnoDB表MyISAM表暂时不支持外键(据说以后的版本有可能支持但至少目前不支持)2.外键列必须建立了索引MySQL 4.1.2以后的版本在建立外键时会自动创建索引但如果在较早的版本则需要显示建立3.外键关系的两个表的列必须是数据类型相似也就是可以相互转换类型的列比如int和tinyint可以而int和char则不可以外键的好处可以使得两张表关联保证数据的一致性和实现一些级联操作外键的定义语法[constraint symbol] foreign key [id] (index_col_name, ...)references tbl_name (index_col_name, ...)[on delete {restrict | cascade | set null | on action | set default}][on update {restrict | cascade | set null | on action | set default}]该语法可以在 create table 和 alter table 时使用如果不指定constraint symbolMYSQL会自动生成一个名字。on delete,on update表示事件触发限制可设参数restrict(限制外表中的外键改动)cascade(跟随外键改动)set null(设空值)set default(设默认值)no action(无动作默认的)1. 先建立1个新的数据库2.在pycharm中新建2张table(Dage,Xiaodi)import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String,ForeignKeyenginecreate_engine(mysqlpymysql://root:1234localhost/chen)Basedeclarative_base()class Dage(Base):__tablename__DageidColumn(Integer,primary_keyTrue)nameColumn(String(32))class Xiaodi(Base):__tablename__XiaodiidColumn(Integer,primary_keyTrue)nameColumn(String(32))Dage_id Column(Integer,ForeignKey(Dage.id))Base.metadata.create_all(engine)在客户端show table已经创建过程show create table xiaodi;----------------------------------------------------------------------| xiaodi | CREATE TABLE xiaodi (id int(11) NOT NULL AUTO_INCREMENT,name varchar(32) DEFAULT NULL,Dage_id int(11) DEFAULT NULL,PRIMARY KEY (id),KEY Dage_id (Dage_id),CONSTRAINT xiaodi_ibfk_1 FOREIGN KEY (Dage_id) REFERENCES dage (id)) ENGINEInnoDB DEFAULT CHARSETutf8 |show create table dage;3.在2张表中各插入1条数据。由于不知名的错误需要把2个表中的数据分开创建。import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String,ForeignKeyfrom sqlalchemy.orm import sessionmakerenginecreate_engine(mysqlpymysql://root:1234localhost/chen)Basedeclarative_base()class Dage(Base):__tablename__DageidColumn(Integer,primary_keyTrue)nameColumn(String(32))class Xiaodi(Base):__tablename__XiaodiidColumn(Integer,primary_keyTrue)nameColumn(String(32))Dage_id Column(Integer,ForeignKey(Dage.id))Base.metadata.create_all(engine)Session_classsessionmaker(bindengine)sessionSession_class()#dage1Dage(nameI_am_dage)xiaodi1Xiaodi(Dage_id1,nameI_am_xiaodi)session.add_all([xiaodi1])session.commit()查看效果发现这种创建方式有个问题id会自增2张表的ID依次为1,24.尝试删除大哥删除不了因为有外键约束插入1个小弟因为没有大哥所以插入不成功5.把外键约束增加事件触发限制alter table xiaodi drop foreign key xiaodi_ibfk_1;alter table xiaodi add foreign key(Dage_id) references dage(id) on delete cascade on update cascade; #意思是从表会跟随主表的改变而改变。理论上现在就能正常删除了。二多外键关联建立一个customer表和一个地址表表结构from sqlalchemy import Integer, ForeignKey, String, Columnfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import relationshipfrom sqlalchemy import create_engineBase declarative_base()class Customer(Base):__tablename__ customerid Column(Integer, primary_keyTrue)name Column(String(64))# 账单地址和邮寄地址 都关联同一个地址表billing_address_id Column(Integer, ForeignKey(address.id))shipping_address_id Column(Integer, ForeignKey(address.id))billing_address relationship(Address, foreign_keys[billing_address_id])shipping_address relationship(Address, foreign_keys[shipping_address_id])class Address(Base):__tablename__ addressid Column(Integer, primary_keyTrue)city Column(String(64))def __repr__(self):return self.streetengine create_engine(mysqlpymysql://root:1234localhost/chen,encodingutf-8)Base.metadata.create_all(engine) # 创建表结构生成表内容from Day12 import ex5from sqlalchemy.orm import sessionmakerSession_class sessionmaker(bindex5.engine) # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例session Session_class() # 生成session实例 #cursoraddr1 ex5.Address(cityBJ)addr2 ex5.Address(cityShanghai)addr3 ex5.Address(cityTianjin)session.add_all([addr1, addr2, addr3])c1 ex5.Customer(nameAlex, billing_addressaddr1, shipping_addressaddr2)c2 ex5.Customer(nameJack, billing_addressaddr3, shipping_addressaddr3)session.add_all([c1, c2])session.commit()效果查询objsession.query(ex5.Customer).filter(ex5.Customer.nameAlex).first()print(obj.name,obj.billing_address,obj.shipping_address)返回结果C:\abccdxddd\Oldboy\python-3.5.2-embed-amd64\python.exe C:/abccdxddd/Oldboy/Py_Exercise/Day12/ex4.pyAlex BJ ShanghaiProcess finished with exit code 0三多对多1.创建表结构import sqlalchemyfrom sqlalchemy import Table, Column, Integer,String,DATE, ForeignKeyfrom sqlalchemy.orm import relationshipfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerBasedeclarative_base()book_m2m_authorTable(book_m2m_author,Base.metadata,Column(book_id,Integer,ForeignKey(books.id)),Column(author_id,Integer,ForeignKey(authors.id)),)class Book(Base):__tablename__booksidColumn(Integer,primary_keyTrue)nameColumn(String(64))authorsrelationship(Author,secondarybook_m2m_author,backrefbooks)def __repr__(self):return self.nameclass Author(Base):__tablename__authorsidColumn(Integer,primary_keyTrue)nameColumn(String(32))def __repr__(self):return self.nameenginecreate_engine(mysqlpymysql://root:1234localhost/chen)Base.metadata.create_all(engine)2. 增加表内容# 添加数据from Day12 import ex4from sqlalchemy.orm import sessionmakerSession_class sessionmaker(bindex4.engine) # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例session Session_class() # 生成session实例 #cursor# 创建书b1 ex4.Book(namelearn python with Alex)b2 ex4.Book(namelearn Zhangbility with Alex)b3 ex4.Book(nameLearn hook up girls with Alex)# 创建作者a1 ex4.Author(nameAlex)a2 ex4.Author(nameJack)a3 ex4.Author(nameRain)# 关联关系b1.authors [a1, a3]b3.authors [a1, a2, a3]session.add_all([b1, b2, b3, a1, a2, a3])session.commit()查看效果3. 通过作者查询书4.通过书查作者
