mysql和SQLAlchemy

mysql和SQLAlchemy

一、MySQL分组查询

1.1 MySQL对数据表进行分组查询（GROUP BY）

 

1、GROUP BY基本语法格式:

GROUP BY关键字可以将查询结果按照某个字段或多个字段进行分组。字段中值相等的为一组。基本的语法格式如下：

GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]

  • 属性名：是指按照该字段的值进行分组。
  • HAVING 条件表达式：用来限制分组后的显示，符合条件表达式的结果将被显示。
  • WITH ROLLUP：将会在所有记录的最后加上一条记录。加上的这一条记录是上面所有记录的总和。

2、GROUP BY联合函数使用：

1）GROUP BY关键字可以和GROUP_CONCAT()函数一起使用。

2）GROUP_CONCAT()函数会把每个分组中指定的字段值都显示出来。

3）同时，GROUP BY关键字通常与集合函数一起使用。集合函数包括COUNT()函数、SUM()函数、AVG()函数、MAX()函数和MIN()函数等。

4）注意：如果GROUP BY不与上述函数一起使用，那么查询结果就是字段取值的分组情况。字段中取值相同的记录为一组，但是只显示该组的第一条记录。

1. 首先执行不带GROUP BY关键字的SELECT语句。如下图所示：

select * from employee;

2. 执行带有GROUP BY关键字的SELECT语句。代码如下：

 

SELECT * FROM employee GROUP BY sex;

+----+------+--------+-----+-----+-------------+

| id | num  | name   | sex | age | homeaddress |

+----+------+--------+-----+-----+-------------+

|  2 | 1001 | 马莉莉 | 女  |  24 | 河南开封    |

|  1 | 1001 | 王冬军 | 男  |  26 | 河南郑州    |

+----+------+--------+-----+-----+-------------+

2 rows in set (0.00 sec)

 

上图中代码执行的结果只显示了两条记录。这两条记录的sex字段的值分别为“女”和“男”。

 

查询结果进行比较，GROUP BY关键字只显示每个分组的一条记录。这说明，GROUP BY关键字单独使用时，只能查询出每个分组的一条记录，这样做的意义不大。

因此，一般在使用集合函数时才使用GROUP BY关键字。

 

1.2 GROUP BY关键字与GROUP_CONCAT()函数一起使用

 

GROUP BY关键字与GROUP_CONCAT()函数一起使用时，每个分组中指定的字段值会全部显示出来。

实例：将employee表按照sex字段进行分组查询。使用GROUP_CONCAT()函数将每个分组的name字段的值显示出来。

SELECT sex,GROUP_CONCAT(name) FROM employee GROUP BY sex;

+-----+--------------------+

| sex | GROUP_CONCAT(name) |

+-----+--------------------+

| 女  | 马莉莉,张雪梅      |

| 男  | 王冬军,刘兵,Tom    |

+-----+--------------------+

2 rows in set (0.00 sec)

上图中代码执行的结果显示，查询结果分为两组。sex字段取值为“女”的记录是一组，取值为“男”的记录是一组。

每一组中所有人的名字都被查询出来了。

该实例说明，使用GROUP_CONCAT()函数可以很好的把分组情况表示出来。

 

mysql> SELECT sex,GROUP_CONCAT(name) FROM employee GROUP BY sex WITH ROLLUP;

+-----+-------------------------------+

| sex | GROUP_CONCAT(name)            |

+-----+-------------------------------+

| 女  | 马莉莉,张雪梅                 |

| 男  | 王冬军,刘兵,Tom               |

| NULL | 马莉莉,张雪梅,王冬军,刘兵,Tom |

+-----+-------------------------------+

3 rows in set (0.00 sec)

1.3 GROUP BY关键字与集合函数一起使用

GROUP BY关键字与集合函数一起使用时，可以通过集合函数计算分组中的总记录、最大值、最小值等。

实例：将employee表的sex字段进行分组查询。sex字段取值相同的为一组。然后对每一组使用集合函数COUNT()函数进行计算，求出每一组的记录数。

mysql> SELECT sex,COUNT(sex) FROM employee GROUP BY sex;

+-----+------------+

| sex | COUNT(sex) |

+-----+------------+

| 女  |          2 |

| 男  |          3 |

+-----+------------+

2 rows in set (0.00 sec)

上图中代码执行的结果显示，查询结果按sex字段的取值进行分组。取值为“女”的记录为一组，取值为“男”的记录为一组。

COUNT(sex)计算出了sex字段不同分组的记录数。第一组共有2条记录，第二组共有3条记录。

 

WITH ROLLUP：将会在所有记录的最后加上一条记录。加上的这一条记录是上面所有记录的总和。

mysql> SELECT sex,COUNT(sex) FROM employee GROUP BY sex WITH ROLLUP;

+-----+------------+

| sex | COUNT(sex) |

+-----+------------+

| 女  |          2 |

| 男  |          3 |

| NULL |          5 |

+-----+------------+

3 rows in set (0.00 sec)

1.4 GROUP BY关键字与HAVING一起使用

使用GROUP BY关键字时，如果加上“HAVING 条件表达式”，则可以限制输出的结果。只有符合条件表达式的结果才会显示。

 

实例：将employee表的sex字段进行分组查询。然后显示记录数大于等于3的分组。

 

SELECT语句的代码如下：

mysql> SELECT sex,COUNT(sex) FROM employee GROUP BY sex HAVING COUNT(sex)>=3;

+-----+------------+

| sex | COUNT(sex) |

+-----+------------+

| 男  |          3 |

+-----+------------+

1 row in set (0.00 sec)

 

1.5按照多个字段进行分组

在MySQL中，还可以按照多个字段进行分组。例如，employee表按照num字段和sex字段进行分组。分组过程中，

先按照num字段进行分组，遇到num字段的值相等的情况时，再把num值相等的记录按照sex字段进行分组。

实例：将employee表按照num字段和sex字段进行分组。

 

SELECT语句的代码如下：

mysql> SELECT * FROM employee GROUP BY num,sex;

+----+------+--------+-----+-----+-------------+

| id | num  | name   | sex | age | homeaddress |

+----+------+--------+-----+-----+-------------+

|  2 | 1001 | 马莉莉 | 女  |  24 | 河南开封    |

|  1 | 1001 | 王冬军 | 男  |  26 | 河南郑州    |

|  3 | 1002 | 刘兵   | 男  |  25 | 广东省广州  |

|  5 | 1004 | 张雪梅 | 女  |  20 | 福建厦门    |

|  4 | 1004 | Tom    | 男  |  18 | America     |

+----+------+--------+-----+-----+-------------+

5 rows in set (0.00 sec)

 

二、Mysql联表查询

2.1 内联结和外联结的含义及区别

1.内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。
2.外联结：分为外左联结和外右联结。

右联结A、B表的结果和左联结B、A的结果是一样的，也就是说：

Select A.name B.name From A Left Join B On A.id=B.id

Select A.name B.name From B Right Join A on B.id=A.id  

执行后的结果是一样的。

 

说明：

1）内外联结的区别是内联结将去除所有不符合条件的记录，而外联结则保留其中部分。

2) 外左联结与外右联结的区别在于如果用A左联 结B则A中所有记录都会保留在结果中，此时B中只有符合联结条件的记录，而右联结相反。

2.2、例子

假设有如下两张表：

表A

ID

Name

1

Tiim

2

Jimmy

3

John

4

Tom

 

表B

ID

Hobby

1

Football

2

Basketball 

2

Tennis 

4

Soccer

 

1）内联结：

Select A.Name B.Hobby from A, B where A.id = B.id

这是隐式的内联结，查询的结果是： 

Name

Hobby

Tim

Football

Jimmy

Basketball

Jimmy

Tennis

Tom

Soccer

它的作用和：

Select A.Name from A INNER JOIN B ON A.id = B.id  

是一样的。

 

2）外左联结

Select A.Name from A Left JOIN B ON A.id = B.id

这样查询得到的结果将会是保留所有A表中联结字段的记录，若无与其相对应的B表中的字段记录则留空，结果如下：

Name

Hobby

Tim

Football

Jimmy

Basketball，Tennis

John

 

Tom

Soccer

所以从上面结果看出，因为A表中的John记录的ID没有在B表中有对应ID，因此为空，但Name栏仍有John记录。

2）外右联结

Select A.Name from A Right JOIN B ON A.id = B.id

结果将会是：

Name

Hobby

Tim

Football

Jimmy

Basketball

Jimmy

Tennis

Tom

Soccer

 

此时B表中的全部记录都打印了，但是A表没有显示完整记录，只是显示了跟B表相关联的记录。

 

2.3、联表查询中用到的一些参数

 

1.USING (column_list)：
其作用是为了方便书写联结的多对应关系，大部分情况下USING语句可以用ON语句来代替，如下面例子：
a LEFT JOIN b USING (c1,c2,c3)，其作用相当于
a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

2.STRAIGHT_JOIN：
由于默认情况下MySQL在进行表的联结的时候会先读入左表，当使用了这个参数后MySQL将会先读入右表，这是个MySQL的内置优化参数，

大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。

 

三、数据库操作

下载

        http://dev.mysql.com/downloads/mysql/

安装

        windows:

            点点点

        Linux:

            

　　　　yum -y install mysql mysql-server mysql-devel

3.1显示数据库，显示表

SHOW DATABASES;

SHOW TABLES;

默认数据库：

　　mysql - 用户权限相关数据

　　test - 用于用户测试数据

　　information_schema - MySQL本身架构相关数据

 

3.2 用户授权

1）用户管理：

创建用户

    create user '用户名'@'IP地址' identified by '密码';

删除用户

    drop user '用户名'@'IP地址';

修改用户

    rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;

修改密码

    set password for '用户名'@'IP地址' = Password('新密码')

PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

 

2）授权管理：

show grants for '用户'@'IP地址'                  -- 查看权限

grant  权限 on 数据库.表 to   '用户'@'IP地址'      -- 授权

revoke 权限 on 数据库.表 from '用户'@'IP地址'      -- 取消权限

 

3）对于权限：

            all privileges  除grant外的所有权限
            select          仅查权限
            select,insert   查和插入权限
            ...
            usage                   无访问权限
            alter                   使用alter table
            alter routine           使用alter procedure和drop procedure
            create                  使用create table
            create routine          使用create procedure
            create temporary tables 使用create temporary tables
            create user             使用create user、drop user、rename user和revoke  all privileges
            create view             使用create view
            delete                  使用delete
            drop                    使用drop table
            execute                 使用call和存储过程
            file                    使用select into outfile 和 load data infile
            grant option            使用grant 和 revoke
            index                   使用index
            insert                  使用insert
            lock tables             使用lock table
            process                 使用show full processlist
            select                  使用select
            show databases          使用show databases
            show view               使用show view
            update                  使用update
            reload                  使用flush
            shutdown                使用mysqladmin shutdown(关闭MySQL)
            super                   􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysqladmin􏵗􏵘􏲊􏲋调试登陆
            replication client      服务器位置的访问
            replication slave       由复制从属使用
对于权限

4）对于数据库:

        对于目标数据库以及内部其他：
            数据库名.*           数据库中的所有
            数据库名.表          指定数据库中的某张表
            数据库名.存储过程     指定数据库中的存储过程
            *.*                所有数据库
对于数据库

5）对于用户和IP:

            用户名@IP地址         用户只能在改IP下才能访问
            用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)
            用户名@%             用户可以再任意IP下访问(默认IP地址为%)
对于用户和IP

6）示例：

            grant all privileges on db1.tb1 TO '用户名'@'IP'

            grant select on db1.* TO '用户名'@'IP'

            grant select,insert on *.* TO '用户名'@'IP'

            revoke select on db1.tb1 from '用户名'@'IP'

 

 

四、表操作

4.1创建表

create table 表名(

    列名  类型  是否可以为空，

    列名  类型  是否可以为空
)
        是否可空，null表示空，非字符串
            not null    - 不可空
            null        - 可空
是否可空
        默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
            create table tb1(
                nid int not null defalut 2,
                num int not null
            )
默认值
        自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）
            create table tb1(
                nid int not null auto_increment primary key,
                num int null
            )
            或
            create table tb1(
                nid int not null auto_increment,
                num int null,
                index(nid)
            )
            注意：1、对于自增列，必须是索引（含主键）。
                 2、对于自增可以设置步长和起始值
                     show session variables like 'auto_inc%';
                     set session auto_increment_increment=2;
                     set session auto_increment_offset=10;

                     shwo global  variables like 'auto_inc%';
                     set global auto_increment_increment=2;
                     set global auto_increment_offset=10;
自增
        主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
            create table tb1(
                nid int not null auto_increment primary key,
                num int null
            )
            或
            create table tb1(
                nid int not null,
                num int not null,
                primary key(nid,num)
            )
主键
        外键，一个特殊的索引，只能是指定内容
            creat table color(
                nid int not null primary key,
                name char(16) not null
            )

            create table fruit(
                nid int not null primary key,
                smt char(32) null ,
                color_id int not null,
                constraint fk_cc foreign key (color_id) references color(nid)
            )
外键

4.2 删除表

drop table 表名

4.3 清空表

delete from 表名
truncate table 表名

4.4 修改表

添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
        alter table 表名 modify column 列名 类型;  -- 类型
        alter table 表名 change 原列名 新列名 类型; -- 列名，类型
 
添加主键：
        alter table 表名 add primary key(列名);
删除主键：
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 int, drop primary key;
 
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称
 
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

 

4.5基本数据类型

MySQL的数据类型大致分为：数值、时间和字符串

http://www.runoob.com/mysql/mysql-data-types.html

 

五、基本操作

5.1增

        insert into 表 (列名,列名...) values (值,值,值...)
        insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
        insert into 表 (列名,列名...) select (列名,列名...) from 表
View Code

5.2 删

        delete from 表
        delete from 表 where id＝1 and name＝'alex'
View Code

5.3 改

update 表 set name ＝ 'alex' where id>1
View Code 

5.4 查

        select * from 表
        select * from 表 where id > 1
        select nid,name,gender as gg from 表 where id > 1
View Code 

5.5 其他

    1、条件
        select * from 表 where id > 1 and name != 'alex' and num = 12;

        select * from 表 where id between 5 and 16;

        select * from 表 where id in (11,22,33)
        select * from 表 where id not in (11,22,33)
        select * from 表 where id in (select nid from 表)

    2、通配符
        select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）
        select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）

    3、限制
        select * from 表 limit 5;            - 前5行
        select * from 表 limit 4,5;          - 从第4行开始的5行
        select * from 表 limit 5 offset 4    - 从第4行开始的5行

    4、排序
        select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
        select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
        select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

    5、分组
        select num from 表 group by num
        select num,nid from 表 group by num,nid
        select num,nid from 表  where nid > 10 group by num,nid order nid desc
        select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid

        select num from 表 group by num having max(id) > 10

        特别的：group by 必须在where之后，order by之前

    6、连表
        无对应关系则不显示
        select A.num, A.name, B.name
        from A,B
        Where A.nid = B.nid

        无对应关系则不显示
        select A.num, A.name, B.name
        from A inner join B
        on A.nid = B.nid

        A表所有显示，如果B中无对应关系，则值为null
        select A.num, A.name, B.name
        from A left join B
        on A.nid = B.nid

        B表所有显示，如果B中无对应关系，则值为null
        select A.num, A.name, B.name
        from A right join B
        on A.nid = B.nid

    7、组合
        组合，自动处理重合
        select nickname
        from A
        union
        select name
        from B

        组合，不处理重合
        select nickname
        from A
        union all
        select name
        from B
View Code

 

六、PyMySQL或MySQLdb

pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。

6.1 下载安装：

pip3 install pymysql

6.2 使用

1、执行SQL

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
 
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cursor = conn.cursor()
 
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
 
# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
 
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
 
 
# 提交，不然无法保存新建或者修改的数据
conn.commit()
 
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

2、获取新创建数据自增ID

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
cursor.close()
conn.close()
 
# 获取最新自增ID
new_id = cursor.lastrowid

3、获取查询数据

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.execute("select * from hosts")
 
# 获取第一行数据
row_1 = cursor.fetchone()
 
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
 
conn.commit()
cursor.close()
conn.close()

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

  • cursor.scroll(1,mode='relative')  # 相对当前位置移动
  • cursor.scroll(2,mode='absolute') # 相对绝对位置移动

4、fetch数据类型

　　关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 
# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")
 
result = cursor.fetchone()
 
conn.commit()
cursor.close()
conn.close()

七、sqlalchemy

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

sqlalchemy默认不支持修改表结构，得下载第三方的工具，才能修改。

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，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

7.1 底层处理

使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

 

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
 
 
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
 
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)"
# )
 
# 新插入行自增ID
# cur.lastrowid
 
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]
# )
 
 
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",
#     host='1.1.1.99', color_id=3
# )
 
# 执行SQL
# cur = engine.execute('select * from hosts')
# 获取第一行数据
# cur.fetchone()
# 获取第n行数据
# cur.fetchmany(3)
# 获取所有数据
# cur.fetchall()

 

7.2 ORM功能使用

 

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。

sqlalchemy不会帮你创建数据库，要自己创建数据库。

sqlalchemy要么创建表，要么删除表，默认不支持修改表结构，得下载sqlalchemy第三方的工具，才能修改。

在django的orm里可以修改表结构。

 

1、创建表

 

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)        # 建立数据库连接

Base = declarative_base()    # 创建base module

# 创建单表
class Users(Base):    # 创建类，要继承Base
    __tablename__ = 'users'        # 定义的表名
    id = Column(Integer, primary_key=True)    # 下面是创建了三列数据
    name = Column(String(32))
    extra = Column(String(16))

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),    # 加了联合索引，上面一行是唯一，下面一行是加索引。
        Index('ix_id_name', 'name', 'extra'),
    )


# 一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)    
    caption = Column(String(50), default='red', unique=True)    


class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))    # 和favor表的id做了外键关联


# 多对多
class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    
class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True)
hostname = Column(String(64), unique=True, nullable=False)
port = Column(Integer, default=22)


class ServerToGroup(Base):    # 创建多对多关系，得创建第三张表。
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))    # 外键到server表的id
    group_id = Column(Integer, ForeignKey('group.id'))    # 外键到group表的id


def init_db():
    Base.metadata.create_all(engine)        # 只要执行这一句，就会自动找base所有的子类，根据这些子类把表批量创建出来。


def drop_db():
    Base.metadata.drop_all(engine)        # 表示批量删除所有base的子类创建的表

 

 

 

ForeignKeyConstraint(['other_id'], ['othertable.other_id']),

 

 

2、操作表

创建session搞上会话。

通过session来操作数据库。

如果想往表里添加数据的话，用

session.add()

或者

session.addall()

就从某个表里加了数据

举例说明：

Session = sessionmaker(bind=engine)
session = Session()
obj = Users(name="qiaomei", extra='qm')    #创建一个记录，就是创建一个对象
session.add(obj)    #把对象加到session里
session.add_all([    
    Users(name="qiaomei1", extra='qm'),
    Users(name="qiaomei2", extra='qm'),
])
session.commit()

 

如果是多个条件，且的话，就在filter里加上逗号，添加多个条件。

session.query(Users).filter(Users.id > 2,Users.name='qiaomei').delete()

session.commit()

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

Base = declarative_base()

# 创建单表
class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_id_name', 'name', 'extra'),
    )

    def __repr__(self):
        return "%s-%s" %(self.id, self.name)

# 一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)

    def __repr__(self):
        return "%s-%s" %(self.nid, self.caption)

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))
    # 与生成表结构无关，仅用于查询方便
    favor = relationship("Favor", backref='pers')

# 多对多
class ServerToGroup(Base):
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))
    group = relationship("Group", backref='s2g')
    server = relationship("Server", backref='s2g')

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)
    # group = relationship('Group',secondary=ServerToGroup,backref='host_list')

class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)




def init_db():
    Base.metadata.create_all(engine)


def drop_db():
    Base.metadata.drop_all(engine)


Session = sessionmaker(bind=engine)
session = Session()
表结构，连接数据库

增

obj = Users(name="qiaomei0", extra='qm')

session.add(obj)

session.add_all([

    Users(name="qiaomei1", extra='qm'),

    Users(name="qiaomei2", extra='qm'),

])

session.commit()
View Code

删

session.query(Users).filter(Users.id > 2,Users.name='qiaomei').delete()

session.commit()
View Code

改

session.query(Users).filter(Users.id > 2).update({"name" : "099"})

session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False)

session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")

session.commit()
View Code 

查

ret = session.query(Users).all()

print(type(ret[0]))     # <class '__main__.Users'>

ret = session.query(Users.name, Users.extra).all()

ret = session.query(Users).filter_by(name='qiaomei').all()

ret = session.query(Users).filter_by(name='qiaomei').first()

 

'''

打印结果：

ret1: [1-qiaomei0, 2-qiaomei1, 3-qiaomei2] 注意：打印的是User类对象列表。（一条记录对应着一个User对象）

ret2: [('qiaomei0', 'qm'), ('qiaomei1', 'qm'), ('qiaomei2', 'qm')]

ret3: []

ret4: None

'''
View Code

# 如果想查看生成的sql语句是什么，就不加all()

q = session.query(Users)

print(q)    # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra FROM users

其他

#　条件
ret = session.query(Users).filter_by(name='alex').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() # filtwr里有逗号，条件是并且的关系
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()  # id是1,3,4其中一个
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() # id不在，~是否的意思
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()   # id在一个数组中，但是要先执行里面的sql
from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()   # and 表示都是并且关系
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(      # 内部是and连接，得出一个布尔值，然后外面再用or
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()

# 通配符
ret = session.query(Users).filter(Users.name.like('e%')).all()      # %表示通配符
ret = session.query(Users).filter(~Users.name.like('e%')).all()     # ~表示否
# 限制
ret = session.query(Users)[1:2]
# 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()    # 首先按照User表倒序排列，如果有相同，再排序
# 分组
from sqlalchemy.sql import func
ret = session.query(Users).group_by(Users.extra).all()
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()  # 按照name分组，并且取其他列（id列）的最大，总数，最小值。

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()    # having是group_by的条件过滤
# 连表
ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()   # 直接让两个表进行连接
# 直接让两个表联合。这里join默认是innerjoin，这里没有写他们的对应关系，它们在内部自己找。
# 它是怎么找的呢，在创建表的时候，有类型是foreignkey，是根据它来找的。
ret = session.query(Person).join(Favor).all()

ret = session.query(Person).join(Favor, isouter=True).all() # isouter=True表示leftjoin。没有rightjoin，如果想rightjoin，替换表写的位置。

# 组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()    # 把q1.q2两个联合的全部取到，union会帮你去重

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()    # union_all不会帮你去重
View Code

 

7.3 sqlalchemy单表查询

1）__repr__ 指定打印对象显示内容

def __repr__ 通过这样定义，print Users对象的时候，就打印出需要显示的内容。

如果是django,如果是python2.7，就是__unicode__方式，如果是python3，就是__str__方式。

 

ret = session.query(Users).filter_by(name='qiaomei').all()

print ret 这时候显示出的是__repr__打印的结果组成的数组。

__repr__对数据库查询，一点作用都没有，只是print对象有用。

 

2）获取属性

ret = session.query(Users).filter_by(name='qiaomei').all()

print ret[0].name就是获取名字。

这种方式是直接获取属性值

q1 = session.query(Users.name).filter(Users.id > 2)

print q1

打印：[('qiaomei1',),('qiaomei2',)]

 

2）建立外键关联

ForeignKey，就加上外键约束

avor_id = Column(Integer, ForeignKey("favor.nid"))

# 创建单表
class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_id_name', 'name', 'extra'),
    )

    def __repr__(self):            
        return "%s-%s" %(self.id, self.name)
创建单表

 

7.4 联表查询-一对多-low方式

4.1）默认是innerjoin。

# 直接让两个表联合。这里join默认是innerjoin，这里没有写他们的对应关系，它们在内部自己找。

# 它是怎么找的呢，在创建表的时候，有类型是foreignkey，是根据它来找的。

ret = session.query(Person).join(Favor).all()

相当于sql语句：

两个表通过on，来关联

 

sql = session.query(Person).join(Favor)
print(sql)
'''
inner join打印sql，只打印person表所有字段信息，不打印favor表
SELECT person.nid AS person_nid, person.name AS person_name, person.favor_id AS person_favor_id
FROM person JOIN favor ON favor.nid = person.favor_id
'''

 

4.2）isouter=True就是left join

ret1 = session.query(Person).join(Favor,isouter=True).all()
sql1 = session.query(Person).join(Favor,isouter=True)
print(sql1)
'''
打印sql，只打印person表所有字段的信息，但是没有打印favor表
SELECT person.nid AS person_nid, person.name AS person_name, person.favor_id AS person_favor_id
FROM person LEFT OUTER JOIN favor ON favor.nid = person.favor_id
'''

4.3）两张表的信息都打印出来

ret2 = session.query(Person,Favor).join(Favor,isouter=True).all()
print(ret2)
sql2 = session.query(Person,Favor).join(Favor,isouter=True)
print(sql2)
'''
left join,打印结果：打印person和favor两张表的所有字段。
[(<__main__.Person object at 0x0000000003B34FD0>, 1-white), (<__main__.Person object at 0x0000000003B69BE0>, 2-blue),
(<__main__.Person object at 0x0000000003B69C50>, 2-blue)]

left join,打印sql：打印person和favor两张表的所有字段。
SELECT person.nid AS person_nid, person.name AS person_name, person.favor_id AS person_favor_id,
favor.nid AS favor_nid, favor.caption AS favor_caption
FROM person LEFT OUTER JOIN favor ON favor.nid = person.favor_id
'''

4.4）联表，只打印某些字段

联表，上面的都是打印对象，而现在我们要获取指定字段的值。

ret3 = session.query(Person.name,Favor.caption).join(Favor,isouter=True).all()
print(ret3)
sql3 = session.query(Person.name,Favor.caption).join(Favor,isouter=True)
print(sql3)
'''
left join,打印结果：某些指定字段值
[('qiaomei0', 'white'), ('qiaomei1', 'blue'), ('qiaomei2', 'blue')]
left join,打印sql：某些指定字段值
SELECT person.name AS person_name, favor.caption AS favor_caption
FROM person LEFT OUTER JOIN favor ON favor.nid = person.favor_id
'''

7.5 联表查询-一对多-推荐方式

1）正向查询

正向查询指的是：多对一，多的那端开始查，也就是foreignkey写在哪里，从哪里查。

使用上面的方法非常麻烦，我们用更高效的方法。

只要在表里加上这一句话：

favor = relationship("Favor", backref='pers')

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))
    # 与生成表结构无关，仅用于查询方便
    favor = relationship("Favor", backref='pers')

    # obj代指的是Person表的每一行数据
    # obj.favor代指favor对象，obj.favor.nid就拿到了Person关联的favor对象的id。
    # 所以你不用做联表，它内部帮你做联表。
ret = session.query(Person).all()
for obj in ret:     # 每个obj就是一行数据。

    print(obj.nid,obj.name,obj.favor_id,obj.favor,obj.favor.nid,obj.favor.caption)

　　

'''
打印结果：
    Person的id, name,     favor_id,favor对象，favor的id，caption
    1          qiaomei0     1       1-white     1       white
    2          qiaomei1     2       2-blue      2       blue
    3          qiaomei2     2       2-blue      2       blue
'''

 

2）反向查询

反向查询指的是：多对一，从一的那端开始查，也就是从没写foreignkey的表里反查。

# 多对一，从一的那端反查。
# Person和Favor是多对一，假如查询喜欢蓝色的所有人。Favor的caption为blue的所有对应的Person

# 传统方式，反向查询：
ret3 = session.query(Person.name,Favor.caption).join(Favor,isouter=True).filter(Favor.caption == 'blue').all()

Person表里，写了backref='pers',就相当于在favor表里加了个字段pers

favor = relationship("Favor", backref='pers')

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))
    # 与生成表结构无关，仅用于查询方便
    favor = relationship("Favor", backref='pers')

class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)
    # Person表里写的：backref='pers',相当于在这里加上字段pers。只是用于查询，不会修改表结构。
    # pers = 。。。。。。。。
    def __repr__(self):
        return "%s-%s" %(self.nid, self.caption)

你可以直接通过Favor对象的pers字段找到跟这个颜色关联的所有person。

在数据库里没有真实的字段对应的，只是帮你生成sql语句而已。

# 新方式，反向查询
obj = session.query(Favor).filter(Favor.caption=='blue').first()    # 先找到caption为blue的Favor对象
print(obj.nid)
print(obj.caption)
print(obj.pers)

'''
打印结果：
2
blue
[<__main__.Person object at 0x0000000003B5BBE0>, <__main__.Person object at 0x0000000003B5BC50>]
'''

3）总结

Foreignkey和relationship要成对写在一个表里。

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))
    # 与生成表结构无关，仅用于查询方便
    favor = relationship("Favor", backref='pers')

Person对Favor 是多对一的关系，foreignkey加在了多的那端（Person表）。

Person对象.favor.favor的字段：叫做正向查找

Favor对象.pers.person的字段：反向查找

7.6 SQLAlchemy表创建过程

我们通过写个类来创建表。

两个步骤：

1）把这个类转为table对象。

2）根据这个table对象生成sql语句（create table。。。）。

一个类就是一张表。

用对象来创建表也可以，但是都不这么用。

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))
    # 与生成表结构无关，仅用于查询方便
    favor = relationship("Favor", backref='pers')

八 sqlalchemy联表查询：多对多

8.1 联表查询多对多结构

#!/usr/bin/env python
# -*- coding:utf-8 -*-
__author__ = 'WangQiaomei'

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/s13", max_overflow=5)

Base = declarative_base()

# 主机
class Host(Base):
    __tablename__ = 'host'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    hostname = Column(String(32))
    port = Column(String(32))
    ip = Column(String(32))

# 主机上的用户
class HostUser(Base):
    __tablename__ = 'host_user'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(32))

# 多对多表
class HostToHostUser(Base):
    __tablename__ = 'host_to_host_user'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    host_id = Column(Integer,ForeignKey('host.nid'))
    host_user_id = Column(Integer,ForeignKey('host_user.nid'))


def init_db():
    Base.metadata.create_all(engine)
    
def drop_db():
    Base.metadata.drop_all(engine)

# init_db()
Session = sessionmaker(bind=engine)
session = Session()

session.add_all([
    Host(hostname="c1",port="22",ip="1.1.1.1"),
    Host(hostname="c2",port="22",ip="1.1.1.2"),
    Host(hostname="c3",port="22",ip="1.1.1.3"),
    Host(hostname="c4",port="22",ip="1.1.1.4"),
    Host(hostname="c5",port="22",ip="1.1.1.5"),
])
session.add_all([
    HostUser(username="root"),
    HostUser(username="db"),
    HostUser(username="nb"),
    HostUser(username="sb"),
])
session.commit()
session.add_all([
    HostToHostUser(host_id=1,host_user_id=1),
    HostToHostUser(host_id=1,host_user_id=2),
    HostToHostUser(host_id=1,host_user_id=3),
    HostToHostUser(host_id=2,host_user_id=2),
    HostToHostUser(host_id=2,host_user_id=4),
    HostToHostUser(host_id=2,host_user_id=3),

])
session.commit()

8.2 联表查询

主机：
1    c1
2    c2
3    c3
服务器用户：
1    root
2    nb
3    db
4    sb
关系表
nid    主机ID    服务器ID
   1        1
   1        2
   1        3
   2        2
   2        4
。。。。。。。。。。。。
约束：外键

1) 传统方式

 

如果我们想查：主机c1上有几个用户。

 

通过传统的方式应该：

 

1）通过主机表：找到c1对应的主机id。

 

2）通过第三张关联表查询：主机id对应的几个用户id。

 

3）通过用户表：找到这些用户id，对应的用户名。

# 需求来了。。。
# 获取主机c1的所有的用户

# 需求来了。。。
# 获取主机c1的所有的用户
# 1 获取c1主机对象
host_obj = session.query(Host).filter(Host.hostname=='c1').first()
# host_obj.nid
# 我们只取映射，不取对象。如果取的是对象，还得循环对象列表，然后取出所有的nid。
# 2 跟c1关联的所有用户id
host_2_host_user=session.query(HostToHostUser.nid).filter(HostToHostUser.host_id == host_obj.nid).all()
print(host_2_host_user) # 打印：[(1,), (2,), (3,)]
# 我们拿到的是用户id，是个集合。接下来要找集合下任意一个的所有用户。应该用in操作
# 3 获取用户名
users = session.query(HostUser.username).filter(HostUser.nid.in_([1,2])).all() # 这样把id为1,2的所有用户都拿到了。
print(users)        # [('root',), ('db',)]

# 我们怎样把[(1,), (2,), (3,)]   转换为     [1,2,3],用zip
r=zip(*host_2_host_user)
print(r)
# print(list(r)[0])      # [(1, 2, 3)]    ==> (1, 2, 3)
# 元组或列表放在这里都可以
users = session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all() # list(r)[0] 这个zip对象是迭代器，取一次就没了。
print(users)    # [('root',), ('db',), ('nb',)]    获取了主机c1的所有的用户

2) 新方式1

host和HostUser是两张表，HostToHostUser是第三张表，是多对多关系表。

这个第三张表都有foreignkey关联着这两张表。

Host 和HostToHostUser是一对多的关系

HostUser和HostToHostUser是一对多的关系

两个一对多就变成多对多了。

 

所以我们可以把relationship在HostToHostUser这张表里写一遍。

添加下面两行：

# 多对多表
class HostToHostUser(Base):
    __tablename__ = 'host_to_host_user'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    host_id = Column(Integer,ForeignKey('host.nid'))
    host_user_id = Column(Integer,ForeignKey('host_user.nid'))
    host = relationship("Host",backref="h")
    host_user = relationship("HostUser",backref="u")

 

# 新方式
host_obj=session.query(Host).filter(Host.hostname=='c1').first()
print(host_obj.nid)
print(host_obj.hostname)
# 主机c1对应的第三张表的对象（通过反查）
# [<__main__.HostToHostUser object at 0x0000000003CBB470>, <__main__.HostToHostUser object at 0x0000000003CBB438>,
#  <__main__.HostToHostUser object at 0x0000000003CBB5F8>]
print(host_obj.h)
# 因为foreignkey写在第三张表上，所以主机或用户表来查第三张表就是反查，如果是第三张表查主机或用户就是正向查找。
# 循环获取第三张表的对象
for item in host_obj.h:
    print(item.host_user.username)

'''
打印：
root
db
nb
'''

　　

3）新方式2[推荐]

 

多对多最终推荐方式：

 

A 关系(B，AB.__table__)

AB ==> fk,

B

AB.__table__就表示第三张表的对象。

更改方式：

1）把HostToHostUser类放在Host前面

2）去掉HostToHostUser的所有relationship

3）Host表加上relationship，关联HostUser表和第三张表的对象

# 多对多表
class HostToHostUser(Base):
    __tablename__ = 'host_to_host_user'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    host_id = Column(Integer,ForeignKey('host.nid'))
    host_user_id = Column(Integer,ForeignKey('host_user.nid'))

# 主机
class Host(Base):
    __tablename__ = 'host'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    hostname = Column(String(32))
    port = Column(String(32))
    ip = Column(String(32))

    host_user = relationship("HostUser",
                             secondary=HostToHostUser.__table__,
                             backref="h")

多对多就非常简单了：

host_obj=session.query(Host).filter(Host.hostname=='c1').first()
print(host_obj.host_user)
'''
打印结果：找到了c1所有关联的主机用户名
[<__main__.HostUser object at 0x0000000003B18C50>, <__main__.HostUser object at 0x0000000003B18940>,
 <__main__.HostUser object at 0x0000000003B18BA8>]
'''

　　

 

posted @ 2016-07-30 18:42  巧妹儿  阅读(2860)  评论(0)  编辑  收藏  举报