#!/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()
Python3如何读写MySQL数据库
最新推荐文章于 2024-02-28 09:21:25 发布