pymysql使用教程

一、安装pymysql

dos先退出mysql

下载mysql：pip install PyMySQL

二、打开pycharm输入代码

import pymysql

# 链接数据库
# 参数1：mysql服务器所在主机ip
# 参数2：用户名
# 参数3：密码
# 参数4：要链接的数据库名
# db = pymysql.connect("localhost", "root", "200829", "wj" )
db = pymysql.connect("192.168.31.19", "root", "200829", "wj" )

# 创建一个cursor对象
cursor = db.cursor()

sql = "select version()"

# 执行sql语句
cursor.execute(sql)

# 获取返回的信息
data = cursor.fetchone()
print(data)

# 断开
cursor.close()
db.close()

三、报错处理

查询本机ip：ipconfig

修改user表

1、Navicat找到数据库mysql

2、在mysql的表中找到user

3、root 的 hos t改成%

以管理员身份重新启动mysql

四、创建数据库表

import pymysql

db = pymysql.connect("192.168.31.19", "root", "200829", "wj")
cursor = db.cursor()

# 检查表是否存在，如果存在则删除
cursor.execute("drop table if exists bandcard")
# 建表
sql = "create table wj.bandcard(id int auto_increment primary key, money int not null)"
cursor.execute(sql)


cursor.close()
db.close()

五、数据库插入数据

import pymysql

db = pymysql.connect("192.168.31.19", "root", "200829", "wj")
cursor = db.cursor()

sql = "insert into bandcard values(0, 100)"
try:
    cursor.execute(sql)
    db.commit()
except:
    # 如果提交失败，回滚到上一次数据
    db.rollback()

cursor.close()
db.close()

六、数据库删除数据

import pymysql

db = pymysql.connect("192.168.31.19", "root", "200829", "wj")
cursor = db.cursor()

sql = "delete from bandcard where money = 1000"
try:
    cursor.execute(sql)
    db.commit()
except:
    # 如果提交失败，回滚到上一次数据
    db.rollback()

cursor.close()
db.close()

七、数据库查询操作

"""
fetchone()
功能：获取下一个查询结果集，结果集是一个对象

fetchall()
功能：接收全部的返回的行

rowcount：是一个只读属性，返回execute()方法影响的行数
"""
import pymysql

db = pymysql.connect("192.168.31.19", "root", "200829", "wj")
cursor = db.cursor()

sql = "select * from bandcard where money>400"
try:
    cursor.execute(sql)
    relist = cursor.fetchall()
    for row in relist:
        print("%d--%d" % (row[0],row[1]))
except:
    # 如果提交失败，回滚到上一次数据
    db.rollback()

cursor.close()
db.close()

八、封装

import pymysql

class WjSql():
    def __init__(self,host,user,password,dbName):
        self.host = host
        self.user = user
        self.password = password
        self.dbName = dbName

    def connect(self):
        self.db = pymysql.connect(self.host,self.user,self.password,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(sql)
            res = self.cursor.fetchone()
            self.close()
        except:
            print("查询失败")

        return res

    def get_all(self,sql):
        res = ()
        try:
            self.connect()
            self.cursor.execute(sql)
            res = self.cursor.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.count()
            count = self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except:
            print("事物提交失败")
            self.db.rollback()
        return count