Python-PyMySQL模块

1. pymysql

1.1 pymysql的安装

在python3的环境中直接使用pip3命令即可：

pip install pymysql

pip3 install pymysql

安装完毕后，使用命令查看是否成功安装

pip3 list | grep PyMySQL

1.2 连接数据库

pymysql使用connect()函数连接数据库，常用参数有以下一些:

import pymysql

conn = pymysql.connect(
    host="localhost",	# 数据库连接地址
    port=3306,			# 端口号
    user='root',		# 数据库用户名
    password='xxxx',	# 用户密码
    database='appgate'	# 要连接的数据库名称
    charset="utf8",		# 要连接的数据库的字符编码
    connec_time=10		# 连接数据库的超时时间，默认为10
)

print(conn) # 此时db为pymysql返回的连接对象

1.3 创建游标对象

在连接数据库后，需要创建游标对象。因为模块要通过游标对象来执行sql语句以及查询结果。

cursor = conn.cursor() #创建游标对象

1.4 基本使用案例

增

import pymysql

conn = pymysql.connect(
    host="localhost",
    port=3306,
    user='root',
    password='000000',
    database='appgate'
)

# 创建游标对象
cursor = conn.cursor()

try:
    # 创建数据库
    cursor.execute("show databases")
    print('未创建数据库前', cursor.fetchall())

    db_name = "test"
    sql = 'create database if not exists %s' % (db_name)
    cursor.execute(sql)
    cursor.execute("show databases")
    print('创建数据库后', cursor.fetchall())
    sql = 'use %s' % (db_name)
    cursor.execute(sql)

    # 创建数据表
    cursor.execute("show tables")
    print('未创建数据表前', cursor.fetchall())

    table_name = "test"
    sql = 'create table if not exists %s(`id` varchar(20) primary key, `name` varchar(10) not null)' % (table_name)
    cursor.execute(sql)
    cursor.execute("show tables")
    print('创建数据表后', cursor.fetchall())

    # 插入一条数据
    sql = 'select * from %s' % (table_name)
    cursor.execute(sql)
    print('插入一条数据前', cursor.fetchall())

    id = '1'
    name = 'ljy'
    sql = 'insert into ' + table_name + '(`id`, `name`) values(%s, %s)'
    cursor.execute(sql, (id, name))
    conn.commit()

    sql = 'select * from %s' % (table_name)
    cursor.execute(sql)
    print('插入一条数据后', cursor.fetchall())

    # 插入多条数据
    sql = 'select * from %s' % (table_name)
    cursor.execute(sql)
    print('插入数据前', cursor.fetchall())

    datas = [('2', 'sye'), ('3', 'obj')]
    sql = 'insert into ' + table_name + '(`id`, `name`) values(%s, %s)'
    cursor.executemany(sql, datas)

    sql = 'select * from %s' % (table_name)
    cursor.execute(sql)
    print('插入数据后', cursor.fetchall())

except Exception as e:
    print(e)

finally:
    # 关闭游标对象
    cursor.close()
    # 关闭数据库连接
    conn.close()

删

import pymysql

conn = pymysql.connect(
    host="localhost",
    port=3306,
    user='root',
    password='000000',
    database='appgate'
)

# 创建游标对象
cursor = conn.cursor()
database_name = 'test'
tab_name = 'test'


try:
    sql = 'drop database %s'
    cursor.execute(sql, database_name)
    conn.commit()
    print('删除指定数据库', cursor.fetchall())

    sql = 'drop table %s'
    cursor.execute(sql, tab_name)
    conn.commit()
    print('删除指定表', cursor.fetchall())

    condition = 'ljy'
    sql = 'delete from ' + tab_name + 'where name = %s'
    cursor.execute(sql, condition)
    conn.commit()
    print('根据指定条件删除一行记录', cursor.fetchall())

except Exception as e:
    print(e)

finally:
    # 关闭游标对象
    cursor.close()
    # 关闭数据库连接
    conn.close()

改

import pymysql

conn = pymysql.connect(
    host="localhost",
    port=3306,
    user='root',
    password='000000',
    database='test'
)

# 创建游标对象
cursor = conn.cursor()
name = 'hjy'
id = '1'

try:

    # 'update test set name = name where id = id'
    # sql = 'update test set name = %s where id = %s' % ('name', 'id')
    # 'update test set name = hjy where id = 1'
    # (1054, "Unknown column 'hjy' in 'field list'")
    sql = 'update test set name = %s where id = %s'
    cursor.execute('update test set name = %s where id = %s', (name, id))
    cursor.execute('update test set name = %s where id = %s' % ('name', 'id'))
    conn.commit()

    datas = []
    datas.append(('dla', '2'))
    datas.append(('oal', '3'))

    sql = 'update test set name = %s where id = %s'
    cursor.executemany(sql, datas)
    conn.commit()

except Exception as e:
    print(e)

finally:
    # 关闭游标对象
    cursor.close()
    # 关闭数据库连接
    conn.close()

查

import pymysql

conn = pymysql.connect(
    host="localhost",
    port=3306,
    user='root',
    password='000000',
    database='appgate'
)

# 创建游标对象
cursor = conn.cursor()

try:
    sql = 'select * from %s' % (table_name)
    cursor.execute(sql)
    print('插入数据后', cursor.fetchall())

except Exception as e:
    print(e)

finally:
    # 关闭游标对象
    cursor.close()
    # 关闭数据库连接
    conn.close()

1.5 注意事项

1. 整体过程
   1. 连接数据库 -> 创建游标对象 -> 编写sql语句 -> 执行sql语句 -> 获取结果 -> 关闭数据库连接
2. 在pymysql中，查询时不需要二次确认，但是设计增删改操作，我们需要手动commit
3. execute返回值是执行SQL语句之后影响的行数
4. fetchone() fetchall() fetchmany()返回的都是元组类型，只不过出fetchone()外其余两个方法返回的是嵌套元组