当前位置:博客首页 > Python > 正文

第23课 Python操纵mysql-Pymysql模块

作者: Jarvan 分类: Python 发布时间: 2019-07-30 23:55 百度已收录

一、 connection对象

  • host – MySQL的地址(主机名)
  • user – MySQL登录用户名
  • password – MySQL登录密码,别名:passwd
  • database – 要连接的数据库名称,别名:db
  • port – MySQL的端口,默认是3306
  • charset – MySQL数据库的字符集,一般是utf8或者是utf8mb4//这个是可以存储emoji表情的
  • cursorclass – cursor的类,主要用户返回结果的展示方式,一般使用字典的方式
  • autocommit_mode = None 是否自动提交,就是执行了sql语句之后是否
    立即提交到数据库

  • close() 关闭连接
  • begin() 开启事务
  • rollback() 回滚事务,比如事务过程中失败了,那么就可以通过回滚撤销之前的操作,必须要在commit之前
  • commit() 提交更改到数据库,默认情况下,执行之后是不会马上提交到数据库的,需要调用commit提交
  • cursor(cursor=None) 创建一个游标,用来执行MySQL语句的
  • open 测试连接是否打开,如果打开,那么就返回True
  • ping(reconnect=True) 测试服务器连接

二、游标对象cursor

  • close() 关闭游标
  • execute(query, args=None) 执行sql语句,如果有参数的,那么就填入参数
  • 参数:
  • query (str) – 要执行的sql语句
  • args (tuple, list or dict) – sql语句中要用到的参数就可以填写在这里
  • fetchall() 获取所有的结果,一般是用于查询语句的
  • fetchmany(size=None)就是获取指定条数的结果
  • fetchone() 每次获取一条数据

【案例1】Pymysql创建数据库及表

# -*- coding: utf-8 -*-
import pymysql

conn = pymysql.Connect(
    host='localhost',
    user='root',
    passwd='root',
    db='sanwen',
    port=3306,
    charset='utf8',  # 注意此处是utf8,而不是utf-8
    cursorclass=pymysql.cursors.DictCursor  # 返回结果转成字典(dictionary)
)

# 创建数据库表
create_table_sql = """create table demo(
id int primary key auto_increment,
title varchar(255) not null unique,
conent text not null
) default character set=utf8 engine=InnoDB;
"""

try:
    with conn.cursor() as cursor:
        ret = cursor.execute(create_table_sql)
        print(ret)
except pymysql.err.InternalError as err:
    print(err)
finally:
    print('done')

【案例2】Pymysql插入数据

# -*- coding: utf-8 -*-
import pymysql

conn = pymysql.Connect(
    host='localhost',
    user='root',
    passwd='root',
    db='sanwen',
    port=3306,
    charset='utf8',
    cursorclass=pymysql.cursors.DictCursor  # 返回结果转成字典(dictionary)
)

try:
    with conn.cursor() as cursor:
        sql = 'INSERT INTO demo(title, content) VALUES(%s, %s)'
        ret = cursor.execute(sql, args=('这是标题', '这是文章内容'))  # 此处的args可填可不填
        print(ret)
except pymysql.err.InternalError as err:
    print(err)
finally:
    conn.commit()  # 要调用commit才能将数据提交到数据库
    conn.close()
    print('done.')

【案例3】Pymysql查询数据

# -*- coding: utf-8 -*-
import pymysql

conn = pymysql.Connect(
    host='localhost',
    user='root',
    passwd='root',
    db='sanwen',
    port=3306,
    charset='utf8',
    cursorclass=pymysql.cursors.DictCursor  # 返回结果转成字典(dictionary)
)

try:
    with conn.cursor() as cursor:
        sql = "SELECT * FROM demo WHERE id = %s"  # 参数的占位符都用%s
        cursor.execute(sql, (1,))
        # result = cursor.fetchall()
        # result = cursor.fetchmany(2)  # 获取指定条数数据
        result = cursor.fetchone()  # 获取1条数数据
        print(result)
except pymysql.err.InternalError as err:
    print(err)
finally:
    # conn.commit()      # 如果不是修改数据,不用提交
    conn.close()
    print('done.')

【案例4】Pymysql修改更新数据

# -*- coding: utf-8 -*-
import pymysql

conn = pymysql.Connect(
    host='localhost',
    user='root',
    passwd='root',
    db='sanwen',
    port=3306,
    charset='utf8',
    cursorclass=pymysql.cursors.DictCursor  # 返回结果转成字典(dictionary)
)

try:
    with conn.cursor() as cursor:
        sql = 'UPDATE demo SET title=%s WHERE id=%s' # 参数的占位符都用%s
        ret = cursor.execute(sql,('python真的好啊',1))
        print(ret)
except pymysql.err.InternalError as err:
        print(err)
finally:
    conn.commit()  # 只要有数据变更,那么就必须commit
    conn.close()
    print('done')

【案例5】Pymysql修改数据、自动提交(用到autocommit_mode)

# -*- coding: utf-8 -*-
import pymysql

conn = pymysql.Connect(
    host='localhost',
    user='root',
    passwd='root',
    db='sanwen',
    port=3306,
    charset='utf8',
    autocommit=True,  # 要在这里设置自动提交才生效
    cursorclass=pymysql.cursors.DictCursor  # 返回结果转成字典(dictionary)
)

try:
    with conn.cursor() as cursor:
        sql = 'UPDATE demo SET title=%s WHERE id=%s'  # 参数的占位符都用%s
        ret = cursor.execute(sql, ('pymysql真香', 2))
        print(ret)
except pymysql.err.InternalError as err:
    print(err)
finally:
    # conn.commit()  # 只要有数据变更,那么就必须commit
    conn.close()
    print('done')

发表评论