14.python与数据库之mysql：pymysql、sqlalchemy

时间：2022-03-15 06:45

直接操作mysql:

前提：

首先需要安装python与mysql交互的库【PyMySQL 是在 Python3 版本中用于连接 MySQL 服务器的一个库】：
- 安装模块:pymysql：
  数据库中映射模块【如果可以从多个模块处导入，用 | 分隔】【方式太多，可能有漏，但不影响导入】表 Table from sqlalchemy import Table int Integer from sqlalchemy.types import Integer 索引 Index from sqlalchemy import Index 字段、列 Column from sqlalchemy import Column varchar VARCHAR、String from sqlalchemy.types import String | from sqlalchemy import String 外键 ForeignKey
```
from sqlalchemy import ForeignKey
```
连接数据库：连接对象=create_engine(‘数据库类型+数据库驱动名称://用户名::端口号/数据库名‘，编码格式，echo)
- sqlalchemy支持多种API操作模式,可以使用不同的模式来连接操作数据库：‘数据库类型+数据库驱动名称://用户名::端口号/数据库名‘
  - 比如pymsql【py3常用】:
  - 5.创建表：metadata.create_all()


 


查看表： 

db_table=Base.metadata.tables#仅有当次运行中继承了Base而创建的新表 
db_tables=engine.table_names()#仅有表名
删除表：Base.metadata.drop_all(engine) 
修改表： 

直接修改表所对应的类结构是无法修改成功的， 
如果需要修改在程序中自定义的表的结构，那么需要手动修改，手动的方式有很多，比如直接engine.execute(sql语句)。。。。

插入数据【这里仅针对使用declarative_base创建的表，对于不是程序中才创建的，可以自己使用declarative_base建一个类来映射之前的表，只要映射一致，就能插入数据】
- 5.提交数据: s.commit()
  使用filter,filter_by时： User.name==’lisi’ User.name.like(“lisi%”)) User.name != ’lisi’ User.name.any() or_(筛选条件) 【代表里面的多个筛选条件以or组合,需要导入：from sqlalchemy import or_】 and_(筛选条件) 【代表里面的多个筛选条件以and组合，需要导入：from sqlalchemy import and_】【默认是and】 in_([筛选条件]) 【使用比如User.name.in_([‘xiaxia‘, ‘lilei‘, ‘lover‘])】 User.name [这相当于不使用where的select name from 表]
- 连接查询使用:s.query(表对应类).join(表对应类.xxx==xxxx)
- 还有group_by,order_by等用法这里不做讲解[什么时候有空再补吧！]
```
from sqlalchemy import create_engine#负责导入连接数据库的对象
from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
from sqlalchemy import Column #负责导入列
from sqlalchemy.types import *#负责导入列类型

#数据库连接
engine = create_engine("mysql+pymysql://root:123456@localhost/python_test",encoding =‘utf-8‘)
Base = declarative_base()
class User(Base):
    __tablename__ = ‘user‘#表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))
    group = Column(Integer)

    def __repr__(self):
        return "<id:%s name:%s group:%s>"%(self.id,self.name,self.group)
Base.metadata.create_all(engine)



from sqlalchemy.orm import sessionmaker
obj1=User(name=‘lisi‘,password=‘123456‘,group=1)

Session=sessionmaker(bind=engine)
s=Session()

a=s.query(User).all()
a2=s.query(User).filter(User.name==‘lisi‘).first()
a3=s.query(User).filter_by(name=‘lisi‘).first()



print(a)
print(a2)
print(a3)
```

修改数据：

修改数据的基础是先查找到数据，查找：row=s.query(X).filter(X.xxx=xxx).first()

使用赋值语句修改 :row.xxx=xxxx

from sqlalchemy import create_engine#负责导入连接数据库的对象
from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
from sqlalchemy import Column #负责导入列
from sqlalchemy.types import *#负责导入列类型

#数据库连接
engine = create_engine("mysql+pymysql://root:123456@localhost/python_test",encoding =‘utf-8‘)
Base = declarative_base()
class User(Base):
    __tablename__ = ‘user‘#表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))
    group = Column(Integer)

    def __repr__(self):
        return "<id:%s name:%s group:%s>"%(self.id,self.name,self.group)
Base.metadata.create_all(engine)



from sqlalchemy.orm import sessionmaker
obj1=User(name=‘lisi‘,password=‘123456‘,group=1)

Session=sessionmaker(bind=engine)
s=Session()


row=s.query(User).filter(User.name==‘lisi‘).first()
row.name=‘lisi2‘
s.commit()

删除数据：

删除数据的基础是先查找到数据，查找：row=s.query(X).filter(X.xxx=xxx)

使用delete删除:row.delete()

# coding: utf-8
from sqlalchemy import create_engine#负责导入连接数据库的对象
from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
from sqlalchemy import Column #负责导入列
from sqlalchemy.types import *#负责导入列类型

#数据库连接
engine = create_engine("mysql+pymysql://root:123456@localhost/python_test",encoding =‘utf-8‘)
Base = declarative_base()
class User(Base):
    __tablename__ = ‘user‘#表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))
    group = Column(Integer)
    def __repr__(self):
        return "<id:%s name:%s group:%s>"%(self.id,self.name,self.group)

Base.metadata.create_all(engine)


from sqlalchemy.orm import sessionmaker
obj1=User(name=‘lisi‘,password=‘123456‘,group=1)

Session=sessionmaker(bind=engine)
s=Session()


a3=s.query(User).filter_by(name=‘lisi1‘)
a3.delete()
s.commit()

外键相关：

外键使用foregin_key创建
类中的relationship的作用：帮助ORM获知他们的外键关系，以便ORM使用外键获取相关数据
- relationship中的backref的用途:relationship使得可以在一个表中定义的relationshop能被两个表使用，另一个表使用backref来获取相关信息
- relationship中的foreign_keys的用途：当有多个relationship时，为了避免ORM混淆多个relationship，特别的标注哪个外键是哪个relationship
- relationship中的secondary的用途:在多对多的关系中，填入的值是中间表，维持两边表关系。
一对一的外键关系：
- 1.导入模块：from sqlalchemy import Foreign_key
- 2.建立外键（如：group = Column(Integer,ForeignKey("group.id")），建立关系（如：group_relation=relationship(‘Group‘,backref="g_users"）
- 3.插入数据
- 4.查询到一条数据：如row=s.query(User).filter(User.name==‘lisi‘).first()
- 5.尝试A表调用关系来获取B（row.group_relation.group_name），B使用backref来获取A的数据（row2.g_users）
- 下面的实例大概就是“一个开发人员对应一个开发组的关系”

#负责导入连接数据库的对象
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
from sqlalchemy import Column,ForeignKey #负责导入列
from sqlalchemy.types import *#负责导入列类型
from sqlalchemy.orm import relationship

#数据库连接
engine = create_engine("mysql+pymysql://root:123456@localhost/python_test",encoding =‘utf-8‘)
Base = declarative_base()



class Group(Base):
    __tablename__="group"
    id=Column(Integer,primary_key=True)
    group_name=Column(String(32),nullable=False)

    def __repr__(self):
        return "<id:%s group_name:%s>"%(self.id,self.group_name)

class User(Base):
    __tablename__ = ‘user‘#表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    password = Column(String(64),nullable=False)
    group = Column(Integer,ForeignKey("group.id"))#这里创建外键

    group_relation=relationship(‘Group‘,backref="g_users")#为ORM指明关系，方便ORM处理,第一个是对应的类
    def __repr__(self):
        return "<id:%s name:%s>"%(self.id,self.name)
Base.metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
# group1=Group(group_name=‘python‘)
# group2=Group(group_name=‘linux‘)
# group3=Group(group_name=‘AI‘)
# user1=User(name=‘lisi‘,password=‘123456‘,group=1)
# user2=User(name=‘zhangsan‘,password=‘123456‘,group=2)
# user3=User(name=‘wangwu‘,password=‘123456‘,group=3)
# user4=User(name=‘lilei‘,password=‘123456‘,group=3)


Session=sessionmaker(bind=engine)
s=Session()
# s.add_all([group1,group2,group3,user1,user2,user3,user4])
# s.commit()

# row=s.query(User).filter(User.name==‘lisi‘).first()
row=s.query(User).first()
print(row.group_relation.group_name)#这里User通过关系来获取Group的数据
row2=s.query(Group).first()
print(row2)
print(row2.g_users)#这里Group通过relationship的backref来获取User的数据

一对多关系，外键关联

以一个老师能做一个班的班主任此外还能做另一个班的副班主任为例【即一个老师能对应多个班级】
一对多关系的创建的核心是relationship中的foreign_keys

附：当你建表成功而插入数据失败时，可以尝试先删除掉数据表，有时候因为外键依赖会导致插入失败 
#负责导入连接数据库的对象
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
from sqlalchemy import Column,ForeignKey #负责导入列
from sqlalchemy.types import *#负责导入列类型
from sqlalchemy.orm import relationship

#数据库连接
engine = create_engine("mysql+pymysql://root:123456@localhost/python_test",encoding =‘utf-8‘)
Base = declarative_base()



class Grade(Base):
    __tablename__="grade"
    id=Column(Integer,primary_key=True)
    grade_name=Column(String(32),nullable=False)

    def __repr__(self):
        return "<id:%s group_name:%s>"%(self.id,self.grade_name)

class Teacher(Base):
    __tablename__ = ‘teacher‘#表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    primary_grade = Column(Integer,ForeignKey("grade.id"))
    second_grade = Column(Integer,ForeignKey("grade.id"))

    primary_grade_relation=relationship(‘Grade‘,backref="first_teacher",foreign_keys=[primary_grade])
    second_grade_relation=relationship(‘Grade‘,backref="second_teacher",foreign_keys=[second_grade])
    def __repr__(self):
        return "<id:%s name:%s>"%(self.id,self.name)


Base.metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
# grade1=Grade(grade_name=‘python‘)
# grade2=Grade(grade_name=‘linux‘)
# grade3=Grade(grade_name=‘AI‘)
# grade4=Grade(grade_name=‘Java‘)
# t1=Teacher(name=‘lisi‘,primary_grade=1,second_grade=2)
# t2=Teacher(name=‘zhangsan‘,primary_grade=2,second_grade=1)
# t3=Teacher(name=‘wangwu‘,primary_grade=4,second_grade=3)
# t4=Teacher(name=‘lilei‘,primary_grade_relation=grade3,second_grade=4)
#这里外键相关的比如primary_grade=x可以使用primary_grade_relation=对象来代替，
# 会根据对象来转成对应id，不过问题是不知道grade3的准确id，因为可能创建顺序不一致

Session=sessionmaker(bind=engine)
s=Session()
# s.add_all([grade1,grade2,grade3,grade4])
# s.add_all([t1,t2,t3,t4])
# s.commit()

row=s.query(Teacher).filter(Teacher.name==‘lisi‘).first()

print(row.name,row.primary_grade_relation.grade_name)#这里Teacher通过关系来获取Grade的数据
print(row.name,row.second_grade_relation.grade_name)
row2=s.query(Grade).first()
print(row2.grade_name,row2.first_teacher)#这里Grade通过relationship的backref来获取Teacher的数据
print(row2.grade_name,row2.second_teacher)

多对多外键关联

以选课中一门课能有多名学生，一个学生可以选多门课为示例：
其中relationship中的secondary的值是中间表，负责维持中间表与另外两表的关系，创建多对多的核心是secondary

#负责导入连接数据库的对象
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
from sqlalchemy import Column,ForeignKey #负责导入列
from sqlalchemy.types import *#负责导入列类型
from sqlalchemy.orm import relationship

#数据库连接
engine = create_engine("mysql+pymysql://root:123456@localhost/python_test",encoding =‘utf-8‘)
Base = declarative_base()

class SelectInfo(Base):
    __tablename__="selectClassInfo"
    id=Column(Integer,primary_key=True)
    sid=Column(Integer,ForeignKey("student.id"))
    cid=Column(Integer,ForeignKey("course.id"))
    
    
"""使用declarative_base和Table 创建表时，secondary的填写不一样
selectInfo2=Table(
‘selectClassInfo‘,Base.metadata,
Column(‘sid‘,Integer,ForeignKey(‘student.id‘))
Column(‘cid‘,Integer,ForeignKey(‘student.id‘))
)
"""

class Student(Base):
    __tablename__="student"
    id=Column(Integer,primary_key=True)
    name=Column(String(32),nullable=False)

    def __repr__(self):
        return "<id:%s name:%s>"%(self.id,self.name)

class Course(Base):
    __tablename__ = ‘course‘
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)

    student_relation=relationship(‘Student‘,secondary="selectClassInfo",backref="courses")
    # student_relation=relationship(‘Student‘,secondary=selectClassInfo2,backref="courses") 
    # #如果使用Table来创建中间表，上面是这样填的

    def __repr__(self):
        return "<id:%s name:%s>"%(self.id,self.name)


Base.metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
#
# s1=Student(name=‘lisi‘)
# s2=Student(name=‘zhangsan‘)
# s3=Student(name=‘wangwu‘)
# s4=Student(name=‘lilei‘)
# c1=Course(name=‘python‘,student_relation=[s1,s2])
# c2=Course(name=‘linux‘,student_relation=[s3])
# c3=Course(name=‘AI‘,student_relation=[s3,s4])
# c4=Course(name=‘Java‘)
# c4.student_relation=[s1,s2,s3,s4]##在一边增加关系之后，在secondary中会加入两边的数据
#
#
#
Session=sessionmaker(bind=engine)
s=Session()
# s.add_all([s1,s2,s3,s4,c1,c2,c3,c4])
# s.commit()

row=s.query(Course).filter(Course.id==‘4‘).first()

print(row.name,row.student_relation)#这里Course通过关系来获取Student的数据
row2=s.query(Student).filter(Student.id=="3").first()
print(row2.name,row2.courses)#这里Student通过relationship的backref来获取Course的数据