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