Python3如何读写MySQL数据库

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import pymysql

def createUserTable():
	# 连接MySQL数据库
	con = pymysql.connect("127.0.0.1", "root", "123456", "userDB")

	# 创建游标对象
	cursor = con.cursor()

	# 如果存在user表，则删除user表
	cursor.execute("Drop table if exists user")

	# 创建表语句
	sql ='''CREATE TABLE USER
	(ID INT PRIMARY KEY NOT NULL,
	NAME CHAR(20) NOT NULL,
	SEX CHAR(4) NOT NULL,
	AGE INT  NOT NULL,
	ADDRESS CHAR(50));''';
	#cursor.execute(sql)
	
	return con;

def insertUserRecords():
	# 连接MySQL数据库
	con = pymysql.connect("127.0.0.1", "root", "123456", "userDB")
	cursor = con.cursor()# 获取操作游标

    try:
		# 执行sql语句
		cursor.execute("INSERT INTO USER(ID, NAME, SEX, AGE, ADDRESS) \ 
		VALUES(1, '张三', '男', 25, '福州市')")
		cursor.execute("INSERT INTO USER(ID, NAME, SEX, AGE, ADDRESS) \
		VALUES(2, '李四', '男', 30, '广州市')")
		cursor.execute("INSERT INTO USER (ID, NAME, SEX, AGE, ADDRESS) \
		VALUES(3, '王五', '女', 20, '成都市')")
		cursor.execute("INSERT INTO USER(ID, NAME, SEX, AGE, ADDRESS) \
		VALUES(4, '赵六', '保密', 40, '杭州市')")
		con.commit()
	except:

		con.rollback()# 发生错误回滚

    	con.close()# 关闭数据连接
    	

def insertRecord():
	# 连接MySQL数据库
    con = pymysql.connect("127.0.0.1", "root", "123456", "userDB")
	cursor = con.cursor()# 获取操作游标
    try:
		# 执行sql语句
        cursor.execute("INSERT INTO USER(ID, NAME, SEX, AGE, ADDRESS) \
	VALUES(%d, '%s', '%s', %d, '%s')" % (5, '杨七', '男', 50, '太原市'))
		con.commit()
	except:
		con.rollback()# 发生错误回滚
	    con.close()# 关闭数据连接

def queryUserRecords():
	# 连接MySQL数据库
    con = pymysql.connect("127.0.0.1", "root", "123456", "userDB")
	cursor = con.cursor()# 获取操作游标

    try:
		cursor.execute("SELECT ID, NAME, SEX, AGE, ADDRESS FROM USER")
		results = cursor.fetchall()# 获取所有记录
        for rowin results:
			print("%d %s %s %d %s" % (row[0], row[1], row[2], row[3], row[4]))
	except:
		print("查询失败")
	con.close()

def updateUserRecords():
# 连接MySQL数据库
    con = pymysql.connect("127.0.0.1", "root", "123456", "userDB")
	cursor = con.cursor()# 获取操作游标
    try:
		cursor.execute("UPDATE USER set address='上海市' where id=4")
		con.commit()
	except:
		con.rollback()
		con.close()
		
def delUserRecords():
	# 连接MySQL数据库
    con = pymysql.connect("127.0.0.1", "root", "123456", "userDB")
	cursor = con.cursor()# 获取操作游标
    try:
		cursor.execute("DELETE FROM USER where ID=3")
		con.commit()
	except:
		con.rollback()
		con.close()
		
if __name__ =="__main__":
	#createUserTable()
	#insertUserRecords()
	#insertRecord()
	#updateUserRecords()
	#delUserRecords()
	queryUserRecords()