python_数据库介绍 / mysql(基本使用、数据类型与创建表、增删改查等系列操作) / sqlalchemy (orm介绍、常用语法、外键关联、多对多关联)
在这里得感谢,老师Alex金角大王(路飞学城IT)
Python(给兄弟们挂个🔗)
更多的拓展,银角大王博客
python边写边更…
一、数据库介绍:
1.简单介绍:
数据库(Database)是按照数据机构来组织、存储和管理数据的仓库;
我们使用关系型数据库管理系统(RDBMS),来存储和管理大量数据;
RDBMS | 特点 |
---|---|
1 | 数据以表格的形式存在; |
2 | 每行为记录的名称 |
3 | 每列为记录名称所对应的数据域 |
4 | 许多的行和列组成一张表单 |
5 | 若干的表单组成database |
1.关系型数据库;里面包含多张表格,每个表里存储数据;
2.每个表格,通过"特殊字段"进行关联;例如:student_id 与 id ,是关联对应的;表一与表二在该字段的信息是关联对应的;
2.关系型数据库(RDBMS)的术语:
数据库 | 关联表的集合 |
---|---|
数据表 | 简单的电子表格 |
列 | 一列中包含了相同类型的数据 |
行 | 一组相关的数据 |
冗余 | 就是多张表进行关联,你想要的数据可能要通过很多次关联查询才能找到;就把你想要的数据copy放在第一个表里,就不用再去关联查询了;直接在当前表中读取到数据;可是这相当于把数据copy两份或者很多份,这样造成的麻烦就是,一处改处处改… |
主键 | 确定某一行的信息是唯一,所进行的“唯一标号” |
外键 | 用来关联两个表 |
索引 | 相当于Hash映射,对某一列的信息进行标号,再通过某一特定算法进行查询…减小运算次数 |
复合键 | 复合索引,多个列作为一个索引键 |
参考完整性 | 参考完整性要求“关系”中不允许引用不存在的实体 |
3.Mysql数据库
Mysql是最流行的关系型数据库管理系统(RDBMS),在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一;
1.安装步骤:
step1:选择8.0.24版本的包:(其他版本的包在Archives)
step2:
(刚开始也不知道为啥限我速QAQ,重新下载几次就好了…)
step3:
下载完,解压;此时需要我们自己添加一个配置文件(my.ini);
(1)自己在安装包的根目录下,新建一个文本文件;把名字改为my.ini
(大家按照他的步骤更改…要不然会报错…他写的这个端口不对;把它的改为3306)
(2)把下面的代码,ctrl c + ctrl c;(这是我改好的…大家可以直接拷贝)
[mysqld]
basedir ="E:\MySQL"
datadir ="E:\MySQL\Data"
port=3306
server_id =10
character-set-server=gbk
character_set_filesystem=gbk
[client]
port=3306
default-character-set=gbk
[mysqld_safe]
timezone="CST"
[mysql]
default-character-set=utf8
(3)写完之后,会出现my.ini配置文件;
step4:
step5:bin目录下执行:
mysqld --initialize --console
上面的命令是用来进行初始化数据库,后面的是随机生成的密码(拷贝下来)…
step6:如果你遇到“mysql 不是内部指令或…”这样的错误反馈;(试着添加环境变量)
(网上的添加五花八门…把人能搞烦了 /暴躁/暴躁/暴躁)
系统变量里面加搜索路径:
(还有在“用户变量”里面加bin路径的操作…)
step7:进行安装
mysqld --install
step8:启动服务器
net start mysql#方式一
sc start mysql#方式二
CMD打开services.msc(查看当前的mysql服务是否开启)
操作来源 >>>以上操作来源于此
2.登录步骤:
<1>登录命令:(在mysql_zip解压的bin目录下;)
mysql -u root -p
(这个时候,密码用上面初始化数据库的随机密码)
<2>更改密码:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
1.new_password;是你要设置的新密码;
2.注意:所有的mysql指令都要以 “ ;” 结尾;
3.我这里的密码自己设置的“123456”
登录,以及修改密码这个兄弟写的不错传送门
conclude:
1.mysql -u root -p;(登录操作)
2.alter user “root”@“localhost” identified by “new_password”;(改密码操作)
二、数据库的使用:
1.基本使用:
<1>显示数据库:
show databases;
<2>使用数据库:
use mysql;
<3>显示表格:
show tables;
<4>显示表格里面的字段:(显示表的结构)
desc func;
show columns from func;
type:
type | 意义 |
---|---|
枚举字段enum(“func1”,“func2”) | 你只能选择“fun1”或者“func2”;(func1、func2在这只是举例,无特殊意义;) |
Null | 是否为空;No为不能为空,必须赋值; |
key | 键,pri主键; |
default | 默认值; |
<5>显示字段里的数据:
获取字段数据 | 指令 |
---|---|
1.获取表里所有的数据 | select * from func\G;func是表格;\G 是把所有的数据 “竖着” 输出; |
2.获取表里特定“字段”的数据; | select user from user;前一个user是字段名;后一个user是表名;这里不要加“\G” |
<6>创建新库以及授权:
功能 | 指令 |
---|---|
创建新的数据库 | create database test;其中test是新建库的库名; |
授权 | grant all on test.* to “root”@"%" ;test是库名,“ .* ”是库下所有的表;“%”是所有机器; |
授权 | grant all privileges on test.* to “alex”@“%” |
查询授权 | show grants for alex;alex是用户名 |
(授权之后,就会有你授权的新用户;这个在mysql库中的user表里面;)
(用Alex用户登录,就只有test库了…)
conclude:
1.show databases;show tables ;
2.use mysql;desc test;show columns from user;
3.select * from user(表里的所有数据);select user from user(user);
4.create database test(创建库,只含英文类型);create database test_1 charset utf-8(创建库,含中文);
5.grant all on test.* to “alex”@“localhost”;grant all privileges on test.* to “alex”@“localhost”;
6.show grants for alex;
2.常用命令:
1.创建数据表:
create table student (
id int not null auto_increment,
name char(32) not null,
age int not null,
reg_date date not null,
primary key(id));
auto_increment | 自增 |
---|---|
primary key (id) | 把id字段设置为主键 |
2.在表里面插入数据:
insert student (name,age,reg_date) values ("alex",100,"2021-4-23")
name、age、reg_date | 都是字段名 |
---|---|
alex、100、2021-4-23 | 插入字段里面的数据;要一 一对应 |
3.查询数据:
select * from student where[condition1] and [condition2] limit num1 offset num2
条件筛选 | where |
---|---|
limit | 允许输出多少个 |
offset | 偏移量;从第几个开始输出 |
1.limit、offset:
(为了看出效果,预先插入多个数据;)
2.where:
(还是这些字段数据;)
1)筛选int字段:
2)筛选date字段:
conclude:
int | select * from student where id<3; |
---|---|
date | select * from student where reg_date like “2021-05%”; |
3.更改字段里的数据:(行修改)
(下面把这个 id=3 的行数据,改掉…)
update student set name = "caixukun",age = 1 where id=3;
(改掉了…)
4.删除字段信息:
delete from student where name = "jack";
conclude:
增、删、改、查 | |
---|---|
增 | set table studnet(…) ; insert student (字段1、字段2、字段3) values (值1、值2、值3) |
删 | delete from studen where 条件 |
改 | update student set name = “caixukun” where 条件 |
查 | select * from student where条件 limit num1 offset num2 |
5.排序:
select * from student order by age;
select * from student order by age desc;
6.分组:
select name,count(*) from student group by name;
select name,sum(age) from student group by name;
select coalesce(name,"total_age"),sum(age)as sum_age from student group by name with rollup;
count(*) | 表里所有的 |
---|---|
sum() | 字段求和 |
with rollup | 求总和 |
coalesce | 定义求总和名称 |
7.更改字段:
alter table student add sex enmu("M","F");#增加字段
alter table student drop sex enmu("M","F");#删除字段
alter table student modify sex enmu("M","F") not null;#修改字段类型,数据类型得写全
alter table student change sex gender char(32) not null;#修改字段名
(add、drop、modify该类型、change改字段名)
3.外键关联:
create table study_record
(id int auto_increment primary key,
day int not null,
status char(32) not null ,
stu_id int not null,
key fk (stu_id),#单独命名
constraint fk foreign key (stu_id) references student (id));#与表一id外键关联;
Tip:下面的细节问题,要看一下…
1.(查看表的创建过程):
show create table study_record;
2.关联的外键,是要一一对应的!!!
(你student里面的id 和 study_record 里面的 stu_id 是关联的;他们的序号也是要一一对应的,完全要一样的;)上图在id只有1 3 5 的情况下;往study_recorde里面插值stu_id = 2 4 ;就报错了…
3.可以删study_record里面的数据(引用的)…但是删不掉student里面的数据(被引用的)
4.NUll值处理:
1. xx =null ; xx != null…nullhere和任何值比较,都是Flase;
2. MySQL中处理NULL使用IS NULL和IS NOT NULL运算符;
5.Mysql连接:
(就是两个表的"联合查询";查两个表的“交”、“并”、“补”)
(left join、right join、inner join 和 full join)
1、inner join (取交集)
select * from a inner join b on A.a = B.b;
2、left join 和 right join (取差集)
select * from a left join b on A.a = B.b;
select * from a right join b on A.a = B.b;
3、full join (取并集)…但是mysql不支持这种操作;
(用其他的指令写…如下:)
select * from a right join b on A.a = B.b union select * from a left join b on A.a = B.b;
6.事务:
(mysql处理一些操作量大,复杂度高的数据;例如:在人员管理系统中,你删除一个人的时候,连并把它的资本资料,如个人信息、文章、信箱等都删除…若在出现问题之后;可以一并撤回…这样一堆的“语句”,被称之为事务)
(其实就很简单;begin、事务语句、commit/rollback)
(commit相当于提交;rollback是撤回;都是结束“事务”的语句)
7.索引:
索引,跟前面的内置模块里面的Hash一样,为的是提高查询速度;但是mysql用的是Btree打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引:组合索引和单列索引;
1.显示索引:(查)
show index from STUDENT
(主键就是一个“索引”…)
2.增加索引:
create index name_index on student(name(32))
create unique index name_index on student(name(32))#创建唯一索引;
#1.修改表结构的时候;
ALTER mytable ADD INDEX [indexName] ON (username(length))
#2.创建表的时候直接指定;
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
2.删除索引:
DROP INDEX [indexName] ON mytable;
索引操作 | |
---|---|
增 | create index index_name on student(name(32)) |
删 | drop index index_name on table(字段名) |
改 | |
查 | show index from student |
三、mysql python交互:
1.装载两个模块python-mysqldb,pymysql;(但是python-mysql在3.0版本上用不了;)
我们用一个,pymsql和python-mysql一模一样;
1.安装:
pip install pymysql#cmd切到你的python的scrips
2.简单操作:
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host="localhost",port= 3306,user="root",password="123456",db="test_2")
#创建游标;
cursor = conn.cursor()
#执行mysql,并返回受影响行数;
effect_now = cursor.execute("select * from student")
print(effect_now)
# print(cursor.fetchone())
# print(cursor.fetchone())
print("----------------")
print(cursor.fetchall())
3.批量插入数据:
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host="localhost",port= 3306,user="root",password="123456",db="test_2")
#创建游标;
cursor = conn.cursor()
#执行mysql,并返回受影响行数;
data =[
("man1",22,"2021-4-21","F"),
("man2",21,"2021-4-21","F"),
("man3",2200,"2021-4-21","F"),
]
effect_now = cursor.executemany("insert into student (name,age,reg_date,gender) VALUES (%s,%s,%s,%s)",data)
print(effect_now)
# print(cursor.fetchone())
# print(cursor.fetchone())
print("----------------")
#print(cursor.fetchall())
#默认开启事务;
conn.commit()
conclude:
1.pymsql.connect();
2.conn.cursor;
3.effct_now =cursor.execute(“指令”);
4.print(effct_now)…有多少条指令,输出;
5.cursor.fetchone(),输出一条指令;cursor.fetchall(),输出所有的指令;
1.cursor.executemany;
2.conn.commit()#默认是事务,需要提交;
四、Sqlalchemy ORM:
1.orm介绍: (orm是一个术语)
我自己感觉就是用面向对象把pymysql进行封装了… 可以用“class”构造表(tables);
增、删、改、查这些语句用面向对象的方式去实现…
通过orm将编程语言的对象模型和数据库的关系模型建立映射关系,这样我们在使用编程语言对数据库进行操作的时候,可以直接使用编程语言的对象模型进行操作就可以了,而不用直接使用sql语言;
2.sqlalchemy(orm的组件)
安装:(cmd切到你python根目录下的scrips)
pip install sqlalchemy
五、sqlalchemy 常用语法:
1.创建表操作:
第一种:(最常用的创建表结构)
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import Column,INTEGER,String
from sqlalchemy.ext.declarative import declarative_base
#1.建立连接;
engine = create_engine("mysql+pymysql://root:123456@localhost/test_2",encoding = "utf-8",echo =True)
#2.生成orm基类;
Base = declarative_base()
#3.创建表结构;
class School(Base):
__tablename__ = "school"#表名
id = Column(INTEGER,primary_key=True)
name = Column(String(32))
address = Column(String(64))
Base.metadata.create_all(engine)#创建表结构
echo =True
(这句话,就是在下方的输出窗口输出执行过程…你要是不想输出…就把这个去掉)
第二种:(不用自动去维护,创建表结构的方法,这种不常用…)
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import Column,INTEGER,String
from sqlalchemy import Table,MetaData,ForeignKey
from sqlalchemy.orm import mapper
metadata = MetaData()
#创建一个class表;
Cla =Table("class",metadata,
Column("Id",INTEGER,primary_key=True),
Column("name",String(64)),
Column("Level",String(32)),
)
class cla(object):
def __init__(self,name,level):
self.name = name
self.level = level
mapper(Cla,cla)
2.往表里插入数据:
(为了是python语句,整洁美观…我把创建库放在了一个py文件里;把写入数据的发在了一个文件里)
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from day_12 import school_test
from sqlalchemy.orm import sessionmaker
Session_class = sessionmaker(bind=school_test.engine) #创建与数据库的会话session_class ,注意,这里返回给session的是个class,不是实例
Session = Session_class() #生成session实例
user_obj = school_test.School(name="HanTai",address="hantaiarea") #生成你要创建的数据对象
print(user_obj.name,user_obj.address) #此时还没创建对象呢,不信你打印一下id发现还是None
Session.add(user_obj) #把要创建的数据对象添加到这个session里, 一会统一创建
print(user_obj.name,user_obj.address) #此时也依然还没创建
Session.commit() #现此才统一提交,创建数据
Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class() #生成session实例
user_obj = User(name="alex",password="alex3714") #生成你要创建的数据对象
print(user_obj.name,user_obj.id) #此时还没创建对象呢,不信你打印一下id发现还是None
Session.add(user_obj) #把要创建的数据对象添加到这个session里, 一会统一创建
print(user_obj.name,user_obj.id) #此时也依然还没创建
Session.commit() #现此才统一提交,创建数据
conclude:
1.session_class = sessionmaker(bind = engine);#生成一个类
2.session = session_class();
3.school_obj = school(name = “xxx”,address =“xxx” );
4.session.add(school_obj);#对象放对象
5.session.commit()#递交,完成创建…
3.查询数据:
1._ _ repr _ _(决定“查询”的返回值;)
class School(Base):
__tablename__ = "school"#表名
id = Column(INTEGER,primary_key=True)
name = Column(String(32))
address = Column(String(64))
def __repr__(self):
return "<%s name:%s address:%s >"%(self.id,self.name,self.address)
2.filter_by.all ( ) 、filter_by.frist ( ) ;
#1.查到 all(所有)符合的 “对象”;
data_obj = session.query(school_test.School).filter_by().all()#生成的是一个对象列表..
print(data_obj)#第一个对象里面的name;
#2.查到 first(第一个)符合的 “对象”;
data_obj2 = session.query(school_test.School).filter_by().first()#生成的是一个对象列表..
print(data_obj2)#第一个对象里面的name;
3.filter(User.id ==1);
data_obj = session.query(school_test.School).filter(school_test.School.id == 2).all()#生成的是一个对象列表..
print(data_obj)#第一个对象里面的name;
4.filter(User.id = =1).filter(User = =2)…多条件筛选;
data_obj = session.query(school_test.School).filter(school_test.School.id == 2).filter(school_test.School.name =="HanTai").all()#生成的是一个对象列表..
print(data_obj)#第一个对象里面的name;
语法 | |
---|---|
_ _repr _ _ | 确定返回值 |
filter_by( xx = xx).all() | 返回所有对象 |
filter_by(xx = xx).frist() | 返回第一个对象 |
filter(xx == xx) | 第二种返回方式 |
filter(条件一).filter(条件二) | 多条件查询 |
session.query(table).filter(条件).all()
4.修改数据
(按条件查到那个“对象”,然后用属性改…)
data_obj = session.query(school_test.School).filter(school_test.School.id == 2).filter(school_test.School.name =="HanTai").all()#生成的是一个对象列表..
print(data_obj)#第一个对象里面的name;
data_obj[0].name = "HenShui_school"
session.commit()#事务的提交
5.回滚:
session.rollback()#事务的回滚
6.统计和分组:
1.统计
data_obj = session.query(school_test.School).filter(school_test.School.name =="HanTai").count()#生成的是一个对象列表..
print(data_obj)#第一个对象里面的name;
2.分组
from sqlalchemy import func
data_obj = session.query(school_test.School.name,func.count(school_test.School.name)).group_by(school_test.School.name).all()
print(data_obj)
7.连表:
session.query(table1,table2).filter(table1.id == table2.id).all()
六、Sqlalchemy 外键关联:
1.一对一外键关联:
(只建立左图和右图的外键关联…)
1.先创建二个student表和study_record表:
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import sqlalchemy
from sqlalchemy import Column,INTEGER,String,create_engine,DATE,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine("mysql+pymysql://root:123456@localhost/test_2",encoding = "utf-8")
Base = declarative_base()
#1.创建“学生”表;
class Student(Base):
__tablename__ = "student"
id = Column(INTEGER,primary_key=True)
name = Column(String(32),nullable=False)
reg_data = Column(DATE,nullable= False)
gender = Column(String(32),nullable= False)
def __repr__(self):
return "<%s name:%s >"%(self.id,self.name)
#2.创建“考勤”表;
class StudyRecord(Base):
__tablename__ = "study_record"
id =Column(INTEGER,primary_key=True)
day =Column(INTEGER)
status =Column(String(32))
#外键关联:
stu_id = Column(INTEGER,ForeignKey("student.id"))
def __repr__(self):
return "<%s day:%s address:%s >"%(self.id,self.day,self.status)
Base.metadata.create_all(engine)#创建表结构
2.在表里面插入数据:
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from day_12 import foreign_key_test
import sqlalchemy_table1
import sqlalchemy
from sqlalchemy.orm import sessionmaker
Session_class = sessionmaker(bind=sqlalchemy_table1.engine)
Session = Session_class()
#1.往student里面扔数据;
s1 = sqlalchemy_table1.Student(name="Alex",reg_data="2021-4-26",gender = "M" )
s2 = sqlalchemy_table1.Student(name="Jack",reg_data="2020-4-26",gender = "M" )
s3 = sqlalchemy_table1.Student(name="Rain",reg_data="2019-4-26",gender = "M" )
s4 = sqlalchemy_table1.Student(name="Eric",reg_data="2018-4-26",gender = "F" )
#2.往study_record里面扔数据;
sty1 = sqlalchemy_table1.StudyRecord(day = 1,status = "Yes",stu_id = 1)#第一个学员Alex的状态;
sty2 = sqlalchemy_table1.StudyRecord(day = 2,status = "No",stu_id = 1)#第一个学员Alex的状态;
sty3 = sqlalchemy_table1.StudyRecord(day = 3,status = "Yes",stu_id = 1)#第一个学员Alex的状态;
sty4 = sqlalchemy_table1.StudyRecord(day = 1,status = "Yes",stu_id = 2)#第二个学员jack的状态;
Session.add_all([s1,s2,s3,s4,sty1,sty2,sty3,sty4])#调加所有;
Session.commit()
3.两个表之间相互关联的查询:
(1)在外键关联下面,加上relationship关系,方便后面调用…
from sqlalchemy.orm import relationship
#外键关联:
stu_id = Column(INTEGER,ForeignKey("student.id"))
#加一个关系对应:
student = reationship("Student",backref = "my_study_record")
(2)session查询的时候加上上面的点调用…“ .my_study_record ”
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from day_12 import foreign_key_test
import sqlalchemy_table1
from sqlalchemy.orm import relationship
import sqlalchemy
from sqlalchemy.orm import sessionmaker
Session_class = sessionmaker(bind=sqlalchemy_table1.engine)
Session = Session_class()
stu_obj = Session.query(sqlalchemy_table1.Student).filter(sqlalchemy_table1.Student.name =="Alex").first()
print(stu_obj.my_study_record)
Session.commit()
2.多对一外键关联:
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import Integer, ForeignKey, String, Column,create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
engine = create_engine("mysql+pymysql://root:123456@localhost/test",encoding = "utf-8")
Base = declarative_base()
class Customer(Base):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True)
name = Column(String)
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__ = 'address'
id = Column(Integer, primary_key=True)
street = Column(String)
city = Column(String)
state = Column(String)
Base.metadata.create_all(engine)#创建表结构
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from day_12 import foreign_key_test
import book_table
from sqlalchemy.orm import relationship
import sqlalchemy
from sqlalchemy.orm import sessionmaker
Session_class = sessionmaker(bind=book_table.engine)
Session = Session_class()
add1 = book_table.Address(street = "tianhan",city = "Hanzhong",state = "shanxi")
add2 = book_table.Address(street = "weiyang",city = "xian",state = "shanxi")
add3 = book_table.Address(street = "daxue",city = "taiyuan",state = "shannxi")
Session.add_all([add1,add2,add3])
c1 = book_table.Customer(name = "Alex", billing_address = add1,shipping_address = add2)
c2 = book_table.Customer(name = "jack", billing_address = add3,shipping_address = add3)
Session.add_all([c1,c2])
Session.commit()
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from day_12 import foreign_key_test
import book_table
from sqlalchemy.orm import relationship
import sqlalchemy
from sqlalchemy.orm import sessionmaker
Session_class = sessionmaker(bind=book_table.engine)
Session = Session_class()
stu_obj = Session.query(book_table.Customer).filter(book_table.Customer.name=="Jack").first()
print(stu_obj.name,stu_obj.billing_address,stu_obj.shipping_address)
Session.commit()
Tip:(可以这样子add数据)
add1 = book_table.Address(street = "tianhan",city = "Hanzhong",state = "shanxi")
add2 = book_table.Address(street = "weiyang",city = "xian",state = "shanxi")
add3 = book_table.Address(street = "daxue",city = "taiyuan",state = "shannxi")
Session.add_all([add1,add2,add3])
c1 = book_table.Customer(name = "Alex", billing_address = add1,shipping_address = add2)
c2 = book_table.Customer(name = "jack", billing_address = add3,shipping_address = add3)
Session.add_all([c1,c2])
(realtionship)
billing_address = relationship("Address",foreign_keys=[billing_address_id])
shipping_address = relationship("Address",foreign_keys=[shipping_address_id])
3.多对多外键关联:
from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine("mysql+pymysql://root:123456@localhost/test",encoding = "utf-8")
Base = declarative_base()
#第三个表存储外键关系;
book_m2m_author = Table('book_m2m_author', Base.metadata,
Column('book_id',Integer,ForeignKey('books.id')),#书
Column('author_id',Integer,ForeignKey('authors.id')),#作者
)
class Book(Base):
__tablename__ = 'books'
id = Column(Integer,primary_key=True)
name = Column(String(64))
pub_date = Column(DATE)
#关联关系改变;
authors = relationship('Author',secondary=book_m2m_author,backref='books')
def __repr__(self):
return self.name
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
name = Column(String(32))
def __repr__(self):
return self.name
Base.metadata.create_all(engine)
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from day_12 import foreign_key_test
import book_table
from sqlalchemy.orm import relationship
import sqlalchemy
from sqlalchemy.orm import sessionmaker
Session_class = sessionmaker(bind=book_table.engine)
Session = Session_class()
b1 = book_table.Book(name = "learn zhangbi by alex",pub_date = "2021-4-26")
b2 = book_table.Book(name = "learn python by alex",pub_date = "2022-4-26")
b3 = book_table.Book(name = "learn hook up girls by alex",pub_date = "2023-4-26")
a1 = book_table.Author(name = "Alex")
a2 = book_table.Author(name = "JACK")
a3 = book_table.Author(name = "rain")
#authors = relationship('Author',secondary=book_m2m_author,backref='books')
b1.authors = [a1,a3]
b3.authors = [a1,a2,a3]
Session.add_all([b1,b2,b3,a1,a2,a3])
Session.commit()
#Author:Jony c
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from day_12 import foreign_key_test
import book_table
from sqlalchemy.orm import relationship
import sqlalchemy
from sqlalchemy.orm import sessionmaker
Session_class = sessionmaker(bind=book_table.engine)
Session = Session_class()
stu_obj = Session.query(book_table.Author).filter(book_table.Author.name=="Alex").first()
print(stu_obj.name,stu_obj.books)
Session.commit()
Tip:(可以这样子add数据)
#authors = relationship('Author',secondary=book_m2m_author,backref='books')
b1.authors = [a1,a3]
b3.authors = [a1,a2,a3]
(realtionship)
authors = relationship('Author',secondary=book_m2m_author,backref='books')
4.写带中文的:
sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8,否则数据库的连接就不是utf8的编码格式
eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)