mysql数据库的连接以及基本操作

一、Mysql数据库的连接

Python标准数据库接口为Python DB-API, Python DB-API为开发人员提供了数据库应用 编程接口。

不同的数据库你需要下载不同的DB API模块,例如你需要访问Oracle数据库和Mysql数据,你需要下载Oracle和MySQL数据库模块。

DB-API是一个规范。它定义了一系列必须的对象和数据库存取方式, 以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口。

Python的DB-API,为大多数的数据库实现了接口,使用它连接各数据库后,就可以用相同 的方式操作各数据库。

Python DB-API使用流程:

(1)引入API模块。

(2)获取与数据库的连接。

(3)执行SQL语句和存储过程。

(4)关闭数据库连接。

1.第一种方式，使用MySQLdb

MySQLdb是用于Python链接Mysql数据库的接口，它实现了Python 数据库API规范V2.0，基于MySQL C API上建立的。

安装

直接使用pip进行安装，在此之前需要安装一些系统依赖包。

在CentOS中

yum install gcc python-devel mysql-devel zlib-devel openssl-devel

在Ubuntu中

​sudo apt-get install libmysqlclient-dev libmysqld-dev python-dev python-setuptools

安装完依赖，直接使用pip安装，MySQLdb模块的名字在pip上叫MySQL-python。

pip install MySQL-python

Python DB API 2.0 对事务提供了两个方法：

（1）commit() 提交

（2）rollback() 回滚

cursor用来执行命令的方法:

（1）callproc(self, procname, args) 用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数

（2）execute(self, query, args) 执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数

（3）executemany(self, query, args) 执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数

（4）nextset(self) 移动到下一个结果集

cursor用来接收返回值的方法:

（1）fetchall(self) 接收全部的返回结果行.

（2）fetchmany(self, size=None) 接收size条返回结果行.如果size的值大于返回的结果行的数量,则会返回cursor.arraysize条数据.

（3）fetchone(self) 返回一条结果行.

（4）rowcount 这是一个只读属性,并返回执行execute() 方法后影响的行数。

（5）scroll(self, value, mode='relative') 移动指针到某一行; 如果mode='relative',则表示从当前所在行移动value条,如果 mode='absolute',则表示从结果集的第一行移动value条.

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb as mdb

# 连接数据库
conn = mdb.connect('localhost', 'root', 'root')

# 也可以使用关键字参数
conn = mdb.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='test', charset='utf8')

# 也可以使用字典进行连接参数的管理
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': 'root',
    'db': 'test',
    'charset': 'utf8'
}
conn = mdb.connect(**config)

# 如果使用事务引擎，可以设置自动提交事务，或者在每次操作完成后手动提交事务conn.commit()
conn.autocommit(1)    # conn.autocommit(True) 

# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# 因该模块底层其实是调用CAPI的，所以，需要先得到当前指向数据库的指针。

try:
    # 创建数据库
    DB_NAME = 'test'
    cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)
    cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)
    conn.select_db(DB_NAME)

    #创建表
    TABLE_NAME = 'user'
    cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' %TABLE_NAME)

    # 插入单条数据
    sql = 'INSERT INTO user values("%d","%s")' %(1,"jack")

    # 不建议直接拼接sql，占位符方面可能会出问题，execute提供了直接传值
    value = [2,'John']
    cursor.execute('INSERT INTO test values(%s,%s)',value)

    # 批量插入数据
    values = []
    for i in range(3, 20):
        values.append((i,'kk'+str(i)))
    cursor.executemany('INSERT INTO user values(%s,%s)',values)

    # 查询数据条目
    count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)
    print 'total records: %d' %count
    print 'total records:', cursor.rowcount

    # 获取表名信息
    desc = cursor.description
    print "%s %3s" % (desc[0][0], desc[1][0])

    # 查询一条记录
    print 'fetch one record:'
    result = cursor.fetchone()
    print result
    print 'id: %s,name: %s' %(result[0],result[1])

    # 查询多条记录
    print 'fetch five record:'
    results = cursor.fetchmany(5)
    for r in results:
        print r

    # 查询所有记录
    # 重置游标位置，偏移量:大于0向后移动;小于0向前移动，mode默认是relative
    # relative:表示从当前所在的行开始移动; absolute:表示从第一行开始移动
    cursor.scroll(0,mode='absolute')
    results = cursor.fetchall()
    for r in results:
        print r

    cursor.scroll(-2)
    results = cursor.fetchall()
    for r in results:
        print r

    # 更新记录
    cursor.execute('UPDATE %s SET name = "%s" WHERE id = %s' %(TABLE_NAME,'Jack',1))
    # 删除记录
    cursor.execute('DELETE FROM %s WHERE id = %s' %(TABLE_NAME,2))

    # 如果没有设置自动提交事务，则这里需要手动提交一次
    conn.commit()
except:
    import traceback
    traceback.print_exc()
    # 发生错误时会滚
    conn.rollback()
finally:
    # 关闭游标连接
    cursor.close()
    # 关闭数据库连接
    conn.close()
查询时返回字典结构
MySQLdb默认查询结果都是返回tuple，通过使用不同的游标可以改变输出格式，这里传递一个cursors.DictCursor参数。

import MySQLdb.cursors

conn = MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', cursorclass=MySQLdb.cursors.DictCursor)
cursor = conn.cursor()

cursor.execute('select * from user')
r = cursor.fetchall()
print r
# 当使用位置参数或字典管理参数时，必须导入MySQLdb.cursors模块

# 也可以用下面的写法
import MySQLdb as mdb
conn  = mdb.connect('localhost', 'root', 'root', 'test')
cursor = conn.cursor(cursorclass=mdb.cursors.DictCursor)

cursor.execute('select * from user')
r = cursor.fetchall()
print r

解决mysqldb查询大量数据导致内存使用过高的问题

connection=MySQLdb.connect(
    host="thehost",user="theuser",
    passwd="thepassword",db="thedb")
cursor=connection.cursor()
cursor.execute(query)
for row in cursor.fetchall():
    print(row)

上述代码无论是fetchall()还是fetchone()都是先将数据载入到本地再进行计算，大量的数据会导致内存资源消耗光。解决办法是使用SSCurosr光标来处理。

解决方法：

1）使用SSCursor，避免客户端占用大量内存。

2）使用迭代器而不用fetchall,即省内存又能很快拿到数据。

import MySQLdb.cursors
connection=MySQLdb.connect(
    host="thehost",user="theuser",
    passwd="thepassword",db="thedb",
    cursorclass = MySQLdb.cursors.SSCursor)
cursor=connection.cursor()
cursor.execute(query)
for row in cursor:
    print(row)

2.第二种方式使用PyMySQL

此方式适用于python2和python3。

PyMySQL是一个纯Python写的MySQL客户端，它的目标是替代MySQLdb，可以在CPython、PyPy、IronPython和Jython环境下运行。PyMySQL在MIT许可下发布。PyMySQL的性能和MySQLdb几乎相当，如果对性能要求不是特别的强，使用PyMySQL将更加方便。PyMySQL的使用方法和MySQLdb几乎一样。

安装

pip install pymysql

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql

config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': 'root',
    'charset':'utf8mb4',
    'cursorclass':pymysql.cursors.DictCursor
    }
conn = pymysql.connect(**config)
conn.autocommit(1)
cursor = conn.cursor()

try:
    # 创建数据库
    DB_NAME = 'test'
    cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)
    cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)
    conn.select_db(DB_NAME)

    #创建表
    TABLE_NAME = 'user'
    cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' %TABLE_NAME)

    # 批量插入纪录
    values = []
    for i in range(20):
        values.append((i,'kk'+str(i)))
    cursor.executemany('INSERT INTO user values(%s,%s)',values)

    # 查询数据条目
    count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)
    print 'total records:', cursor.rowcount

    # 获取表名信息
    desc = cursor.description
    print "%s %3s" % (desc[0][0], desc[1][0])

    cursor.scroll(10,mode='absolute')
    results = cursor.fetchall()
    for result in results:
        print result

except:
    import traceback
    traceback.print_exc()
    # 发生错误时会滚
    conn.rollback()
finally:
    # 关闭游标连接
    cursor.close()
    # 关闭数据库连接
    conn.close()

输出结果：

total records: 20
id name
{u'id': 10, u'name': u'kk10'}
{u'id': 11, u'name': u'kk11'}
{u'id': 12, u'name': u'kk12'}
{u'id': 13, u'name': u'kk13'}
{u'id': 14, u'name': u'kk14'}
{u'id': 15, u'name': u'kk15'}
{u'id': 16, u'name': u'kk16'}
{u'id': 17, u'name': u'kk17'}
{u'id': 18, u'name': u'kk18'}
{u'id': 19, u'name': u'kk19'}

 

二、MySQL数据库的基本操作

### 数据定义语言(DDL)
- 查看库：`show databases;`，会显示数据库服务器上所有的数据库
- 创建库：`create database test;`，创建数据库test
- 删除库：`drop database test;`，删除数据库test
- 选择库：`use test;`，选择数据库test
  - 查看当前选中数据库：
    - show tables；
    - select database();
- 查看表：`show tables;`，查看当前选中数据库中的数据表
- 创建表：`create table user(username varchar(20), password char(32));`
- 查看表结构：`desc user;`
- 删除表：`drop table user; `
- 查看创建方式：
  - 库：`show create database test;`
  - 表：`show create table user;`
- 修改字符集：
  - 创建时指定：在创建表的语句后指定字符集。
  - 修改配置文件：`sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf`，在[mysqld]后面添加
    - `character-set-server=utf8`
    - 重启服务：`sudo service mysql restart`
    - 说明：以后再创建的库中的表默认字符集就是utf8
- 修改字段：`alter table user modify username char(30);`，不能修改名字
- 添加字段：
  - 默认(最后)添加：`alter table user add age int(3);`
  - 在指定字段后添加：`alter table user add email varchar(60) after password;`
  - 在最开始出添加：`alter table user add id int(11) first;`
- 删除字段：`alter table user drop age;`
- 修改字段名称：`alter table user change email em char(32);`
- 修改位置及名称：add/change/modify
  - `alter table user modify em char(32) first;`
- 修改表名：`alter table user rename new_user;`

### 数据类型

- 整型：tinyint(1个字节)、smallint(2个字节)、mediumint(3个字节)、int(4个字节)、bigint(8个字节)

- 浮点类型：

  - float(m, d)，单精度浮点数，4个字节，m表示总位数，d表示小数位数
  - double(m, d)，双精度浮点数，8个字节，m表示总位数，d表示小数位数
  - decimal(m, d)，以字符串的形式存储浮点数，用于金融领域等要求严格的场景

- 字符类型：
  - char：定长字符串，0~255个字节
  - varchar：变长字符串，0~65535个字节
- 时间日期：
  - date：日期，格式：2018-04-16
  - time：时间，格式：15:36:30
  - datetime：日期时间，格式：2018-04-16 15:37:38
  - timestamp：时间戳
  - year：年，只占1个字节，年份范围：1901~2155

- 符合类型：
  - set：集合类型，格式：set(s1, s2, ..., s63)，最多63种可能
  - enum：枚举类型，格式：enum(e1,e2, ... ,e65535)，最多65535种可能
- 字段修饰：
  - unsigned：无符号数
  - zerofill：高位0填充，防止出现负数
  - auto_increment：自动增加(1)，用于整数，经常与主键结合使用
  - default：设置默认值
  - not null：不能为空
- 字符集及存储引擎
  - 查看支持的字符集：`show character set;`，我们通常只使用utf8，不能书写成utf-8
  - 查看支持的存储引擎：`show engines;`
    - 常用存储引擎：MyISAM和InnoDB
- 索引：
  - 说明：简单理解，就是一本书最前面的目录，虽然可以提高读取效率，单并非越多越好。
  - 分类：
    - 普通索引(index)：最基本的索引
    - 唯一索引(unique)：修饰的字段不能重复
    - 主键索引(primary key)：是一种特殊的唯一索引，一张表中只能有一个字段设置
    - 全文索引(fulltext)：多全局数据添加索引。
  - 示例：
    ```
    alter table user add index(em);                # 给em字段添加普通索引
    alter table user add unique(username);        # 给username字段添加唯一索引
    alter table user add primary key(id);        # 将id设置为主键索引

    alter table user drop index em;                # 删除em字段的普通索引
    ```
    > alter table 表名add fulltext(字段);
- 很多选项都可在创建表时指定，如：
  ```sql
  create table user(
      id int auto_increment,
      name varchar(20),
      primary key(id),
      unique(name)
  )engine=innodb default charset=utf8;
  ```
 游标
 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
 游标充当指针的作用，尽管游标能遍历结果中的所有行，但他一次只指向一行。
 游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。
 游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。
 索引
 mysql的索引分为单列索引(主键索引,唯索引,普通索引)和组合索引.
单列索引:一个索引只包含一个列,一个表可以有多个单列索引.
组合索引:一个组合索引包含两个或两个以上的列。
（1）单列索引
普通索引,这个是最基本的索引,
其sql格式是 CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 或者 ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length))
唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,
其sql格式是 CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length)); 或者 ALTER TABLE TableName ADD UNIQUE (column_list)  
主键索引,不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)
主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的
一个表中含有多个单列索引不代表是组合索引,通俗一点讲 组合索引是:包含多个字段但是只有索引名称
其sql格式是 CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);
1、数据操作语言：最大多数的使用中，都是对数据的增、删、改、查的操作(CURD)
创建一个star表：
  create table star(
      id int auto_increment,
      name varchar(20) not null,
      money float not null,
      province varchar(20) default null,
      age tinyint unsigned not null,
      sex tinyint not null,
      primary key(id)
  )engine=innodb default charset=utf8;
2、指定字段，只需要传递指定字段的值
    insert into star(name,money,age,sex,province) values('小岳岳',4000000, 33, 0, '河南')；
  查询数据：
  - `select * from star;`
  删除数据：
  - `delete from star where id=1;`
  - 警告：删除操作一定不要忘了指定条件，否则后果自负。
  修改数据：
  - `update star set age=22,money=8000000 where id=6;`
  - 警告：修改操作一定不要忘了指定条件，否则后果自负。
  数据查询语言(DQL)
  基础查询：`select * from star;`
  指定字段查询：`select id,name,money from star;`
  排除重复记录：`select distinct name from star;`
  - 使用distinct修饰的字段组合不能重复

    | 符号                    | 说明              |
    | --------------------- | --------------- |
    | >                     | 大于              |
    | >=                    | 大于等于            |
    | <                     | 小于              |
    | <=                    | 小于等于            |
    | =                     | 等于              |
    | != 或 <>               | 不等于             |
    | and                   | 并且              |
    | or                    | 或者              |
    | [not] between m and n | [不]在指定[m,n]的闭区间 |
    | [not] in              | [不]在指定的集合中      |
    | [not] like            | 模糊匹配，%表示任意字符    |
  select id,name,money from star where id > 4;
  select id,name from star where id between 2 and 5;
  select id,name from star where id in(2,4,6);
  select id,name from star where name like '小%';
  结果集排序：
  - 示例：`select id,name,money from star order by money desc;`
  - order by ：指定排序字段
    - asc：升序，默认的
    - desc：降序
  - 也可以多字段排序，先按照前面的字段排序，再按照后面字段排序
    - 示例：`select id,name,money,age from star order by money desc,age asc;`
 - 限制结果集：
 select id,name,money from star limit 3 offset 2;   # 偏移2条，然后取3条数据
  - 用途：分页显示，假设一页显示10条数据
    ```
    第一页：limit 0, 10
    第二页：limit 10, 10
    第三页：limit 20, 10
    page：表示页数，pageSize：表示一页的大小
    查询条件：limit (page-1)*pageSize, pageSize

- 常用聚合函数
  | 函数    | 功能   |
  | ----- | ---- |
  | count | 统计个数 |
  | sum   | 求和   |
  | max   | 最大值  |
  | min   | 最小值  |
  | avg   | 平均值  |
  > 1. 使用count时指定任何字段都行
  > 2. 使用其他函数时必须要指定字段
  示例：
  ```sql
  select count(*) from star;
  select max(money) as m from star;
分组操作
  - 示例：
    ```sql
    select * from star group by sex;          # 分组
    select count(*), sex from star group by sex;    # 分组统计  ```
    结果集过滤：
  - 示例：
    ```sql
    select count(*) as c,province from star group by province having c>1;
    ```
    > 搜索所有记录，然后按照省份分组，统计成员大于1的省份
#数据控制语言(DCL)
- 创建用户：
  - 格式：`create user '用户名'@'主机' identified by '密码'`
  - 示例：`create user 'jerry'@'%' identified by '123456'`
- 授权用户：
  - 格式：`grant 权限 privileges on 库.表 to '用户'@'主机' identified by '密码'`
  - 示例：`grant all privileges on test.* to 'jerry'@'%' identified by '123456'`
- 查看授权：
  - 格式：`show grants for '用户'@'主机'`
  - 示例：`show grants from 'jerry'@'%'`
- 刷新权限：`flush privileges;`
- 取消授权：
  - 格式：`revoke 权限 privileges on 库.表 from '用户'@'主机';`
  - 示例：`revoke all privileges on test.* from 'jerry'@'%';`
- 删除用户：
  - 格式：`drop user '用户'@'主机'`
  - 示例：`drop user 'jerry@'%'`
- root用户远程登录：
  - `sudo mysql_secure_installation`，根据提示配置，允许root用户远程登录
  - 修改配置文件：`sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf`
    - 将bind-address=127.0.0.1，改为bind-address=0.0.0.0
  - 给root添加授权主机，%表示所有主机
    - 如：`grant all privileges on *.* to 'root'@'%' identified by '123456'`
    - 给root用户添加任意主机以123456作为密码登录，对所有的库下的所有表用于所有权限
# 多表联合查询
- 隐式内连接：没有出现join关键的连接
  - 示例：`select username,name from user,goods where user.gid=goods.gid;`
  - 说明：查看用户买的商品名
- 显式内连接：会出现join关键字，后面的条件使用on
  - 示例：`select username,name from user [inner/cross] join goods on user.gid=goods.gid;`
  - 说明：功能同上
  - join前可以加inner，也可以使用cross，也可以省略
外左连接：以左表为主
  - 示例：`select username,name from user left [outer] join goods on user.gid=goods.gid;`
  - 说明：以左表为主，显示左边所有内容，右表不匹配的选项显示NULL
- 外右连接：以右表为主
  - 说明：将left换成right即表示右连接
多表联合操作：
- 子(嵌套)查询操作
  - 说明：一条sql语句的查询结果作为另一条sql语句的条件
  - 示例：`select * from user where gid in (select gid from goods);`
- 联合更新(同时更新多个表的数据)
  - 示例：`update user u,good g set u.gid=0,g.price=8000 where u.gid=g.gid and u.id=1`
###清空数据

- 方式1：`delete from aaa;`
  - 删除数据，在插入数据，自增字段的值仍然接着原来的数据增加
- 方式2：`truncate table aaa;`
  - 删除数据，同时将auto_increment的初始值设为1
## 事务处理语言(DTL)
- 开启事务：禁止自动提交
  - `set autocommit=0;`
- 操作回滚：出现异常时使用
  - `rollback;`
- 提交操作：没有异常
  - `commit;`
  ### 外键索引(约束)

- 简介：所谓外键就是一个表的主键作为了另一个表的关联字段，然后在添加一点设置就成为了外键

- 作用：可以简化多个关联表之间的操作，如：删除一个另一个跟着删除

- 示例：一个表示用户组的表t_group，一个用户表t_user

  ```
  create table t_group(
    id int,
    name varchar(20),
    primary key(id)
  );
  create table t_user(
    id int,
    name varchar(20),
    groupid int,
    primary key(id),
    foreign key(groupid) references t_group(id) on delete restrict on update restrict
  );
  ```
- 约束行为
  - NO ACTION 或 RESTRICT：删除或修改时不做任何操作，直接报错
  - SET NULL：删除或更新时，都设置为null
  - CASCADE：删除或更新时，同时删除或更新从表的数据
### python操作mysql
- 安装pycharm，并配置解析器
- 安装pip3：`sudo apt-get install python3-pip3`
- 安装pymysql：`pip3 install pymysql`
- 示例：见代码。
### 数据库的备份与恢复
- 备份：`mysqldump u-uroot -p test > test.sql`
- 恢复：`mysql -uroot -p test < test.sql`
import pymysql

db = pymysql.connect(host='localhost', user='root', password='123456')
db.select_db('test')
db.set_charset('utf8')
cursor = db.cursor()
# 开启事务
db.begin()
try:
    sql = 'insert into user(username, password, gid) values ("xiaoling", "123456", 1)'
    # 返回受影响的行
    rowcount = cursor.execute(sql)
    print(rowcount)
    # 提交事务
    db.commit()
except:
    print('出现异常')
    # 回滚操作
    db.rollback()
# 获取最后插入数据的自增ID
print(cursor.lastrowid)
cursor.close()
db.close()

修改字段编码方式：

alter table 表名 convert to character set utf8;