# -*- encoding: utf-8 -*-

import pymssql

# server    数据库服务器名称或IP
# user      用户名
# password  密码
# database  数据库名称

server = "."
user = "***"
password = "***"
database = "***"
conn = pymssql.connect(server, user, password, database)
cursor = conn.cursor()


# 新建表


def CreateTable():
    sql = """
    IF OBJECT_ID('persons', 'U') IS NOT NULL DROP TABLE persons
    CREATE TABLE persons (id INT NOT NULL identity(1,1),name VARCHAR(100),age int,PRIMARY KEY(id))
    """
    cursor.execute(sql)
    conn.commit()


# 批量插入数据
def InsertData():
    sql = "INSERT INTO persons(name,age) VALUES (%s, %d)"
    data = [
        ('zhangsan', 15),
        ('lisi', 16),
        ('wangwu T.', 17)]
    cursor.executemany(sql, data)
    # 如果没有指定autocommit属性为True的话就需要调用commit()方法
    conn.commit()


# 删除操作
def DeleteData():
    sql = "delete persons where id=5"
    cursor.execute(sql)
    conn.commit()


# 查询操作
def SelectTable():
    sql = "SELECT * FROM sys_user"
    cursor.execute(sql)
    row = cursor.fetchone()
    while row:
        print("ID=%s, Name=%s" % (row[0], row[1]))
        row = cursor.fetchone()
    # 也可以使用for循环来迭代查询结果
    # for row in cursor:
    #     print("ID=%d, Name=%s" % (row[0], row[1]))


# 修改操作
def UpdateData():
    sql = "update [persons] set name ='Python1' where id<3"
    cursor.execute(sql)
    conn.commit()


def main():
    # CreateTable()
    # InsertData()
    # DeleteData()
    # UpdateData()
    SelectTable()
    conn.close()


if __name__ == '__main__':
    main()