Python连接,关闭,执行和获取MySQL数据库操作

import pymysql

# 首先在cmd中安装MySQL库
# 方法： pip install PyMySQL 安装结束重启pycharm

# 一.连接数据库
# 1.参数1：MySQL服务所在主机IP
# 2.参数2：用户名
# 3.参数3：密码
# 4.参数4：要链接的数据库名
db = pymysql.connect("localhost", "root", "wsw..177122", "wakey")
# 创建一个cursor对象
cursor = db.cursor()
sql = "select version()"
# 执行SQL语句
cursor.execute(sql)
# 获取返回信息
data = cursor.fetchone()  # 查看一条数据
print(data)
# 断开链接
cursor.close()
db.close()

'''
# 创建数据库表
db = pymysql.connect("localhost", "root", "wsw..177122", "wakey")
cursor = db.cursor()
# 检查表是不是存在，如果存在则删除
cursor.execute("drop table if exists handcard")
# 建表
sql = 'create table bandcard(id int auto_increment primary key,money int not null)'
cursor.execute(sql)
cursor.close()
db.close()


# 插入数据
db = pymysql.connect("localhost", "root", "wsw..177122", "wakey")
cursor = db.cursor()
# 插入数据
sql = 'insert into bandcard values(0,100)'
try:
    cursor.execute(sql)  # 存储数据到缓存
    db.commit()  # 把缓存数据放到数据库
except:
    # 如果提交失败，回到上一次数据
    db.rollback()
cursor.close()
db.close()


# 数据库更新操作
db = pymysql.connect("localhost", "root", "wsw..177122", "wakey")
cursor = db.cursor()
sql = 'update bandcard set money = 1000 where id = 1'
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
cursor.close()
db.close()


# 删除数据
db = pymysql.connect("localhost", "root", "wsw..177122", "wakey")
cursor = db.cursor()
sql = 'delete from bandcard where money =200'
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
cursor.close()
db.close()


# 数据库查询操作
# fetchone() 获取下一个查询结果集 ，结果集是一个对象
# fetchall() 接收全部的返回的行
# rowcount   是一个只读属性，返回execute()方法影响的行数
db = pymysql.connect("localhost", "root", "wsw..177122", "wakey")
cursor = db.cursor()
sql = 'select * from bandcard where money>400'
try:
    cursor.execute(sql)
    reslist = cursor.fetchall()
    for row in reslist:
        print("%d--%d" % (row[0], row[1]))  # 打印键值对
except:
    db.rollback()
cursor.close()
db.close()


# 封装所有操作
import pymysql


class AllMySql():
    def __init__(self, host, user, passwd, dbName):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.dbName = dbName

    def connect(self):
        self.db = pymysql.connect(self.host, self.user, self.passwd, self.dbName)
        self.cursor = self.db.cursor()

    def close(self):
        self.cursor.close()
        self.db.close()

    # 查询
    def get_one(self, sql):
        res = None
        try:
            self.connect()
            self.cursor.execute()
            res = self.cursoe.fetchone()
            self.close()
        except:
            print("查询失败")
        return res

    def get_all(self, sql):
        res = ()
        try:
            self.connect()
            self.cursor.execute()
            res = self.cursoe.fetchall()
            self.close()
        except:
            print("查询失败")
        return res

    # 插入
    def insert(self, sql):
        return self.__edit(sql)

    # 增加
    def update(self, sql):
        return self.__edit(sql)

    # 删除
    def delete(self, sql):
        return self.__edit(sql)

    def __edit(self, sql):
        count = 0
        try:
            self.connect()
            count = self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except:
            print("事物提交失败")
            self.db.rollback()
        return count


# 直接使用封装代码执行操作
import AllMySql

s = AllMySql("", "", "", "")
res = s.get_all("")
for row in res:
    print()
    
'''