python3操作mysql数据库表01（基本操作）

#!/usr/bin/env python
# -*- coding:UTF-8 -*-

import requests
from bs4 import BeautifulSoup
from bs4 import NavigableString
import os
import xlrd
import pymysql

#设置语言
os.environ['NLS_LANG']='SIMPLIFIED CHINESE_CHINA.UTF8'

# 打开数据库连接
db = pymysql.connect("192.168.1.133","root","123456","employees")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()

print ("Database version : %s " % data)

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


#查询 插入 删除 更新操作
db=pymysql.connect("192.168.1.133","root","123456","employees")
cur=db.cursor()
res=cur.execute("select version()")
print(res)

data=cur.fetchone()
print("data version %s"%data)

dropSql="DROP TABLE IF EXISTS test01"

createSql="""CREATE TABLE test01 (
            FIRST_NAME  CHAR(20) NOT NULL,
            LAST_NAME  CHAR(20),
            AGE INT,
            SEX CHAR(1),
            INCOME FLOAT )"""

insertSql="""INSERT INTO test01(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

# selectSql="select * from salaries sal where sal.salary > '%d'" % (150000)
updateSql = "UPDATE salaries sal SET sal.salary = sal.salary + 1000 WHERE emp_no = '%d'" % (43624)

db.close()
try:
    cur.execute(insertSql)
    db.commit()
except:
    db.rollback()

    cur.execute(dropSql)
    cur.execute(createSql)

try:
    cur.execute(updateSql)
    
    #获取对应字段值
    # results=cur.fetchall()
    # for row in results:
    #     emp_no = row[0]
    #     salary = row[1]
    #     from_date = row[2]
    #     to_date = row[3]
    #     print ("emp_no=%d,salary=%.0f,from_date=%s,to_date=%s" % \
    #        (emp_no, salary, from_date, to_date ))
except:
    print("unget data,happen errors")

db.close()