python操作mysql数据库

1.第三方库

pymysql

pip install pymysql

2.具体操作

# !/usr/bin/env/python
# _*_coding:utf-8_*_
# Data:2019-04-07
# Auther:苏莫
# Link:QQ2388873062
# Address:https://blog.csdn.net/lingluofengzang
# PythonVersion:python2.7
# Filename:MySQL_operate.py

import sys
import pymysql

reload(sys)
sys.setdefaultencoding('utf-8')

# 连接数据库
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 创建数据表
sql_create = """CREATE TABLE EMPLOYEE (
		FIRST_NAME  CHAR(20) NOT NULL,
		LAST_NAME  CHAR(20),
		AGE INT,  
		SEX CHAR(1),
		INCOME FLOAT )"""

# 删除数据表
# 清除表内数据，保存表结构
sql_truncate = "truncate table EMPLOYEE"
# 删除表
sql_drop = "drop table EMPLOYEE"

# 插入数据
sql_insert = """INSERT INTO EMPLOYEE(FIRST_NAME,
		LAST_NAME, AGE, SEX, INCOME)
		VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

sql_insert = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s',  %s,  '%s',  %s)" % \
       ('Mac', 'Mohan', 20, 'M', 2000)

# 删除数据
sql_delete = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)

# 更新数据
sql_update = "UPDATE EMPLOYEE \
		SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')

# 查询数据
sql_select = "SELECT * FROM EMPLOYEE \
		WHERE INCOME > %s" % (1000)

try:
	# 创建游标对象
	cursor = db.cursor()
	# 执行SQL语句、返回受影响的行数
	reCount = cursor.execute(sql)
	# 提交到数据库执行[如果是查询可以不用commit]
	db.commit()
except Exception as e:
	# 如果发生错误则回滚
	db.rollback()
else:
	# 读取查询数据
	# 使用 fetchone() 方法获取单条数据.
	data = cursor.fetchone()
	# 获取所有记录列表,tuple类型
	results = cursor.fetchall()
finally:
	cursor.close()
	# 关闭数据库
	db.close()

.