时间:2021-07-01 10:21:17 帮助过:11人阅读
orm英文全称object relational mapping,就是对象映射关系程序,简单来说我们类似python这种面向对象的程序来说一切皆对象,但是我们使用的数据库却都是关系型的,为了保证一致的使用习惯,通过orm将编程语言的对象模型和数据库的关系模型建立映射关系,这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了,而不用直接使用sql语言
优点:
缺点:
二、SQLAlchemy
SQLAlchemy是Python编程语言下的一款ORM框架,该框架建立在数据库API之上,使用关系对象映射进行数据库操作,简言之便是:将对象转换成SQL,然后使用数据API执行SQL并获取执行结果。
在Python中,最有名的ORM框架是SQLAlchemy。用户包括openstack\Dropbox等知名公司或应用
Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:
MySQL
-
Python
mysql
+
mysqldb:
/
/
<user>:<password>@<host>[:<port>]
/
<dbname>
pymysql
mysql
+
pymysql:
/
/
<username>:<password>@<host>
/
<dbname>[?<options>]
MySQL
-
Connector
mysql
+
mysqlconnector:
/
/
<user>:<password>@<host>[:<port>]
/
<dbname>
cx_Oracle
oracle
+
cx_oracle:
/
/
user:
pass
@host:port
/
dbname[?key
=
value&key
=
value...]
更多详见:http:
/
/
docs.sqlalchemy.org
/
en
/
latest
/
dialects
/
index.html
注:支持连接MySQL、Oracles数据库
安装:
pip install SQLAlchemy
pip install pymysql
#由于mysqldb依然不支持py3,所以这里我们用pymysql与sqlalchemy交互
步骤一:
使用 Engine/ConnectionPooling/Dialect 进行数据库操作,Engine使用ConnectionPooling连接数据库,然后再通过Dialect执行SQL语句。
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from
sqlalchemy
import
create_engine
engine
=
create_engine(
"mysql+mysqldb://root:123@127.0.0.1:3306/s11"
, max_overflow
=
5
)
engine.execute(
"INSERT INTO ts_test (a, b) VALUES (‘2‘, ‘v1‘)"
)
engine.execute(
"INSERT INTO ts_test (a, b) VALUES (%s, %s)"
,
((
555
,
"v1"
),(
666
,
"v1"
),)
)
engine.execute(
"INSERT INTO ts_test (a, b) VALUES (%(id)s, %(name)s)"
,
id
=
999
, name
=
"v1"
)
result
=
engine.execute(
‘select * from ts_test‘
)
result.fetchall()
事务操作:
- <span style="color: #008000">#<span style="color: #008000">!/usr/bin/env python<span style="color: #008000">
- #<span style="color: #008000"> -*- coding:utf-8 -*-
- <span style="color: #0000ff">from sqlalchemy <span style="color: #0000ff">import<span style="color: #000000"> create_engine
- engine = create_engine(<span style="color: #800000">"<span style="color: #800000">mysql+mysqldb://root:123@127.0.0.1:3306/s11<span style="color: #800000">", max_overflow=5<span style="color: #000000">)
- <span style="color: #008000">#<span style="color: #008000"> 事务操作
- <span style="color: #000000">with engine.begin() as conn:
- conn.execute(<span style="color: #800000">"<span style="color: #800000">insert into table (x, y, z) values (1, 2, 3)<span style="color: #800000">"<span style="color: #000000">)
- conn.execute(<span style="color: #800000">"<span style="color: #800000">my_special_procedure(5)<span style="color: #800000">"<span style="color: #000000">)
- conn =<span style="color: #000000"> engine.connect()
- <span style="color: #008000">#<span style="color: #008000"> 事务操作
- <span style="color: #000000">with conn.begin():
- conn.execute(<span style="color: #800000">"<span style="color: #800000">some statement<span style="color: #800000">", {<span style="color: #800000">‘<span style="color: #800000">x<span style="color: #800000">‘:5, <span style="color: #800000">‘<span style="color: #800000">y<span style="color: #800000">‘:10})<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
注:查看数据库连接:show status like ‘Threads%‘;
步骤二:
使用 Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 进行数据库操作。Engine使用Schema Type创建一个特定的结构对象,之后通过SQL Expression Language将该对象转换成SQL语句,然后通过 ConnectionPooling 连接数据库,再然后通过 Dialect 执行SQL,并获取结果。
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from
sqlalchemy
import
create_engine, Table, Column, Integer, String, MetaData, ForeignKey
metadata
=
MetaData()
user
=
Table(
‘user‘
, metadata,
Column(
‘id‘
, Integer, primary_key
=
True
),
Column(
‘name‘
, String(
20
)),
)
color
=
Table(
‘color‘
, metadata,
Column(
‘id‘
, Integer, primary_key
=
True
),
Column(
‘name‘
, String(
20
)),
)
engine
=
create_engine(
"mysql+mysqldb://root:123@127.0.0.1:3306/s11"
, max_overflow
=
5
)
metadata.create_all(engine)
# metadata.clear()
# metadata.remove()
- <span style="color: #008000">#<span style="color: #008000">!/usr/bin/env python<span style="color: #008000">
- #<span style="color: #008000"> -*- coding:utf-8 -*-
- <span style="color: #0000ff">from sqlalchemy <span style="color: #0000ff">import<span style="color: #000000"> create_engine, Table, Column, Integer, String, MetaData, ForeignKey
- metadata =<span style="color: #000000"> MetaData()
- user = Table(<span style="color: #800000">‘<span style="color: #800000">user<span style="color: #800000">‘<span style="color: #000000">, metadata,
- Column(<span style="color: #800000">‘<span style="color: #800000">id<span style="color: #800000">‘, Integer, primary_key=<span style="color: #000000">True),
- Column(<span style="color: #800000">‘<span style="color: #800000">name<span style="color: #800000">‘, String(20<span style="color: #000000">)),
- )
- color = Table(<span style="color: #800000">‘<span style="color: #800000">color<span style="color: #800000">‘<span style="color: #000000">, metadata,
- Column(<span style="color: #800000">‘<span style="color: #800000">id<span style="color: #800000">‘, Integer, primary_key=<span style="color: #000000">True),
- Column(<span style="color: #800000">‘<span style="color: #800000">name<span style="color: #800000">‘, String(20<span style="color: #000000">)),
- )
- engine = create_engine(<span style="color: #800000">"<span style="color: #800000">mysql+mysqldb://root:123@127.0.0.1:3306/s11<span style="color: #800000">", max_overflow=5<span style="color: #000000">)
- conn =<span style="color: #000000"> engine.connect()
- <span style="color: #008000">#<span style="color: #008000"> 创建SQL语句,INSERT INTO "user" (id, name) VALUES (:id, :name)
- conn.execute(user.insert(),{<span style="color: #800000">‘<span style="color: #800000">id<span style="color: #800000">‘:7,<span style="color: #800000">‘<span style="color: #800000">name<span style="color: #800000">‘:<span style="color: #800000">‘<span style="color: #800000">seven<span style="color: #800000">‘<span style="color: #000000">})
- conn.close()
- <span style="color: #008000">#<span style="color: #008000"> sql = user.insert().values(id=123, name=‘wu‘)<span style="color: #008000">
- #<span style="color: #008000"> conn.execute(sql)<span style="color: #008000">
- #<span style="color: #008000"> conn.close()
- <span style="color: #008000">#<span style="color: #008000"> sql = user.delete().where(user.c.id > 1)
- <span style="color: #008000">#<span style="color: #008000"> sql = user.update().values(fullname=user.c.name)<span style="color: #008000">
- #<span style="color: #008000"> sql = user.update().where(user.c.name == ‘jack‘).values(name=‘ed‘)
- <span style="color: #008000">#<span style="color: #008000"> sql = select([user, ])<span style="color: #008000">
- #<span style="color: #008000"> sql = select([user.c.id, ])<span style="color: #008000">
- #<span style="color: #008000"> sql = select([user.c.name, color.c.name]).where(user.c.id==color.c.id)<span style="color: #008000">
- #<span style="color: #008000"> sql = select([user.c.name]).order_by(user.c.name)<span style="color: #008000">
- #<span style="color: #008000"> sql = select([user]).group_by(user.c.name)
- <span style="color: #008000">#<span style="color: #008000"> result = conn.execute(sql)<span style="color: #008000">
- #<span style="color: #008000"> print result.fetchall()<span style="color: #008000">
- #<span style="color: #008000"> conn.close()</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span><br><br>
注:SQLAlchemy无法修改表结构,如果需要可以使用SQLAlchemy开发者开源的另外一个软件Alembic来完成。
步骤三:
使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象,对象转换成SQL,执行SQL。
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
Column, Integer, String
from
sqlalchemy.orm
import
sessionmaker
from
sqlalchemy
import
create_engine
engine
=
create_engine(
"mysql+mysqldb://root:123@127.0.0.1:3306/s11"
, max_overflow
=
5
)
Base
=
declarative_base()
class
User(Base):
__tablename__
=
‘users‘
id
=
Column(Integer, primary_key
=
True
)
name
=
Column(String(
50
))
# 寻找Base的所有子类,按照子类的结构在数据库中生成对应的数据表信息
# Base.metadata.create_all(engine)
Session
=
sessionmaker(bind
=
engine)
session
=
Session()
# ########## 增 ##########
# u = User(id=2, name=‘sb‘)
# session.add(u)
# session.add_all([
# User(id=3, name=‘sb‘),
# User(id=4, name=‘sb‘)
# ])
# session.commit()
# ########## 删除 ##########
# session.query(User).filter(User.id > 2).delete()
# session.commit()
# ########## 修改 ##########
# session.query(User).filter(User.id > 2).update({‘cluster_id‘ : 0})
# session.commit()
# ########## 查 ##########
# ret = session.query(User).filter_by(name=‘sb‘).first()
# ret = session.query(User).filter_by(name=‘sb‘).all()
# print ret
# ret = session.query(User).filter(User.name.in_([‘sb‘,‘bb‘])).all()
# print ret
# ret = session.query(User.name.label(‘name_label‘)).all()
# print ret,type(ret)
# ret = session.query(User).order_by(User.id).all()
# print ret
# ret = session.query(User).order_by(User.id)[1:3]
# print ret
# session.commit()
1、基本使用
CREATE TABLE user (
id
INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(
32
),
password VARCHAR(
64
),
PRIMARY KEY (
id
)
)
这只是最简单的sql表,如果再加上外键关联什么的,一般程序员的脑容量是记不住那些sql语句的,于是有了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:zyw@123@192.168.20.219/lzl"
,
encoding
=
"utf-8"
,echo
=
True
)
#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)
#创建表结构
# 打印输出
# 2016-10-26 08:42:02,619 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE ‘sql_mode‘
# 2016-10-26 08:42:02,619 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,622 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
# 2016-10-26 08:42:02,622 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,624 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = ‘utf8‘ and `Collation` = ‘utf8_bin‘
# 2016-10-26 08:42:02,624 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,649 INFO sqlalchemy.engine.base.Engine SELECT CAST(‘test plain returns‘ AS CHAR(60)) AS anon_1
# 2016-10-26 08:42:02,649 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,651 INFO sqlalchemy.engine.base.Engine SELECT CAST(‘test unicode returns‘ AS CHAR(60)) AS anon_1
# 2016-10-26 08:42:02,651 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,652 INFO sqlalchemy.engine.base.Engine SELECT CAST(‘test collated returns‘ AS CHAR CHARACTER SET utf8)
# COLLATE utf8_bin AS anon_1
# 2016-10-26 08:42:02,652 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,655 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
# 2016-10-26 08:42:02,655 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,657 INFO sqlalchemy.engine.base.Engine ROLLBACK
# 2016-10-26 08:42:02,660 INFO sqlalchemy.engine.base.Engine
# CREATE TABLE user (
# id INTEGER NOT NULL AUTO_INCREMENT,
# name VARCHAR(32),
# password VARCHAR(64),
# PRIMARY KEY (id)
# )
#
#
# 2016-10-26 08:42:02,660 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,904 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.01
sec)
2、创建数据
最基本的表我们创建好了,那我们开始用orm创建一条数据试试
# 创建表数据
from
sqlalchemy
import
create_engine
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
String,Integer,Column
from
sqlalchemy.orm
import
sessionmaker
engine
=
create_engine(
"mysql+pymysql://root:zyw@123@192.168.20.219/lzl"
,
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_class()
#生成Session实例
user_obj
=
User(name
=
"lzl"
,password
=
"123456"
)
#生成你要创建的数据对象
print
(user_obj.name,user_obj.
id
)
#此时还没创建对象呢,不信你打印一下id发现还是None
Session.add(user_obj)
#把要创建的数据对象添加到这个session里, 一会统一创建
print
(user_obj.name,user_obj.
id
)
#此时也依然还没创建
Session.commit()
#现此才统一提交,创建数据
3、查询数据
# 查询
from
sqlalchemy
import
create_engine
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
String,Integer,Column
from
sqlalchemy.orm
import
sessionmaker
engine
=
create_engine(
"mysql+pymysql://root:zyw@123@192.168.20.219/lzl"
,
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
))
Session_class
=
sessionmaker(bind
=
engine)
#Session_class现在不是实例,而是类
Session
=
Session_class()
#生成Session实例
my_user
=
Session.query(User).filter_by(name
=
"lzl"
).first()
print
(my_user)
#my_user此时是一个对象
#<__main__.User object at 0x03EFC6D0>
print
(my_user.
id
,my_user.name,my_user.password)
多条件查询:
from
sqlalchemy
import
create_engine
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
String,Integer,Column
from
sqlalchemy.orm
import
sessionmaker
from
sqlalchemy
import
func
engine
=
create_engine(
"mysql+pymysql://root:zyw@123@192.168.20.219/lzl"
,
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
"<User(name=‘%s‘, password=‘%s‘)>"
%
(
self
.name,
self
.password)
Session_class
=
sessionmaker(bind
=
engine)
#Session_class现在不是实例,而是类
Session
=
Session_class()
#生成Session实例
objs
=
Session.query(User).
filter
(User.
id
>
3
).
filter
(User.
id
<
8
).
all
()
print
(objs)
统计:
# 统计
from
sqlalchemy
import
create_engine
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
String,Integer,Column
from
sqlalchemy.orm
import
sessionmaker
from
sqlalchemy
import
func
engine
=
create_engine(
"mysql+pymysql://root:zyw@123@192.168.20.219/lzl"
,
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
"<User(name=‘%s‘, password=‘%s‘)>"
%
(
self
.name,
self
.password)
Session_class
=
sessionmaker(bind
=
engine)
#Session_class现在不是实例,而是类
Session
=
Session_class()
#生成Session实例
分组:
# 分组
from
sqlalchemy
import
create_engine
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
String,Integer,Column
from
sqlalchemy.orm
import
sessionmaker
from
sqlalchemy
import
func
engine
=
create_engine(
"mysql+pymysql://root:zyw@123@192.168.20.219/lzl"
,
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
"<User(name=‘%s‘, password=‘%s‘)>"
%
(
self
.name,
self
.password)
Session_class
=
sessionmaker(bind
=
engine)
#Session_class现在不是实例,而是类
Session
=
Session_class()
#生成Session实例
print
(Session.query(func.count(User.name),User.name).group_by(User.name).
all
())
4、修改
# 修改
from
sqlalchemy
import
create_engine
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
String,Integer,Column
from
sqlalchemy.orm
import
sessionmaker
engine
=
create_engine(
"mysql+pymysql://root:zyw@123@192.168.20.219/lzl"
,
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
__str__(
self
):
return
"<User(name=‘%s‘, password=‘%s‘)>"
%
(
self
.name,
self
.password)
Session_class
=
sessionmaker(bind
=
engine)
#Session_class现在不是实例,而是类
人气教程排行
-
229次
1
oracle 用户解锁和修改用户密码
-
229次
2
解决:安装SQL Server 2008 Native Client遇到错误(在Navicat premium新建sqlserver连接时 需要):An error occurred during ...HRESULT: 0x80070422(注意尾部的错误号)
-
229次
3
rocksdb 编译步骤
-
229次
4
adb命令开关蓝牙及NFC
-
229次
5
解决数据库连接错误 您在wp-config.php文件中提供的数据库用户名和密码可能不正确,或者无法连接到localhost上的数据库服务器,这意味着您的主机数据库服务器已停止工作。
-
229次
6
使用java自动填充,实现mysql的创建修改时间的自动填充
-
229次
7
Linux mount挂载磁盘报错 mount: wrong fs type, bad option, bad superblock on /dev/vdb
-
228次
8
关于mysql创建数据库中字符集和排序规则的选择
-
228次
9
Linux设置Mysql开机自启动服务
-
227次
10
SQL2008:WITH MOVE 子句可用于重新定位一个或多个文件
-
227次
11
图数据库Neo4j在GIS系统的应用
-
227次
12
SpringBoot配置 druid 数据源配置 慢SQL记录
-
227次
13
SQL state [72000]; error code [1013]; ORA-03111: 通信通道收到中断; java.sql.SQLException: ORA-01745: 无效的主机/绑定变量名;java.sql.SQLException: ORA-01013: 用户请求取消当前的操作
-
227次
14
docker-compose启动MySQL并配置远程登录
-
225次
15
Mysql安装(for mac)
-
225次
16
kettle将图片转换至二进制存储至数据库
-
225次
17
adb的安装及配置
-
224次
18
使用PHP控制MODBUS-RTU设备
-
224次
19
MySQL 查询时间差值大于某一个值的 记录
-
224次
20
MongoDB内存配置 --wiredTigerCacheSizeGB