#coding=utf8
import pymssql



#連接數(shù)據(jù)庫函數(shù)
def get_db(sql, typd='select',charset='GB18030'):
    #sql server 配置數(shù)據(jù)庫時候 避免中文亂碼   設(shè)置編碼方式  為 GB18030 即可   插入 需要使用 utf8
    #或者convert(nvarchar(20), mtl_name)    二選一
    connect = pymssql.connect('***.***.***.***', '***', '***', '***',charset=charset) # 服務(wù)器名,賬戶,密碼,數(shù)據(jù)庫名   , 最后這個一定要寫要不中文就是亂碼

    cursor = connect.cursor()
    cursor.execute(sql)
    if typd == 'add':
        connect.commit()  # 提交
        rs = ''
    elif typd == 'select':
        rs = cursor.fetchall()
    print('創(chuàng)建游標(biāo)')
    try:
        print('提交數(shù)據(jù)')
        yield rs
    finally:
        print('關(guān)閉')
        cursor.close()  # 關(guān)閉游標(biāo)
        connect.close()  # 關(guān)閉連接

name ='aa00002'

# sql = "insert into wzh_ic_porid_mes (mtl_no,mtl_name,special,uom,qty) values ({},{},{},{},{})".format('aa00002','粉頭部','粉色','個',50)
sql ="select *  from wzh_ic_porid_mes where mtl_no ='{0}'".format(name)

getmess = next(get_db(sql, typd='select'))
print(getmess)

sql = "insert into wzh_ic_porid_mes (mtl_no,mtl_name,special,uom,qty) values ('{0}','{1}','{2}','{3}',{4})".format('aa00003','粉頭部','粉色','個',50)
# sql ="select *  from wzh_ic_porid_mes where mtl_no ='{0}'".format(name)

getmess = next(get_db(sql, typd='add',charset='utf8'))

sql ="update wzh_ic_porid_mes set mtl_name ='粉1頭部' where mtl_no='{}'".format('aa00003')

# sql ="delete from wzh_ic_porid_mes where mtl_no ='{0}' ".format('aa00003')
getmess = next(get_db(sql, typd='add',charset='utf8'))

sql ="delete from wzh_ic_porid_mes where mtl_no ='{0}' ".format('aa00003')
getmess = next(get_db(sql, typd='add',charset='utf8'))