python mysql orm框架----sqlalchemy(一)

2019-09-25 13:43 321人阅读评论(0)

前言

    在学习mysql以及python和mysql交互的时候,我们发现,如果我们操作数据库,我们需要写很多的sql语句.在几个月之前,我做了一个简单的人脸识别系统,当时我的编程水平还很菜,就是用的原生的sql语句,今天回去看了一下发现,总共写了252条sql语句,我现在想想,当初我是怎么写下来的…
    话不多说,言归正传.在我们实际工程开发过程中,我们会很频繁的去操作数据库,甚至会写很多几乎重复的sql语句.如果来避免这种事情呢?首先,我们可以把sql语句写到一个函数里,然后传入不同的参数,但是即使是这样,我们依然要写很多的sql语句,有时候写多了就会容易乱.解决这种事情最有效的方式就是使用orm.
    参考博客:python 之路，Day11 - sqlalchemy ORM

1.orm简介

orm英文全称object Relationalmapping,就是对象映射关系程序,简单来说我们类似python面向对象的程序来说一切皆对象,但是我们使用的数据库却都是关系型的,为了保证一致的使用习惯,通过orm将编程语言的对象模型和数据库的关系模型建立映射关系,这样我们在使用编程语言对数据库进行操作的时候可以直接编程语言的对象模型进行操作就可以了,而不用直接使用sql语言.
orm的优点:

隐藏了数据访问细节(不用写原生数据库),"封闭"的通用数据库交互,ORM的核心.它使得我们的通用数据库交互变得简单易行,并且完全不用考虑该死的SQL语句.快速开发,由此而来.
ORM使我们构造固化数据结构变得简单易行

orm的缺点:

(1)无可避免,自动化意味着映射和关系管理,代价是牺牲性能,而现在的各种ORM框架都在尝试使用各种方法来减轻负担.

注:orm是一个术语,而不是一个软件;
在python中,最著名的ORM框架就是SQLAlchemy.

sqlalchemy安装:

pip  install sqlalchemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

2.sqlalchemy 基本使用

我们先来看一个简单的例子:
如果我们想创建一个表,利用sql语句是这样的:

CREATE TABLE IF NOT EXISTS `users`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `password` VARCHAR(40) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

这个例子比较简单,只用简单的sql语句就可以完成,但是如果遇到比较复杂的事务的话,如果我们不是专业的sqldb开发人员,可能我们的脑子就跟不上了.
我们再来看一下利用sqlalchemy创建数据表:

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

#############################################

# 创建数据表
# 连接数据库
# create_engine()里面的顺序:mysql+pymysql(当然也可以是其他的包)://用户名:密码@主机/数据库名
engine = create_engine("mysql+pymysql://root:123456@localhost/student",
                       encoding='utf-8',echo=False)
# print(engine)   #Engine(mysql+pymysql://root:***@localhost/student)

# 生成orm基类
Base = declarative_base()

class User(Base):       #继承上面的那个基类
    __tablename__ = 'user'  #表名
    id = Column(Integer,primary_key=True)   # id:int类型,是主键
    name = Column(String(32))               # name:string类型,长度是32
    password = Column(String(64))           # password:string类型,长度是64

Base.metadata.create_all(engine)

这个程序的功能就是创建一个数据表,和上面的sql语句创建的表是一样的:

mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(32) | YES  |     | NULL    |                |
| password | varchar(64) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

上面我们提到了,sqlalchemy就是将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。那么我们就来看看它的具体流程是什么:
(1)首先我们要进行数据库的连接
pymysql连接数据库是这样的:

import pymysql

try:
    mysqldb = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='student'
    )

    # 使用cursor()方法创建一个游标对象cursor.
    cursor = mysqldb.cursor()
    print('数据库连接成功')

except Exception as e:
    print('数据库连接失败---',e)

而sqlalchemy连接数据库是这样的:

engine = create_engine("mysql+pymysql://root:123456@localhost/student",
                       encoding='utf-8',echo=True)

create_engine()的作用就是连接数据库,第一个参数就是用来连接数据库的,顺序为:
(1)mysq+pymsql:mysql指定数据库,pymsql指定python与mysql交互的工具包,也可以是别的,比如说mysql-connector.(2)用户名:密码(3)主机(4)数据库.这个和pymsql连接数据库其实是差不多的,只不过是形式不一样.
(2)生成一个基类,并且创建自己的数据表

# 生成orm基类
Base = declarative_base()

class User(Base):       #继承上面的那个基类
    __tablename__ = 'user'  #表名
    id = Column(Integer,primary_key=True)   # id:int类型,是主键
    name = Column(String(32))               # name:string类型,长度是32
    password = Column(String(64))           # password:string类型,长度是64

Base=declarative_base()是生成orm基类,所有的数据表的创建都要继承这个基类.
class User是创建数据表:

tablename = ‘user’ :数据表的名字是user,用__tablename__来说明;
id = Column(Integer,primary_key=True):id是字段名,Column是创建列,Integer是指定类型为int型,primary_key=True是指定主键;
name = Column(String(32)) :name:string类型,长度是32
password = Column(String(64)) :password:string类型,长度是64

(3)生成数据表

Base.metadata.create_all(engine)

在这里是调用父类Base类的方法metadata.crreate_all(),意思是所有继承这个父类的子类都要执行.

最终,程序执行结果为:

2019-09-24 11:19:23,139 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-09-24 11:19:23,139 INFO sqlalchemy.engine.base.Engine {}
2019-09-24 11:19:23,141 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-09-24 11:19:23,141 INFO sqlalchemy.engine.base.Engine {}
2019-09-24 11:19:23,142 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2019-09-24 11:19:23,142 INFO sqlalchemy.engine.base.Engine {}
2019-09-24 11:19:23,144 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-09-24 11:19:23,145 INFO sqlalchemy.engine.base.Engine {}
2019-09-24 11:19:23,145 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-09-24 11:19:23,145 INFO sqlalchemy.engine.base.Engine {}
2019-09-24 11:19:23,146 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2019-09-24 11:19:23,146 INFO sqlalchemy.engine.base.Engine {}
2019-09-24 11:19:23,147 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
2019-09-24 11:19:23,147 INFO sqlalchemy.engine.base.Engine {}
2019-09-24 11:19:23,148 INFO sqlalchemy.engine.base.Engine ROLLBACK
2019-09-24 11:19:23,149 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE user (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(32), 
	password VARCHAR(64), 
	PRIMARY KEY (id)
)


2019-09-24 11:19:23,149 INFO sqlalchemy.engine.base.Engine {}
2019-09-24 11:19:23,328 INFO sqlalchemy.engine.base.Engine COMMIT

生成的数据表:

mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(32) | YES  |     | NULL    |                |
| password | varchar(64) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

程序是否输出这些信息是由"echo"这个参数来决定的.如果echo=True就输出这些信息,如果echo=False,就不输出这些信息了.
在程序的输出中我们可以看到有这么一段:

CREATE TABLE user (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(32), 
	password VARCHAR(64), 
	PRIMARY KEY (id)
)

这就是将对象转化成了sql语言,不用我们自己去写原生的sql语言了.

3.sqlalchemy增删改查

数据表创建好以后,我们就要对数据表来进行数据的操作,当然了,我们知道最基本的就是增删改查了.
(1)插入数据

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:123456@localhost/student",
                       encoding='utf-8',
                       echo=False)


Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)  # id:int型,是主键
    name = Column(String(32))  # name:string型,大小为32
    password = Column(String(64))  # password:string型,大小为64

Base.metadata.create_all(engine)

# 创建与数据库的会话session class
# 注意这里返回的是一个class类,而不是一个实例
Session_class = sessionmaker(bind=engine)

# 生成session实例
Session = Session_class()

# 生成要创建的数据对象
user_obj = User(name='fan',password='123456')
user_obj_2 = User(name='fan',password='111111')
user_obj_3 = User(name='fan',password='123123')
user_obj_4 = User(name='feng',password='123456')
user_obj_5 = User(name='feng',password='111111')
user_obj_6 = User(name='feng',password='123123')


# 把要创建的数据对象添加到session里,一会统一创建
Session.add(user_obj)
Session.add(user_obj_2)
Session.add(user_obj_3)
Session.add(user_obj_4)
Session.add(user_obj_5)
Session.add(user_obj_6)

Session.commit()                            # 此时才是统一创建数据

结果:

mysql> select * from user;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | fan  | 123456   |
|  2 | fan  | 111111   |
|  3 | fan  | 123123   |
|  4 | feng | 123456   |
|  5 | feng | 111111   |
|  6 | feng | 123123   |
+----+------+----------+
6 rows in set (0.00 sec)

(2)查询数据

查询所有数据:

data_all = Session.query(User).all()
print('data_all =',data_all)

结果:

data_all = [<__main__.User object at 0x7f2f22d3ccc0>, <__main__.User object at 0x7f2f225f46a0>, <__main__.User object at 0x7f2f225f49e8>, <__main__.User object at 0x7f2f225f4a58>, <__main__.User object at 0x7f2f225f4b00>, <__main__.User object at 0x7f2f225f4ba8>]

我们发现结果是一个列表,列表里面是对象,要想将对象中的数据打印出来,可以修改class User中的def __repr__函数:

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:123456@localhost/student",
                       encoding='utf-8',
                       echo=False)


Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)  # id:int型,是主键
    name = Column(String(32))  # name:string型,大小为32
    password = Column(String(64))  # password:string型,大小为64

    def __repr__(self):
        return "(id:{},name:{},password:{})".format(self.id,self.name,self.password)

Base.metadata.create_all(engine)

# 创建与数据库的会话session class
# 注意这里返回的是一个class类,而不是一个实例
Session_class = sessionmaker(bind=engine)

# 生成session实例
Session = Session_class()

data_all = Session.query(User).all()
print('data_all =',data_all)

结果:

data_all = [(id:1,name:fan,password:123456), (id:2,name:fan,password:111111), (id:3,name:fan,password:123123), (id:4,name:feng,password:123456), (id:5,name:feng,password:111111), (id:6,name:feng,password:123123)]

注意:

def __repr__(self):
        return "(id:{},name:{},password:{})".format(self.id,self.name,self.password)

这个函数是将类中的结果格式化输出.

查询特定条件的数据:

data_fan = Session.query(User).filter_by(name='fan').all()
print('data_fan =',data_fan)

查询所有name='fan’的数据:

data_fan = [(id:1,name:fan,password:123456), (id:2,name:fan,password:111111), (id:3,name:fan,password:123123)]

如果只想查第一个数据,则:

data_fan_first = Session.query(User).filter_by(name='fan').first()
print('data_fan_fisrt =',data_fan_first)

结果:

data_fan_fisrt = (id:1,name:fan,password:123456)

查询多个条件

data_fan_filter = Session.query(User).filter_by(name='fan').filter_by(password='123456').all()
print('data_fan_filter =',data_fan_filter)

结果:

data_fan_filter = [(id:1,name:fan,password:123456)]

(3)修改数据
修改数据的原理是先查出来再修改.
先来看我们的原始数据:

mysql> select * from user;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | fan  | 123456   |
|  2 | fan  | 111111   |
|  3 | fan  | 123123   |
|  4 | feng | 123456   |
|  5 | feng | 111111   |
|  6 | feng | 123123   |
+----+------+----------+
6 rows in set (0.00 sec)

修改:

'''
    修改数据的原理是先查出来再修改
'''

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:123456@localhost/student",
                       encoding='utf-8',
                       echo=False)


Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)  # id:int型,是主键
    name = Column(String(32))  # name:string型,大小为32
    password = Column(String(64))  # password:string型,大小为64

    def __repr__(self):
        return "(id:{},name:{},password:{})".format(self.id,self.name,self.password)

Base.metadata.create_all(engine)

# 创建与数据库的会话session class
# 注意这里返回的是一个class类,而不是一个实例
Session_class = sessionmaker(bind=engine)

# 生成session实例
Session = Session_class()

data_update = Session.query(User).filter_by(name='fan').filter_by(password='123456').first()
data_update.name = 'ming'
Session.commit()

修改后的数据:

mysql> select * from user;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | ming | 123456   |
|  2 | fan  | 111111   |
|  3 | fan  | 123123   |
|  4 | feng | 123456   |
|  5 | feng | 111111   |
|  6 | feng | 123123   |
+----+------+----------+
6 rows in set (0.00 sec)

(4)删除数据
删除数据的原理也是先查出来再删除.
原始数据:

mysql> select * from user;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | ming | 123456   |
|  2 | fan  | 111111   |
|  3 | fan  | 123123   |
|  4 | feng | 123456   |
|  5 | feng | 111111   |
|  6 | feng | 123123   |
+----+------+----------+
6 rows in set (0.00 sec)

删除数据:

'''
    修改数据的原理是先查出来再修改
'''

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:123456@localhost/student",
                       encoding='utf-8',
                       echo=False)


Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)  # id:int型,是主键
    name = Column(String(32))  # name:string型,大小为32
    password = Column(String(64))  # password:string型,大小为64

    def __repr__(self):
        return "(id:{},name:{},password:{})".format(self.id,self.name,self.password)

Base.metadata.create_all(engine)

# 创建与数据库的会话session class
# 注意这里返回的是一个class类,而不是一个实例
Session_class = sessionmaker(bind=engine)

# 生成session实例
Session = Session_class()



data_delete = Session.query(User).filter_by(name='feng').filter_by(password='123456').first()
Session.delete(data_delete)
Session.commit()

删除后的数据:

mysql> select * from user;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | ming | 123456   |
|  2 | fan  | 111111   |
|  3 | fan  | 123123   |
|  5 | feng | 111111   |
|  6 | feng | 123123   |
+----+------+----------+
5 rows in set (0.00 sec)

(5)统计

print('>>>统计:')
data_count = Session.query(User).filter_by(name='fan').count()
print(data_count)
data_count_1 = Session.query(User).filter(User.name.like('fa%')).count()
print(data_count_1)
data_count_2 = Session.query(User).filter_by(name='feng').count()
print(data_count_2)
data_count_3 = Session.query(User).count()
print(data_count_3)

结果:

>>>统计:
2
2
2
5

(6)分组
先来看我们的数据:

mysql> select * from user;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | ming | 123456   |
|  2 | fan  | 111111   |
|  3 | fan  | 123123   |
|  5 | feng | 111111   |
|  6 | feng | 123123   |
+----+------+----------+
5 rows in set (0.00 sec)

分组:

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

结果:

[('fan', 2), ('feng', 2), ('ming', 1)]

**说明:**func.count(User.name)是对User.name进行分组统计,group_by(User.name)是根据User.name来进行分组.

写在最后

本文是个人的一些学习笔记,如有侵权,请及时联系我进行删除,谢谢大家.

转载：https://blog.csdn.net/weixin_42567323/article/details/101270941

查看评论

小言_互联网的博客

小言_互联网的博客

个人资料

文章分类

文章存档

阅读排行

评论排行

推荐文章

python mysql orm框架----sqlalchemy(一)

前言

1.orm简介

2.sqlalchemy 基本使用

3.sqlalchemy增删改查

写在最后

* 以上用户言论只代表其个人观点，不代表本网站的观点或立场