python使用mysql的两种使用方式-木庄网络博客

本文整理自网络，侵删。

Python操作MySQL主要使用两种方式：

原生模块 pymsql
ORM框架 SQLAchemy

pymql

pymsql是Python中操作MySQL的模块,在windows中的安装：

pip install pymysql

入门：我们连接虚拟机中的centos中的mysql，然后查询test数据库中student表的数据

import pymysql
#创建连接
conn = pymysql.connect(host='192.168.123.207',port=3306,user='root',passwd='root',db="test");
#创建游标
cursor = conn.cursor()
#执行sql，并返回受影响的行数
effect_row = cursor.execute("select * from student")
print(effect_row)

运行结果：

需要给权限

mysql> grant all on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

这个时候我们在运行上面的程序我们就可以看到，运行成功

这是说明查询到了5条数据，那如果我们需要查看这五条具体的数据是什么，我们要用：

print(cursor.fetchone())

cursor.fetchone()是一条一条的把数据取出来

这里我们用了两条cursor.fetchone()

如果我们想要只取出前3条数据：

print('>>>>>',cursor.fetchmany(3))

一次性取出所有数据：

print('------',cursor.fetchall())

如果我们要插入多条数据：

import pymysql
#创建连接
conn = pymysql.connect(host='192.168.123.207',port=3306,user='root',passwd='root',db="test");
#创建游标
cursor = conn.cursor()
data = [
 ("N1",23,"2015-01-01","M"),
 ("N2",24,"2015-01-02","F"),
 ("N3",25,"2015-01-03","M"),
]
#执行sql，并返回受影响的行数
effect_row = cursor.executemany("insert into student (name,age,register_date,gender)values(%s,%s,%s,%s)",data)
conn.commit()

注：executemany默认会启动一个事务，如果没有conn.commit()，数据将不会被插入成功

sqlalchemy ORM

1.ORM介绍

orm英文全称object relational mapping,就是对象映射关系程序，简单来说类似python这种面向对象的程序来说一切皆对象，我们实例化一个对象，通过点的形式来调用里面的函数。orm相当于把数据库给实例化了，数据库都是关系型的，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

优点：

1.隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句

2.ORM使我们构造固化数据结构变得简单易行

2.sqlalchemy安装

安装：

pip install sqlalchemy

3.sqlalchemy基本使用

首先我们看一下我们没有用orm之前我们创建一个数据表是这个样的：

create table student(
 id int auto_increment,
 name char(32) not null,
 age int not null,
 register_date date not null,
 primary key(id) 
);

使用了orm,实现上面的表的创建，代码如下：

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
engine = create_engine("mysql+pymysql://root:root@192.168.123.207/test",encoding='utf-8',echo=True)
Base = declarative_base()#生成orm基类

class User(Base):
 __tablename__ = 'user'#表名
 id = Column(Integer,primary_key=True)
 name = Column(String(32))
 password = Column(String(64))
Base.metadata.create_all(engine)#创建表结构

用orm创建一条数据：

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker
engine = create_engine("mysql+pymysql://root:root@192.168.123.207/test",encoding='utf-8',echo=True)
Base = declarative_base()#生成orm基类
class User(Base):
  __tablename__ = 'user'#表名
  id = Column(Integer,primary_key=True)
  name = Column(String(32))
  password = Column(String(64))
Base.metadata.create_all(engine)#创建表结构
Session_class = sessionmaker(bind=engine)#创建与数据库的会话session class ,注意这里返回给session的是一个类，不是实例
Session = Session_class()#生成session实例
user_obj = User(name = "xiaoming" , password = "123456")#生成你要创建的数据对象
user_obj2 = User(name = "jack" , password = "123564")#生成你要创建的数据对象
print(user_obj.name,user_obj.id)#此时还没有创建对象，打印一下会发现id还是None
Session.add(user_obj)
Session.add(user_obj2)
print(user_obj.name,user_obj.id)#此时也依然还没有创建

Session.commit()#现在才统一提交，创建数据

插入数据是使用sessionmaker,通过绑定上面创建的连接创建出一个类，生成session实例（相当于之前的cursor）,用面向对象的方式创建两条记录，然后添加，最后commit就可以了

下面我们来看看数据库的增删改查：

查询：

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker
engine = create_engine("mysql+pymysql://root:root@192.168.123.207/test",encoding='utf-8')
Base = declarative_base()#生成orm基类
class User(Base):
  __tablename__ = 'user'#表名
  id = Column(Integer,primary_key=True)
  name = Column(String(32))
  password = Column(String(64))
Base.metadata.create_all(engine)#创建表结构
Session_class = sessionmaker(bind=engine)#创建与数据库的会话session class ,注意这里返回给session的是一个类，不是实例
Session = Session_class()#生成session实例
data=Session.query(User).filter_by(name="xiaoming").all()
#print(data[0].name,data[0].password)
print(data)

如果只是Session.query(User).filter_by(name="xiaoming"),只会看到一条sql语句：

filter_by()查出来的是一个列表，是一组数据

加上.all()

这个是一个对象，这也看不出来是那个，所以我们要手动去调出数据

我们用print(data[0].name,data[0].password):

这样就查出了数据

现在是查一条数据，如果filter_by()里面什么都不写：

data=Session.query(User).filter_by().all()

我们就查出了好几条数据，我们要循环才能看出来具体的数据。我们想要直接看到谁是谁，我们怎么办呢？

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker
engine = create_engine("mysql+pymysql://root:root@192.168.123.207/test",encoding='utf-8')
Base = declarative_base()#生成orm基类
class User(Base):
  __tablename__ = 'user'#表名
  id = Column(Integer,primary_key=True)
  name = Column(String(32))
  password = Column(String(64))
  def __repr__(self):
    return "<%s name:%s>" % (self.id,self.name)
Base.metadata.create_all(engine)#创建表结构
Session_class = sessionmaker(bind=engine)#创建与数据库的会话session class ,注意这里返回给session的是一个类，不是实例
Session = Session_class()#生成session实例
data=Session.query(User).filter_by().all()
print(data)

我们添加了__repr__（）函数，这样看看运行结果：

这样就显现出我们查询的结果了。就相当于，如果你要查看什么数据，以什么格式的方式显示，就可以在__repr__这个函数中设置

在这里，我们用filter_by().all()查询出所有的数据，那我们用 filter_by().first() ，就可以查询出数据库中的第一条数据

上面我们用filter_by(name="xiaoming")查询出了姓名为xiaoming的数据，那我们想要查询用户id>1的数据应该怎么查询呢？

data=Session.query(User).filter(User.id>1).all()

多个条件查询：再加几个filter

data=Session.query(User).filter(User.id>1).filter(User.id<3).all()

修改：

data=Session.query(User).filter(User.id>1).first()
print(data)
data.name = "Jack Chen"
data.password = "555555"
Session.commit()

查询到你要修改的这个数据，然后想修改面向对象里的数据一样，对数据进行修改，最后commit（）就可以了

删除：

data = Session.query(User).filter_by(name = 'Rain').first()
Session.delete(data)
Session.commit()

同样的，先查询到要删除的数据，然后对数据进行删除，最后提交commit

回滚：

fake_user = User(name = 'Rain',password = "123456")
Session.add(fake_user)
print(Session.query(User).filter(User.name.in_(['jack','rain'])).all())#这时候看看session里有了刚刚添加的数据
Session.rollback()#此时你rollback一下
print(Session.query(User).filter(User.name.in_(['jack','rain'])).all())#再查就发现刚刚添加的数据就没有了

运行结果看看：

这个时候可以看到一开始我们是能够看到刚刚插入的数据，但是回滚之后我们就看不到了，我们到数据库中看看：

我们插入一条数据看看

可以看出我们插入的数据的id是4，这样看来确实是先插入进去，然后回滚之后删除的

分组统计：

统计：

Session.query(User).filter(User.name.in_(['xiaoming','rain'])).count()

分组：

from sqlalchemy import func
print(Session.query(func.count(User.name),User.name).group_by(User.name).all())

join多表查询:

Session.query(User,Student).filter(User.id == Student.id).all()
Session.query(User).join(Student).all()#这种写法必须要求两个表有外键关联

外键关联

我们先创建两个表student,study_record：

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DATE,ForeignKey
from sqlalchemy.orm import sessionmaker
engine = create_engine("mysql+pymysql://root:root@192.168.123.207/xumingdb",encoding='utf-8')
Base = declarative_base()#生成orm基类
class Student(Base):
  __tablename__ = 'student'#表名
  id = Column(Integer,primary_key=True)
  name = Column(String(32),nullable=False)
  register_date = Column(DATE,nullable=False)
  def __repr__(self):
    return "<%s name:%s>" % (self.id,self.name)
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"))
  def __repr__(self):
    return "<%s day:%s>" % (self.id,self.day)
Base.metadata.create_all(engine)#创建表结构

外键表的创建，要用到ForeignKey("student.id")里面就直接是表名.字段名

阅读剩余部分