from sqlalchemy import create_engine,Column,Integer,String,Float,DECIMAL,Boolean,Enum,Date,DateTime,Time,Text,and_,or_,ForeignKey
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy.orm.collections import InstrumentedList
from sqlalchemy import func
import random
import enum
from datetime import date,datetime,time
# 准备连接数据库基本信息
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'first_sqlalchemy'
USERNAME = 'root'
PASSWORD = ''
# 按照上述的格式来 组织数据库信息
DB_URI = 'mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8'.format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()
# 需求：sqlalchemy实现外键及其四种约束讲解
class User(Base):
__tablename__='user'
id =Column(Integer,primary_key=True,autoincrement=True)
uname = Column(String(50),nullable=False)
# 1:1关系的表示
extend = relationship('UserExtend',uselist=False)
# newss = relationship('News') # 这种写法不是最优的，通常会把它通过反向声明的方式写在‘多’的那一方
def __repr__(self):
return '<User(uname:%s)>'%self.uname
class UserExtend(Base):
__tablename__ ='user_extend'
id = Column(Integer,primary_key=True,autoincrement=True)
school = Column(String(50))
# 外键
uid = Column(Integer,ForeignKey('user.id'))
# 1:1关系的表示
user = relationship('User')
class News(Base):
__tablename__ = 'news'
id = Column(Integer,primary_key=True,autoincrement=True)
title = Column(String(50),nullable=False)
content = Column(Text,nullable=False)
# sqlalchemgy实现外键的方法
uid = Column(Integer,ForeignKey('user.id')) # 默认删除策为：restrict
# 添加属性 优化2表查询操作
# 正向
# author = relationship('User')
# 最终：会把正向 和反向 关系 写在一起
author = relationship('User',backref='newss')
def __repr__(self):
return '<News(title:%s,content=%s)>'%(self.title,self.content)
# 创建表
Base.metadata.drop_all()
Base.metadata.create_all()
# 需求： ORM层面外键 一对一
# 好处1： 添加数据 User 添加UserExtend
user = User(uname='zhangsan')
ux = UserExtend(school = 'shangdong')
user.extend = ux
print(user.extend)
# 好处2：查询数据D:\python_VirtualEnv\flask_env\Scripts\python.exe D:/python_project/sqlalchemy_demo/demo11.py
<__main__.UserExtend object at 0x0000024CC40712E0>
D:\python_project\sqlalchemy_demo\demo11.py:72: SAWarning: relationship 'UserExtend.user' will copy column user.id to column user_extend.uid, which conflicts with relationship(s): 'User.extend' (copies user.id to user_extend.uid). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards. To silence this warning, add the parameter 'overlaps="extend"' to the 'UserExtend.user' relationship.
user = User(uname='zhangsan')
老师，运行后为什么会出现这种错误，视频上没有显示这部分内容
京公网安备 11011402011233号