前言
上一个博客中介绍了sqlalchemy以及简单的创建表和对数据的增删改查.在这篇文章中,进一步学习sqlalchemy.
参考博客:python 之路，Day11 - sqlalchemy ORM
1.主键和外键
很惭愧,自己连数据库中的主键和外键都不知道是什么意思,今天花了点时间来了解主键和外键.
先来看主键和外键的定义:
- 主键:主键是唯一的。一个数据表中只能包含一个主键。
- 外键:外键用于关联两个表。
主键我们好理解,就是能唯一标识记录的键.而外键我就不太好理解了(很惭愧,因为自己本科时期是数学专业的,没有学过计算机的相关知识,所以现在才会连这些基础都不懂,只能现学现卖了),下面举个例子来说明主键和外键:
我们现在有两张表:学生表和院系表:
学生表(学号,姓名,所在院系号),则学号是学生表的主键;
院系表(院系号,院系名字),则院系号是院系表的主键,而院系号就称为学生表的外键.
定义主键和外键主要是为了维护关系数据库的完整性,总结一下就是:
(1)主键是能确定一条记录的唯一标识,比如,一条记录包括身份证号,姓名,年龄,身份证好是唯一能确定你这个人的,而其他的都有可能会重复,所以,身份证好是主键;
(2)外键用于与另一张表进行关联.是能确定另一张表记录的字段,用于保持数据的一致性;比如,A表中有一个字段,是B表的主键,那他就是A表的外键.
2.sqlalchemy 外键关联
我们知道了什么是主键,什么是外键,并且知道了主键和外键的作用,那么sqlalchemy是如何来实现外键关联的呢?我们来看下面的代码:
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DATE,Enum,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
#连接数据库
engine = create_engine("mysql+pymysql://root:123456@localhost/student",
encoding='utf-8',
echo=False)
# 创建基类
Base = declarative_base()
# 创建一个Student表
class Student(Base):
__tablename__='student'
id = Column(Integer,primary_key=True)
name = Column(String(32),nullable=False) # nullable=False是不为空，就是原生sql中的NOT NULL
register_Date = Column(DATE,nullable=False) # Date类型
def __repr__(self):
return "(id:{},name:{},register_Date:{})".\
format(self.id,self.name,self.register_Date)
class StudyRecord(Base):
__tablename__="study_record"
id = Column(Integer,primary_key=True)
day = Column(Integer,nullable=False)
status = Column(String(32),nullable=False)
stu_id = Column(Integer,ForeignKey("student.id")) # 外键,是student中的主键id
student = relationship("Student",backref="my_study_record")
def __repr__(self):
return "< day:%s status:%s>" % (self.id, self.status)
Base.metadata.create_all(engine) # 创建表结构,只要是继承Base类的子类,都会执行
Session_class = sessionmaker(bind=engine)
Session = Session_class()
# s1 = Student(name="Tom",register_Date="2018-08-08")
# s2 = Student(name="Jerry",register_Date="2018-06-08")
# s3 = Student(name="Lucy",register_Date="2018-08-28")
# s4 = Student(name="Lily",register_Date="2018-08-18")
#
# study_obj1 = StudyRecord(day=1,status="Yes",stu_id=1)
# study_obj2 = StudyRecord(day=2,status="No",stu_id=1)
# study_obj3 = StudyRecord(day=3,status="Yes",stu_id=1)
# study_obj4 = StudyRecord(day=1,status="Yes",stu_id=2)
#
# Session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4])
# Session.commit()
stu_obj = Session.query(Student).filter(Student.name=="Tom").first()
print(stu_obj.my_study_record)
我们先来分析一下程序:首先创建两个表student,study_record,然后向里面插入数据(注释掉的部分就是插入数据的部分),两个表的信息如下:
student表:
mysql> desc student;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| register_Date | date | NO | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from student;
+----+-------+---------------+
| id | name | register_Date |
+----+-------+---------------+
| 1 | Tom | 2018-08-08 |
| 2 | Jerry | 2018-06-08 |
| 3 | Lucy | 2018-08-28 |
| 4 | Lily | 2018-08-18 |
+----+-------+---------------+
4 rows in set (0.00 sec)
study_record表:
mysql> desc study_record;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| day | int(11) | NO | | NULL | |
| status | varchar(32) | NO | | NULL | |
| stu_id | int(11) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from study_record;
+----+-----+--------+--------+
| id | day | status | stu_id |
+----+-----+--------+--------+
| 1 | 1 | Yes | 1 |
| 2 | 2 | No | 1 |
| 3 | 3 | Yes | 1 |
| 4 | 1 | Yes | 2 |
+----+-----+--------+--------+
4 rows in set (0.00 sec)
程序的目的就是利用外键来实现关联查询,即通过student中的字段来查询study_record中的数据.
stu_obj = Session.query(Student).filter(Student.name=="Tom").first()
print(stu_obj.my_study_record)
执行结果:
[< day:1 status:Yes>, < day:2 status:No>, < day:3 status:Yes>]
可以看到,通过查询student中的name=Tom的信息,来查询study_record的Tom的信息,主要是通过id这个键来实现的,id在student中是主键,在study_record中是stu_id,是外键.核心代码为:
student = relationship("Student",backref="my_study_record")
这句话的意思是:可以通过Student在表study_record中直接引用表Student,那么可以通过my_study_record直接引用Study_record中的数据,这是因为他们两者存在外键关联,所以当程序执行时,就会根据外键去查询study_record中的数据.
Relationship:允许Student表通过backref字段反向查出所有它在study_record表里的关键项,外键关联也是通过这个来实现的.
3.sqlalchemy 多外键关联
上一节中我们写到了外键关联,只有一个外键关联,那如果有多个外键与一个表进行关联呢?来看下面代码:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy import create_engine
#连接数据库
engine = create_engine("mysql+pymysql://root:123456@localhost/student",
encoding='utf-8',
echo=False)
# 创建基类
Base = declarative_base()
class Customer(Base):
__tablename__ = 'customer'
id = Column(Integer,primary_key=True)
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])
def __repr__(self):
return self.name
class Address(Base):
__tablename__ = 'address'
id = Column(Integer,primary_key=True)
street = Column(String(64))
city = Column(String(64))
state = Column(String(64))
def __repr__(self):
return self.street
Base.metadata.create_all(engine) # 创建表结构,只要是继承Base类的子类,都会执行
Session_class = sessionmaker(bind=engine)
Session = Session_class()
# addr1 = Address(street = 'street1',city = 'city1',state = 'state1')
# addr2 = Address(street = 'street2',city = 'city2',state = 'state2')
# addr3 = Address(street = 'street3',city = 'city3',state = 'state3')
# Session.add_all([addr1,addr2,addr3])
# c1 = Customer(name = 'Tim',billing_address = addr1,shipping_address = addr2)
# c2 = Customer(name = 'kite',billing_address = addr3,shipping_address = addr3)
# Session.add_all([c1,c2])
# Session.commit()
obj = Session.query(Customer).filter(Customer.name == 'Tim').first()
print(obj.name,obj.billing_address,obj.shipping_address)
代码具体流程就不解释了,和上面那个流程是一样的,主要解释以下两句:
billing_address = relationship('Address',foreign_keys = [billing_address_id])
shipping_address = relationship('Address',foreign_keys = [shipping_address_id])
主要作用:用来区分哪个外键对应哪个字段.
创建的数据表:
address:
mysql> select * from address;
+----+---------+-------+--------+
| id | street | city | state |
+----+---------+-------+--------+
| 1 | street1 | city1 | state1 |
| 2 | street2 | city2 | state2 |
| 3 | street3 | city3 | state3 |
+----+---------+-------+--------+
3 rows in set (0.00 sec)
customer:
mysql> select * from customer;
+----+------+--------------------+---------------------+
| id | name | billing_address_id | shipping_address_id |
+----+------+--------------------+---------------------+
| 1 | Tim | 1 | 2 |
| 2 | kite | 3 | 3 |
+----+------+--------------------+---------------------+
2 rows in set (0.00 sec)
程序执行结果:
Tim street1 street2
4.sqlalchemy 多对多关联
参考:python 之路，Day11 - sqlalchemy ORM;Python sqlalchemy orm 多对多外键关联
在关系型数据库中,多对多的关系普遍存在,比如"图书"和"作者"的关系:
- 一本图书可以由多个作者一起出版;
- 一个作者也可以出版多本图书.
在进行多对多关联的时候,我们需要有一个"额外的中间表"来连接两个关联的表:
书名表:(主键:图书id)
| id | 书名 |
|---|---|
| 1 | python |
| 2 | linux |
| 3 | 机器学习 |
作者表:(主键:作者id)
| id | 作者 |
|---|---|
| 1 | Bob |
| 2 | Jack |
中间表:(主键:作者id,书名id)
| 作者id | 图书id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
使用一个中间表就可以将图书表和作者表关联在一起了,那么如何使用sqlalchemy来实现这种多对多的关联查询呢?来看下面的例子:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey,Table,DATE
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy import create_engine
#连接数据库
engine = create_engine("mysql+pymysql://root:123456@localhost/student",
encoding='utf-8',
echo=False)
# 创建基类
Base = declarative_base()
# 先做一张中间表
# 通过这种方式,不需要对此表对用orm映射操作
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 "name:{},id:{}".format(self.name,self.id)
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)
Session_class = sessionmaker(bind=engine)
Session = Session_class()
# # 创建一些数据
# b1 = Book(name='python')
# b2 = Book(name='C++')
# b3 = Book(name='Java')
# b4 = Book(name='C')
#
# a1 = Author(name = 'Bob')
# a2 = Author(name = 'Jack')
# a3 = Author(name = 'James')
#
# b1.authors = [a1,a2]
# b2.authors = [a1,a2,a3]
#
# Session.add_all([b1,b2,b3,b4,a1,a2,a3])
# Session.commit()
print('多对多查询>>>>>:')
print('------------------通过书表查关联的作者----------------------')
book_obj = Session.query(Book).filter_by(name='python').first()
print(book_obj.name,book_obj.authors)
print('------------------通过书表查关联的作者----------------------')
author_obj = Session.query(Author).filter_by(name='Bob').first()
print(author_obj.name,author_obj.books)
print('多对多删除>>>>>:')
print('通过书来删除作者:')
# author_obj_1 = Session.query(Author).filter_by(name='Bob').first()
# book_obj_1 = Session.query(Book).filter_by(name='python').first()
#
# book_obj_1.authors.remove(author_obj_1) # 从一本书里删除一个作者
# Session.commit()
# print('直接删除作者:')
# author_obj_2 = Session.query(Author).filter_by(name='Bob').first()
# Session.delete(author_obj_2)
Session.commit()
程序流程:
- 先创建Book表和Author表,然后创建一个中间表book_m2m_author,然后创建一些数据:
mysql> select * from authors;
+----+-------+
| id | name |
+----+-------+
| 1 | Bob |
| 2 | James |
| 3 | Jack |
+----+-------+
3 rows in set (0.00 sec)
mysql> select * from books;
+----+--------+----------+
| id | name | pub_date |
+----+--------+----------+
| 1 | python | NULL |
| 2 | C++ | NULL |
| 3 | Java | NULL |
| 4 | C | NULL |
+----+--------+----------+
4 rows in set (0.00 sec)
mysql> select * from book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
| 2 | 2 |
| 1 | 3 |
| 2 | 3 |
| 1 | 1 |
| 2 | 1 |
+---------+-----------+
5 rows in set (0.00 sec)
- 然后利用多对多关联进行查询:
print('------------------通过书表查关联的作者----------------------')
book_obj = Session.query(Book).filter_by(name='python').first()
print(book_obj.name,book_obj.authors)
print('------------------通过书表查关联的作者----------------------')
author_obj = Session.query(Author).filter_by(name='Bob').first()
print(author_obj.name,author_obj.books)
执行结果:
------------------通过书表查关联的作者----------------------
python [Jack]
------------------通过书表查关联的作者----------------------
Bob [name:C++,id:2]
- 最后是多对多的删除:
author_obj_1 = Session.query(Author).filter_by(name='Bob').first()
book_obj_1 = Session.query(Book).filter_by(name='python').first()
book_obj_1.authors.remove(author_obj_1) # 从一本书里删除一个作者
Session.commit()
print('直接删除作者:')
author_obj_2 = Session.query(Author).filter_by(name='Bob').first()
Session.delete(author_obj_2)
删除后的结果:
mysql> select * from book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
| 2 | 2 |
| 1 | 3 |
| 2 | 3 |
| 2 | 1 |
+---------+-----------+
4 rows in set (0.00 sec)
mysql> select * from book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
| 2 | 2 |
| 1 | 3 |
| 2 | 3 |
+---------+-----------+
3 rows in set (0.00 sec)
mysql> select * from books;
+----+--------+----------+
| id | name | pub_date |
+----+--------+----------+
| 1 | python | NULL |
| 2 | C++ | NULL |
| 3 | Java | NULL |
| 4 | C | NULL |
+----+--------+----------+
4 rows in set (0.00 sec)
mysql> select * from authors;
+----+-------+
| id | name |
+----+-------+
| 2 | James |
| 3 | Jack |
+----+-------+
2 rows in set (0.00 sec)
5.sqlalchemy 处理中文
sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式:
engine = create_engine("mysql+pymysql://root:123456@localhost/student?charset=utf8",
encoding='utf-8',
echo=False)
写在最后
本文是个人的一些学习笔记,如有侵权,请及时联系我进行删除,谢谢大家.
扫一扫
392
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
