import pymysql
import sys


try:
    db = pymysql.connect(host="localhost",user="root",passwd="X8FN;k!TLr?F",database = "mysql")
 #   db = pymysql.connect(host='8.0.23',port=3306,user='root',passwd='IfbBwatr,3vd',db='stock')

except pymysql.connect.Error as err:
    print(err.args[0])
    print(err.args[1])
    print("connect fail")
    sys.exit()
    
cursor = db.cursor()
"""
#建立表
try:
    createSql = "create table stock (date varchar (255), num int)"
    cursor.execute(createSql)
except:
    print("create fail")
"""  

#插入  
try:
    insertSql = "insert into stock (date, num) values ('hello', 12580)"
    cursor.execute(insertSql)
    db.commit()
except:
    print("insert fail")
    db.rollback()

#更新
try:
    updateSql = "update stock set num = 666 where num = 12580"
    cursor.execute(updateSql)
    db.commit()
except:
    print("update fail")
    db.rollback()

#查询
try:
    selectSql = "select * from stock"
    cursor.execute(selectSql)  # 执行sql语句

    results = cursor.fetchall()  # 获取查询的所有记录
    print("date", "num")
    # 遍历结果
    for row in results:
        date = row[0]
        num = row[1]
        print(date, num)
except:
    print("select fail")

#删除
try:
    deleteSql = "delete from stock where num = 666"
    cursor.execute(deleteSql)
    db.commit()
except:
    print("delete fail")


cursor.close()
db.close()