SQLAchemy、MYSQL、PYMYSQL
什么是数据库?
个人理解：将一些有关系的数据组织在一起，并建立一定规则便于查询、修改、删除我们关系的数据仓库。
常用的数据库：mysql、oracle、sqlserver等。
接下来我们主要讲mysql.
1)安装：
linux环境
下载地址：
1 http://dev.mysql.com/downloads/mysql/
或者：
1 yum install -y mysql-server
默认情况，yum安装之后数据库的账号：root 无密码。
1 mysql -u root
2）operation
一、基本操作：
1、查看数据库：
1 mysql> show databases; 2 +--------------------+ 3 | Database | 4 +--------------------+ 5 | information_schema | 6 | evil | 7 | homework | 8 | mysql | 9 | test | 10 +--------------------+ 11 5 rows in set (0.01 sec
默认数据库：
1 mysql - 用户权限相关数据 2 test - 用于用户测试数据 3 information_schema - MySQL本身架构相关数据
2、切换或者使用数据库：use +dbname
1 mysql> use evil; 2 Reading table information for completion of table and column names 3 You can turn off this feature to get a quicker startup with -A 4 5 Database changed
3、显示当前数据库下所有表：
1 mysql> show tables; 2 +----------------+ 3 | Tables_in_evil | 4 +----------------+ 5 | host | 6 | service | 7 | user | 8 +----------------+ 9 3 rows in set (0.00 sec)
4、用户授权：刷新权限：flush privileges;
1 创建用户 2 create user '用户名'@'IP地址' identified by '密码'; 3 删除用户 4 drop user '用户名'@'IP地址'; 5 修改用户 6 rename user '用户名'@'IP地址' to '新用户名'@'IP地址'; 7 修改密码 8 set password for '用户名'@'IP地址' = Password('新密码')
创建用户：
1 mysql> create user 'test'@'192.168.1.100' identified by '123'; 2 Query OK, 0 rows affected (0.00 sec)
删除用户：
1 drop user 'test'@'192.168.1.100'; 2 Query OK, 0 rows affected (0.00 sec)
修改用户：
1 mysql> rename user 'test'@'192.168.1.100' to 'test_1'@'192.168.1.101'; 2 Query OK, 0 rows affected (0.00 sec)
修改用户密码：
1 mysql> set password for 'test_1'@'192.168.1.101' = Password('321'); 2 Query OK, 0 rows affected (0.00 sec
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）
在数据库mysql里User表里有我们刚才配置的用户信息。
1 mysql> use mysql; 2 Database changed 3 mysql> select * from user where User="test_1";
授权管理：
1 show grants for '用户'@'IP地址' -- 查看权限 2 grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权 3 revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
权限类型：
1 all privileges 除grant外的所有权限 2 select 仅查权限 3 select,insert 查和插入权限 4 ... 5 usage 无访问权限 6 alter 使用alter table 7 alter routine 使用alter procedure和drop procedure 8 create 使用create table 9 create routine 使用create procedure 10 create temporary tables 使用create temporary tables 11 create user 使用create user、drop user、rename user和revoke all privileges 12 create view 使用create view 13 delete 使用delete 14 drop 使用drop table 15 execute 使用call和存储过程 16 file 使用select into outfile 和 load data infile 17 grant option 使用grant 和 revoke 18 index 使用index 19 insert 使用insert 20 lock tables 使用lock table 21 process 使用show full processlist 22 select 使用select 23 show databases 使用show databases 24 show view 使用show view 25 update 使用update 26 reload 使用flush 27 shutdown 使用mysqladmin shutdown(关闭MySQL) 28 super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 29 replication client 服务器位置的访问 30 replication slave 由复制从属使用 31
对于数据库权限：
1 对于目标数据库以及内部其他： 2 数据库名.* 数据库中的所有 3 数据库名.表 指定数据库中的某张表 4 数据库名.存储过程 指定数据库中的存储过程 5 *.* 所有数据库
对于用户和访问IP权限：
1 用户名@IP地址 用户只能在改IP下才能访问 2 用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意) 3 用户名@% 用户可以再任意IP下访问(默认IP地址为%)
举例：
1 grant all privileges on db1.tb1 TO '用户名'@'IP' 2 3 grant select on db1.* TO '用户名'@'IP' 4 5 grant select,insert on *.* TO '用户名'@'IP' 6 7 revoke select on db1.tb1 from '用户名'@'IP'
二：操作表
创建表：
1 create table 表名( 2 列名 类型 是否可以为空， 3 列名 类型 是否可以为空 4 )
举例：
1 mysql> create table test (id int not null, name varchar(20)); 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql> desc test; 5 +-------+-------------+------+-----+---------+-------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +-------+-------------+------+-----+---------+-------+ 8 | id | int(11) | NO | | NULL | | 9 | name | varchar(20) | YES | | NULL | | 10 +-------+-------------+------+-----+---------+-------+ 11 2 rows in set (0.00 sec)
1 是否可空，null表示空，非字符串 2 not null - 不可空 3 null - 可空
默认值：
1 默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值 2 create table tb1( 3 nid int not null defalut 2, 4 num int not null 5 )
自增：
1 自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列） 2 create table tb1( 3 nid int not null auto_increment primary key, 4 num int null 5 ) 6 或 7 create table tb1( 8 nid int not null auto_increment, 9 num int null, 10 index(nid) 11 ) 12 注意：1、对于自增列，必须是索引（含主键）。 13 2、对于自增可以设置步长和起始值 14 show session variables like 'auto_inc%'; 15 set session auto_increment_increment=2; 16 set session auto_increment_offset=10; 17 18 shwo global variables like 'auto_inc%'; 19 set global auto_increment_increment=2; 20 set global auto_increment_offset=10; 21
设置主键：
1 主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。 2 create table tb1( 3 nid int not null auto_increment primary key, 4 num int null 5 ) 6 或 7 create table tb1( 8 nid int not null, 9 num int not null, 10 primary key(nid,num) 11 )
设置外键：
1 外键，一个特殊的索引，只能是指定内容 2 creat table color( 3 nid int not null primary key, 4 name char(16) not null 5 ) 6 7 create table fruit( 8 nid int not null primary key, 9 smt char(32) null , 10 color_id int not null, 11 constraint fk_cc foreign key (color_id) references color(nid) 12 )
补充：2个表设置外键的时候，一表里的设置外键的列值不能出现另一个表里的设置外键的列不存在的值。否则报错。这就是约束。
删除表：
1 mysql> drop table test; 2 Query OK, 0 rows affected (0.00 sec)
清空表：
1 mysql> truncate table test; 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql> delete from test; 5 Query OK, 0 rows affected (0.00 sec)
修改表：
1 添加列：alter table 表名 add 列名 类型 2 删除列：alter table 表名 drop column 列名 3 修改列： 4 alter table 表名 modify column 列名 类型; -- 类型 5 alter table 表名 change 原列名 新列名 类型; -- 列名，类型 6 7 添加主键： 8 alter table 表名 add primary key(列名); 9 删除主键： 10 alter table 表名 drop primary key; 11 alter table 表名 modify 列名 int, drop primary key; 12 13 添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段); 14 删除外键：alter table 表名 drop foreign key 外键名称 15 16 修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; 17 删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
基本数据类型：分字符串、时间和数值。http://www.runoob.com/mysql/mysql-data-types.html
三：对表里的数据的操作：
1、增：
1 insert into 表 (列名,列名...) values (值,值,值...) 2 insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) 3 insert into 表 (列名,列名...) select (列名,列名...) from 表
1 mysql> select * from test; 2 Empty set (0.00 sec) 3 4 mysql> insert into test (id,name) values(1,"evil"); 5 Query OK, 1 row affected (0.00 sec) 6 7 mysql> select * from test; 8 +----+------+ 9 | id | name | 10 +----+------+ 11 | 1 | evil | 12 +----+------+ 13 1 row in set (0.00 sec)
2、删除表格里内的数据：
1 delete from 表 2 delete from 表 where id＝1 and name＝'alex'
1 mysql> delete from test where id =1 and name="evil"; 2 Query OK, 1 row affected (0.00 sec)
3、修改
1 update 表 set name ＝ 'alex' where id>1
1 mysql> update test set name="jack" where id =1; 2 Query OK, 1 row affected (0.00 sec) 3 Rows matched: 1 Changed: 1 Warnings: 0 4 5 mysql> select * from test; 6 +----+------+ 7 | id | name | 8 +----+------+ 9 | 1 | jack | 10 +----+------+ 11 1 row in set (0.00 sec)
4、查询：
1 select * from 表 2 select * from 表 where id > 1 3 select nid,name,gender as gg from 表 where id > 1
1 mysql> select * from test where id =1; 2 +----+------+ 3 | id | name | 4 +----+------+ 5 | 1 | jack | 6 +----+------+ 7 1 row in set (0.00 sec)
其他的一些操作：
1 1、条件 2 select * from 表 where id > 1 and name != 'alex' and num = 12; 3 4 select * from 表 where id between 5 and 16; 5 6 select * from 表 where id in (11,22,33) 7 select * from 表 where id not in (11,22,33) 8 select * from 表 where id in (select nid from 表) 9 10 2、通配符 11 select * from 表 where name like 'ale%' - ale开头的所有（多个字符串） 12 select * from 表 where name like 'ale_' - ale开头的所有（一个字符） 13 14 3、限制 15 select * from 表 limit 5; - 前5行 16 select * from 表 limit 4,5; - 从第4行开始的5行 17 select * from 表 limit 5 offset 4 - 从第4行开始的5行 18 19 4、排序 20 select * from 表 order by 列 asc - 根据 “列” 从小到大排列 21 select * from 表 order by 列 desc - 根据 “列” 从大到小排列 22 select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序 23 24 5、分组 25 select num from 表 group by num 26 select num,nid from 表 group by num,nid 27 select num,nid from 表 where nid > 10 group by num,nid order nid desc 28 select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid 29 30 select num from 表 group by num having max(id) > 10 31 32 特别的：group by 必须在where之后，order by之前 33 34 6、连表 35 无对应关系则不显示 36 select A.num, A.name, B.name 37 from A,B 38 Where A.nid = B.nid 39 40 无对应关系则不显示 41 select A.num, A.name, B.name 42 from A inner join B 43 on A.nid = B.nid 44 45 A表所有显示，如果B中无对应关系，则值为null 46 select A.num, A.name, B.name 47 from A left join B 48 on A.nid = B.nid 49 50 B表所有显示，如果B中无对应关系，则值为null 51 select A.num, A.name, B.name 52 from A right join B 53 on A.nid = B.nid 54 55 7、组合 56 组合，自动处理重合 57 select nickname 58 from A 59 union 60 select name 61 from B 62 63 组合，不处理重合 64 select nickname 65 from A 66 union all 67 select name 68 from B
补充：
mysql中的主键必须设置自增属性吗？
不是的 。
相反：设置自增属性的列必须是主键 或者加UNIQUE索引
主键是有唯一性的 即不可以重复输入相同的值
索引就是查找数据的一种特殊结构。比如：书籍的目录。
pymsyql
pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。
1）安装：
pip3 install pymysql
2）执行sql
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import pymysql 4 5 # 创建连接 6 conn = pymysql.connect(host='192.168.1.104', port=3306, user='root', passwd='', db='test') 7 # 创建游标 8 cursor = conn.cursor() 9 10 # 执行SQL，并返回收影响行数 11 effect_row = cursor.execute("update test set name = 'ok' where id=1") 12 13 # 提交，不然无法保存新建或者修改的数据 14 conn.commit() 15 16 # 关闭游标 17 cursor.close() 18 # 关闭连接 19 conn.close() 20 21 #执行结果 22 mysql> select * from test where id =1; 23 +----+------+ 24 | id | name | 25 +----+------+ 26 | 1 | ok | 27 +----+------+ 28 1 row in set (0.00 sec)
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import pymysql 4 5 # 创建连接 6 conn = pymysql.connect(host='192.168.1.104', port=3306, user='root', passwd='', db='test') 7 # 创建游标 8 cursor = conn.cursor() 9 10 # 执行SQL，并返回收影响行数 11 effect_row = cursor.execute("update test set name = 'cc' where id=%s", (1,)) 12 # 执行SQL，并返回受影响行数 13 effect_row = cursor.executemany("insert into test(id,name)values(%s,%s)", [(2,"tom"),(3,"co")]) 14 15 # 提交，不然无法保存新建或者修改的数据 16 conn.commit() 17 18 # 关闭游标 19 cursor.close() 20 # 关闭连接 21 conn.close() 22 23 #执行结果： 24 25 mysql> select * from test; 26 +----+------+ 27 | id | name | 28 +----+------+ 29 | 1 | cc | 30 | 2 | tom | 31 | 3 | co | 32 +----+------+ 33 3 rows in set (0.00 sec)
3）获取新创自增id
创建自增表：
1 mysql> create table user (id int auto_increment,name varchar(20),primary key(id)); 2 Query OK, 0 rows affected (0.02 sec)
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import pymysql 4 5 conn = pymysql.connect(host='192.168.1.104', port=3306, user='root', passwd='', db='test') 6 cursor = conn.cursor() 7 cursor.executemany("insert into user(name)values(%s)", [("tom")]) 8 conn.commit() 9 cursor.close() 10 conn.close() 11 12 # 获取最新自增ID 13 new_id = cursor.lastrowid 14 print(new_id) 15 16 1
1 mysql> select * from user; 2 +----+------+ 3 | id | name | 4 +----+------+ 5 | 1 | tom | 6 +----+------+ 7 1 row in set (0.00 sec
4)获取查询数据：
1 # -*- coding:utf-8 -*- 2 import pymysql 3 4 conn = pymysql.connect(host='192.168.1.104', port=3306, user='root', passwd='', db='test') 5 cursor = conn.cursor() 6 cursor.execute("select * from user") 7 8 # 获取第一行数据 9 row_1 = cursor.fetchone() 10 11 # 获取前n行数据 12 # row_2 = cursor.fetchmany(3) 13 # 获取所有数据 14 # row_3 = cursor.fetchall() 15 print(row_1) 16 conn.commit() 17 cursor.close() 18 conn.close() 19 (1, 'tom')
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
- cursor.scroll(1,mode='relative') # 相对当前位置移动
- cursor.scroll(2,mode='absolute') # 相对绝对位置移动
5）fetch数据类型：（不懂）
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import pymysql 4 5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') 6 7 # 游标设置为字典类型 8 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) 9 r = cursor.execute("call p1()") 10 11 result = cursor.fetchone() 12 13 conn.commit() 14 cursor.close() 15 conn.close()
SQLALchemy
介绍：
SQLAlchemy是python的一种ORM框架，他将类和对象映射成相应的sql语句，调用DBAPI（pytmysql,mysql 等。一系列的第三方模块。）来操作数据库。
Dialect：根据你写连接串挑选那个DBAPI（连接mysql还是oracle）进行连接数据库。
格式：数据库类型+数据库连接驱动：//用户名：用户密码@数据库IP【数据库端口】/数据库名称。``dialect[+driver]://user:password@host/dbname[?key=value..]``
1 MySQL-Python 2 mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> 3 4 pymysql 5 mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] 6 7 MySQL-Connector 8 mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> 9 10 cx_Oracle 11 oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 12 13 更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html
NOTE：
1 在python2.7的时候使用：mysqldb.在python3.5中使用pymysql. 2 在创建连接的时候需要注意： 3 1）访问的数据库的IP有权限访问数据库服务器。 4 GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100'WITH GRANT OPTION; 5 其中192.168.1.100是访问数据库的IP。 6 2）连接的数据库存在。 7 # mysql -u root -p 8 Enter password: 9 Welcome to the MySQL monitor. Commands end with ; or \g. 10 Your MySQL connection id is 57 11 Server version: 5.1.73 Source distribution 12 13 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 14 15 Oracle is a registered trademark of Oracle Corporation and/or its 16 affiliates. Other names may be trademarks of their respective 17 owners. 18 19 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 20 21 mysql> show databases; 22 +--------------------+ 23 | Database | 24 +--------------------+ 25 | information_schema | 26 | homework | 27 | mysql | 28 | test | 29 +--------------------+ 30 4 rows in set (0.00 sec)
1）establish db（连接数据库）：
1 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=5)
解释：
1 create_engine()创建数据库连接引擎对象。``dialect[+driver]://user:password@host/dbname[?key=value..]``第一个参数是字符串，说明连接数据库的DBAPI,一个是访问的数据库的url。 2 dialect：is a database name such as ``mysql``, ``oracle``，``postgresql``。 3 ``driver``：the name of a DBAPI, such as:pymysql,`cx_oracle`` and so on. 4 参数:encoding表示数据库编码，默认是UTF-8，可以指定编码：encoding="gbk" 5 max_overflow.数据库连接池的大小。默认是5.max_overflow=10. 6 其他的参数查看create_engine函数的文档。
7 echo参数如果等于True的时候输出创建数据库日志。默认是关闭False.
connection Poolling:连接池。
Engine：引擎。（类似rabbitMQ的Chanel，mysql的游标）
有如上3个部分我们就可以连接数据库并进行相关操作。（使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。）
SQLALchemy数据结构：
1)在SQLALchemy.sql包里有我们经常操作的sql语句部分：
from .sql import ( insert, join, select, ..... }
2)在SQLALchemy.types,包含操作的数据类型：
1 from .types import ( 2 Enum, 3 FLOAT, 4 Float, 5 INT, 6 INTEGER, 7 Integer, 8 ..... 9 }
3）在SQLALchemy.schema里包含数据库的字段：
1 from .schema import ( 2 CheckConstraint, 3 Column, 4 ColumnDefault, 5 Constraint, 6 DefaultClause, 7 FetchedValue, 8 ForeignKey, 9 ForeignKeyConstraint, 10 Index, 11 MetaData, 12 }
2)创建表结构
使用 Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 进行数据库操作。Engine使用Schema Type创建一个特定的结构对象，之后通过SQL Expression Language将该对象转换成SQL语句，然后通过 ConnectionPooling 连接数据库，再然后通过 Dialect 执行SQL，并获取结果
1 from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey 2 import pymysql 3 #创建数据库连接 4 engine = create_engine("mysql+pymysql://root@192.168.1.105:3306/homework", max_overflow=5,echo=True) 5 # 创建MetaData对象。 6 metadata = MetaData() 7 # 定义表 8 user = Table('meide', metadata,#创建表。 9 Column('id', Integer, primary_key=True),#创建列属性。 10 Column('name', String(20)), 11 ) 12 13 color = Table('color', metadata, 14 Column('id', Integer, primary_key=True), 15 Column('name', String(20)), 16 ) 17 # 创建数据表，如果数据表存在，则忽视 18 metadata.create_all(engine)#创建表。 19 20 21 mysql> show tables; 22 +--------------------+ 23 | Tables_in_homework | 24 +--------------------+ 25 | color | 26 | favor | 27 | group | 28 | meide | 29 | name | 30 | person | 31 | server | 32 | servertogroup | 33 | ts_test | 34 | users | 35 +--------------------+ 36 10 rows in set (0.00 sec)
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 4 from sqlalchemy import create_engine 5 6 7 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=5) 8 9 engine.execute( 10 "INSERT INTO ts_test (name,age) VALUES ('jack2',22)" 11 ) 12 13 result = engine.execute('select * from ts_test') 14 row=result.fetchall() 15 print(row)
ORM只能创建表、删除表但是不能帮你修改表结构，需要第三方模块操作。
通过SQLALchemy创建表结构：
创建单表结构：
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index#导入列数据类型字段。 5 from sqlalchemy.orm import sessionmaker, relationship#导入会话函数、 6 from sqlalchemy import create_engine 7 8 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=15)#创建数据库引擎，通过连接池获取数据库的连接。数据库连接池数量为：15，默认值是5. 9 #创建sqlorm基类。（为声明的类定义基类。） 10 Base = declarative_base() 11 12 # 创建一个类继承Base类。 13 class Users(Base): 14 __tablename__ = 'users'#创建表名字。 15 id = Column(Integer, primary_key=True)#创建整型的列并设为主键。 16 name = Column(String(32))#创建字符串类型的数据。 17 extra = Column(String(16)) 18 19 __table_args__ = ( 20 UniqueConstraint('id', 'name', name='uix_id_name'), 21 Index('ix_id_name', 'name', 'extra'), 22 )#创建联合索引。 23 24 def __repr__(self): 25 return "%s-%s" %(self.id, self.name) 26 27 Base.metadata.create_all(engine)##执行该函数，他就会执行所有Base所有的子类。调用我们定义类并创建相应的表结构。
一对多创建表结构：
1 from sqlalchemy.ext.declarative import declarative_base 2 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 3 from sqlalchemy.orm import sessionmaker, relationship 4 from sqlalchemy import create_engine 5 6 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=5)#创建数据库引擎，通过连接池获取数据库的连接。默认值是5. 7 8 Base = declarative_base() #自定义的类声明sqlorm基类。 9 class Favor(Base): 10 __tablename__ = 'favor'#表名字。 11 nid = Column(Integer, primary_key=True)#设置列的属性：整型，主键。 12 caption = Column(String(50), default='red', unique=True)#设置默认值。并设置唯一性约束。此列可以为空但是不能不能重复值。主键不可以为空。 13 14 def __repr__(self): 15 return "%s-%s" %(self.nid, self.caption)###这个是什么意思？ 16 17 class Person(Base): 18 __tablename__ = 'person' 19 nid = Column(Integer, primary_key=True) 20 name = Column(String(32), index=True, nullable=True)#该列为索引列，可以为null。 21 favor_id = Column(Integer, ForeignKey("favor.nid")) 22 # 与生成表结构无关，仅用于查询方便 23 favor = relationship("Favor", backref='pers') 24 Base.metadata.create_all(engine)#执行我们类中定义的表结构。
多对多：
1 from sqlalchemy.ext.declarative import declarative_base 2 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 3 from sqlalchemy.orm import sessionmaker, relationship 4 from sqlalchemy import create_engine 5 6 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=5)#创建数据库引擎，通过连接池获取数据库的连接。默认值是5. 7 8 Base = declarative_base()#为自定义的类声明sqlorm基类。 9 class Group(Base): 10 __tablename__ = 'group' 11 id = Column(Integer, primary_key=True) 12 name = Column(String(64), unique=True, nullable=False)#唯一性约束、不能为空。唯一性约束的列值可以为空，但是这里设置：nullable=False 不能为空。 13 port = Column(Integer, default=22)#设置列的默认值以及数据类型。 14 15 16 class Server(Base): 17 __tablename__ = 'server' 18 19 id = Column(Integer, primary_key=True, autoincrement=True)#设置一个整型自增的主键。 20 hostname = Column(String(64), unique=True, nullable=False)#设置唯一性约束，不为null。 21 22 23 class ServerToGroup(Base): 24 __tablename__ = 'servertogroup' 25 nid = Column(Integer, primary_key=True, autoincrement=True) 26 server_id = Column(Integer, ForeignKey('server.id'))#和sever的id列设置外键。 27 group_id = Column(Integer, ForeignKey('group.id'))#和group的表的id列设置外键。 28 Base.metadata.create_all(engine)#执行Base我们定义的子类。
ORM只能创建所有表和删除所有表，需要修改表结构，需要借助第三方模块。 删除表：Base.metadata.drop_all(engine)
通过SQLALchemy对表进行增、删、改、查操作。
表里增加数据：使用 add和addall方法进行
增（insert）：
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 5 from sqlalchemy.orm import sessionmaker, relationship 6 from sqlalchemy import create_engine 7 8 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=5,echo=True) 9 10 Base = declarative_base() 11 12 # 创建单表 13 class Users(Base): 14 __tablename__ = 'users'#创建表名字。 15 id = Column(Integer, primary_key=True)#主键。 16 name = Column(String(32)) 17 extra = Column(String(16)) 18 19 __table_args__ = ( 20 UniqueConstraint('id', 'name', name='uix_id_name'), 21 Index('ix_id_name', 'name', 'extra'), 22 )#创建联合索引。 23 if __name__ == '__main__': 24 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 25 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话的类。 26 session=Session_cls()#创建会话类的对象,可以把他当做数据库当前连接。 27 obj=Users(name="ack",extra="beautifiul girl")#插入数据。 28 session.add(obj)#调用会话对象的add方法添我们项插入的字段。 29 #session.rollback()#回滚，只要没提交，就可以回滚。 30 session.commit()#提交。 31 32 mysql> select * from users; 33 +----+------+-----------------+ 34 | id | name | extra | 35 +----+------+-----------------+ 36 | 1 | ack | beautifiul girl | 37 +----+------+-----------------+ 38 1 row in set (0.00 sec)
添加多条数据：add_all内部是for循环我们传入元组的多个数据，调用add方法插入数据库的。
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 5 from sqlalchemy.orm import sessionmaker, relationship 6 from sqlalchemy import create_engine 7 8 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=5,echo=True) 9 10 Base = declarative_base() 11 12 # 创建单表 13 class Users(Base): 14 __tablename__ = 'users'#创建表名字。 15 id = Column(Integer, primary_key=True)#主键。 16 name = Column(String(32)) 17 extra = Column(String(16)) 18 19 __table_args__ = ( 20 UniqueConstraint('id', 'name', name='uix_id_name'), 21 Index('ix_id_name', 'name', 'extra'), 22 )#创建联合索引。 23 if __name__ == '__main__': 24 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 25 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 26 session=Session_cls()#创建会话类的对象,可以把他当做数据库当前连接。 27 obj=Users(name="evil",extra="handsome man")#创建我们构建表的类对象。 28 obj_2=Users(name="tom",extra="brave man")#创建我们构建表的类对象。 29 obj_3=Users(name="jack",extra="diaosi ")#创建我们构建表的类对象。 30 session.add_all((obj, 31 obj_2, 32 obj_3 33 ))#调用会话对象的ad_dall方法添我们项插入的字段。 34 #session.rollback()#回滚，只要没提交，就可以回滚。 35 session.commit()#提交。 36 37 38 mysql> select * from users; 39 +----+------+-----------------+ 40 | id | name | extra | 41 +----+------+-----------------+ 42 | 1 | ack | beautifiul girl | 43 | 2 | evil | handsome man | 44 | 3 | tom | brave man | 45 | 4 | jack | diaosi | 46 +----+------+-----------------+ 47 4 rows in set (0.00 sec)
删(delete)：形式：query(classname).filer(condition).delete()
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 5 from sqlalchemy.orm import sessionmaker, relationship 6 from sqlalchemy import create_engine 7 8 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=5,echo=True) 9 10 Base = declarative_base() 11 12 # 创建单表 13 class Users(Base): 14 __tablename__ = 'users'#创建表名字。 15 id = Column(Integer, primary_key=True)#主键。 16 name = Column(String(32)) 17 extra = Column(String(16)) 18 19 __table_args__ = ( 20 UniqueConstraint('id', 'name', name='uix_id_name'), 21 Index('ix_id_name', 'name', 'extra'), 22 )#创建联合索引。 23 if __name__ == '__main__': 24 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 25 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 26 session=Session_cls()#创建会话类的对象，可以理解当前数据库连接。 27 session.query(Users).filter(Users.name=="jack").delete()#删除name=jack的行。 28 #session.rollback()#回滚，只要没提交，就可以回滚。 29 session.commit()#提交。 30 mysql> select * from users; 31 +----+------+-----------------+ 32 | id | name | extra | 33 +----+------+-----------------+ 34 | 1 | ack | beautifiul girl | 35 | 21 | tom | brave man | 36 | 20 | evil | handsome man | 37 +----+------+-----------------+ 38 3 rows in set (0.00 sec)
删除id>20的列：
1 if __name__ == '__main__': 2 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 3 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 4 session=Session_cls()#创建会话类的对象。 5 session.query(Users).filter(Users.id >20).delete() 6 #session.rollback()#回滚，只要没提交，就可以回滚。 7 session.commit()#提交。 8 mysql> select * from users; 9 +----+------+-----------------+ 10 | id | name | extra | 11 +----+------+-----------------+ 12 | 1 | ack | beautifiul girl | 13 | 20 | evil | handsome man | 14 +----+------+-----------------+ 15 2 rows in set (0.00 sec)
如果满足多个条件的时候，条件之间用逗号（，）隔开，而不是用and。还需要注意的是：条件判断的时候（filter）是python语言操作对象还是直接列的操作。列操作（django）是=而python对象操作相等需要==
错误的写法：
1 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 2 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 3 session=Session_cls()#创建会话类的对象。 4 session.query(Users).filter(Users.name=="tom" and Users.id ==30).delete() 5 #session.rollback()#回滚，只要没提交，就可以回滚。 6 session.commit()#提交。
这样SQLALchemy只删除符合 User.name=="tom" 条件的数据。后面的id=30没有生效。
正确的写法：
1 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 2 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 3 session=Session_cls()#创建会话类的对象。 4 session.query(Users).filter(Users.name=="tom" , Users.id ==30).delete()#多个条件删除用逗号隔开。 5 #session.rollback()#回滚，只要没提交，就可以回滚。 6 session.commit()#提交。
改(update)：
用：session.query(类名).filter(条件).update({"列名":"值"})注意这个是字典。或者：session.query(类名).filter(条件).update({类名.列名:"值"})
1 #没修改之前： 2 mysql> select * from users; 3 +----+------+-----------------+ 4 | id | name | extra | 5 +----+------+-----------------+ 6 | 1 | ack | beautifiul girl | 7 | 24 | test | brave man | 8 | 27 | test | brave man | 9 +----+------+-----------------+ 10 #用：session.query(对象名).filter(条件).update({"列名":"值"})注意这个是字典。 11 12 #!/usr/bin/env python 13 # -*- coding:utf-8 -*- 14 from sqlalchemy.ext.declarative import declarative_base 15 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 16 from sqlalchemy.orm import sessionmaker, relationship 17 from sqlalchemy import create_engine 18 19 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=5,echo=True) 20 21 Base = declarative_base() 22 23 # 创建单表 24 class Users(Base): 25 __tablename__ = 'users'#创建表名字。 26 id = Column(Integer, primary_key=True)#主键。 27 name = Column(String(32)) 28 extra = Column(String(16)) 29 30 __table_args__ = ( 31 UniqueConstraint('id', 'name', name='uix_id_name'), 32 Index('ix_id_name', 'name', 'extra'), 33 )#创建联合索引。 34 if __name__ == '__main__': 35 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 36 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 37 session=Session_cls()#创建会话类的对象。 38 session.query(Users).filter(Users.id > 24).update({"name":"co"})#更新。 39 #session.rollback()#回滚，只要没提交，就可以回滚。 40 session.commit()#提交。 41 42 mysql> select * from users; 43 +----+------+-----------------+ 44 | id | name | extra | 45 +----+------+-----------------+ 46 | 1 | ack | beautifiul girl | 47 | 24 | test | brave man | 48 | 27 | co | brave man | 49 +----+------+-----------------+ 50 3 rows in set (0.00 sec)
session.query(类名).filter(条件).update({类名.列名:"值"})我比较喜欢第2种。
1 if __name__ == '__main__': 2 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 3 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 4 session=Session_cls()#创建会话类的对象。 5 session.query(Users).filter(Users.id > 24).update({Users.name:"cc"}) 6 #session.rollback()#回滚，只要没提交，就可以回滚。 7 session.commit()#提交。
在数据后面添加数据。query(Users).filter(condition).update({classname.coulmnname:classname.coulmnname+"add data",},synchronize_session=False)
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 5 from sqlalchemy.orm import sessionmaker, relationship 6 from sqlalchemy import create_engine 7 8 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=5,echo=True) 9 10 Base = declarative_base() 11 12 # 创建单表 13 class Users(Base): 14 __tablename__ = 'users'#创建表名字。 15 id = Column(Integer, primary_key=True)#主键。 16 name = Column(String(32)) 17 extra = Column(String(16)) 18 19 __table_args__ = ( 20 UniqueConstraint('id', 'name', name='uix_id_name'), 21 Index('ix_id_name', 'name', 'extra'), 22 )#创建联合索引。 23 if __name__ == '__main__': 24 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 25 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 26 session=Session_cls()#创建会话类的对象。 27 session.query(Users).filter(Users.id > 20).update({Users.name:Users.name+"good",},synchronize_session=False)#更改id>20的字段。 28 #session.rollback()#回滚，只要没提交，就可以回滚。 29 session.commit()#提交。 30 31 mysql> select * from users; 32 +----+----------+-----------------+ 33 | id | name | extra | 34 +----+----------+-----------------+ 35 | 1 | ack | beautifiul girl | 36 | 24 | testgood | brave man | 37 | 27 | ccgood | brave man | 38 +----+----------+-----------------+ 39 3 rows in set (0.00 sec)
notice：synchronize_session=False：这个不同步会话，如果会话过期的时候，数据库这个字段依然是旧值。防止数据的混乱。
相应的数据进行计算：比如：query(Users).filter(Users.id > 20).update({Users.id:Users.id+100,},synchronize_session="evaluate")
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 5 from sqlalchemy.orm import sessionmaker, relationship 6 from sqlalchemy import create_engine 7 8 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=5,echo=True) 9 10 Base = declarative_base() 11 12 # 创建单表 13 class Users(Base): 14 __tablename__ = 'users'#创建表名字。 15 id = Column(Integer, primary_key=True)#主键。 16 name = Column(String(32)) 17 extra = Column(String(16)) 18 19 __table_args__ = ( 20 UniqueConstraint('id', 'name', name='uix_id_name'), 21 Index('ix_id_name', 'name', 'extra'), 22 )#创建联合索引。 23 if __name__ == '__main__': 24 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 25 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 26 session=Session_cls()#创建会话类的对象。 27 session.query(Users).filter(Users.id > 20).update({Users.id:Users.id+100,},synchronize_session="evaluate")#id列进行计算。 28 #session.rollback()#回滚，只要没提交，就可以回滚。 29 session.commit()#提交。 30 31 32 mysql> select * from users; 33 +-----+----------+-----------------+ 34 | id | name | extra | 35 +-----+----------+-----------------+ 36 | 1 | ack | beautifiul girl | 37 | 124 | testgood | brave man | 38 | 127 | ccgood | brave man | 39 +-----+----------+-----------------+ 40 3 rows in set (0.00 sec)
查（query）：
查询表所有的数据。
注意res=session.query(Users).all()，res返回时User类的对象列表。想获取相应的结果可以用for循环获取。
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 5 from sqlalchemy.orm import sessionmaker, relationship 6 from sqlalchemy import create_engine 7 8 engine = create_engine("mysql+pymysql://root:@192.168.1.105:3306/homework", max_overflow=5) 9 10 Base = declarative_base() 11 12 # 创建单表 13 class Users(Base): 14 __tablename__ = 'users'#创建表名字。 15 id = Column(Integer, primary_key=True)#主键。 16 name = Column(String(32)) 17 extra = Column(String(16)) 18 19 __table_args__ = ( 20 UniqueConstraint('id', 'name', name='uix_id_name'), 21 Index('ix_id_name', 'name', 'extra'), 22 )#创建联合索引。 23 if __name__ == '__main__': 24 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 25 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 26 session=Session_cls()#创建会话类的对象。 27 res=session.query(Users).all()#获取的是User对象的列表，需要for循环相应的字段。 28 for i in res: 29 print(i.name,i.extra) 30 #session.rollback()#回滚，只要没提交，就可以回滚。 31 session.commit()#提交。 32 ack beautifiul girl 33 testgood brave man 34 ccgood brave man
如果不清楚执行的代码转换什么sql语句。可以用：
1 res=session.query(Users).all() 2 q=session.query(Users)#不加all() 3 print(q) 4 SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra 5 FROM users
查找具体的列：res=session.query(Users.name).all()
1 if __name__ == '__main__': 2 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 3 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 4 session=Session_cls()#创建会话类的对象。 5 res=session.query(Users.name).all() 6 for i in res: 7 print(i.name) 8 #session.rollback()#回滚，只要没提交，就可以回滚。 9 session.commit()#提交。 10 ack 11 testgood 12 ccgood
查找所有符合的条件的列： res=session.query(Users).filter_by(name="tom").all()
1 if __name__ == '__main__': 2 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 3 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 4 session=Session_cls()#创建会话类的对象。 5 res=session.query(Users).filter_by(name="tom").all() 6 for i in res: 7 print(i.name,i.extra) 8 #session.rollback()#回滚，只要没提交，就可以回滚。 9 session.commit()#提交。 10 tom brave man 11 tom brave man
查找所有符合条件的数据中的第一列数据：注意返回的不是列表。单个对象。res=session.query(Users).filter_by(name="tom").first()
1 if __name__ == '__main__': 2 Base.metadata.create_all(engine)##调用我们定义类并创建相应的表结构。 3 Session_cls=sessionmaker(bind=engine)#首先绑定我们创建的engine与会话（session）关联。并创建连接数据库会话类。 4 session=Session_cls()#创建会话类的对象。 5 res=session.query(Users).filter_by(name="tom").first() 6 print(res) 7 # for i in res: 8 print(res.name,res.extra) 9 #session.rollback()#回滚，只要没提交，就可以回滚。 10 session.commit()#提交。 11 <__main__.Users object at 0x033FB910> 12 tom brave man
其他的操作：
1 # 条件 2 ret = session.query(Users).filter_by(name='alex').all() 3 ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() 4 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() 5 ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() 6 ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() 7 ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() 8 from sqlalchemy import and_, or_ 9 ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() 10 ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all() 11 ret = session.query(Users).filter( 12 or_( 13 Users.id < 2, 14 and_(Users.name == 'eric', Users.id > 3), 15 Users.extra != "" 16 )).all() 17 18 19 # 通配符 20 ret = session.query(Users).filter(Users.name.like('e%')).all() 21 ret = session.query(Users).filter(~Users.name.like('e%')).all() 22 23 # 限制 24 ret = session.query(Users)[1:2] 25 26 # 排序 27 ret = session.query(Users).order_by(Users.name.desc()).all() 28 ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() 29 30 # 分组 31 from sqlalchemy.sql import func 32 33 ret = session.query(Users).group_by(Users.extra).all() 34 ret = session.query( 35 func.max(Users.id), 36 func.sum(Users.id), 37 func.min(Users.id)).group_by(Users.name).all() 38 39 ret = session.query( 40 func.max(Users.id), 41 func.sum(Users.id), 42 func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() 43 44 # 连表 45 46 ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() 47 48 ret = session.query(Person).join(Favor).all() 49 50 ret = session.query(Person).join(Favor, isouter=True).all() 51 52 53 # 组合 54 q1 = session.query(Users.name).filter(Users.id > 2) 55 q2 = session.query(Favor.caption).filter(Favor.nid < 2) 56 ret = q1.union(q2).all() 57 58 q1 = session.query(Users.name).filter(Users.id > 2) 59 q2 = session.query(Favor.caption).filter(Favor.nid < 2) 60 ret = q1.union_all(q2).all()
更多功能参见文档，猛击这里下载PDF！
学习是一种态度，坚持是质变的利器！
