# -*- coding:utf-8 -*-
# 数据库操作,使用pymysql

import pymysql

# 打开数据库
db = pymysql.connect(host = "127.0.0.1", 
 port = 3306, 
 user = "root", 
 password = "12345678", 
 db = "pythonDB",
 cursorclass = pymysql.cursors.DictCursor)


# 错误用法-----------------------------------

# 错误示例-----------
# 报错信息:AttributeError: 'function' object has no attribute 'execute'

# # 创建游标对象
# # 1 结果以元组显示
# cursor = db.cursor
# # 2 查询数据
# sqlRead = "select * from personTable;"
# cursor.execute(sqlUpdate)
# data = cursor.fetchone() # 取一条数据
# print "获取信息:",data
# # 3 关闭游标
# cursor.close()
# # 4 关闭数据库
# db.close()

# 错误示例-----------


# 正解用法-----------------------------------

# 正确示例-----------

# 获取数据
# sqlRead = "select * from personTable;"
# try:
# with db.cursor() as cursor:
# cursor.execute(sqlRead)
# # data = cursor.fetchone() # 获取一条数据
# data = cursor.fetchall() # 获取所有数据
# # data = cursor.fetchmany(5) # 获取n条数据
# # print "data:",data
# for item in data:
# print "item:",item
# except Exception as e:
# print "read error:",e
# finally:
# db.close()

# 正确示例-----------





# 删除已存在表
# sqlDropTable = "drop table if exists personTable"
# cursor.execute(sqlDropTable)


# 使用预处理语句创建表
# sqlCreateTable = "create table personTable (userid int(11) not null auto_increment primary key, name varchar(255) collate utf8_bin not null, nickname varchar(255) collate utf8_bin not null)"
# try:
# with db.cursor() as cursor:
# cursor.execute(sqlCreateTable)
# db.commit() # 事务提交修改
# except Exception as e:
# print "create table error:",e
# db.rollback() # 事务回滚
# finally:
# db.close()


# 获取数据
# sqlRead = "select * from personTable;"
# sqlReadCondition = "select userid, name from personTable where name = %s"
# try:
# with db.cursor() as cursor:
# # cursor.execute(sqlRead)
# cursor.execute(sqlReadCondition, ("devZhang"))
# data = cursor.fetchone()
# print "fetch one data:",data
# except Exception as e:
# print "fetch error:",e
# finally:
# db.close()



# 插入数据
# sqlInsert = "insert into personTable (userid, name, nickname) values (%s, %s, %s)"
# # 单条记录
# # dataInsert = [10005, "李四", "大番薯"]
# # 多条记录
# dataInsert = [(10001, "张绍裕", "大番薯"),(10002, "张三丰", "007"),(10003, "张国梁", "大佬")]
# try:
# with db.cursor() as cursor:
# # cursor.execute(sqlInsert, (10000, "张绍裕", "大番薯"))
# # cursor.execute(sqlInsert, dataInsert)
# cursor.executemany(sqlInsert, dataInsert)
# db.commit()
# with db.cursor() as cursor:
# cursor.execute("select * from personTable")
# data = cursor.fetchall()
# print "fetch data:",data
# except Exception as e:
# print "insert error:",e
# db.rollback()
# finally:
# db.close()


# 更新数据
# sqlUpdate = "update personTable set nickname = %s where userid = %s;"
# try:
# with db.cursor() as cursor:
# cursor.execute(sqlUpdate, ["番薯大佬", 10000])
# db.commit()
# except Exception as e:
# print "update error:",e
# db.rollback()
# finally:
# db.close()


# 删除数据
# sqlDelete = "delete from personTable where userid = %s"
# userid = 10001
# try:
# with db.cursor() as cursor:
# cursor.execute(sqlDelete, userid)
# db.commit()
# except Exception as e:
# print "delete error:", e
# db.rollback()
# finally:
# db.close()


# 获取数据
# sqlRead = "select * from personTable;"
# try:
# with db.cursor() as cursor:
# cursor.execute(sqlRead)
# # data = cursor.fetchone() # 获取一条数据
# # data = cursor.fetchall() # 获取所有数据
# data = cursor.fetchmany(5) # 获取n条数据
# # print "data:",data
# for item in data:
# print "item:",item
# except Exception as e:
# print "read error:",e
# finally:
# db.close()