《Python从小白到大牛》第二部分-第二章（数据库编程）

"""
数据库编程
Python中将数据保存在数据库中技术有很多种，但主要分为两类，遵循Python DB-API规范技术和ORM技术。
Python DB-API规范通过在Python中编写SQL语句访问数据库。（重点）
ORM技术是面向对象的，对数据的访问是通过对象实现的，程序员不需要使用SQL语句。

现在主流的数据库管理系统有oracle、SQL Server、DB 2、Sysbase、MySQL和SQLite等，以下主要基于MySQL数据库管理系统的使用和管理。
建立数据库连接：
通过connect(parameters...)函数实现，使用parameters参数连接数据库，连接成功返回Connection对象。
host:数据库主机名或IP地址
port:连接数据库端口号
user:访问数据库账号
password或passwd：访问数据库密码
database或db:数据库中的库名
charset:数据库编码格式
还有很多参数，可以参考：http://pymysql.readthedocs.io/en/latest/modules/connections.html

Connection对象有一些重要的方法，这些方法如下
close()：关闭数据库连接，关闭之后再使用数据库连接将引发异常
commit():提交数据库事物
rollback():回滚数据库事物
cursor（）：获得Cursor游标对象

如果事务中有的操作没有成功完成，则事务中所有操作都需要被回滚，回到事务执行前的状态。

创建游标：
一个cursor游标对象表示一个数据库游标，游标暂时保存了SQL操作所影响到的数据。
游标Cursor对象有很多方法和属性，其中基本SQL操作方法有以下几种：
execute(operation[,parameters])
执行一条SQL语句，operation是SQL语句，parameters是为SQL提供的参数，可以是序列或者字典类型，返回值是整数，表示执行SQL语句影响的行数。
executemany(operation[,seq_of_params])
批量执行SQL语句，operation是SQL语句，seq_of_params是为SQL提供的参数，seq_of_params是序列，返回值是整数，表示执行SQL语句影响的行数。
callproc(procname[,parameters])
执行存储过程，procname是存储过程名，parameters是为存储过程提供的参数。
执行SQL查询语句也是通过execute()和executemany()方法实现的，但是这两个方法返回的都是整数，对于查询没有意义，因此使用execute()he executemany（）
方法执行查询后，还要通过提取方法提取结果集，相关提取方法如下
fetchone():从结果集中返回一条记录的序列，如果没有数据返回None;
fetchmany():从结果集返回小于或等于size的记录数序列，如果没有数据返回空序列，size默认情况下是整个游标的行数。
fetchall():从结果集中返回所有数据

对数据库表中可以进行4类操作：数据插入(Create)、数据删除（Delete）、数据更新（Update）、数据查询（Read）

数据库编程的一般过程：
1.建立数据库连接 2.创建游标对象 3.执行SQL操作（插入、删除、更新） 4.SQL操作成功 5.提交数据库事务 6.关闭游标 7.关闭数据连接
                                                        4.SQL操作失败 5.回滚数据库事务 6.关闭游标 7.关闭数据连接
                           3.执行SQL操作（查询）  4.提取结果集  5.关闭游标  6.关闭数据连接

编写数据库脚本mydb-mysql-schema-gbk.sql
/* chapter17/mydb-mysql-schema-gbk.sql */
/* 创建数据库 */
CREATE DATABASE IF NOT EXISTS MyDB;

use MyDB;

/* 用户表 表名以及各列的名称以及属性 */
CREATE TABLE IF NOT EXISTS user(
name varchar(20),    /* 用户ID */
userid int,         /* 用户密码 */
PRIMARY KEY (userid));

/* 插入初始数据 */
INSERT INTO user VALUES('Tom',1);
INSERT INTO user VALUES('Ben',2);

Python DB-API2规范中的异常类继承层次可以自行了解，
StandardError是Python DB-API基类，一般的数据库开发使用DatabaseError异常及其子类。

"""
import pymysql

#1.建立连接
connection = pymysql.connect(host='localhost', user='root', password='Hkx19991117', database='MyDB', charset='utf8')
print(type(connection))

try:
    #2.创建游标对象
    with connection.cursor() as cursor:
        #3.执行SQL操作
        sql = 'select name,userid from user where userid >%(id)s'    #%（id）s是命名占位符
        cursor.execute(sql,{'id':0})     #执行sql语句，并绑定参数，绑定参数是字典类型，id是占位符中的名字，是字典中的键。

        #还有一种写法：
        #sql = 'select name,userid from user where userid > %s'    #%s是占位符
        #cursor.execute(sql, [0])            #绑定参数是序列类型，另外参数只有一个时，可以直接绑定，例如cursor.execute(sql,0)

        #4.提取结果集
        result_set = cursor.fetchall()

        for row in result_set:
            print('id:{0} - name:{1}'.format(row[1], row[0]))
        #with 代码块结束，5.关闭游标
finally:
    #6.关闭数据连接
    connection.close()


#无条件查询
#1.建立连接
connection = pymysql.connect(host='localhost', user='root', password='********', database='MyDB', charset='utf8')
try:
    #2.创建游标对象
    with connection.cursor() as cursor:
        #3.执行SQL操作
        sql = 'select max(userid) from user'
        cursor.execute(sql)

        #4.提取结果集
        row = cursor.fetchone()
        print(row)
        if row is not None:
            print('最大用户id:{0}'.format(row[0]))
        #with 代码块结束，5.关闭游标
finally:
    #6.关闭数据连接
    connection.close()

def read_max_userid():  #查询最大ID值
    # 无条件查询
    # 1.建立连接
    connection = pymysql.connect(host='localhost', user='root', password='Hkx19991117', database='MyDB', charset='utf8')
    try:
        # 2.创建游标对象
        with connection.cursor() as cursor:
            # 3.执行SQL操作
            sql = 'select max(userid) from user'
            cursor.execute(sql)

            # 4.提取结果集
            row = cursor.fetchone()
            print(row)
            if row is not None:
                print('最大用户id:{0}'.format(row[0]))
                return row[0]
            else:
                return -1
            # with 代码块结束，5.关闭游标
    finally:
        # 6.关闭数据连接
        connection.close()




#数据修改操作
connection = pymysql.connect(host='localhost', user='root', password='Hkx19991117', database='MyDB', charset='utf8')

max_id = read_max_userid()

try:
    #2.创建游标对象
    with connection.cursor() as cursor:
        #3.执行SQL操作
        sql = 'insert into user (userid, name) values (%s,%s)'
        nextid = max_id + 1
        name = 'Tony' + str(nextid)
        cursor.execute(sql, (nextid, name))

        #4.向数据库提交事务
        connection.commit()

        #with代码块结束 5.关闭游标

except pymysql.DatabaseError:
    #4.回滚数据库事务
    connection.rollback()
finally:
    #6.关闭数据连接
    connection.close()

#数据更新
connection = pymysql.connect(host='localhost', user='root', password='Hkx19991117', database='MyDB', charset='utf8')

#2.创建游标
try:
    with connection.cursor() as cursor:

        #执行sql指令
        sql = 'update user set name = %s where userid > %s'
        affectedcount = cursor.execute(sql, ('Tom', 2))
        print('影响的数据行数：{0}'.format(affectedcount))

        #提交数据库事务
        connection.commit()
    #with代码块结束，游标关闭
except pymysql.DatabaseError as e:
    #4.回滚数据库事务
    connection.rollback()
    print(e)

finally:
    #6.关闭数据连接
    connection.close()

#数据删除
connection = pymysql.connect(host='localhost', user='root', password='Hkx19991117', database='MyDB', charset='utf8')

max_id = read_max_userid()

try:
    #2.创建游标对象
    with connection.cursor() as cursor:
        #执行sql指令
        sql = 'delete from user where userid = %s'
        affectedcount = cursor.execute(sql,(max_id))
        print('影响行数：{0}'.format(affectedcount))

        #4.提交数据库事务
        connection.commit()

        #with代码块结束 5.关闭游标
except pymysql.DatabaseError:
    #4.回滚数据库事务
    connection.rollback()

finally:
    #6.关闭数据库连接
    connection.close()

"""
NoSQL数据存储
目前大部分数据库都是关系型的，通过SQL语句操作数据库。但是也有一些数据库是非关系型的，不通过SQL语句操作数据库，这些数据库称为NoSQL数据库。
dbm数据库是最简单的NoSQL数据库，它不需要安装，直接通过键值对数据存储。
Python内置dbm模块提供了存储dbm数据的API

dbm数据库的打开和关闭：
打开数据库用open()函数
dbm.open(file, flag='r')
file是数据库文件名，包括路径；参数flag是文件打开方式，flag取值‘r’ 只读（默认） ‘w’ 读写 ‘c’以读写方式打开数据库，如果不存在则创建 ‘n’ 始终创建一个新的空数据库，打开方式为读写

关闭数据库用close()函数，更推荐用with as语句来管理数据资源释放
with dbm.open(DB_NAME,'c') as db:
    pass

dbm数据存储方式类似于字典数据结构，通过键写入或读取数据。（注意：dbm数据库保存的数据是字符串类型或者字节序列类型）
1.写入数据
d[key] = data
2.读取数据
data = d[key] 或者 d.get(key, defaultvalue)
data = d[key]这中方法，如果key值不在则会抛出KeyError异常。d.get(key, defaultvalue)则会返回默认值
3.删除数据
del d[key]
4.查找数据
flag = key in d

"""
import dbm

with dbm.open('mydb', 'c') as db:
    db['name'] = 'tony'
    print(db['name'].decode())     #取出数据  db['name']取出的是字节序列，如果要取出字符串，则需要加.decode()
    age = int(db.get('age', b'18').decode())  #取出数据
    print(age)
    if 'age' in db:
        db['age'] = '20'
    
    del db['name']