Jupyter Notebook中使用pymysql连接mysql数据库

一、Jupyter Notebook中使用pymysql操作mysql数据库

1. 首先在cmd控制台，下载安装PyMySQL ，PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库
下载命令：pip install pymysql

2. 导入pymysql，使用pymysql进行连接测试

# 1.导入pymysql
import pymysql

# 2.测试pymysql连接
db=pymysql.connect("localhost","root","a")  # 注意自己的用户名 与密码
print(db)

3. 使用pymysql进行建表操作

# 3.建表操作
db=pymysql.connect("localhost","root","a","bike")  # bike为连接的database
cursor=db.cursor() # 使用cursor() 方法创建一个游标对象
#如果数据表已经存在使用execute()方法删除表
cursor.execute("drop table if exists employee")
# 创建数据库表sql语句
sql="""create table employee(
        first_name char(20) primary key ,
        last_name char(20),
        age int,
        sex char(1),
        income float)"""
cursor.execute(sql) # 执行sql语句
db.close() # 关闭连接

4. 使用pymsql进行插入数据操作

# 4. 用pymsql插入数据
db=pymysql.connect("localhost","root","a","bike")
cursor=db.cursor()
sql="""insert into employee(first_name,
        last_name,age,sex,income)
        values ('mac','mohan',20,'M',2000)"""
try:
    #执行sql语句
    cursor.execute(sql)
    # 提交到数据库执行
    db.commit() # 事务代码
    print("添加成功")
except Exception as e:
    print("添加失败",e)
    db.rollback() # 发生错误 回滚事务
db.close() # 最后关闭连接

5. 使用占位符 ，进行插入数据操作

# 5. 用占位符  插入数据
pymysql.paramstyle='format'
db=pymysql.connect("localhost","root","a","bike")
cursor=db.cursor()
sql="""insert into employee(first_name,
        last_name,age,sex,income)
        values ('%s','%s','%s','%s','%s')"""
try:
    #执行sql语句
    cursor.execute(sql % ('mac2','mohan2','20','M','2000') )
    # 提交到数据库执行
    db.commit() # 事务代码
    print("添加成功")
except Exception as e:
    print("添加失败",e)
    db.rollback() # 发生错误 回滚事务
db.close() # 最后关闭连接

6. 使用pymysql进行更新数据操作

# 6.用pymysql更新数据
pymysql.parastyle='format'
db=pymysql.connect("localhost","root","a","bike")
cursor=db.cursor()
sql="update employee set age=age+10 where sex='%c'" %('M')
try:
    #执行sql语句
    cursor.execute(sql )
    # 提交到数据库执行
    db.commit() # 事务代码
    print("更新成功")
except Exception as e:
    print("更新失败",e)
    db.rollback() # 发生错误 回滚事务
db.close() # 最后关闭连接

7. 使用pymysql进行删除数据操作

# 7.用pymysql删除一条数据
pymysql.parastyle='format'
db=pymysql.connect("localhost","root","a","bike")
cursor=db.cursor()
sql="delete from employee where age<%s"
try:
    #执行sql语句
    cursor.execute(sql,'100' )
    # 提交到数据库执行
    db.commit() # 事务代码
    print("删除成功")
except Exception as e:
    print("删除失败",e)
    db.rollback() # 发生错误 回滚事务
db.close() # 最后关闭连接

8. 使用pymysql进行查询所有数据操作

# 5. 查询所有数据
pymysql.parastyle='format'
db=pymysql.connect("localhost","root","a","bike")
cursor=db.cursor()
sql="select * from employee where income > '%d'" %(1000)
try:
    #执行sql语句
    cursor.execute(sql)
    # 获取结果集
    results =cursor.fetchall()
    #循环打印每条记录
    for row in results:
        fname=row[0]
        lname=row[1]
        age=row[2]
        sex=row[3]
        income=row[4]
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" %(fname,lname,age,sex,income))
except Exception as e:
    print("Error: Unable to fectch data",e)
db.close() # 最后关闭连接

9. 使用class类对pymysql进行容器封装

# 更高级：with -> 使用容器封装
# 定义上下文管理器，连接后自动关闭连接
class mysql: # 定义一个生命周期控制
    #初始化获取连接
    def __init__(self,host='127.0.0.1',port=3306,user='root',passwd='a',db='bike',charset='utf8'):
        self.conn=pymysql.connect(host=host,port=port,user=user,passwd=passwd,db=db,charset=charset)
    # 获取游标
    def __enter__(self):
        self.cursor=self.conn.cursor()
        return self.cursor
    # 关闭连接
    def __exit__(self,exceptionType,exceptionVal,trace):
        self.conn.commit()
        self.cursor.close()
        self.conn.close()
        
  #执行sql语句
with mysql() as cursor:
    row_count=cursor.execute("select * from employee")
    # 获取结果集
    results =cursor.fetchall()
    #循环打印每条记录
    for row in results:
        fname=row[0]
        lname=row[1]
        age=row[2]
        sex=row[3]
        income=row[4]
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" %(fname,lname,age,sex,income))

10. 使用函数对pymysql进行容器封装

# 更高级：with -> 使用函数容器封装
# 定义上下文管理器，连接后自动关闭连接
import contextlib

@contextlib.contextmanager
def mysql(host='127.0.0.1',port=3306,user='root',passwd='a',db='bike',charset='utf8'): # 定义一个生命周期控制
    #获取连接
    conn=pymysql.connect(host=host,port=port,user=user,passwd=passwd,db=db,charset=charset)
    # 获取游标
    cursor=conn.cursor()
    try:
        yield cursor  # 惰性求值
    # 关闭连接
    finally:
        conn.commit()
        cursor.close()
        conn.close()
        
  #执行sql语句
with mysql() as cursor:
    row_count=cursor.execute("select * from employee")
    # 获取结果集
    results =cursor.fetchall()
    #循环打印每条记录
    for row in results:
        fname=row[0]
        lname=row[1]
        age=row[2]
        sex=row[3]
        income=row[4]
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" %(fname,lname,age,sex,income))