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()