这篇文章主要介绍了python数据库操作mysql：pymysql、sqlalchemy常见用法,结合实例形式分析了Python mysql操作库pymysql、sqlalchemy基本使用技巧与操作注意事项,需要的朋友可以参考下

本文实例讲述了python数据库操作mysql：pymysql、sqlalchemy常见用法。分享给大家供大家参考，具体如下：

直接操作mysql--pymysql:

直接操作mysql意思是利用python实现类似命令行模式下的mysql交互。

前提：

首先需要安装python与mysql交互的库【PyMySQL 是在 Python3 版本中用于连接 MySQL 服务器的一个库】：
- 安装模块:pymysql：
```
 pip3 install pymysql
```
  或者在Pycharm中安装

使用：

首先导入模块：import pymysql
连接数据库：数据库连接对象 = pymysql.connect("host="localhost",port=3306,user='root',passwd='123456',db='python_test') 【如果需要支持中文，则加上charset=”utf8”】
创建游标【游标用于执行sql语句和管理查询到的结果】：游标对象 = 数据库连接对象.cursor()
执行sql语句：游标对象.execute(SQL语句) ，返回值是受影响行数【execute可以执行所有的sql语句，不论是表相关的，还是数据相关的。】
- 由于默认开始事务，如果涉及修改、插入，则需要提交：连接对象.commit() ；以及撤销修改、插入的回滚:连接对象.rollback()
- executemany是同时执行多条sql语句【以多组参数的格式,executemany(self,query,args)】：
获取结果：
- 获取一条结果：data = 游标对象.fetchone()
- 获取全部结果：data=游标对象.fetchall()
- 获取指定数量结果:data=游标对象.fetmany(x)
- 获取结果后，就会将对应的结果删掉，比如fetchone是获取一条，那么这一条就会从原来的结果中删除
- 游标对象.rowcount()可以获得执行sql语句后受影响的行数
关闭游标：游标对象.close()
关闭数据库连接：数据库连接对象.close()

示例：

1.创建连接：

 import pymysql #创建连接 conn=pymysql.connect(host="localhost",port=3306,user="root",passwd="123456",db="python_test") #创建游标 cursor=conn.cursor() #..............操作过程 #关闭游标 cursor.close() #关闭连接 conn.close()

2.执行创建表：

 import pymysql conn=pymysql.connect(host="localhost",port=3306,user="root",password="123456",db="it",charset="utf8") cursor=conn.cursor() sql=""" create table user( id int PRIMARY KEY auto_increment, username VARCHAR(20), password VARCHAR(20), address VARCHAR(35) ) """ cursor.execute(sql) conn.commit() cursor.close() conn.close()

3.执行查询：

 import pymysql #创建连接 conn=pymysql.connect(host="localhost",port=3306,user="root",passwd="123456",db="python_test",charset="utf8") #创建游标 cursor=conn.cursor() cursor.execute("select * from student;") print(cursor.fetchone())#获取一条 print(cursor.fetchmany(2))#获取两条 print(cursor.fetchall())#获取结果集合中的全部 #关闭游标 cursor.close() #关闭连接 conn.close()

4.执行插入、修改、删除：

 import pymysql #创建连接 conn=pymysql.connect(host="localhost",port=3306,user="root",password="123456",db="python_test",charset="utf8") #创建游标 cursor=conn.cursor() print("-----------插入----------------") cursor.execute("insert into student values ('nazha',2000,'男');") cursor.execute("select * from student;") print(cursor.fetchall()) print("-----------插入----------------") #cursor.executemany(self,query,args) cursor.executemany("insert into student value(%s,%s,%s);",[('zhangsan',18,'男'),('lisi',18,'男')]) cursor.execute("select * from student;") print(cursor.fetchall()) print("-----------修改----------------") cursor.execute("update student set name = 'zhangsan1' where name = 'zhangsan';") cursor.execute("select * from student;") print(cursor.fetchall()) print("----------删除-----------------") cursor.execute("delete from student where name = 'lisi';") cursor.execute("select * from student;") print(cursor.fetchall()) print("---------------------------") #需要提交才能插入、成功修改、删除 conn.commit() #关闭游标 cursor.close() #关闭连接 conn.close()

结果：

 (('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男')) --------------------------- (('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男'), ('zhangsan', 18, '男'), ('lisi', 18, '男')) --------------------------- (('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男'), ('zhangsan1', 18, '男'), ('lisi', 18, '男')) --------------------------- (('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男'), ('zhangsan1', 18, '男')) ---------------------------

5.设置支持中文【创建连接时添加charset=”utf8”】：

 import pymysql #创建连接 # conn=pymysql.connect(host="localhost",port=3306,user='root',passwd='123456',db='python_test') conn=pymysql.connect(host="localhost",port=3306,user='root',passwd='123456',db='python_test',charset="utf8") #创建游标 cursor = conn.cursor() effect_row= cursor.execute("select * from student;") print("执行成功，受影响行数:",effect_row) print(cursor.fetchall()) conn.commit() cursor.close() conn.close()

添加前：

添加后：

使用sqlalchemy操作mysql:

介绍：

ORM 将数据库中的表与面向对象语言中的类建立了一种对应关系，【ORM可以说是参照映射来处理数据的模型，比如说：需要创建一个表，可以定义一个类，而这个类存在与表相映射的属性，那么可以通过操作这个类来创建一个表】
sqlmary是一个mysql的ORM

前提：

安装模块：pip3 install sqlalchemy

使用：

导入模块：

导入连接数据库模块：from sqlalchemy import create_engine
如果需要创建新表，则需要导入表结构定义模块：from sqlalchemy.ext.declarative import declarative_base
导入其他相关模块，主要是映射的类，如字段映射为Column，如数据类型int映射为Integer，如索引映射为Index,需要什么导入什么:from sqlalchemy import Column,Integer,String

映射关系：

数据库中	映射	模块【如果可以从多个模块处导入，用 \| 分隔】【方式太多，可能有漏，但不影响导入】
表	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常用】:mysql+pymysql://:@/[?
- 其他参数：
  - echo是否显示ORM转成实际sql语句的过程,echo=True为显
  - encoding为连接时使用的字符集

操作：

基本操作：

创建新表

方法一：使用declarative
- 1.导入模块from sqlalchemy.ext.declarative import declarative_base
- 2.根据需要的元素来导入模块from sqlalchemy import Column
  - 导入需要的数据类型【注：数据类型在sqlalchemy中也有指向，所以也可以from sqlalchemy import String,Integer,Char】：from sqlalchemy.types import *
- 3.创建连接，
- 3.使用declarative_base来获得一个类对象,此处我定义该对象为Base
- 定义一个类，继承declarative_base生成的类对象Base
  - 使用__tablename__来定义表名
  - 使用列名 = Column(数据类型,其他列属性…)等类似格式来定义字段
    - nullable=False 代表这一列不可以为空，index=True 表示在该列创建索
- 创建表：Base.metadata.create_all(engine)
```
 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',echo=True) #方式一： Base = declarative_base() class User(Base): __tablename__ = 'user'#表名 id = Column(Integer,primary_key=True) name = Column(String(32)) password = Column(String(64)) Base.metadata.create_all(engine)
```

方法二：使用Table

1.导入模块： from sqlalchemy import Table
2.连接数据库:engine=create_engine(….)
3.获取meta类，metadata=MetaData(engine)
4.创建Table对象（比如：t=Table("group" ,metadata,Column("id",Integer,primary_key=True),Column("group_name",String(32))) ）

5.创建表：metadata.create_all()

 from sqlalchemy import create_engine from sqlalchemy import Table from sqlalchemy import MetaData from sqlalchemy import Column from sqlalchemy.types import * from sqlalchemy.ext.declarative import declarative_base ####下面的注释部分可以与上面一句的未注释的替换 engine=create_engine("mysql+pymysql://root:123456@localhost/python_test",encoding ='utf-8',echo=True) metadata=MetaData(engine) ### # Base=declarative_base() t=Table( "group" ,metadata,#表名 # "group",Base.metadata, Column("id",Integer,primary_key=True), Column("group_name",String(32)) ) metadata.create_all() # Base.metadata.create_all(engine)

查看表：
- db_table=Base.metadata.tables#仅有当次运行中继承了Base而创建的新表
- db_tables=engine.table_names()#仅有表名
删除表：Base.metadata.drop_all(engine)
修改表：
- 直接修改表所对应的类结构是无法修改成功的，
- 如果需要修改在程序中自定义的表的结构，那么需要手动修改，手动的方式有很多，比如直接engine.execute(sql语句)。。。。

插入数据【这里仅针对使用declarative_base创建的表，对于不是程序中才创建的，可以自己使用declarative_base建一个类来映射之前的表，只要映射一致，就能插入数据】

1.连接数据库：engine=create_engine(….)
1.导入模块：from sqlalchemy.orm import sessionmaker
2.获取session_class类:Session_class=sessionmaker(bind=engine)
3.获取session对象:s=Session_class()
4.使用s来添加：
- s.add()
- s.add_all()

5.提交数据: s.commit()

 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',echo=True) 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) Base.metadata.create_all(engine) from sqlalchemy.orm import sessionmaker obj1=User(name='lisi',password='123456',group=1) Session=sessionmaker(bind=engine) s=Session() s.add(obj1)# users=[User(name='wangwu',password='123456',group=1), User(name='zhaoliu', password='123456', group=1), User(name='sunqi', password='123456', group=1) ] s.add_all(users)# s.commit()

查找数据

同样适用sessionmaker来查找，与插入相同，需要创建session_class对象(我定义为s)

使用s来查找：

s.query(表对应类)是相当于select对应表，后面可以跟first()、all()等来获取结果，也可以加filter、filter_by等来筛选结果
获取全部： s.query(表对应类).all() 【返回的是一个结果列表】
查找指定: s.query(表对应类).filter(表对应类.xxx==xxxx)【filter获取的是结果集，需要使用all(),first()等方法来获取结果】
查找指定: s.query(表对应类).filter_by(xxx=xxxx)
附：虽然返回值是一个结果集，但这个集合是一个类对象，如果想查看内容，需要在表对应的类中增加__repr__方法。
多个筛选条件使用“,”隔开

常见可用筛选条件【User是一个表对应的类】：
        
          
            
              使用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'])】

              使用all时，以下是放在query里面的:

              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 ""%(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 ""%(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 ""%(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 ""%(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 ""%(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 ""%(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 ""%(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 ""%(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 ""%(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的数据