#!/usr/bin/env python3
# -*- coding: utf-8 -*-
#----------------------------------------------------------#
# Date    : xxxx-xx-xx                                     #
# Author  : Created by zhouwanchun.                        #
# Wechat  : loveoracle11g                                  #
# Function: This scripts function is ...                   #
# Version : 1.1                                            #
#----------------------------------------------------------#

### 导出mysql-connector-python模块
import mysql.connector

### 连接MySQL数据库
conn = mysql.connector.connect(
    host='172.17.0.17',
    port=5730,
    user='zhouwanchun',
    password='zhouwanchun123',
    charset='utf8'
)

### 创建SQL命令通道
sql_cmd = conn.cursor()



# 使用fetchall()，获取所有的查询结果集，返回值为一个list，每一个元素是一个tuple.
sql1 = "show databases;"
sql_cmd.execute(sql1)
result = sql_cmd.fetchall()
# print(result)
print("----------------取出数据库库名----------------")
for i in result:
    print(i[0])



# 使用fetchone()每次只取一条结果集，每调用一次之后，内部指针会指向下一条结果集
### 取出每一个账号
sql3 = "select user,host from mysql.user;"
sql_cmd.execute(sql3)
result_1_row = sql_cmd.fetchone()
# print(result_1_row)
print("----------------取出数据库账号----------------")
print("'" + result_1_row[0] + "'" + '@' + "'" + result_1_row[1] + "'")



# 使用fetchmany(num)指定每次取出num条结果集
sql2 = "select * from app01.t1;"
sql_cmd.execute(sql2)
result_5_row = sql_cmd.fetchmany(5)
print("----------------取出5条业务数据---------------")
for i in result_5_row:
    print(i)



# insert、update、delete操作
sql4 = "insert into app01.t1(c1,c2,c3) values(uuid(),@@hostname,@@port);"
sql_cmd.execute(sql4)
conn.commit()
print(sql_cmd.rowcount, "数据插入成功")



# 使用预处理格式（占位符格式）
sql5 = "select * from app01.t1 where id=%s and c2=%s;"
val = (1, 'db01')
sql_cmd.execute(sql5, val)
res = sql_cmd.fetchall()
print(res)



# 批量插入数据
sql6 = "insert into app01.t1(c1,c2,c3) values(%s, %s, %s);"
val = [
    (uuid(),@@hostname,@@port),
    (uuid(),@@hostname,@@port),
    (uuid(),@@hostname,@@port)
]
sql_cmd.executemany(sql6, val)
conn.commit()
print(sql_cmd.rowcount, "数据插入成功")