# -*- coding: UTF-8 -*-

import MySQLdb

# 数据库参数
user = 'root'
pwd = '******'
host = 'localhost'
db = 'pythonTest'

# # 创建数据库
# createDB="create database pythonTest"
#
# try:
#     connect = MySQLdb.connect(host=host, user=user, passwd=pwd)
#     cursor = connect.cursor()
#     cursor.execute(createDB)
# except Exception as e:
#     print  e
#

# 连接已存在的数据库
try:
    connect = MySQLdb.connect(host=host,user=user,passwd=pwd,db=db)
    cursor = connect.cursor()
except Exception as e:
    print  e

# # 删除数据库
# deleteDB="drop database pythonTest1"
#
# try:
#     connect = MySQLdb.connect(host=host, user=user, passwd=pwd)
#     cursor = connect.cursor()
#     print "数据库连接成功"
# except Exception as e:
#     print  e
#
#
# try:
#     cursor.execute(deleteDB)
# except Exception as e:
#     print  e

#判断表是否存在
def existTable(cursor,name):

    selectTab = 'select * from %s' % (name)
    try:
        cursor.execute(selectTab)
        rows= cursor.fetchall()
        return True
    except Exception as e:
        return False




# 创建表

exist = existTable(cursor,'user')

if exist == False:

    createTab = "create table user1 if NOT EXISTS (id int  NOT NULL ,name VARCHAR(45) NOT NULL ,PRIMARY KEY(id))"
    try:
        cursor.execute(createTab)
    except Exception as e:
         print  e


# # 删除表
# dropTab = 'drop table user1'
#
# try:
#     cursor.execute(dropTab)
# except Exception as e:
#     print  e
#


# 插入数据

if exist:

    # # 单条插入
    # insertTab = 'insert into user(id,name ) values(0, "name1") '
    #
    # try:
    #     cursor.execute(insertTab)
    #     connect.commit()
    # except Exception as e:
    #     print e

    # 多条插入
    i = 1
    insertTab = list()
    insertTab = "insert into user(id,name ) values(%s,%s) "
    insertArr = []
    while i < 10:
        insertArr.append((i, 'lv%s' % i))
        i += 1

    try:
        print cursor.executemany(insertTab, insertArr)
        connect.commit()
    except (ZeroDivisionError, Exception) as e:
        connect.rollback()
        print e

# 查询数据
selectTab='select * from user'

try:
    cursor.execute(selectTab)
    # data = cursor.fetchone()
    results = cursor.fetchall()
except Exception as e:
    print  e

for user in results:
    print "id=%d name=%s" % (user[0],user[1])

# 删除数据
deleteTab = 'delete from user where id<11'
try:
    cursor.execute(deleteTab)
    connect.commit()
except Exception as e:
    print  e

# 关闭数据库连接
connect.close()