python3数据库操作怎么建自定义名字表_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()