# encoding = utf-8

create_database = 'CREATE DATABASE IF NOT EXISTS davieyang DEFAULT CHARSET utf8 COLLATE utf8_general_ci;'

drop_table = 'DROP TABLE testdata;'

create_table = """

    CREATE TABLE testdata(

        ID int primary key not null auto_increment comment '主键',

        BOOKNAME varchar(40) unique not null comment '书名',

        AUTHOR varchar(30) not null comment '作者'

    )engine = innodb character set utf8 comment '测试数据表';

"""

# encoding = utf-8

"""

__title__ = ''

__author__ = 'davieyang'

__mtime__ = '2018/4/21'

"""

import pymysql

from TestData.SqlScripts import create_table

from TestData.SqlScripts import create_database

from TestData.SqlScripts import drop_table

class MySQL(object):

    def __init__(self, host, port, dbName, username, password, charset):

        self.conn = pymysql.connect(

            host=host,

            port=port,

            db=dbName,

            user=username,

            password=password,

            charset=charset

        )

        self.cur = self.conn.cursor()

    def create(self):

        try:

            self.cur.execute(create_database)

            self.conn.select_db("davieyang")

            self.cur.execute(drop_table)

            self.cur.execute(create_table)

            '''

            cur.execute("drop database if exists davieyang")   #如果davieyang数据库存在则删除

            cur.execute("create database davieyang")   #新创建一个数据库davieyang

            cur.execute("use davieyang")         #选择davieyang这个数据库

            # sql 中的内容为创建一个名为testdata的表

            sql = """create table testdata(id BIGINT,name VARCHAR(20),age INT DEFAULT 1)"""  #()中的参数可以自行设置

            conn.execute("drop table if exists testdata") # 如果表存在则删除

            conn.execute(sql)# 创建表

            # 删除

            # conn.execute("drop table testdata")

            conn.close()# 关闭游标连接

            connect.close()# 关闭数据库服务器连接 释放内存

            '''

        except pymysql.Error as e:

            raise e

        else:

            self.cur.close()

            self.conn.commit()

            self.conn.close()

            print(u"创建数据库和表成功")

    def insertDatas(self):

        try:

            sql = "insert into testdata(bookname, author) values(%s, %s);"

            self.cur.executemany(sql, [('selenium xml DataDriven', 'davieyang'),

                                    ('selenium excel DataDriven', 'davieyang'),

                                    ('selenium ddt data list', 'davieyang')])

        except pymysql.Error as e:

            raise e

        else:

            self.conn.commit()

            print(u"初始数据插入成功")

            self.cur.execute("select * from testData;")

            for i in self.cur.fetchall():

                print(i[1], i[2])

            self.cur.close()

            self.conn.close()

    def getDataFromDataBase(self):

        # 从数据库中获取数据

        # bookname作为搜索关键词，author作为期望结果

        self.cur.execute("select bookname, author from testdata;")

        # 从查询区域取回所有查询结果

        dataTuple = self.cur.fetchall()

        return dataTuple

    def closeDataBase(self):

        # 数据库清理

        self.cur.close()

        self.conn.commit()

        self.conn.close()

if __name__ == "__main__":

    db = MySQL(

        host="localhost",

        port=3306,

        dbName="davieyang",

        username="root",

        password="root",

        charset="utf8"

    )

    print(db.getDataFromDataBase())

    db.closeDataBase()

# encoding = utf-8

"""

__title__ = ''

__author__ = 'davieyang'

__mtime__ = '2018/4/21'

"""

from selenium import webdriver

import unittest

import time

import logging

import traceback

import ddt

from Util.MysqlDBUtil import MySQL

from selenium.common.exceptions import NoSuchElementException

# 初始化日志对象

logging.basicConfig(

    # 日志级别

    level=logging.INFO,

    # 时间、代码所在文件名、代码行号、日志级别名字、日志信息

    format='%(asctime)s %(filename)s[line: %(lineno)d] %(levelname)s %(message)s',

    # 打印日志的时间

    datefmt='%a, %d %b %Y %H:%M:%S',

    # 日志文件存放的目录及日志文件名

    filename='F:\\DataDriven\\TestResults\TestResults.TestResults',

    # 打开日志的方式

    filemode='w'

)

def getTestDatas():

    db = MySQL(

        host="localhost",

        port=3306,

        dbName="davieyang",

        username="root",

        password="root",

        charset="utf8"

    )

    # 从数据库中获取测试数据

    testData = db.getDataFromDataBase()

    db.closeDataBase()

    return testData

@ddt.ddt

class DataDrivenByMySQL(unittest.TestCase):

    def setUp(self):

        self.driver = webdriver.Chrome(executable_path=r"F:\automation\webdriver\chromedriver.exe")

    @ddt.data(* getTestDatas())

    def test_dataDrivenByMySQL(self, data):

        # 对获得的数据进行解包

        testData, expectData =data

        url = "http://www.baidu.com"

        self.driver.get(url)

        self.driver.maximize_window()

        print(testData, expectData)

        self.driver.implicitly_wait(10)

        try:

            self.driver.find_element_by_id("kw").send_keys(testData)

            self.driver.find_element_by_id("su").click()

            time.sleep(3)

            self.assertTrue(expectData in self.driver.page_source)

        except NoSuchElementException as e:

            logging.error(u"查找的页面元素不存在，异常堆栈信息为：" + str(traceback.format_exc()))

        except AssertionError as e:

            logging.info(u"搜索 ‘%s’,期望 ‘%s’ ,失败" % (testData, expectData))

        except Exception as e:

            logging.error(u"未知错误，错误信息：" + str(traceback.format_exc()))

        else:

            logging.info(u"搜索 ‘%s’,期望 ‘%s’ ,通过" % (testData, expectData))

    def tearDown(self):

        self.driver.quit()

if __name__ == "__main__":

    unittest.main()